Re: PGPLSql Select Into problem.

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(at)postgresql(dot)org
[mailto:pgsql-novice-owner(at)postgresql(dot)org] On Behalf Of Michael Fuhr
Sent: June 9, 2007 5:27 AM
To: A. Kretschmer
Cc: pgsql-novice(at)postgresql(dot)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(at)postgresql(dot)org so that your
message can get through to the mailing list cleanly