Thanks for all your for the reply.
I tried the function and when I execute it using
select * from myfunction()
ERROR: a column definition list is required for functions returning
Could you please help me to fix this error?
Thanks so much for your help.
On Tue, Jun 3, 2008 at 3:06 AM, Bart Degryse <Bart(dot)Degryse(at)indicator(dot)be>
> Hi Maria,
> Try something like
> CREATE OR REPLACE FUNCTION myfunction() RETURNS SETOF RECORD AS
> rec record;
> FOR rec IN (
> SELECT * FROM sometable)
> RETURN NEXT rec;
> END LOOP;
> 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
> Is there anyway to return a resultset with any number of column?
> Thanks for your help.
In response to
pgsql-sql by date
|Next:||From: Paul Dam||Date: 2008-06-03 13:12:08|
|Subject: cross-database references are not implemented|
|Previous:||From: Bart Degryse||Date: 2008-06-03 07:06:29|
|Subject: Re: function returning result set of varying column|