Re: Cursor names in a self-nested function - Mailing list pgsql-sql

Pavel Stehule <pavel.stehule@gmail.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
FETCHmycursor INTO newid;   EXIT WHEN newid IS NULL;   out := out || test (newid); END LOOP; RETURN out;
 
END;$BODY$
LANGUAGE 'plpgsql' VOLATILE;
        regards, tom lane