Re: Retrieving multiple columns from a subquery

On Mon, May 14, 2012 at 8:36 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Chris Hanks <christopher(dot)m(dot)hanks(at)gmail(dot)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
>

Thanks, I tried playing with the row function a bit. It gave me the idea to
try:

SELECT *, (SELECT ARRAY[address, (confirmed_at is not null)::text]
FROM "emails"
WHERE ("user_id" = "id")
ORDER BY "confirmed_at" DESC NULLS LAST, "created_at" DESC LIMIT 1) as
best_email
FROM "users"

Since my ORM already handles Postgres arrays for me, this winds up being a
bit easier to handle in my app. It's a bit ugly, but it works. I'll keep
the idea of the named composite type around in case I need to revisit this
later, though.

Thanks again for the advice!