Postgres Pro
Downloads
Home   >   mailing lists

Re: function returning result set of varying column - Mailing list pgsql-sql

From Bart Degryse
Subject Re: function returning result set of varying column
Date
Msg-id 48456097.A3DD.0030.0@indicator.be
Whole thread Raw
In response to Re: function returning result set of varying column  ("maria s" <psmg01@gmail.com>)
List pgsql-sql
Tree view
As I wrote before you will have to define your fields when querying the function,
eg. select * from myfunction() as ("field1" integer, "field2" text, ...)
So suppose you have a table like this
CREATE TABLE sometable (
  "id" serial,
  "sometextfield" text,
  "aninteger" int,
  "andavarchar" varchar(20)
);
you would have to query the function below like this:
select * from myfunction() as ("id" integer, "sometextfield" text, "aninteger" integer, "andavarchar" varchar);
 
You are however not obliged to use the same fieldnames as defined in the table definition, so this will work too:
select * from myfunction() as ("myid" integer, "textfield" text, "myint" integer, "varfield" varchar);
 
Automatic type conversion will work too, so this is ok too
select * from myfunction() as ("myid" integer, "textfield" text, "myint" integer, "varfield" text);
 
Now suppose you change the selecte statement in myfunction to
SELECT sometextfield, andavarchar FROM sometable WHERE id > 5
 
Now myfunction will not return 4 fields but only 2 and thus you would query myfunction like this
select * from myfunction() as ("sometextfield" text, "andavarchar" varchar);
or
select * from myfunction() as ("textfield" text, "varfield" varchar);
or
select * from myfunction() as ("textfield" text, "varfield" text);
 
So defining your function as RETURNS SETOF RECORD only postpones the moment you define your output fields.
 
Either you define your output fields when creating your function, or (like in the above example) when querying your function.
 
 
>>> "maria s" <psmg01@gmail.com> 2008-06-03 15:12 >>>
Hi Bart,
I will not know the number of fields. Because it will vary .
Is there any solution for this?

Thanks,
Maria

>>> "maria s" <psmg01@gmail.com> 2008-06-03 15:01 >>>
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.Degryse@indicator.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@gmail.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

pgsql-sql by date:

Previous
From: "maria s"
Date:
Subject: Re: function returning result set of varying column
Next
From: Ivan Sergio Borgonovo
Date:
Subject: Re: function returning result set of varying column