Subject: Re: How to manually load RETURNS SETOF RECORD?
To: Michael Moore <michaeljmoore@gmail.com>,
 postgres list <pgsql-sql@postgresql.org>
References: 
 <CACpWLjNhf9+9qZhVBBc+Wy9cdo3hXKHSEYsZweoj9nitMJM8JA@mail.gmail.com>
From: Adrian Klaver <adrian.klaver@aklaver.com>
Message-ID: <56673A1E.5050602@aklaver.com>
Date: Tue, 8 Dec 2015 12:14:22 -0800
User-Agent: Mozilla/5.0 (X11; Linux i686; rv:38.0) Gecko/20100101
 Thunderbird/38.4.0
MIME-Version: 1.0
In-Reply-To: 
 <CACpWLjNhf9+9qZhVBBc+Wy9cdo3hXKHSEYsZweoj9nitMJM8JA@mail.gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

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


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql