Обсуждение: PGPLSql Select Into problem.
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
|
Вложения
am Fri, dem 08.06.2007, um 9:46:14 -0700 mailte Gary Townsend folgendes: > CREATE OR REPLACE FUNCTION vts_insert_stop(text, text, numeric, numeric, > numeric) > > RETURNS numeric AS > > ' > > BEGIN > > SELECT INTO transCoord X(SubSel.transformed_geom), Y > (SubSel.transformed_geom) FROM ( > > SELECT SetSRID( > > Transform( > > GeomFromText( > > ''POINT('' || stopeasting || '' '' stopnorthing || '')'', > projection If you want to call dynamicaly created sql-statements you need to use EXECUTE. http://www.postgresql.org/docs/current/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
On Sat, Jun 09, 2007 at 10:48:49AM +0200, A. Kretschmer wrote: > am Fri, dem 08.06.2007, um 9:46:14 -0700 mailte Gary Townsend folgendes: > > GeomFromText( > > ''POINT('' || stopeasting || '' '' stopnorthing || '')'', > > projection > > If you want to call dynamicaly created sql-statements you need to use > EXECUTE. > http://www.postgresql.org/docs/current/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN This isn't a dynamic SQL statement. POINT isn't a function; it's part of the text representation of a geometry object that PostGIS uses. The error here is a missing || operator before stopnorthing. The string concatenation could also be replaced with a call to makepoint(). -- Michael Fuhr
am Sat, dem 09.06.2007, um 6:27:19 -0600 mailte Michael Fuhr folgendes: > On Sat, Jun 09, 2007 at 10:48:49AM +0200, A. Kretschmer wrote: > > am Fri, dem 08.06.2007, um 9:46:14 -0700 mailte Gary Townsend folgendes: > > > GeomFromText( > > > ''POINT('' || stopeasting || '' '' stopnorthing || '')'', > > > projection > > > > If you want to call dynamicaly created sql-statements you need to use > > EXECUTE. > > http://www.postgresql.org/docs/current/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN > > This isn't a dynamic SQL statement. POINT isn't a function; it's Oh, thanks for the clarification, my fault. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
Ahh yes it was the concatenation, sheesh talk about missin the obvious. Ahh well there was also a problem in that the function declaration had projection declared as numeric when it had to be declared as integer or cast as integer later on. So in the end I ended up with this. Thanks to all who helped. CREATE OR REPLACE FUNCTION vts_insert_stop(text, text, numeric, numeric, integer) RETURNS void 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); RETURN void; END' LANGUAGE 'plpgsql' VOLATILE; -----Original Message----- From: pgsql-novice-owner@postgresql.org [mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Michael Fuhr Sent: June 9, 2007 5:27 AM To: A. Kretschmer Cc: pgsql-novice@postgresql.org Subject: Re: [NOVICE] PGPLSql Select Into problem. On Sat, Jun 09, 2007 at 10:48:49AM +0200, A. Kretschmer wrote: > am Fri, dem 08.06.2007, um 9:46:14 -0700 mailte Gary Townsend folgendes: > > GeomFromText( > > ''POINT('' || stopeasting || '' '' stopnorthing || '')'', > > projection > > If you want to call dynamicaly created sql-statements you need to use > EXECUTE. > http://www.postgresql.org/docs/current/interactive/plpgsql-statements.html#P LPGSQL-STATEMENTS-EXECUTING-DYN This isn't a dynamic SQL statement. POINT isn't a function; it's part of the text representation of a geometry object that PostGIS uses. The error here is a missing || operator before stopnorthing. The string concatenation could also be replaced with a call to makepoint(). -- Michael Fuhr ---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo@postgresql.org so that your message can get through to the mailing list cleanly