pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedresult 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