Thread: subselects in the target list

This behavior seems inconsistent:

neilc=# create table abc (a int, b int);
CREATE TABLE
neilc=# create function foo_abc() returns setof abc as 'select * from
abc' language sql;
CREATE FUNCTION
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;
CREATE FUNCTION
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

-Neil

Neil Conway <neilc@samurai.com> writes:
> 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?

Given the 8.0 infrastructure for unnamed record types it might be
possible to do that; it was surely never possible before.  Whether it's
a good idea is another question.  The syntax you are showing is designed
to return a scalar.  It will (and should) barf on multiple rows as well
as multiple columns.

> For that matter, is this behavior also intentional?

> 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

It's an implementation restriction in plpgsql: we didn't make it support
the old-style SRF API.  I'm unconvinced that it's worth fixing
considering that this whole behavior (SRFs in the targetlist) is
deprecated.
        regards, tom lane

On Wed, 2005-02-02 at 23:22 -0500, Tom Lane wrote:
> Neil Conway <neilc@samurai.com> writes:
> > 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?
> 
> Given the 8.0 infrastructure for unnamed record types it might be
> possible to do that; it was surely never possible before.  Whether it's
> a good idea is another question.  The syntax you are showing is designed
> to return a scalar.  It will (and should) barf on multiple rows as well
> as multiple columns.

Right, the point is, that is does not, if said srf-function is written
in say, C.

However, this is somewhat similar to the WITH LATERAL clause previously
discussed in connection with UNNEST and multisets, so perhaps it's not
such a bad idea after all?

> > For that matter, is this behavior also intentional?
> 
> > 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
> 
> It's an implementation restriction in plpgsql: we didn't make it support
> the old-style SRF API.  I'm unconvinced that it's worth fixing
> considering that this whole behavior (SRFs in the targetlist) is
> deprecated.
> 
>             regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
-- 
John Hansen <john@geeknet.com.au>
GeekNET

On Wed, 2005-02-02 at 23:22 -0500, Tom Lane wrote:
> The syntax you are showing is designed
> to return a scalar.  It will (and should) barf on multiple rows as well
> as multiple columns.

I don't understand; the example I posted is of an SRF that returns
multiple rows of multiple columns, which is transformed into multiple
rows of a single column of composite type.

> I'm unconvinced that it's worth fixing
> considering that this whole behavior (SRFs in the targetlist) is
> deprecated.

It is? I think if we polished it somewhat, this is reasonably cool
functionality to have, and is consistent with the 8.0 work to make
composite types more widely usable.

-Neil

Neil Conway <neilc@samurai.com> writes:
> On Wed, 2005-02-02 at 23:22 -0500, Tom Lane wrote:
>> The syntax you are showing is designed
>> to return a scalar.  It will (and should) barf on multiple rows as well
>> as multiple columns.

> I don't understand; the example I posted is of an SRF that returns
> multiple rows of multiple columns, which is transformed into multiple
> rows of a single column of composite type.

I was speaking of the subselect syntax.

>> I'm unconvinced that it's worth fixing
>> considering that this whole behavior (SRFs in the targetlist) is
>> deprecated.

> It is? I think if we polished it somewhat, this is reasonably cool
> functionality to have, and is consistent with the 8.0 work to make
> composite types more widely usable.

What's deprecated is SETOF functions (ie, multiple return *rows*)
in the targetlist.  Although that may appear to work, it doesn't
actually work very well --- in particular the behavior when you have
more than one in the same targetlist isn't real sensible.  So I'm
not eager to see the subselect syntax extended to allow multiple
rows to be returned, which is the other thing that this line of
argument would lead to.

I don't particularly care one way or the other about allowing a
subselect to return a single row value; I'm just saying that reasoning
from the behavior of SRFs isn't necessarily a good guide to what to do.
There's some legacy behavior there.
        regards, tom lane