Postgres Pro
Downloads
Home   >   mailing lists

Thread: PGPLSql Select Into problem.

PGPLSql Select Into problem.

From
"Gary Townsend"
Date:

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

 

 

Attachment

Re: PGPLSql Select Into problem.

From
"A. Kretschmer"
Date:
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

Re: PGPLSql Select Into problem.

From
Michael Fuhr
Date:
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

Re: PGPLSql Select Into problem.

From
"A. Kretschmer"
Date:
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

Re: PGPLSql Select Into problem.

From
"Gary Townsend"
Date:
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