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

  • Philippe Schmid at Nov 25, 2004 at 8:49 pm

    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

Related Discussions

Discussion Navigation
viewthread | post
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