Re: Cursor names in a self-nested function

Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
> you can use a refcursor type

> http://developer.postgresql.org/pgdocs/postgres/plpgsql-cursors.html

It would suffice to explicitly set mycursor to null before doing the
OPEN, thus instructing the system to assign a unique cursor name.

CREATE FUNCTION test(id integer) RETURNS TEXT AS
$BODY$
DECLARE
mycursor CURSOR FOR SELECT * FROM int4_tbl WHERE f1 > id;
newid INTEGER;
out TEXT;
BEGIN
out := id::text || ' ';
mycursor := null;
OPEN mycursor;
raise notice 'mycursor = %', mycursor; -- debug
LOOP
FETCH mycursor INTO newid;
EXIT WHEN newid IS NULL;
out := out || test (newid);
END LOOP;
RETURN out;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

regards, tom lane