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

Re: function returning result set of varying column

From: "Bart Degryse" <Bart(dot)Degryse(at)indicator(dot)be>
To: "maria s" <psmg01(at)gmail(dot)com>,<pgsql-sql(at)postgresql(dot)org>
Subject: Re: function returning result set of varying column
Date: 2008-06-03 07:06:29
Message-ID: 48450995.A3DD.0030.0@indicator.be (view raw or flat)
Thread:
Lists: pgsql-sql
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: maria sDate: 2008-06-03 13:01:02
Subject: Re: function returning result set of varying column
Previous:From: maria sDate: 2008-06-02 20:40:48
Subject: function returning result set of varying column

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