pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
result sets from functions...
6+ messages / 6 participants
[nested] [flat]

* result sets from functions...
@ 2001-08-15 02:35  Robert J. Sanford, Jr. <rsanford@nolimitsystems.com>
  0 siblings, 1 reply; 6+ messages in thread

From: Robert J. Sanford, Jr. @ 2001-08-15 02:35 UTC (permalink / raw)
  To: pgsql-sql

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;"

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




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: result sets from functions...
@ 2001-08-15 02:54  Josh Berkus <josh@agliodbs.com>
  parent: Robert J. Sanford, Jr. <rsanford@nolimitsystems.com>
  0 siblings, 1 reply; 6+ messages in thread

From: Josh Berkus @ 2001-08-15 02:54 UTC (permalink / raw)
  To: Robert J. Sanford, Jr. <rsanford@nolimitsystems.com>; pgsql-sql

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 information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: result sets from functions...
@ 2001-08-15 05:49  Jan Wieck <JanWieck@Yahoo.com>
  parent: Josh Berkus <josh@agliodbs.com>
  0 siblings, 0 replies; 6+ messages in thread

From: Jan Wieck @ 2001-08-15 05:49 UTC (permalink / raw)
  To: Josh Berkus <josh@agliodbs.com>; +Cc: Robert J. Sanford, Jr. <rsanford@nolimitsystems.com>; pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Result sets from functions
@ 2002-01-10 08:23  Kevin Zapico <kevin.zapico@viewgate.com>
  0 siblings, 1 reply; 6+ messages in thread

From: Kevin Zapico @ 2002-01-10 08:23 UTC (permalink / raw)
  To: pgsql-sql

I am new to postgres and am trying to get a function to return a result set
with multiple columns.

The only way I have seen to do this so far is

select column1(proc()), column2(proc())

This looks like it should call the proc() function twice, although I am sure
that it does not. However, I am trying to find out if there is another way
of doing this.

Please help.





^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Result sets from functions
@ 2002-01-10 22:53  steve boyle <boylesa@dial.pipex.com>
  parent: Kevin Zapico <kevin.zapico@viewgate.com>
  0 siblings, 1 reply; 6+ messages in thread

From: steve boyle @ 2002-01-10 22:53 UTC (permalink / raw)
  To: pgsql-sql

Kevin,

AFAIK you cannot currently return resultsets from functions in Postgres.  I
remember seing something ages ago that suggested it may be added at some
point but haven't seen anything since.

The docs: http://developer.postgresql.org/docs/postgres/xfunc-sql.html go
through the current usage.

The following shows how you can emulate the return of a resultset using SQL
functions and the SQL IN operator (although I don't know how efficient it
would be over large resultsets):

drop table master;
create table master (
    id int4,
    ma_val varchar(5),
    primary key(id)
);

drop table slave;
drop sequence slave_slave_id_seq;

create table slave (
    slave_id serial,
    fk_id int4,
    sl_val varchar(5),
    foreign key (fk_id) references master(id)
);

insert into master(id, ma_val) values(1, 'a');
insert into master(id, ma_val) values(2, 'b');
insert into master(id, ma_val) values(3, 'c');

insert into slave(fk_id, sl_val) values(1, 'c1');
insert into slave(fk_id, sl_val) values(1, 'c2');
insert into slave(fk_id, sl_val) values(1, 'c3');
insert into slave(fk_id, sl_val) values(2, 'c4');
insert into slave(fk_id, sl_val) values(2, 'c5');
insert into slave(fk_id, sl_val) values(3, 'c6');

drop function f_spTest(int4);

create function f_spTest(int) returns setof int as
    'select slave_id as slave_id from slave where fk_id = $1;'
language 'SQL';

select * from slave where slave_id in (select f_sptest(1));

