Обсуждение: How to call a function that returns a refcursor ?
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;
2009/12/19 Postgres User <postgres.developer@gmail.com>: > 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 t You cannot use SELECT FROM cursor. Look on FETCH statement. If you need iterate over cursor in other function, then you have to use cycle over cursor - some like FETCH FROM refcursor_variable INTO recvariable; LOOP EXIT WHEN NOT FOUND; do some with values FETCH FROM refcursor_variable INTO recvariable; END LOOP; see http://www.postgresql.org/docs/8.3/static/plpgsql-cursors.html Regards Pavel Stehule > > 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; > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >