User-Agent: Microsoft-Entourage/10.1.4.030702.0
Date: Wed, 26 Oct 2005 06:58:16 -0400
Subject: Re: SETOF RECORD RETURN VALUE
From: Sean Davis <sdavis2@mail.nih.gov>
To: "Christian Paul B. Cosinas" <cpc@cybees.com>,
	<pgsql-sql@postgresql.org>
Message-ID: <BF84D988.11C6E%sdavis2@mail.nih.gov>
In-Reply-To: <000001c5da18$e1d9ba20$1e21100a@ghwk02002147>
Mime-version: 1.0
Content-type: text/plain; charset="US-ASCII"
Content-transfer-encoding: 7bit

On 10/26/05 6:34 AM, "Christian Paul B. Cosinas" <cpc@cybees.com> wrote:

> Hi I am having some problem with function that returns SETOF RECORD
> 
> Here is my function:
> 
> CREATE OR REPLACE FUNCTION test_record(text)
> RETURNS SETOF RECORD AS
> $BODY$
> 
> 
> DECLARE
> p_table_name ALIAS FOR $1;
> temp_rec RECORD;
> v_query text;
> 
> BEGIN 
> 
> v_query = 'SELECT * FROM ' || p_table_name; FOR temp_rec IN EXECUTE v_query
> LOOP
> RETURN NEXT temp_rec;
> END LOOP;
> 
> RETURN ;
> 
> END;
> 
> $BODY$
> LANGUAGE 'plpgsql' VOLATILE;
> 
> 
> And here is how I execute the function:
> select * from test_record('field_list')
> 
> I have this error:
> 
> ERROR:  a column definition list is required for functions returning
> "record"

Since Postgres doesn't know what to expect from your function, you have to
tell it by giving the list of columns that are actually returned:

select * from test_record('field_list') as s(a,b,c,d)

where a,b,c,d are the columns in your returned set.  (ie., in your example,
if p_table_name has 5 columns, you would use "as s(a,b,c,d,e)", etc.).

See here for more detail:

http://techdocs.postgresql.org/guides/SetReturningFunctions

Sean