Skip site navigation (1) Skip section navigation (2)

subselects in the target list

From: Neil Conway <neilc(at)samurai(dot)com>
To: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: subselects in the target list
Date: 2005-02-03 04:02:14
Message-ID: 1107403334.26960.38.camel@localhost.localdomain (view raw, whole thread or download thread mbox)
Lists: pgsql-hackers
This behavior seems inconsistent:

neilc=# create table abc (a int, b int);
neilc=# create function foo_abc() returns setof abc as 'select * from
abc' language sql;
neilc=# insert into abc values (5, 10);
INSERT 17234 1
neilc=# insert into abc values (10, 20);
INSERT 17235 1
neilc=# select a, foo_abc() from abc;
 a  | foo_abc 
  5 | (5,10)
  5 | (10,20)
 10 | (5,10)
 10 | (10,20)
(4 rows)
neilc=# select a, (select * from abc) from abc;
ERROR:  subquery must return only one column

Is there a reason we can't treat a subselect in the target list as
returning a composite type?

For that matter, is this behavior also intentional?

neilc=# create function foo_abc2() returns setof abc as
'declare row record;
begin for row in select * from abc loop
  return next row;
end loop;
return; end' language plpgsql;
neilc=# select a, foo_abc2() FROM abc;
ERROR:  set-valued function called in context that cannot accept a set
CONTEXT:  PL/pgSQL function "foo_abc2" line 1 at return next



pgsql-hackers by date

Next:From: Tom LaneDate: 2005-02-03 04:22:24
Subject: Re: subselects in the target list
Previous:From: Greg StarkDate: 2005-02-03 03:43:42
Subject: Re: libpq API incompatibility between 7.4 and 8.0

Privacy Policy | About PostgreSQL
Copyright © 1996-2018 The PostgreSQL Global Development Group