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

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)

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

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(at)postgresql(dot)org

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