[GENERAL] Dynamically created cursors vanish in PLPgSQL

Hi all.

I'm running PGSQL v.8.3.3

I tried to adapt the examples from the friendly manual (38.7.3.5) in order to
to have a function to create cursors based on a parametric query string:

CREATE SEQUENCE s_cursors;

CREATE OR REPLACE FUNCTION f_cursor( query text, out curs refcursor )
LANGUAGE PLPGSQL STRICT
AS $BODY$
DECLARE
c refcursor;
BEGIN
c := 'cursor_'||nextval( 's_cursors' );
OPEN c SCROLL FOR EXECUTE query;
curs := c;
END;
$BODY$;

SELECT f_cursor( 'SELECT * FROM pg_tables' );

curs
-----------
cursor_1
(1 row)

FETCH 10 FROM cursor_1;

ERROR: cursor "cursor_1" does not exist

SELECT * from pg_cursors ;
name | statement | is_holdable | is_binary | is_scrollable | creation_time
------+-----------+-------------+-----------+---------------+---------------
(0 rows)

The cursor is (should have been) created as there's no error but it seems it
vanishes as soon as the creating function returns.
As if it was created "WITHOUT HOLD", which doesn't make much of sense in a
function returning a refcursor, this is why there is (seems to be) no "HOLD"
part in the cursor creation in PLPgSQL.

I think more likely I am making some mistake. But have n ìo idea where.

Any hint?

Thanks in advance

RMP.

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