Re: return rows question

[Joe Conway <mail(at)joeconway(dot)com>]

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