[postgis-users] PL/PgSQL and PostGIS

[Michael Fuhr]

On Sat, Jun 10, 2006 at 05:00:26PM -0500, CYW wrote:
> 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;
[...]
> 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:                         ^

You declared a variable named ptid; PL/pgSQL is trying to use it
where you wrote "gid as ptid", which results in a syntax error.
Use variable names that differ from other identifiers you need to
reference.

-- 
Michael Fuhr