[postgis-users] PL/PgSQL and PostGIS

[CYW]

Now trying the following script using Record:
_______________________________________________
CREATE OR REPLACE FUNCTION testxy(rtid integer, n integer) RETURNS text AS
$$
DECLARE
    rec RECORD;
    ptid integer; 
    ptstr text DEFAULT 'default';
    cur CURSOR(key integer) FOR
        SELECT gid as ptid, AsText(geom) as point -- column gid is of int4
type
        FROM point_table  WHERE guid=key ORDER BY measure ASC;
BEGIN
    OPEN cur(rtid);
    FETCH cur INTO rec ;
    CLOSE cur;
    RETURN ptstr;
END;
$$ LANGUAGE plpgsql;
_____________________________________________________
I got the following errors while loading it:

psql:testpl.sql:45: ERROR:  syntax error at or near "$1" at character 17
QUERY:   SELECT gid as  $1 , AsText(geom) as point FROM point_table WHERE
guid=$2  ORDER BY measure ASC
CONTEXT:  SQL statement in PL/PgSQL function "testxy" near line 18
psql:testpl.sql:45: LINE 1:  SELECT gid as  $1 , AsText(geom) as point FROM
point_table ... 
psql:testpl.sql:45:                         ^
______________________________________________________
The error message is not very informative to me. What am I doing wrong?

Thanks a bunch.
cyw

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Michael
Fuhr
Sent: Saturday, June 10, 2006 7:26 AM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] PL/PgSQL and PostGIS

On Sat, Jun 10, 2006 at 01:30:19AM -0500, CYW wrote:
> I am trying a PL/PgSQL script below, and getting the following error:
> 	invalid input syntax for integer: "Point(111.000 999.22)"
> 	Context: PL/pgSQL function "testxy"  line # 12 at fecth
> 
> Any suggestions?
> ====================================================================
> CREATE OR REPLACE FUNCTION testxy() RETURNS text AS $$
> DECLARE
>     thisrow point_table%ROWTYPE;
>     rid integer DEFAULT 1;
>     msg text;
>     cur CURSOR(key integer) FOR SELECT AsText(geom) as geom
>             FROM point_table WHERE guid=rid ORDER BY measure ASC;
> BEGIN
>     OPEN cur(rid);
>     FETCH cur INTO thisrow ;

You've declared thisrow to be point_table%ROWTYPE, which is presumably
something like (integer, ..., geometry).  The cursor's select list
has a single text column; FETCH INTO is trying to store that text
column in thisrow's first column (an integer) so you get a type
mismatch.  Declare thisrow to be of type record, or declare it to
be text and return thisrow instead of thisrow.geom.  Examples:

  DECLARE
      thisrow  record;

or

  DECLARE
      thisrow  text;
  ...
      RETURN thisrow;

-- 
Michael Fuhr
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users