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

[Greg Stark]

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
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[darren]

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
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Ron]

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.

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Greg Stark]

da****@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
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Ron Johnson]

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. ro***********@cox.net
Jefferson, LA USA

"Perl is worse than Python because people wanted it worse."
Larry Wall, 10/14/1998
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Ron]

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.........
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[DeJuan Jackson]

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 ma*******@postgresql.org)