Received: from news.tht.net (news.hub.org [216.126.91.242]) by postgresql.org (8.11.3/8.11.4) with ESMTP id g0ANvoB75916 for ; Thu, 10 Jan 2002 18:57:50 -0500 (EST) (envelope-from news@news.tht.net) Received: (from news@localhost) by news.tht.net (8.11.4/8.11.4) id g0ANs7k93438 for pgsql-sql@postgresql.org; Thu, 10 Jan 2002 18:54:07 -0500 (EST) (envelope-from news) From: "steve boyle" X-Newsgroups: comp.databases.postgresql.sql Subject: Re: Result sets from functions Date: Thu, 10 Jan 2002 22:53:35 -0000 Organization: Hub.Org Networking Services (http://www.hub.org) Lines: 78 Message-ID: References: X-Complaints-To: news@hub.org X-Newsreader: Microsoft Outlook Express 5.50.4522.1200 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4522.1200 To: pgsql-sql@postgresql.org X-Archive-Number: 200201/139 X-Sequence-Number: 6227 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" 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. > >