Re: Retrieving multiple columns from a subquery

Chris Hanks <christopher.m.hanks@gmail.com> writes:
> Nothing? Are subqueries just not meant to be used this way?

The SQL standard says not ;-).

You could approximate it like this:

    select ..., (select row(x,y,z) from ...), ... from ...;

as long as you don't mind pulling the composite-value output syntax
apart.  This avoids the single-output-column syntactic restriction
by cramming all the values into one column.

[ thinks for a bit... ]  It seems like you ought to be able to get PG
to pull the composite values apart again, with something like

    select ..., (x).*, ... from
    (select ..., (select row(x,y,z) from ...) as x, ...
     from ... offset 0) ss;

but when I try this I get
    ERROR:  record type has not been registered
That's a bug, probably, but dunno how hard to fix.  In the meantime you
could work around it by casting the row() expression to a named
composite type; which might be a good idea anyway since there's no other
obvious way to control the column names that will be exposed by the
(x).* expansion.

            regards, tom lane