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...
---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
