To: terence.kearns@canberra.edu.au
Cc: Stephan Szabo <sszabo@megazone.bigpanda.com>,
	pgsql-sql@postgresql.org
Subject: Re: returning a recordset from PLpg/SQL 
In-reply-to: <20040301073006.A71391@megazone.bigpanda.com> 
References: <4041DCBF.203@isd.canberra.edu.au>
 <14883.1078067508@sss.pgh.pa.us>
	<40435499.7050805@isd.canberra.edu.au>
	<20040301073006.A71391@megazone.bigpanda.com>
Comments: In-reply-to Stephan Szabo <sszabo@megazone.bigpanda.com>
	message dated "Mon, 01 Mar 2004 07:31:24 -0800"
Date: Mon, 01 Mar 2004 12:10:15 -0500
Message-ID: <471.1078161015@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> On Tue, 2 Mar 2004, Terence Kearns wrote:
>> Well I haven't yet done anything because I couldn't get anything to
>> compile which returned SETOF RECORD..

> As a starting point, SETOF "RECORD" is different from SETOF RECORD given
> PostgreSQL's fold case to lower case for unquoted names.

Also, you can hardly expect a function to return a rowtype that doesn't
even exist until the function executes --- how the heck is the parser
supposed to make sense of the calling query?  So the "execute create
type" part of this is nonsense, I'm afraid.  The SETOF RECORD mechanism
will let you return a rowtype that is not known fully at the time the
function is written, but the rowtype does have to be known when the
calling query is parsed.

You might be able to replace the CREATE TYPE with an anonymous record
type in the calling query:

	select ...
	from details_for_profile(...) as x(doc_id int4,
	                                   doc_title varchar(256),
	                                   ...);

			regards, tom lane