[postgis-users] PL/PgSQL and PostGIS

[Michael Fuhr]

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