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.
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_longitude)
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 |