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