Re: [SQL] function return multiply rows

En Fri, 27 Sep 2002 09:44:55 -0700
Joe Conway <mail(at)joeconway(dot)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)