Re: Result sets from functions

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(dot)zapico(at)viewgate(dot)com> wrote in message
news:a1jiuh$2i2a$1(at)news(dot)tht(dot)net(dot)(dot)(dot)
> 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.
>
>