Re: Retrieving multiple columns from a subquery - Mailing list pgsql-general

From Chris Hanks
Subject Re: Retrieving multiple columns from a subquery
Date
Msg-id CAK7KUdBkZcm1+7yeD76oExZkvWNn97=uN=N3ANS0YkBcV7L-7g@mail.gmail.com
Whole thread Raw
In response to Re: Retrieving multiple columns from a subquery  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-general


On Mon, May 14, 2012 at 8:36 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
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


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!

pgsql-general by date:

Previous
From: Lee Hachadoorian
Date:
Subject: Re: dblink.sql and Linux
Next
From: "Albe Laurenz"
Date:
Subject: Re: Cannot find installers for 9.2 Beta