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.
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? |