Skip site navigation (1) Skip section navigation (2)

Re: function returning result set of varying column

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 (view raw or flat)
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
>

In response to

Responses

pgsql-sql by date

Next:From: Paul DamDate: 2008-06-03 13:12:08
Subject: cross-database references are not implemented
Previous:From: Bart DegryseDate: 2008-06-03 07:06:29
Subject: Re: function returning result set of varying column

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group