Thread: 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/