X-Original-To: pgsql-sql@postgresql.org Received: from localhost (unknown [64.117.224.193]) by developer.postgresql.org (Postfix) with ESMTP id 15C8D925207 for ; Tue, 20 May 2003 21:38:36 -0400 (EDT) Received: from developer.postgresql.org ([64.117.224.193]) by localhost (developer.postgresql.org [64.117.224.193:10024]) (amavisd-new) with ESMTP id 24261-02 for ; Tue, 20 May 2003 21:38:04 -0400 (EDT) Received: from cfftechnologies.com (dsl081-147-127.chi1.dsl.speakeasy.net [64.81.147.127]) by developer.postgresql.org (Postfix) with ESMTP id D5D3D92520D for ; Tue, 20 May 2003 21:38:03 -0400 (EDT) Received: from localhost (localhost [127.0.0.1]) by cfftechnologies.com (Postfix on SuSE Linux 8.0 (i386)) with ESMTP id CBAB120C09B for ; Tue, 20 May 2003 20:34:05 -0500 (CDT) Received: from cfftechnologies.com ([127.0.0.1]) by localhost (coral [127.0.0.1]) (amavisd-new) with ESMTP id 11839-06 for ; Tue, 20 May 2003 20:34:05 -0000 (CDT) Received: from CFFtechnologies.com (dhcp109.cfftechnologies.world [192.168.129.109]) by cfftechnologies.com (Postfix on SuSE Linux 8.0 (i386)) with ESMTP id 144F620C09A for ; Tue, 20 May 2003 20:34:05 -0500 (CDT) Date: Tue, 20 May 2003 20:38:01 -0500 Subject: Re: Functions that return Record Sets Content-Type: text/plain; charset=US-ASCII; format=flowed Mime-Version: 1.0 (Apple Message framework v552) From: Avi Schwartz To: pgsql-sql@postgresql.org Content-Transfer-Encoding: 7bit In-Reply-To: <3ECB5418.6060509@yahoo.com.au> Message-Id: X-Mailer: Apple Mail (2.552) X-Virus-Scanned: by amavisd-new amavisd-new-20020630 X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=4.9 tests=whitelisted X-Razor-id: 049d3e8564bfa6a07db84343954097300ed17376 X-Virus-Scanned: by amavisd-new X-Archive-Number: 200305/403 X-Sequence-Number: 13568 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@CFFtechnologies.com