Re: Dynamically created cursors vanish in PLPgSQL

Hello

2008/9/26 Reg Me Please <regmeplease@gmail.com>:
> Nice suggestion.
>
> In the meanwhile I've found a "workaround" that works for me (unless there's a
> hidden pitfall):
>
> CREATE OR REPLACE FUNCTION f_cursor2( query text, out curs refcursor )
> LANGUAGE PLPGSQL STRICT
> AS $BODY$
> DECLARE
>   c refcursor;
> BEGIN
>   c := 'cursor_'||nextval( 's_cursors' );
>  EXECUTE 'DECLARE '||curs||' SCROLL CURSOR WITH HOLD FOR '||query;
>   curs := c;
> END;
> $BODY$;
>
> SELECT f_cursor( 'SELECT * FROM pg_tables' );
>
>   curs
> -----------
>  cursor_2
> (1 row)
>
> FETCH 10 FROM cursor_2;
>
>     schemaname     |        tablename        | tableowner | tablespace |
> hasindexes | hasrules | hastriggers
> --------------------+-------------------------+------------+------------+------------+----------+-------------
>  information_schema | sql_features            | postgres   | [NULL]     |
> f          | f        | f
>  information_schema | sql_implementation_info | postgres   | [NULL]     |
> f          | f        | f
>  pg_catalog         | pg_statistic            | postgres   | [NULL]     |
> t          | f        | f
>  information_schema | sql_languages           | postgres   | [NULL]     |
> f          | f        | f
>  information_schema | sql_packages            | postgres   | [NULL]     |
> f          | f        | f
>  information_schema | sql_parts               | postgres   | [NULL]     |
> f          | f        | f
>  information_schema | sql_sizing              | postgres   | [NULL]     |
> f          | f        | f
>  pg_catalog         | pg_authid               | postgres   | pg_global  |
> t          | f        | t
>  information_schema | sql_sizing_profiles     | postgres   | [NULL]     |
> f          | f        | f
>  pg_catalog         | pg_database             | postgres   | pg_global  |
> t          | f        | t
> (10 rows)
>
> SELECT * from pg_cursors ;
>   name    |
> statement                               | is_holdable | is_binary |
> is_scrollable |         creation_time
>
-----------+-----------------------------------------------------------------------+-------------+-----------+---------------+-------------------------------
>  cursor_2 | DECLARE cursor_2 SCROLL CURSOR WITH HOLD FOR SELECT * FROM
> pg_tables | t           | f         | t             | 2008-09-26
> 10:05:38.963548+02
> (1 row)
>
> I would then say the PLPgSQL should also have the "WITH / WITHOUT HOLD"
> feature, otherwise a function that creates a cursor needs a transaction
> despite the cursor is read-only.
>
> In my very humble opinion.
>

ofcourse it's better, than some our hacks

regards
Pavel Stehule


> On Thursday 25 September 2008 19:58:07 Pavel Stehule wrote:
>> 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@gmail.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@postgresql.org)
>> > To make changes to your subscription:
>> > http://www.postgresql.org/mailpref/pgsql-general
>
>
>