Re: 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 GCC gcc (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?