From: Stephan Szabo <sszabo@megazone23.bigpanda.com>
To: jack <datactrl@tpg.com.au>
Cc: pgsql-sql@postgresql.org
Subject: Re: pl/pgsql how to return multiple values from a function
Date: Sun, 9 Mar 2003 00:51:00 -0800 (PST)
Message-ID: <20030309004652.X14723-100000@megazone23.bigpanda.com> (raw)
In-Reply-To: <000901c2e5ec$b1411430$1400a8c0@jac>
On Sun, 9 Mar 2003, jack wrote:
> Hi, all
>
> I use "RETURNS RECORD" to return a multiple values from a function as
> following but it doesn't work at all. Or is there any other way to do it?
> Thank you jin advance!
It's easier if you define a composite type to hold the return type,
something like:
CREATE TYPE tesp_return1 AS (jj int, ss varchar(10));
Then have tesp_returnRec1() return a tesp_return1 and tesp_returnrec2
changes a little bit as well, rec becomes of type tesp_return1%ROWTYPE
and you want to select into using SELECT INTO rec * from tesP_returnRec1()
I believe.
If you do it all with RECORD, you don't need to define the type, change
the return type or type of rec, but you need to put the type information
on the select into, something like:
SELECT INTO rec * from tesP_returnRec1() as foo(jj int, ss varchar(10));
> CREATE OR REPLACE FUNCTION tesP_returnRec2( ) RETURNS INTEGER AS'
> DECLARE
> rec RECORD;
> BEGIN
> SELECT INTO rec tesP_returnRec1();
> RAISE NOTICE ''jj=%, ss=%'', rec.jj,rec.ss;
> RETURN 0;
> END;'
> LANGUAGE 'PLPGSQL';
> ;-------
> CREATE OR REPLACE FUNCTION tesP_returnRec1( ) RETURNS RECORD AS'
> DECLARE
> rec RECORD;
> BEGIN
> SELECT INTO rec
> CAST(100 AS INTEGER) AS jj,
> CAST(''ABC'' AS VARCHAR(10)) AS ss;
> RETURN rec;
> END;'
> LANGUAGE 'PLPGSQL';
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
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: sszabo@megazone23.bigpanda.com, datactrl@tpg.com.au
Subject: Re: pl/pgsql how to return multiple values from a function
In-Reply-To: <20030309004652.X14723-100000@megazone23.bigpanda.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox