From: Jeff Eckermann <jeff_eckermann@yahoo.com>
To: steve boyle <boylesa@dial.pipex.com>
To: pgsql-sql@postgresql.org
Subject: Re: Result sets from functions
Date: Mon, 14 Jan 2002 14:26:56 -0800 (PST)
Message-ID: <20020114222656.9496.qmail@web20803.mail.yahoo.com> (raw)
In-Reply-To: <a1l9is$2r7p$1@news.tht.net>
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/
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: jeff_eckermann@yahoo.com, boylesa@dial.pipex.com
Subject: Re: Result sets from functions
In-Reply-To: <20020114222656.9496.qmail@web20803.mail.yahoo.com>
* 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