[postgis-users] (no subject)

[Obe, Regina]

Gary,
 
1) That's the old way of writing pgsql functions.  I forget the
transition period - I think it was around 8.0 - the new way uses $
dollar quoting and doesn't require aliasing in pgsql althought it still
does in sql procedural.
 
2) If you are using RECORD objects, I think you need to specify the
field datatypes before you can use it.  
3) That whole record thing seems like an unnecessary step anyway.
 
 
Try instead - hope I didn't make any mistakes since I didn't test it in
my typing (you might be able to skip the declare part since I've
eradicated all the variables)
 
CREATE OR REPLACE FUNCTION vts_insert_stop(stopnum text, stopdes text,
stopeasting numeric, stopnorthing numeric, projection numeric)

  RETURNS numeric AS

$BODY$ 

DECLARE

BEGIN

      

      INSERT INTO
vts_route_stops(stop_number,stop_description,stop_latitude,stop_longitud
e)

        SELECT stopnum, stopdes, Y(SubSel.transformed_geom),
X(SubSel.transformed_geom)

            FROM 

        (SELECT SetSRID(

          Transform(GeomFromText('POINT(' || stopeasting || ' '
stopnorthing || ')', projection), 4326),-1) AS transformed_geom) SubSel;

 

      RERTURN void;

 

END

$BODY$

  LANGUAGE 'plpgsql' VOLATILE;


________________________________

From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Gary
Townsend
Sent: Friday, June 08, 2007 5:17 PM
To: postgis-users at postgis.refractions.net
Subject: [postgis-users] (no subject)



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 I
thought maybe someone here may have a better soloution to what I'm
trying to do or can see what I'm doin wrong.

 

 

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_longitud
e)

      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

 

 



-----------------------------------------
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and delete the material from any computer.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20070611/473629c1/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image001.gif
Type: image/gif
Size: 2661 bytes
Desc: image001.gif
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20070611/473629c1/attachment.gif>