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

--
greg

Search Discussions

Discussion Posts

Follow ups

Related Discussions

Discussion Navigation
viewthread | post
posts ‹ prev | 1 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