pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

Return a set of values from postgres Function
2+ messages / 2 participants
[nested] [flat]

* Return a set of values from postgres Function
@ 2003-07-17 22:30 Derrick Betts <Derrick@grifflink.com>
  2003-07-20 06:20 ` Re: Return a set of values from postgres Function Stephan Szabo <sszabo@megazone.bigpanda.com>
  0 siblings, 1 reply; 2+ messages in thread

From: Derrick Betts @ 2003-07-17 22:30 UTC (permalink / raw)
  To: pgsql-sql

I want to get a set of values returned from a function.  The values (there will need to be four of them) come from 4 separate SELECT statements inside the Function.  For example SELECT one INTO variable1 from table1 where ...    then SELECT two INTO variable2 from table1 where...   At first you might think I could just combine the select statements into one statement, but I can't.  I need to pass the results of each separate select statement into distinct variables and return them together to the application.

This is what I'm using but isn't working....

create function allincomes(int4) RETURNS setof RECORD AS '

declare 

vResult RECORD;

begin

select into vResult CAST(monthly AS varchar) as w2client

from income

where caseid = $1

and incometype = ''W2''

and who = ''Client'';

select into vResult CAST(monthly AS varchar) as selfemployedclient

from income

where caseid = $1

and incometype = ''Self Employed''

and who = ''Client'';

return ;

end;

'

language 'plpgsql';


Any ideas?

Thanks,
Derrick

^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Return a set of values from postgres Function
  2003-07-17 22:30 Return a set of values from postgres Function Derrick Betts <Derrick@grifflink.com>
@ 2003-07-20 06:20 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Stephan Szabo @ 2003-07-20 06:20 UTC (permalink / raw)
  To: Derrick Betts <Derrick@grifflink.com>; +Cc: pgsql-sql


On Thu, 17 Jul 2003, Derrick Betts wrote:

> I want to get a set of values returned from a function.  The values
> (there will need to be four of them) come from 4 separate SELECT
> statements inside the Function.  For example SELECT one INTO variable1
> from table1 where ...  then SELECT two INTO variable2 from table1
> where...  At first you might think I could just combine the select
> statements into one statement, but I can't.  I need to pass the
> results of each separate select statement into distinct variables and
> return them together to the application.

I'd suggest reading one of the items on the set returning functions:

http://www.varlena.com/GeneralBits/26.html
http://techdocs.postgresql.org/guides/SetReturningFunctions




^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2003-07-20 06:20 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-07-17 22:30 Return a set of values from postgres Function Derrick Betts <Derrick@grifflink.com>
2003-07-20 06:20 ` Stephan Szabo <sszabo@megazone.bigpanda.com>

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