From: | Joe Conway <mail(at)joeconway(dot)com> |
---|---|
To: | Laurette Cisneros <laurette(at)nextbus(dot)com> |
Cc: | pgsql-interfaces(at)postgresql(dot)org |
Subject: | Re: return rows question |
Date: | 2002-06-14 22:54:15 |
Message-ID: | 3D0A7417.4040900@joeconway.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-interfaces |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Laurette Cisneros | 2002-06-14 23:24:16 | Re: return rows question |
Previous Message | Amit Lamba | 2002-06-14 21:49:31 | oracle to postgresql |