Re: function returning result set of varying column

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.

-maria

On Tue, Jun 3, 2008 at 3:06 AM, Bart Degryse <Bart(dot)Degryse(at)indicator(dot)be>
wrote:

> Hi Maria,
> Try something like
> 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$
> LANGUAGE 'plpgsql' VOLATILE;
>
> As you can see, the number and type of the output fields only depends on
> whatever table you query in the FOR loop.
> It's not magic though. It just postpones defining the number and type of
> the output fields until querying the function.
> You will have to define the output fields when querying your function, like
> select * from myfunction() as ("field1" integer, "field2" text, ...)
>
> >>> "maria s" <psmg01(at)gmail(dot)com> 2008-06-02 22:40 >>>
>
> Hi friends,
> I am very new to plsql.
>
> I have to write a function that quries few tables and returns a resultset
> of varying column.
>
> In that case I cannot predefine the table with column.
> If I use RETURNS SETOF then I should know the number of columns and its
> type?!
>
> Is there anyway to return a resultset with any number of column?
>
> Thanks for your help.
>
> -maria
>