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