Date: Sat, 28 Sep 2002 00:46:00 -0400
From: Alvaro Herrera <alvherre@atentus.com>
To: Joe Conway <mail@joeconway.com>
Cc: j.olthof@millipede.nl, pgsql-general@postgresql.org,
	pgsql-sql@postgresql.org
Subject: Re: [SQL] function return multiply rows
Message-Id: <20020928004600.741e3e8f.alvherre@atentus.com>
In-Reply-To: <3D948B07.5050205@joeconway.com>
References: <amrnt5$1ivj$1@news.hub.org>
	<3D948B07.5050205@joeconway.com>
Organization: Atentus.com
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit

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)