Re: Functions that return Record Sets

There is a great article in the tech docs area explaining how to create
these functions:

http://techdocs.postgresql.org/guides/SetReturningFunctions

There is also the ability to return cursors (refcursor) but I couldn't
use this method with my application server (Coldfusion MX). sets work
though.

Avi

On Wednesday, May 21, 2003, at 05:25 America/Chicago, Benjamin Stewart
wrote:

> Greetings,
> Sorry if this question has been posted before, but I searched the
> archives and documentation and was unable to find ther elevant
> information or any other developers with first hand experience at > this.
>
> 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.
>
> 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.
>
> I have worked previously with sybase and we have the ability to create
> a stored procedure (different from a user defined function) that
> returns a record set that is passed back as and I can use as a
> standard RS and itterate throught it etc. I am also able to get meta
> data about field names, type etc from the RS.
>
--
Avi Schwartz
avi(at)CFFtechnologies(dot)com