Re: Result sets from functions - Mailing list pgsql-sql

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/