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

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!
;-----
CREATE OR REPLACE FUNCTION tesP_returnRec2( ) RETURNS INTEGER  AS'
DECLARErec RECORD;
BEGINSELECT 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'
DECLARErec RECORD;
BEGINSELECT INTO rec CAST(100 AS INTEGER) AS jj, CAST(''ABC'' AS VARCHAR(10)) AS ss;RETURN rec;
END;'LANGUAGE 'PLPGSQL';

I found following notes on news list with Google. And I have a try but it
still doesn't work. The problem is pl/pgsql complains that function of col1
is not found. My postgres is v7.3.2. Please help. Thank you in advance.

Jack
------
Sure you can, I wouldn't nessessarily recommend it. rec_tmp must be a
table somewhere so it can be a 'type', you get the values from the record
by using syntaxt like SELECT col1(teste_tmp(1)), col2(teste_tmp(1)),
col3(teste_tmp(1));

You could also return a CURSOR but I've never tried that (I access
PostgreSQL from perl's DBI which doesn't support a cursor return type).

Joshua b. Jore ; http://www.greentechnologist.org ; 10012 11010 11022
10202 1012 2122 11020 10202 10202 11002 1020 1012 11102 11102 11102 1201
11001 11002 10211 11020 10202 10202 11002 11021 1201 11010 11020 10211

On Tue, 28 May 2002, Vilson farias wrote:

> Greetings,
>
>   I would like to know if it's possible to return a record from a pl-pgsql
> function.
>
>   The procedure I did is bellow and of course doesn't work. Is there some
> possibility or work-around?
>
> CREATE FUNCTION teste_tmp(INT4)
>   RETURNS RECORD AS '
>   DECLARE
>     rec_tmp record;
>   BEGIN
>     SELECT
>       INTO rec_tmp
>       1 as col1, ''teste'' as col2, now() as col3 ;
>
>     RETURN rec_tmp;
>   END;
> '
> LANGUAGE 'plpgsql';
>
> Best regards,
>

http://techdocs.postgresql.org/guides/SetReturningFunctions



On Sunday 09 March 2003 11:12 am, jack wrote:
> I found following notes on news list with Google. And I have a try but it
> still doesn't work. The problem is pl/pgsql complains that function of col1
> is not found. My postgres is v7.3.2. Please help. Thank you in advance.
> 
> Jack
> ------
> Sure you can, I wouldn't nessessarily recommend it. rec_tmp must be a
> table somewhere so it can be a 'type', you get the values from the record
> by using syntaxt like SELECT col1(teste_tmp(1)), col2(teste_tmp(1)),
> col3(teste_tmp(1));
> 
> You could also return a CURSOR but I've never tried that (I access
> PostgreSQL from perl's DBI which doesn't support a cursor return type).
> 
> Joshua b. Jore ; http://www.greentechnologist.org ; 10012 11010 11022
> 10202 1012 2122 11020 10202 10202 11002 1020 1012 11102 11102 11102 1201
> 11001 11002 10211 11020 10202 10202 11002 11021 1201 11010 11020 10211
> 
> On Tue, 28 May 2002, Vilson farias wrote:
> 
> > Greetings,
> >
> >   I would like to know if it's possible to return a record from a pl-pgsql
> > function.
> >
> >   The procedure I did is bellow and of course doesn't work. Is there some
> > possibility or work-around?
> >
> > CREATE FUNCTION teste_tmp(INT4)
> >   RETURNS RECORD AS '
> >   DECLARE
> >     rec_tmp record;
> >   BEGIN
> >     SELECT
> >       INTO rec_tmp
> >       1 as col1, ''teste'' as col2, now() as col3 ;
> >
> >     RETURN rec_tmp;
> >   END;
> > '
> > LANGUAGE 'plpgsql';
> >
> > Best regards,
> >
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
> 
> 

-- 

Regds
Mallah

----------------------------------------
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)6152172 (221) (L) ,9811255597 (M)

Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.

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)
>

Stephan ,

Both of two suggestion work. Thank you very much!

Jack

Thanks Rajesh. It 's very useful reference site.

Jack