agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedHow to manually load RETURNS SETOF RECORD?
4+ messages / 3 participants
[nested] [flat]
* How to manually load RETURNS SETOF RECORD?
@ 2015-12-08 19:34 Michael Moore <michaeljmoore@gmail.com>
0 siblings, 2 replies; 4+ messages in thread
From: Michael Moore @ 2015-12-08 19:34 UTC (permalink / raw)
To: pgsql-sql
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*
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How to manually load RETURNS SETOF RECORD?
@ 2015-12-08 19:49 Tom Lane <tgl@sss.pgh.pa.us>
parent: Michael Moore <michaeljmoore@gmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: Tom Lane @ 2015-12-08 19:49 UTC (permalink / raw)
To: Michael Moore <michaeljmoore@gmail.com>; +Cc: pgsql-sql
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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How to manually load RETURNS SETOF RECORD?
@ 2015-12-08 20:14 Adrian Klaver <adrian.klaver@aklaver.com>
parent: Michael Moore <michaeljmoore@gmail.com>
1 sibling, 1 reply; 4+ messages in thread
From: Adrian Klaver @ 2015-12-08 20:14 UTC (permalink / raw)
To: Michael Moore <michaeljmoore@gmail.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How to manually load RETURNS SETOF RECORD?
@ 2015-12-08 20:42 Michael Moore <michaeljmoore@gmail.com>
parent: Adrian Klaver <adrian.klaver@aklaver.com>
0 siblings, 0 replies; 4+ messages in thread
From: Michael Moore @ 2015-12-08 20:42 UTC (permalink / raw)
To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: pgsql-sql
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
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2015-12-08 20:42 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2015-12-08 19:34 How to manually load RETURNS SETOF RECORD? Michael Moore <michaeljmoore@gmail.com>
2015-12-08 19:49 ` Tom Lane <tgl@sss.pgh.pa.us>
2015-12-08 20:14 ` Adrian Klaver <adrian.klaver@aklaver.com>
2015-12-08 20:42 ` Michael Moore <michaeljmoore@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox