From: | DeJuan Jackson <djackson(at)speedfc(dot)com> |
---|---|
To: | |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: is it possible to do this? have a subselect that |
Date: | 2003-09-04 20:34:03 |
Message-ID: | 3F57A1BB.3060603@speedfc.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
>>>> 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...
From | Date | Subject | |
---|---|---|---|
Next Message | Doug McNaught | 2003-09-04 20:40:39 | Re: Backup? |
Previous Message | Stephan Szabo | 2003-09-04 20:26:31 | Re: Optimizer picks an ineffient plan |