How to call a function that returns a refcursor ?

From: Postgres User <postgres(dot)developer(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: How to call a function that returns a refcursor ?
Date: 2009-12-19 18:37:22
Message-ID: b88c3460912191037j34f4cccbr5ef6c101813a6ebe@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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;

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Merlin Moncure 2009-12-19 18:41:55 Re: Selecting from table into an array var
Previous Message Postgres User 2009-12-19 18:30:12 Re: Selecting from table into an array var