pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedfunction return multiply rows
3+ messages / 3 participants
[nested] [flat]
* function return multiply rows
@ 2002-09-25 07:17 Jeroen Olthof <j.olthof@millipede.nl>
0 siblings, 1 reply; 3+ messages in thread
From: Jeroen Olthof @ 2002-09-25 07:17 UTC (permalink / raw)
To: pgsql-general@postgresql.org; pgsql-sql
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)
and this is what I expect
team | datum
----------+------------
groenwit | 2002-07-09
ordenbos | 2002-09-14
(2 rows)
????
kind regard
Jeroen Olthof
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [SQL] function return multiply rows
@ 2002-09-27 16:44 Joe Conway <mail@joeconway.com>
parent: Jeroen Olthof <j.olthof@millipede.nl>
0 siblings, 1 reply; 3+ messages in thread
From: Joe Conway @ 2002-09-27 16:44 UTC (permalink / raw)
To: Jeroen Olthof <j.olthof@millipede.nl>; +Cc: pgsql-general@postgresql.org; pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [SQL] function return multiply rows
@ 2002-09-28 04:46 Alvaro Herrera <alvherre@atentus.com>
parent: Joe Conway <mail@joeconway.com>
0 siblings, 0 replies; 3+ messages in thread
From: Alvaro Herrera @ 2002-09-28 04:46 UTC (permalink / raw)
To: Joe Conway <mail@joeconway.com>; +Cc: j.olthof@millipede.nl; pgsql-general@postgresql.org; pgsql-sql
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)
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2002-09-28 04:46 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-09-25 07:17 function return multiply rows Jeroen Olthof <j.olthof@millipede.nl>
2002-09-27 16:44 ` Joe Conway <mail@joeconway.com>
2002-09-28 04:46 ` Alvaro Herrera <alvherre@atentus.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox