Dynamically created cursors vanish in PLPgSQL

Поиск
Список
Период
Сортировка
От Reg Me Please
Тема Dynamically created cursors vanish in PLPgSQL
Дата
Msg-id 200809251930.27893.regmeplease@gmail.com
обсуждение исходный текст
Ответы Re: Dynamically created cursors vanish in PLPgSQL  ("Pavel Stehule" <pavel.stehule@gmail.com>)
Список pgsql-general
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.


В списке pgsql-general по дате отправления:

Предыдущее
От: Christopher Browne
Дата:
Сообщение: Re: [Slony1-general] Re: Stripping out slony after / before / during pg_restore?
Следующее
От: "Dmitry Koterov"
Дата:
Сообщение: Indirect access to NEW or OLD records