Re: function returning result set of varying column

2008/6/3 maria s <psmg01(at)gmail(dot)com>:
> 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?

no, it's not possible. You have to know number and types of result
columns before function's execution.

try to use arrays.

Regards
Pavel Stehule
>
> 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
>
>