agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Adrian Klaver <adrian.klaver@aklaver.com>
To: Michael Moore <michaeljmoore@gmail.com>
To: postgres list <pgsql-sql@postgresql.org>
Subject: Re: How to manually load RETURNS SETOF RECORD?
Date: Tue, 8 Dec 2015 12:14:22 -0800
Message-ID: <56673A1E.5050602@aklaver.com> (raw)
In-Reply-To: <CACpWLjNhf9+9qZhVBBc+Wy9cdo3hXKHSEYsZweoj9nitMJM8JA@mail.gmail.com>
References: <CACpWLjNhf9+9qZhVBBc+Wy9cdo3hXKHSEYsZweoj9nitMJM8JA@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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
view thread (4+ messages) latest in thread
Message-ID: <56673A1E.5050602@aklaver.com>
Permalink: ../56673A1E.5050602@aklaver.com/
Also on: postgresql.org/message-id/56673A1E.5050602@aklaver.com
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: adrian.klaver@aklaver.com, michaeljmoore@gmail.com
Subject: Re: How to manually load RETURNS SETOF RECORD?
In-Reply-To: <56673A1E.5050602@aklaver.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox