Date: Tue, 02 Mar 2004 11:53:03 +1100
From: Terence Kearns <terencek@isd.canberra.edu.au>
Subject: Re: returning a recordset from PLpg/SQL
In-reply-to: <471.1078161015@sss.pgh.pa.us>
To: pgsql-sql@postgresql.org
Message-id: <4043DAEF.6070508@isd.canberra.edu.au>
MIME-version: 1.0
Content-type: text/plain; charset=us-ascii; format=flowed
Content-transfer-encoding: 7BIT
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.5)
	Gecko/20030925
References: <4041DCBF.203@isd.canberra.edu.au>
 <14883.1078067508@sss.pgh.pa.us>
	<40435499.7050805@isd.canberra.edu.au>
	<20040301073006.A71391@megazone.bigpanda.com>
	<471.1078161015@sss.pgh.pa.us>

Tom Lane wrote:

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


Right you are. I did mention that I didn't expect that code to work at 
all, I just used it as an indicator or a desired outcome. I'm sorry if I 
didn't make that clear enough. I just hoped that it would illustrate 
what I'm trying to achieve. And that is:
"return a set of rows where the columns in that row are not yet determined."

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


Interesting.

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

yeah but then you're back to square one with dynamically building the 
columns to match the anonymous type you declared in the query.

I've got some ideas about creating persistent types using RULEs on the 
attribute_profiles table. So when someone INSERTs or UPDATEs an 
attribute profile, a datatype going by the name 'profile_type_' || 
att_profile_id::text is created. That way the types are already known 
and maybe can somehow be passed to the details_for_profile() prcedure.

I'll have to experiment and get back.