Thread: result sets from functions...

i'm reading the postgres documentation and i'm specifically
interested in creating stored procedures so that i can keep
as much of the business logic in the database as possible.
while reading 13.1.3 (SQL Functions on Composite Types) in
the Programmer's Guide i come across the phrase...  "When calling a function that returns a row, we cannot  retrieve
theentire row. We must either project an  attribute out of the row or pass the entire row into  another function.
SELECTname(new_emp()) AS nobody;"
 

how does this affect calling functions as stored procedures
from the jdbc driver and getting a result set returned bac?
am i allowed to use Connection.PrepareCall() to create a
CallableStatement against a postgres function and receive a
ResultSet that can be iterated over?

thanks!

rjsjr

Robert,

> i'm reading the postgres documentation and i'm specifically
> interested in creating stored procedures so that i can keep
> as much of the business logic in the database as possible.
> while reading 13.1.3 (SQL Functions on Composite Types) in
> the Programmer's Guide i come across the phrase...
>    "When calling a function that returns a row, we cannot
>    retrieve the entire row. We must either project an
>    attribute out of the row or pass the entire row into
>    another function.
>       SELECT name(new_emp()) AS nobody;"

Unfortunately, PostgreSQL functions do not yet return result sets.  This
is on the "todo" list but unfortunately requires an overhaul of how
postgresql functions work.

-Josh Berkus

______AGLIO DATABASE SOLUTIONS___________________________                                      Josh Berkus Complete
informationtechnology      josh@agliodbs.com  and data management solutions       (415) 565-7293 for law firms, small
businesses       fax 621-2533   and non-profit organizations.      San Francisco
 

Josh Berkus wrote:
> Robert,
>
> > i'm reading the postgres documentation and i'm specifically
> > interested in creating stored procedures so that i can keep
> > as much of the business logic in the database as possible.
> > while reading 13.1.3 (SQL Functions on Composite Types) in
> > the Programmer's Guide i come across the phrase...
> >    "When calling a function that returns a row, we cannot
> >    retrieve the entire row. We must either project an
> >    attribute out of the row or pass the entire row into
> >    another function.
> >       SELECT name(new_emp()) AS nobody;"
>
> Unfortunately, PostgreSQL functions do not yet return result sets.  This
> is on the "todo" list but unfortunately requires an overhaul of how
> postgresql functions work.
   As  said,  in  v7.2  we'll have the ability to return cursors   from functions (and I'm looking actually into the
ability of   doing  RETURN AND RESUME).  So there is at least some sort of   workaround on the horizon.
 


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com