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;