Thread: Functions that return Record Sets
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. Would appreciate any help/suggestions/comments. Regards Ben Stewart
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
Your in luck http://www.varlena.com/GeneralBits/ Has a good tutorial on that. Cheers Ritchie On Wednesday 21 May 2003 03:25, 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. > > Would appreciate any help/suggestions/comments. > > Regards > Ben Stewart > > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
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 eitherhttp://techdocs.postgresql.org/guides/SetReturningFunctions or the most recent general bitshttp:/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)
Benjamin Stewart wrote: > 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. See: http://techdocs.postgresql.org/guides/SetReturningFunctions http://www.brasileiro.net:8080/postgres/cookbook/view-one-recipe.adp?recipe_id=11378 There are plpgsql and sql function examples in the mailing list archives. Couple of examples: http://archives.postgresql.org/pgsql-general/2002-10/msg00022.php and http://archives.postgresql.org/pgsql-general/2002-12/msg00426.php There is a recent example in Elein Mustain's "General Bits" column: http://archives.postgresql.org/pgsql-general/2002-12/msg00426.php And finally, in the manual see: general -> http://developer.postgresql.org/docs/postgres/xfunc-tablefunctions.html sql -> http://developer.postgresql.org/docs/postgres/xfunc-sql.html (sec 9.2.4) C -> http://developer.postgresql.org/docs/postgres/xfunc-c.html (sec 9.5.6) plpgsql -> http://developer.postgresql.org/docs/postgres/plpgsql-control-structures.html usage -> http://developer.postgresql.org/docs/postgres/sql-select.html (from_item, inputs, from clause, usage) > 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. Sybase/MSSQL style stored procedures are not currently supported. There isn't even general agreement amongst the developers that they should be. Personally, I'd like to see them supported and may one day make a proposal and (assuming it is accepted) add them to Postgres. But don't hold your breath -- it certainly won't happen before 7.4 is released. Joe