Обсуждение: return rows question
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, -- Laurette Cisneros The Database Group (510) 420-3137 NextBus Information Systems, Inc. www.nextbus.com Where's my....bus?
Please search -hackers list for "SRF", there are patches by Joe Conway to do that with SQL functions. There aren't any patches to do that in plpgsql yet. -alex On Thu, 13 Jun 2002, 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, > >
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 GCCgcc (GCC) 3.1 20020205 (Red Hat Linux Rawhide 3.1-0.21) (1 row) HTH, Joe
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?
Laurette Cisneros wrote: > 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. Sorry, but even in current development sources, PL/pgSQL cannot return a set (or at least if it can, I can't figure out how). I'm hoping to change that before 7.3 is released, but at this point I haven't even looked at it too hard. You might look at having your function return a refcursor. See: http://developer.postgresql.org/docs/postgres/plpgsql-cursors.html section 23.7.3.3. Returning Cursors. The URL is for the 7.3 development docs, but I think the example shown will work in 7.2.x. Joe
Yeah, saw that too. Thanks for all the help. L. On Fri, 14 Jun 2002, Joe Conway wrote: > Laurette Cisneros wrote: > > 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. > > Sorry, but even in current development sources, PL/pgSQL cannot return a > set (or at least if it can, I can't figure out how). I'm hoping to > change that before 7.3 is released, but at this point I haven't even > looked at it too hard. > > You might look at having your function return a refcursor. See: > http://developer.postgresql.org/docs/postgres/plpgsql-cursors.html > section 23.7.3.3. Returning Cursors. The URL is for the 7.3 development > docs, but I think the example shown will work in 7.2.x. > > Joe > > > -- Laurette Cisneros The Database Group (510) 420-3137 NextBus Information Systems, Inc. www.nextbus.com Where's my....bus?