2009/12/19 Postgres User <postgres.developer@xxxxxxxxx>: > 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@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
