Обсуждение: Dynamically created cursors vanish in PLPgSQL

Поиск

Список

Период

Сортировка

Dynamically created cursors vanish in PLPgSQL

От

Reg Me Please

Дата:

25 сентября 2008 г., 17:30:30

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.

Re: Dynamically created cursors vanish in PLPgSQL

От

"Pavel Stehule"

Дата:

25 сентября 2008 г., 17:58:14

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
>

Re: Dynamically created cursors vanish in PLPgSQL

От

Reg Me Please

Дата:

26 сентября 2008 г., 08:31:15

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

Re: Dynamically created cursors vanish in PLPgSQL

От

"Pavel Stehule"

Дата:

26 сентября 2008 г., 12:11:29

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

Вход в личный кабинет

Восстановление пароля

Подтверждение аккаунта

Изменение пароля

Обсуждение: Dynamically created cursors vanish in PLPgSQL

Dynamically created cursors vanish in PLPgSQL

Re: Dynamically created cursors vanish in PLPgSQL

Re: Dynamically created cursors vanish in PLPgSQL

Re: Dynamically created cursors vanish in PLPgSQL