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 <[EMAIL PROTECTED]>
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" <[EMAIL PROTECTED]> 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
>
