Message-ID: <40C6D140.8010701@archonet.com>
Date: Wed, 09 Jun 2004 09:58:40 +0100
From: Richard Huxton <dev@archonet.com>
User-Agent: Mozilla Thunderbird 0.6 (X11/20040502)
MIME-Version: 1.0
To: terence.kearns@canberra.edu.au
Cc: pgsql-sql@postgresql.org
Subject: Re: returning a recordset from PLpg/SQL
References: <4041DCBF.203@isd.canberra.edu.au>
In-Reply-To: <4041DCBF.203@isd.canberra.edu.au>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

Terence Kearns wrote:
> Looking at the datatypes you can RETURN, it doesn't seem that there is a 
> way to return a recordset
> 
> I tried
> RETURNS SETOF RECORD
> but that doesn't work
> 
> I even tried
> RETURNS SETOF fooTable%ROWTYPE
> 
> What I would like to do is not that simple, I need to be able to 
> build/declare a record definition with an arbitary list of columns and 
> then return a set of that record which has it's values populated by 
> queries and calculations inside the procedure.

Declare your datatype (CREATE TYPE ...) and return a SETOF that. What 
you can't do is declare your datatype inside your function. Clearly 
PostgreSQL needs to know what type is being returned while parsing the 
function, so you can't have a "dynamic type" (if such an idea has any 
meaning at all).

-- 
   Richard Huxton
   Archonet Ltd