pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedpl/pgsql how to return multiple values from a function
6+ messages / 3 participants
[nested] [flat]
* pl/pgsql how to return multiple values from a function
@ 2003-03-09 03:33 jack <datactrl@tpg.com.au>
2003-03-09 08:51 ` Re: pl/pgsql how to return multiple values from a function Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 1 reply; 6+ messages in thread
From: jack @ 2003-03-09 03:33 UTC (permalink / raw)
To: pgsql-sql
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'
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';
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: pl/pgsql how to return multiple values from a function
2003-03-09 03:33 pl/pgsql how to return multiple values from a function jack <datactrl@tpg.com.au>
@ 2003-03-09 08:51 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2003-03-10 00:47 ` Re: pl/pgsql how to return multiple values from a function jack <datactrl@tpg.com.au>
0 siblings, 1 reply; 6+ messages in thread
From: Stephan Szabo @ 2003-03-09 08:51 UTC (permalink / raw)
To: jack <datactrl@tpg.com.au>; +Cc: pgsql-sql
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)
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: pl/pgsql how to return multiple values from a function
2003-03-09 03:33 pl/pgsql how to return multiple values from a function jack <datactrl@tpg.com.au>
2003-03-09 08:51 ` Re: pl/pgsql how to return multiple values from a function Stephan Szabo <sszabo@megazone23.bigpanda.com>
@ 2003-03-10 00:47 ` jack <datactrl@tpg.com.au>
0 siblings, 0 replies; 6+ messages in thread
From: jack @ 2003-03-10 00:47 UTC (permalink / raw)
To: pgsql-sql; Stephan Szabo <sszabo@megazone23.bigpanda.com>
Stephan ,
Both of two suggestion work. Thank you very much!
Jack
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: pl/pgsql how to return multiple values from a function
@ 2003-03-09 05:42 jack <datactrl@tpg.com.au>
2003-03-09 05:59 ` Re: pl/pgsql how to return multiple values from a function Rajesh Kumar Mallah <mallah@trade-india.com>
0 siblings, 1 reply; 6+ messages in thread
From: jack @ 2003-03-09 05:42 UTC (permalink / raw)
To: pgsql-sql
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,
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: pl/pgsql how to return multiple values from a function
2003-03-09 05:42 Re: pl/pgsql how to return multiple values from a function jack <datactrl@tpg.com.au>
@ 2003-03-09 05:59 ` Rajesh Kumar Mallah <mallah@trade-india.com>
2003-03-10 00:50 ` Re: pl/pgsql how to return multiple values from a function jack <datactrl@tpg.com.au>
0 siblings, 1 reply; 6+ messages in thread
From: Rajesh Kumar Mallah @ 2003-03-09 05:59 UTC (permalink / raw)
To: jack <datactrl@tpg.com.au>; pgsql-sql
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: pl/pgsql how to return multiple values from a function
2003-03-09 05:42 Re: pl/pgsql how to return multiple values from a function jack <datactrl@tpg.com.au>
2003-03-09 05:59 ` Re: pl/pgsql how to return multiple values from a function Rajesh Kumar Mallah <mallah@trade-india.com>
@ 2003-03-10 00:50 ` jack <datactrl@tpg.com.au>
0 siblings, 0 replies; 6+ messages in thread
From: jack @ 2003-03-10 00:50 UTC (permalink / raw)
To: pgsql-sql; Rajesh Kumar Mallah <mallah@trade-india.com>
Thanks Rajesh. It 's very useful reference site.
Jack
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2003-03-10 00:50 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-03-09 03:33 pl/pgsql how to return multiple values from a function jack <datactrl@tpg.com.au>
2003-03-09 08:51 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2003-03-10 00:47 ` jack <datactrl@tpg.com.au>
2003-03-09 05:42 Re: pl/pgsql how to return multiple values from a function jack <datactrl@tpg.com.au>
2003-03-09 05:59 ` Rajesh Kumar Mallah <mallah@trade-india.com>
2003-03-10 00:50 ` jack <datactrl@tpg.com.au>
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