Re: Dynamically created cursors vanish in PLPgSQL

Hello

try to look at http://okbob.blogspot.com/2008/08/using-cursors-for-generating-cross.html

regards
Pavel Stehule

p.s. you should to use transaction

2008/9/25 Reg Me Please <regmeplease(at)gmail(dot)com>:
> 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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>