I've several times wanted a way to add multiple select output columns
using a
single expression. A typical scenario would be if the columns come
from a
subselect from another table where repeating the subselect means slow
performance as well as awkward and repetitive code.

Sometimes the subselect can be rewritten as a join, but that is not
always the
case. Consider something like:

select customer.*,
(select avg(amount),sum(amount) from purchases
where purchases.customer_id = customer.customer_id
) as (avg_purchase, total_purchase),
(select avg(amount),sum(amount) from quotes
where quotes.customer_id = customer.customer_id
) as (avg_quote, total_quote)
from customer

(Ok, actually that could be done as a join using some trickery with
GROUP BY,
but I have other scenarios where it can't because the subselects
overlap.)
Yes !! this would be very useful.
I am using such constructs a lot for crosstabs with different time
periods for ex. (lots of subselects) and then doing some simple math
with the resulting columns, ratios for ex.
With the new support for complex data types like arrays and structures
perhaps
I could do this by constructing a RECORD in each subselect and then
wrapping
another layer around the query where I explicitly list each element of
the
RECORD that I want to include in the result set.

But it would be nice to have some more convenient mechanisms for
handling this
case.

--
greg
Philippe Schmid

Search Discussions

Discussion Posts

Previous

Related Discussions

Discussion Navigation
viewthread | post
posts ‹ prev | 2 of 2 | next ›
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedNov 25, '04 at 8:30p
activeNov 25, '04 at 8:49p
posts2
users2
websitepostgresql.org...
irc#postgresql

2 users in discussion

Greg Stark: 1 post Philippe Schmid: 1 post

People

Translate

site design / logo © 2021 Grokbase