typo I spelled RETURN wrong - but evidentally I think I got that from you :)
________________________________ From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Obe, Regina Sent: Monday, June 11, 2007 8:42 AM To: PostGIS Users Discussion Subject: RE: [postgis-users] (no subject) 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. ----------------------------------------- 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
