From: | "maria s" <psmg01(at)gmail(dot)com> |
---|---|
To: | "Bart Degryse" <Bart(dot)Degryse(at)indicator(dot)be> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: function returning result set of varying column |
Date: | 2008-06-03 13:01:02 |
Message-ID: | d9d42a0f0806030601i4c7c4c7dnb9182338f1d6d2e7@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Paul Dam | 2008-06-03 13:12:08 | cross-database references are not implemented |
Previous Message | Bart Degryse | 2008-06-03 07:06:29 | Re: function returning result set of varying column |