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.)

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


Search Discussions

Discussion Posts

Follow ups

Related Discussions

Discussion Navigation
viewthread | post
posts ‹ prev | 1 of 2 | next ›
Discussion Overview
grouppgsql-hackers @
postedNov 25, '04 at 8:30p
activeNov 25, '04 at 8:49p

2 users in discussion

Greg Stark: 1 post Philippe Schmid: 1 post



site design / logo © 2021 Grokbase