RETURN NEXT ROW sounds like the ticket. I am trying to duplicate some functionality that Oracle has in PL/SQL.
specifically
PROCEDURE dGetQuestionSetKeyOrder
(pweb_site_name_i IN tx_web_site.web_site_name%TYPE,
pqs_table_i IN OUT NOCOPY XPORTAL_QUESTION_SET_TABLE
)
where xportal_question_set_table is an array of OBJECTS. Really it's an array of composite types, but Oracle uses and Object to construct a composite type.
CREATE OR REPLACE FUNCTION PXPORTAL_COMMON_helper.fn_plpgsqltestmulti( param_subject varchar, OUT test_id integer, OUT test_stuff text) RETURNS SETOF record AS $$ BEGIN _record.test_id[0] := 100; _record.test_id[1] := 555; _record.test_stuff[0] := 'cat'; _record.test_stuff[1] := 'cow'; END; $$ LANGUAGE 'plpgsql' VOLATILE;
*select test_id from PXPORTAL_COMMON_helper.fn_plpgsqltestmulti('123');* ERROR: subscripted object is not an array CONTEXT: PL/pgSQL function pxportal_common_helper.fn_plpgsqltestmulti(character varying) line 3 at assignment ********** Error **********
ERROR: subscripted object is not an array SQL state: 42804 Context: PL/pgSQL function pxportal_common_helper.fn_plpgsqltestmulti(character varying) line 3 at assignment
*/What is the correct way to accomplish this?/*
What is it that you are trying to accomplish?
Assuming it is to return a set of rows, would something like the below work:
CREATE OR REPLACE FUNCTION fn_plpgsqltestmulti( param_subject varchar, OUT test_id integer, OUT test_stuff text) RETURNS SETOF record AS $$ BEGIN FOR i IN 1..10 LOOP test_id = i; test_stuff = i::text || '_stuff'; RETURN NEXT; END LOOP; END; $$ LANGUAGE 'plpgsql' VOLATILE;
