Re: is it possible to do this? have a subselect that returns two columns

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