Date: Tue, 20 May 2003 18:51:23 -0700 (PDT)
From: Stephan Szabo <sszabo@megazone23.bigpanda.com>
To: Benjamin Stewart <benstewart74@yahoo.com.au>
Cc: <pgsql-sql@postgresql.org>
Subject: Re: Functions that return Record Sets
In-Reply-To: <3ECB5418.6060509@yahoo.com.au>
Message-ID: <20030520184638.I82284-100000@megazone23.bigpanda.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII


On Wed, 21 May 2003, Benjamin Stewart wrote:

> We are writting an application that is web based. We will beusing
> struts, java and all the wonderful things that that technology brings.
> We have decided to use Postgresql as our DB (v7.3.1). I noticed that one
> of the major changes that came with the release of 7.3 was the ability
> to return a record set from a function. I have not been able to find any
> specific documentation on how to achieve this. If there is any I would
> appreciate it if somebody could point it out.

You might want to look at either
 http://techdocs.postgresql.org/guides/SetReturningFunctions
or the most recent general bits
 http:/www.varlena.com/GeneralBits/
for some examples and info.

> What i really want is to hear from somebody that has first hand
> experience and returning recordsets from postgresql (using pl/pgsql)
> functions. The other developer here has investigated and was only able
> to find a method that is a little bit tedious, and requires that actual
> call to the database from the java code (using jdbc) to define the
> fields that we are expecting etc.

Right now you have the option of returning a set of a defined record type
(you can use create type to make a holder type) or a set of record, the
latter of which requires you to specify the fields on the call. There was
some additional work that was going to be done with them for 7.4, but I
don't remember the details off hand (you might be able to find something
in archives)