X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.192]) by svr1.postgresql.org (Postfix) with ESMTP id 7893ECCB4A for ; Thu, 17 Jul 2003 22:45:36 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr4.postgresql.org [64.117.224.192]) (amavisd-new, port 10024) with ESMTP id 19459-01 for ; Thu, 17 Jul 2003 22:46:03 +0000 (GMT) Received: from linux.grifflink.com (linux.royaleng.com [216.83.131.67]) by svr1.postgresql.org (Postfix) with ESMTP id 6060ACCB49 for ; Thu, 17 Jul 2003 19:45:34 -0300 (ADT) Received: from BigOne (user-52.grifflink3.fiber.net [216.83.147.52]) by linux.grifflink.com (8.12.8/8.12.8) with SMTP id h6HMhRS9024075 for ; Thu, 17 Jul 2003 16:43:27 -0600 Message-ID: <00b301c34cb5$346b1390$0100a8c0@BigOne> From: "Derrick Betts" To: Subject: Return a set of values from postgres Function Date: Thu, 17 Jul 2003 16:30:10 -0600 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_00B0_01C34C80.B0B9D740" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1158 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=1.2 tagged_above=0.0 required=5.0 tests=HTML_40_50, HTML_FONT_COLOR_BLUE, HTML_FONT_COLOR_GREEN, HTML_FONT_COLOR_RED, HTML_FONT_COLOR_UNSAFE X-Spam-Level: * X-Archive-Number: 200307/282 X-Sequence-Number: 14396 This is a multi-part message in MIME format. ------=_NextPart_000_00B0_01C34C80.B0B9D740 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable 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 migh= t 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=20 vResult RECORD; begin select into vResult CAST(monthly AS varchar) as w2client from income where caseid =3D $1 and incometype =3D ''W2'' and who =3D ''Client''; select into vResult CAST(monthly AS varchar) as selfemployedclient from income where caseid =3D $1 and incometype =3D ''Self Employed'' and who =3D ''Client''; return ; end; ' language 'plpgsql'; Any ideas? Thanks, Derrick ------=_NextPart_000_00B0_01C34C80.B0B9D740 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
I want to get a set of values returned fro= m a=20 function.  The values (there will need to be four of them) c= ome=20 from 4 separate SELECT statements inside the Function.  For example SE= LECT=20 one INTO variable1 from table1 where ...    then SELECT two = INTO=20 variable2 from table1 where...   At first you might think I could= just=20 combine the select statements into one statement, but I can't.  I need= to=20 pass the results of each separate select statement into distinct variables = and=20 return them together to the application.
 
This is what I'm using but isn't=20 working....
 

create function allincomes(int4) RETURNS setof RECORD AS '

declare

vResult RECORD;

begin

select into vResult CAST(monthly AS=20 varchar) as w2client

from income

where caseid =3D $1

and incometype =3D=20 ''W2''

and who =3D ''Client'';<= /FONT>

select into vResult CAST(monthly AS=20 varchar) as selfemployedclient

from income

where caseid =3D $1

and incometype =3D ''Sel= f=20 Employed''

and who =3D ''Client'';<= /FONT>

return ;

end;

'

language=20 'plpgsql';

 
Any ideas?
 
Thanks,
Derrick
------=_NextPart_000_00B0_01C34C80.B0B9D740--