Dynamically created cursors vanish in PLPgSQL

From: Reg Me Please <regmeplease(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Dynamically created cursors vanish in PLPgSQL
Date: 2008-09-25 17:30:27
Message-ID: 200809251930.27893.regmeplease@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Dmitry Koterov 2008-09-25 17:42:45 Indirect access to NEW or OLD records
Previous Message Christopher Browne 2008-09-25 17:05:24 Re: [Slony1-general] Re: Stripping out slony after / before / during pg_restore?