On 12/08/2015 11:34 AM, Michael Moore wrote:
> 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;
test=> select * from fn_plpgsqltestmulti('123'); test_id | test_stuff
---------+------------ 1 | 1_stuff 2 | 2_stuff 3 | 3_stuff 4 | 4_stuff 5 | 5_stuff
6| 6_stuff 7 | 7_stuff 8 | 8_stuff 9 | 9_stuff 10 | 10_stuff
(10 rows)
> */TIA, Mike/*
--
Adrian Klaver
adrian.klaver@aklaver.com
