Re: function returning result set of varying column

From: Ivan Sergio Borgonovo <mail(at)webthatworks(dot)it>
To: pgsql-sql(at)postgresql(dot)org
Subject: Re: function returning result set of varying column
Date: 2008-06-03 13:28:41
Message-ID: 20080603152841.64719a02@dawn.webthatworks.it
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

On Tue, 3 Jun 2008 09:01:02 -0400
"maria s" <psmg01(at)gmail(dot)com> wrote:

> 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.

you can specify the returned types in each statement that call your
function or you can specify the returned type in the function itself.

CREATE OR REPLACE FUNCTION myfunction(out col1 int, out col2
varchar(32), out ...)
RETURNS
SETOF
RECORD
AS
$body$
DECLARE
rec record;
BEGIN
FOR rec IN (
SELECT * FROM sometable)
LOOP
col1:=rec.col1;
col2:=rec.col2;
-- col3:=...;
RETURN NEXT;
END LOOP;
RETURN;
END;
$body$

> > 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$

--
Ivan Sergio Borgonovo
http://www.webthatworks.it

In response to

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message maria s 2008-06-03 13:41:27 Re: function returning result set of varying column
Previous Message Bart Degryse 2008-06-03 13:17:43 Re: function returning result set of varying column