Обсуждение: Result sets from functions

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.

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.
>
>

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/