Message-ID: <3F4D54AE.3030604@joeconway.com>
Date: Wed, 27 Aug 2003 18:02:38 -0700
From: Joe Conway <mail@joeconway.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US;
	rv:1.3) Gecko/20030312
MIME-Version: 1.0
To: Kumar <sgnerd@yahoo.com.sg>
Cc: psql <pgsql-sql@postgresql.org>
Subject: Re: How to return a record set from function.
References: <006901c36c6b$65ab2000$7502a8c0@hdsc.com>
In-Reply-To: <006901c36c6b$65ab2000$7502a8c0@hdsc.com>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

Kumar wrote:
> Create table t1 (c1 int, c2 varchar, c3 varchar);
> 
> Create or Replace function sel_t1 () returns setof records as ' 
> select c1, c2, c3 from t1; ' Language SQL;
> 
> It was fine and created a function. while i execute it as
> 
> select sel_t1;
> 
> I got the following error.
> 
> ERROR:  Cannot display a value of type RECORD

I see three problems.

1) you need parenthesis on the function call, i.e. "sel_t1()" as
    compared with "sel_t1"
2) when returning setof record, the "sel_t1()" must be in the FROM
    clause of the statement
3) when the function is declared as returning "record" as compared to
    a named complex type such as "t1", you need to include a column
    definition list in the SQL statement

So, putting it all together, try something like this:

SELECT f1, f2, f3 FROM sel_t1() AS (f1 int, f2 varchar, f3 varchar);

See:
http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=xfunc-tablefunctions.html
and
http://techdocs.postgresql.org/guides/SetReturningFunctions

HTH,

Joe