Skip site navigation (1) Skip section navigation (2)

Re: Wishlist: subqueries that return multiple columns

From: Philippe Schmid <phschmid(at)cadinfo(dot)ch>
To: Greg Stark <gsstark(at)mit(dot)edu>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Wishlist: subqueries that return multiple columns
Date: 2004-11-25 20:49:27
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-hackers
> 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 
> 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

In response to

pgsql-hackers by date

Next:From: Gaetano MendolaDate: 2004-11-26 00:05:04
Subject: Re: Beta5 now Available
Previous:From: Greg StarkDate: 2004-11-25 20:29:55
Subject: Wishlist: subqueries that return multiple columns

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group