Re: pl/pgsql how to return multiple values from a function

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(at)postgresql(dot)org)
>