Postgres Pro
Downloads
Home   >   mailing lists

Thread: How to manually load RETURNS SETOF RECORD?

How to manually load RETURNS SETOF RECORD?

From
Michael Moore
Date:
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?
TIA, Mike

Re: How to manually load RETURNS SETOF RECORD?

From
Tom Lane
Date:
Michael Moore <michaeljmoore@gmail.com> writes:
> *What is the correct way to accomplish this?*

Something like RETURN NEXT ROW('foo', 200, ...);
Or you could assign the ROW construct to a variable of RECORD type.
        regards, tom lane

Re: How to manually load RETURNS SETOF RECORD?

From
Adrian Klaver
Date:
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

Re: How to manually load RETURNS SETOF RECORD?

From
Michael Moore
Date:
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. 

And later in the procedure body we see:
            pqs_table_i.EXTEND;
            pqs_table_i(pqs_table_i.LAST) :=
                    QUESTION_SET_KEY_ORDER_OBJECT(v_current_qs_key, v_qs_table_cnt + 1);

This is appending data to pqs_table_i.

So, in a nutshell,
1. Pass in an array of composite type
2. Append to said array
3. Return updated array

Regards,
Mike







On Tue, Dec 8, 2015 at 12:14 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
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