From: steve boyle <boylesa@dial.pipex.com>
To: pgsql-sql@postgresql.org
Subject: Re: Result sets from functions
Date: Thu, 10 Jan 2002 22:53:35 -0000
Message-ID: <a1l9is$2r7p$1@news.tht.net> (raw)
References: <a1jiuh$2i2a$1@news.tht.net>
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.
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: boylesa@dial.pipex.com
Subject: Re: Result sets from functions
In-Reply-To: <a1l9is$2r7p$1@news.tht.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox