Re: Result sets from functions

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(at)dial(dot)pipex(dot)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(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.
> >
> >
>
>
>
>
> ---------------------------(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/