Re: Wishlist: subqueries that return multiple columns

> 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