One limitation with this is that you need to have a single key into the
table your querying (but you could probably use the OID for this so it
shouldn't be too much of a problem).

hih

sb

"Kevin Zapico" <kevin.zapico@viewgate.com> wrote in message
news:a1jiuh$2i2a$1@news.tht.net...
> I am new to postgres and am trying to get a function to return a result
set
> with multiple columns.
>
> The only way I have seen to do this so far is
>
> select column1(proc()), column2(proc())
>
> This looks like it should call the proc() function twice, although I am
sure
> that it does not. However, I am trying to find out if there is another way
> of doing this.
>
> Please help.
>
>






^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Result sets from functions
@ 2002-01-14 22:26  Jeff Eckermann <jeff_eckermann@yahoo.com>
  parent: steve boyle <boylesa@dial.pipex.com>
  0 siblings, 0 replies; 6+ messages in thread

From: Jeff Eckermann @ 2002-01-14 22:26 UTC (permalink / raw)
  To: steve boyle <boylesa@dial.pipex.com>; pgsql-sql

Two other possible ways to get resultsets (or
equivalent) from functions:
1. (Indirect solution) Make inserts to a table from
your function; do a separate select for the results
2. Have your function return a string which your
application can parse into records.  Maybe: "RETURN
field1 || chr(9) || field2 || chr(10) || field3 ||
chr(9) || field4 || chr(10);

--- steve boyle <boylesa@dial.pipex.com> wrote:
> Kevin,
> 
> AFAIK you cannot currently return resultsets from
> functions in Postgres.  I
> remember seing something ages ago that suggested it
> may be added at some
> point but haven't seen anything since.
> 
> The docs:
>
http://developer.postgresql.org/docs/postgres/xfunc-sql.html
> go
> through the current usage.
> 
> The following shows how you can emulate the return
> of a resultset using SQL
> functions and the SQL IN operator (although I don't
> know how efficient it
> would be over large resultsets):
> 
> drop table master;
> create table master (
>     id int4,
>     ma_val varchar(5),
>     primary key(id)
> );
> 
> drop table slave;
> drop sequence slave_slave_id_seq;
> 
> create table slave (
>     slave_id serial,
>     fk_id int4,
>     sl_val varchar(5),
>     foreign key (fk_id) references master(id)
> );
> 
> insert into master(id, ma_val) values(1, 'a');
> insert into master(id, ma_val) values(2, 'b');
> insert into master(id, ma_val) values(3, 'c');
> 
> insert into slave(fk_id, sl_val) values(1, 'c1');
> insert into slave(fk_id, sl_val) values(1, 'c2');
> insert into slave(fk_id, sl_val) values(1, 'c3');
> insert into slave(fk_id, sl_val) values(2, 'c4');
> insert into slave(fk_id, sl_val) values(2, 'c5');
> insert into slave(fk_id, sl_val) values(3, 'c6');
> 
> drop function f_spTest(int4);
> 
> create function f_spTest(int) returns setof int as
>     'select slave_id as slave_id from slave where
> fk_id = $1;'
> language 'SQL';
> 
> select * from slave where slave_id in (select
> f_sptest(1));
> 
> One limitation with this is that you need to have a
> single key into the
> table your querying (but you could probably use the
> OID for this so it
> shouldn't be too much of a problem).
> 
> hih
> 
> sb
> 
> "Kevin Zapico" <kevin.zapico@viewgate.com> wrote in
> message
> news:a1jiuh$2i2a$1@news.tht.net...
> > I am new to postgres and am trying to get a
> function to return a result
> set
> > with multiple columns.
> >
> > The only way I have seen to do this so far is
> >
> > select column1(proc()), column2(proc())
> >
> > This looks like it should call the proc() function
> twice, although I am
> sure
> > that it does not. However, I am trying to find out
> if there is another way
> > of doing this.
> >
> > Please help.
> >
> >
> 
> 
> 
> 
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
> http://archives.postgresql.org


__________________________________________________
Do You Yahoo!?
Send FREE video emails in Yahoo! Mail!
http://promo.yahoo.com/videomail/



^ permalink  raw  reply  [nested|flat] 6+ messages in thread


end of thread, other threads:[~2002-01-14 22:26 UTC | newest]

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-08-15 02:35 result sets from functions... Robert J. Sanford, Jr. <rsanford@nolimitsystems.com>
2001-08-15 02:54 ` Josh Berkus <josh@agliodbs.com>
2001-08-15 05:49   ` Jan Wieck <JanWieck@Yahoo.com>
2002-01-10 08:23 Result sets from functions Kevin Zapico <kevin.zapico@viewgate.com>
2002-01-10 22:53 ` Re: Result sets from functions steve boyle <boylesa@dial.pipex.com>
2002-01-14 22:26   ` Re: Result sets from functions Jeff Eckermann <jeff_eckermann@yahoo.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