Re: Dynamically created cursors vanish in PLPgSQL

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.

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