>>>> So I have a query in which some of the select values are
>>>> subqueries. The
>>>> subqueries are aggregates so I don't want to turn this into a join,
>>>> it would
>>>> become too complex and postgres would have trouble optimizing things.
>>>>
>>>> So my question is, is there some way to have a subselect return
>>>> multiple
>>>> columns and break those out in the outer query?
>>>>
>>>> Something like:
>>>>
>>>> SELECT x,y,z, (SELECT a,b FROM foo) AS (sub_a,sub_b) FROM tab
>>>>
Assuming the select from foo only returns 1 row, see if this works for
you and can be planned effectively.
SELECT x, y, z, sub_a, sub_b
FROM (SELECT a,b FROM foo) t1(sub_a, sub_b),
(SELECT x, y, z FROM tab) t2
If a or b is aggregates and the foo subselect will return more than one
row (ie SELECT a , count(DISTINCT b) FROM foo GROUP BY a), then you
would need to have a JOIN field, or settle for a cartesian(sp?) product.
SELECT x, y, z, a, sub_b
FROM (SELECT a, sum(b) FROM foo GROUP BY a) t1(a, sub_b)
JOIN (SELECT a, x, y, z FROM tab) t2 USING(a)
hope this helps...
