Re: Dynamically created cursors vanish in PLPgSQL

Hello

2008/9/26 Reg Me Please <regmeplease(at)gmail(dot)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(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
>
>
>