pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: Functions that return Record Sets
5+ messages / 5 participants
[nested] [flat]
* Re: Functions that return Record Sets
@ 2003-05-21 01:38 Avi Schwartz <avi@CFFtechnologies.com>
parent: Benjamin Stewart <benstewart74@yahoo.com.au>
3 siblings, 0 replies; 5+ messages in thread
From: Avi Schwartz @ 2003-05-21 01:38 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Functions that return Record Sets
@ 2003-05-21 01:51 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Benjamin Stewart <benstewart74@yahoo.com.au>
3 siblings, 0 replies; 5+ messages in thread
From: Stephan Szabo @ 2003-05-21 01:51 UTC (permalink / raw)
To: Benjamin Stewart <benstewart74@yahoo.com.au>; +Cc: pgsql-sql
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)
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Functions that return Record Sets
@ 2003-05-21 01:57 Joe Conway <mail@joeconway.com>
parent: Benjamin Stewart <benstewart74@yahoo.com.au>
3 siblings, 0 replies; 5+ messages in thread
From: Joe Conway @ 2003-05-21 01:57 UTC (permalink / raw)
To: Benjamin Stewart <benstewart74@yahoo.com.au>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Functions that return Record Sets
@ 2003-05-21 04:39 ritchie turner <ritchie@ipowerhouse.com>
parent: Benjamin Stewart <benstewart74@yahoo.com.au>
3 siblings, 0 replies; 5+ messages in thread
From: ritchie turner @ 2003-05-21 04:39 UTC (permalink / raw)
To: Benjamin Stewart <benstewart74@yahoo.com.au>; pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Functions that return Record Sets
@ 2003-05-21 10:25 Benjamin Stewart <benstewart74@yahoo.com.au>
0 siblings, 4 replies; 5+ messages in thread
From: Benjamin Stewart @ 2003-05-21 10:25 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2003-05-21 10:25 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-05-21 10:25 Functions that return Record Sets Benjamin Stewart <benstewart74@yahoo.com.au>
2003-05-21 01:38 ` Avi Schwartz <avi@CFFtechnologies.com>
2003-05-21 01:51 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2003-05-21 01:57 ` Joe Conway <mail@joeconway.com>
2003-05-21 04:39 ` ritchie turner <ritchie@ipowerhouse.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox