[postgis-users] PL/PgSQL and PostGIS
Michael Fuhr
mike at fuhr.org
Sat Jun 10 05:26:11 PDT 2006
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
More information about the postgis-users
mailing list