Thread: function return multiply rows

What is going wrong here?


An example of what I'm trying to do.

vw_teams is a view but same problem when trying it on a single table
CREATE FUNCTION test() RETURNS SETOF vw_teams AS 'select * from vw_teams;'
LANGUAGE 'sql';

SELECT test();

results in
  test
-----------137789256137789256
(2 rows)

and this is what I expect
  team   |   datum
----------+------------groenwit | 2002-07-09ordenbos | 2002-09-14
(2 rows)


????

kind regard

Jeroen Olthof

Jeroen Olthof wrote:
> What is going wrong here?
>
> An example of what I'm trying to do.
>
> vw_teams is a view but same problem when trying it on a single table
> CREATE FUNCTION test() RETURNS SETOF vw_teams AS 'select * from vw_teams;'
> LANGUAGE 'sql';
>
> SELECT test();
>
> results in
>
>    test
> -----------
>  137789256
>  137789256
> (2 rows)

The capability to return composite types (multi-column rows) is limited in <=
PostgreSQL 7.2.x. What you are seeing are pointers to the rows, not the rows
themselves.

Version 7.3, in beta testing now, will do what you are looking for. If you
can, please give it a try. See:
   http://developer.postgresql.org/docs/postgres/xfunc-tablefunctions.html
for more info and examples.

HTH,

Joe

En Fri, 27 Sep 2002 09:44:55 -0700
Joe Conway <mail@joeconway.com> escribió:

> Jeroen Olthof wrote:

> > vw_teams is a view but same problem when trying it on a single table
> > CREATE FUNCTION test() RETURNS SETOF vw_teams AS 'select * from vw_teams;'
> > LANGUAGE 'sql';
> >
> > SELECT test();
> >
> > results in
> >
> >    test
> > -----------
> >  137789256
> >  137789256
> > (2 rows)
>
> The capability to return composite types (multi-column rows) is limited in <=
> PostgreSQL 7.2.x. What you are seeing are pointers to the rows, not the rows
> themselves.

While you are using the old versions, I believe you can retrieve the
columns by calling them by name.  At least this works on 7.2:

create table vw_teams (a int, b int);
insert into vw_teams values (1, 2);
insert into vw_teams values (3, 4);
select a(test()), b(test());
 a | b
---+---
 1 | 2
 3 | 4
(2 rows)

I suspect the function is being evaluated twice for each row.  Maybe
there's a better way.

--
Alvaro Herrera (<alvherre[a]atentus.com>)
La web junta la gente porque no importa que clase de mutante sexual seas,
tienes millones de posibles parejas. Pon "buscar gente que tengan sexo con
ciervos incendiánse", y el computador dirá "especifique el tipo de ciervo"
(Jason Alexander)