Skip site navigation (1) Skip section navigation (2)

Re: PGPLSql Select Into problem.

From: "Gary Townsend" <garyt(at)spatialmapping(dot)com>
To: "'Michael Fuhr'" <mike(at)fuhr(dot)org>
Cc: <pgsql-novice(at)postgresql(dot)org>
Subject: Re: PGPLSql Select Into problem.
Date: 2007-06-11 15:41:55
Message-ID: 003f01c7ac3f$0a7e1260$8a00a8c0@spatialmapping.com (view raw or flat)
Thread:
Lists: pgsql-novice
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


In response to

pgsql-novice by date

Next:From: AndrewDate: 2007-06-12 14:55:00
Subject: Recovering Postgres databases
Previous:From: Tom LaneDate: 2007-06-11 14:35:05
Subject: Re: query plan and the use of indexes

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group