Re: function returning result set of varying column - Mailing list pgsql-sql

Thanks for all your replies.<br /><br />Actually I don't know the number of columns that I am going to return.<br /><br
/>Ihave 2 tables. For a single entry E1  in one table(t1), I have to fetch all the matching entries for E1 from the
othertable(t2),  K1,..Kn.<br /> and finally the function should return E1, K1..Kn. So I don't know the number of
columnsthat I am going to get. <br /><br />Is it possible to write a function that returns this kind of result? <br
/><br/>Please help.<br /><br /> Thanks,<br />maria<br /><br /><div class="gmail_quote">On Tue, Jun 3, 2008 at 9:28 AM,
IvanSergio Borgonovo <<a href="mailto:mail@webthatworks.it">mail@webthatworks.it</a>> wrote:<br /><blockquote
class="gmail_quote"style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left:
1ex;"><divclass="Ih2E3d">On Tue, 3 Jun 2008 09:01:02 -0400<br /> "maria s" <<a
href="mailto:psmg01@gmail.com">psmg01@gmail.com</a>>wrote:<br /><br /> > Hi Friends,<br /> > Thanks for all
yourfor the reply.<br /> ><br /> > I tried the function and when I execute it using<br /> > select * from
myfunction()<br/> > it says<br /> > ERROR:  a column definition list is required for functions<br /> >
returning"record"<br /> ><br /> > Could you please help me to fix this error?<br /> ><br /> > Thanks so
muchfor your help.<br /><br /></div>you can specify the returned types in each statement that call your<br /> function
oryou can specify the returned type in the function itself.<br /><br /> CREATE OR REPLACE FUNCTION myfunction(out col1
int,out col2<br /> varchar(32), out ...)<br /><div class="Ih2E3d">RETURNS<br /> SETOF<br /> RECORD<br /> AS<br />
$body$<br/> DECLARE<br />  rec record;<br /> BEGIN<br />  FOR rec IN (<br />    SELECT * FROM sometable)<br />  LOOP<br
/></div>   col1:=rec.col1;<br />    col2:=rec.col2;<br /> --    col3:=...;<br />    RETURN NEXT;<br /><div
class="Ih2E3d"> END LOOP;<br />  RETURN;<br /> END;<br /> $body$<br /><br /> > > CREATE OR REPLACE FUNCTION
myfunction()RETURNS SETOF RECORD AS<br /> > > $body$<br /> > > DECLARE<br /> > >   rec record;<br />
>> BEGIN<br /> > >   FOR rec IN (<br /> > >     SELECT * FROM sometable)<br /> > >   LOOP<br />
>>     RETURN NEXT rec;<br /> > >   END LOOP;<br /> > >   RETURN;<br /> > > END;<br /> >
>$body$<br /><br /></div>--<br /> Ivan Sergio Borgonovo<br /><a href="http://www.webthatworks.it"
target="_blank">http://www.webthatworks.it</a><br/><font color="#888888"><br /><br /> --<br /> Sent via pgsql-sql
mailinglist (<a href="mailto:pgsql-sql@postgresql.org">pgsql-sql@postgresql.org</a>)<br /> To make changes to your
subscription:<br/><a href="http://www.postgresql.org/mailpref/pgsql-sql"
target="_blank">http://www.postgresql.org/mailpref/pgsql-sql</a><br/></font></blockquote></div><br />