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(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