Re: function returning result set of varying column

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 <mail(at)webthatworks(dot)it>
wrote:

> On Tue, 3 Jun 2008 09:01:02 -0400
> "maria s" <psmg01(at)gmail(dot)com> 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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>