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