Обсуждение: is it possible to do this? have a subselect that returns two columns

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

I don't think it's possible but it would simplify my life a whole heck of a
lot if it was, so I figured I would double-check before tearing my hair out.

--
greg

You could try using it as a dynamic select as shown in the query below.
This would give you the answer by you would have to have a binding between
tab and the dynamic table z i believe

SELECT x,y,z,
       z.a,
       z.b
FROM tab,
     (SELECT a,b FROM foo) z

HTH
Darren

On 3 Sep 2003, Greg Stark wrote:

>
> 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
>
> I don't think it's possible but it would simplify my life a whole heck of a
> lot if it was, so I figured I would double-check before tearing my hair out.
>
>

--
Darren Ferguson

see below....

Greg Stark wrote:

>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
>

SELECT x, y, z, SS.*
    FROM tab, (SELECT a,b FROM foo) SS

>
>I don't think it's possible but it would simplify my life a whole heck of a
>lot if it was, so I figured I would double-check before tearing my hair out.
>
>
>

darren@crystalballinc.com writes:
>
> On 3 Sep 2003, Greg Stark wrote:
>
> > 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.
>
> You could try using it as a dynamic select as shown in the query below.
> This would give you the answer by you would have to have a binding between
> tab and the dynamic table z i believe

What you describe as a "dynamic select" is more precisely a "view" and turns
the query into a join, which is what I explained I didn't want to do.

To give a better idea why I don't want to do it, try using that approach for a
more complex example:

SELECT x,y,z, count(*) as n
       (select a,count(*) as b from foo where b.x=tab.x group by a) as (a,b),
       (select c,count(g) as d from bar where c.y=tab.y group by c) as (c,d)
 FROM tab
GROUP BY x,y,z

The only way to turn that into a join is to do make both views aggregates like
this:

SELECT x,y,z,count(*) as n, a,b,c,d
  FROM tab
  JOIN (select x,a,count(*) as b from foo group by x) AS foo USING (x)
  JOIN (select x,c,count(g) as d from bar group by x) AS bar USING (x)
 GROUP BY x,y,z

However as I showed in another thread, postgres will be incapable of using an
index on x to do this join, leading it to have to do a full seq scan of both b
and d and calculate the aggregates on the entire table. That's what I meant by
"it would become too complex and postgres would have trouble optimizing
things"

--
greg

On Wed, 2003-09-03 at 13:49, Ron wrote:
> see below....
>
> Greg Stark wrote:
>
> >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
> >
>
> SELECT x, y, z, SS.*
>     FROM tab, (SELECT a,b FROM foo) SS

But where's the join between tab and foo?  Wouldn't you then get
a combinatorial explosion?

--
-----------------------------------------------------------------
Ron Johnson, Jr. ron.l.johnson@cox.net
Jefferson, LA USA

"Perl is worse than Python because people wanted it worse."
Larry Wall, 10/14/1998

Ron Johnson wrote:

>On Wed, 2003-09-03 at 13:49, Ron wrote:
>
>
>>see below....
>>
>>Greg Stark wrote:
>>
>>
>>
>>>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
>>>
>>>
>>>
>>SELECT x, y, z, SS.*
>>    FROM tab, (SELECT a,b FROM foo) SS
>>
>>
>
>But where's the join between tab and foo?  Wouldn't you then get
>a combinatorial explosion?
>
>
>
Oops, forgot to put the join in. And having re-read the original post I
can see that's what Greg was wanting to avoid. I'll just crawl back to
my corner now.........

>>>> 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...