Thanks for all your replies. Actually I don't know the number of columns that I am going to return.
I have 2 tables. For a single entry E1 in one table(t1), I have to fetch all the matching entries for E1 from the other table(t2), K1,..Kn. and finally the function should return E1, K1..Kn. So I don't know the number of columns that I am going to get. Is it possible to write a function that returns this kind of result? Please help. Thanks, maria On Tue, Jun 3, 2008 at 9:28 AM, Ivan Sergio Borgonovo <[EMAIL PROTECTED]> wrote: > On Tue, 3 Jun 2008 09:01:02 -0400 > "maria s" <[EMAIL PROTECTED]> wrote: > > > Hi Friends, > > Thanks for all your for the reply. > > > > I tried the function and when I execute it using > > select * from myfunction() > > it says > > ERROR: a column definition list is required for functions > > returning "record" > > > > Could you please help me to fix this error? > > > > Thanks so much for your help. > > you can specify the returned types in each statement that call your > function or you can specify the returned type in the function itself. > > CREATE OR REPLACE FUNCTION myfunction(out col1 int, out col2 > varchar(32), out ...) > RETURNS > SETOF > RECORD > AS > $body$ > DECLARE > rec record; > BEGIN > FOR rec IN ( > SELECT * FROM sometable) > LOOP > col1:=rec.col1; > col2:=rec.col2; > -- col3:=...; > RETURN NEXT; > END LOOP; > RETURN; > END; > $body$ > > > > CREATE OR REPLACE FUNCTION myfunction() RETURNS SETOF RECORD AS > > > $body$ > > > DECLARE > > > rec record; > > > BEGIN > > > FOR rec IN ( > > > SELECT * FROM sometable) > > > LOOP > > > RETURN NEXT rec; > > > END LOOP; > > > RETURN; > > > END; > > > $body$ > > -- > Ivan Sergio Borgonovo > http://www.webthatworks.it > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql >
