Re: [SQL] Cursor names in a self-nested function

[Tom Lane]

Pavel Stehule <pavel.steh...@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
    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

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql