Re: return rows question

Thanks.  I had looked into this.  But, it's not quite what I was looking
for.

I am writing a set of functions to act as an api to access data in the
database.  There are times when I would need to return a set of rows or
even a set of text from a pl/pgsql function based on some logic (no such
thing as logic in SQL).

Any ideas?

Thanks for the help,

L.
On Fri, 14 Jun 2002, Joe Conway wrote:

> Laurette Cisneros wrote:
> > Is there any way to write a function that will return a set of rows?  I
> > can't seem to figure it out (in pl/pgsql or some other way)?
> > 
> > Thanks,
> > 
> 
> <This question should probably be moved to the SQL list>
> 
> In 7.2.x it is possible to return SETOF sometype using SQL language 
> functions and C language functions. However the functionality is pretty 
> limited. For example this works:
> 
> test=# select * from foo;
>   fooid | f2
> -------+-----
>       1 |  11
>       2 |  22
>       1 | 111
> (3 rows)
> 
> test=# create or replace function getfoo(int) returns setof int as 
> 'select f2 from foo where fooid = $1;' language sql;
> CREATE FUNCTION
> test=# select getfoo(1);
>   getfoo
> --------
>       11
>      111
> (2 rows)
> 
> But if you want multiple columns:
> test=# drop function getfoo(int);
> DROP FUNCTION
> test=# create or replace function getfoo(int) returns setof foo as 
> 'select * from foo where fooid = $1;' language sql;
> CREATE FUNCTION
> test=# select getfoo(1);
>    getfoo
> -----------
>   139014152
>   139014152
> (2 rows)
> 
> The numbers are actually pointers to the returned composite data type. 
> You can do:
> 
> test=# select fooid(getfoo(1)), f2(getfoo(1));
> select fooid(getfoo(1)), f2(getfoo(1));
>   fooid | f2
> -------+-----
>       1 |  11
>       1 | 111
> (2 rows)
> 
> test=# select version();
> select version();
>                             version
> -------------------------------------------------------------
>   PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC 2.96
> (1 row)
> 
> but it is a bit strange looking. For an example C function which returns 
> a set, see contrib/dblink.
> 
> In the next release (7.3) things will be substantially better. You will 
> be able to do:
> 
> test=# select * from getfoo(1);
>   fooid | f2
> -------+-----
>       1 |  11
>       1 | 111
> (2 rows)
> 
> test=# select version();
>                            version
> ---------------------------------------------------------------
>   PostgreSQL 7.3devel on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 
> 3.1 20020205 (Red Hat Linux Rawhide 3.1-0.21)
> (1 row)
> 
> 
> HTH,
> 
> Joe
> 
> 

-- 
Laurette Cisneros
The Database Group
(510) 420-3137
NextBus Information Systems, Inc.
www.nextbus.com
Where's my....bus?