Gary, 1) That's the old way of writing pgsql functions. I forget the transition period - I think it was around 8.0 - the new way uses $ dollar quoting and doesn't require aliasing in pgsql althought it still does in sql procedural. 2) If you are using RECORD objects, I think you need to specify the field datatypes before you can use it. 3) That whole record thing seems like an unnecessary step anyway. Try instead - hope I didn't make any mistakes since I didn't test it in my typing (you might be able to skip the declare part since I've eradicated all the variables) CREATE OR REPLACE FUNCTION vts_insert_stop(stopnum text, stopdes text, stopeasting numeric, stopnorthing numeric, projection numeric)
RETURNS numeric AS
$BODY$
DECLARE
BEGIN
INSERT INTO
vts_route_stops(stop_number,stop_description,stop_latitude,stop_longitud
e)
SELECT stopnum, stopdes, Y(SubSel.transformed_geom),
X(SubSel.transformed_geom)
FROM
(SELECT SetSRID(
Transform(GeomFromText('POINT(' || stopeasting || ' '
stopnorthing || ')', projection), 4326),-1) AS transformed_geom) SubSel;
RERTURN void;
END
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
________________________________
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Gary
Townsend
Sent: Friday, June 08, 2007 5:17 PM
To: [email protected]
Subject: [postgis-users] (no subject)
Below is a function I am trying to create that will take a series of
geographic points form a UTM projection and translate it to lat/long
that's not the problem though the translation works wonderfully as a
separate select statement. I'm trying to encapsulate this into a
function now and I am getting a problem returned saying:
ERROR: syntax error at or near "$2"
SQL state: 42601
Character: 137
Context: PL/pgSQL function "vts_insert_stop" line 10 at select into
variables
I'm not sure where I've gone sideways on this but seeing as how I'm
still learning the pgplsql syntax I figured it could be anywhere I
thought maybe someone here may have a better soloution to what I'm
trying to do or can see what I'm doin wrong.
CREATE OR REPLACE FUNCTION vts_insert_stop(text, text, numeric, numeric,
numeric)
RETURNS numeric AS
'
DECLARE
stopnum ALIAS for $1;
stopdes ALIAS for $2;
stopeasting ALIAS for $3;
stopnorthing ALIAS for $4;
projection ALIAS for $5;
transCoord RECORD;
BEGIN
SELECT INTO transCoord X(SubSel.transformed_geom),
Y(SubSel.transformed_geom) FROM (
SELECT SetSRID(
Transform(
GeomFromText(
''POINT('' || stopeasting || '' '' stopnorthing || '')'',
projection
), 4326
),
-1) AS transformed_geom) SubSel;
INSERT INTO
vts_route_stops(stop_number,stop_description,stop_latitude,stop_longitud
e)
VALUES(stopnum,stopdes,transCoord.Y,transCoord.X);
RERTURN void;
END'
LANGUAGE 'plpgsql' VOLATILE;
Gary Townsend (Systems Programmer & Developer )
Spatial Mapping Ltd.
#200 484 2nd Ave. Prince George, B.C., Canada V2L 2Z7
Phone: 250 564 1928
Fax: 250 564 0751
-----------------------------------------
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and delete the material from any computer.<<image001.gif>>
_______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
