How to call a function that returns a refcursor ?

Поиск
Список
Период
Сортировка
От Postgres User
Тема How to call a function that returns a refcursor ?
Дата
Msg-id b88c3460912191037j34f4cccbr5ef6c101813a6ebe@mail.gmail.com
обсуждение исходный текст
Ответы Re: How to call a function that returns a refcursor ?  (Pavel Stehule <pavel.stehule@gmail.com>)
Список pgsql-general
Hi,

I have a function that returns a refcursor that I need to call from a
second function.  In the second function, I'd like to read a column
value from each row.  However, I'm having a problem accessing the rows
of the refcursor.
Can anyone point me to a working example of how to pull this off?

This is the latest iteration of the function code that I've tried to
run without any success:

CREATE OR REPLACE FUNCTION "return_cursor" (
)
RETURNS SETOF "pg_catalog"."refcursor" AS
$body$
DECLARE
  rf refcursor;
BEGIN
  OPEN rf FOR
    SELECT * FROM category;
  RETURN Next rf;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE;

CREATE OR REPLACE FUNCTION "test"."read_cursor" (
)
RETURNS integer [] AS
$body$
DECLARE
  r record;
  cat_list integer[];
BEGIN
  FOR r IN SELECT * FROM test.return_cursor() LOOP
    cat_list = cat_list || r.category_id;
  END LOOP;
  Return cat_list;
END;
$body$
LANGUAGE 'plpgsql';

with this table struct:

CREATE TABLE "category" (
 "category_id" SERIAL,
 "parent_id" INTEGER,
 "category_name" VARCHAR(50)
) WITHOUT OIDS;

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

Предыдущее
От: Postgres User
Дата:
Сообщение: Re: Selecting from table into an array var
Следующее
От: Merlin Moncure
Дата:
Сообщение: Re: Selecting from table into an array var