WITH AS vs subselect was: count (DISTINCT expression [ , ... ] ) and documentation

On Fri, 26 Dec 2008 12:04:48 -0800
David Fetter <david(at)fetter(dot)org> wrote:

> On Fri, Dec 26, 2008 at 08:03:30PM +0100, Ivan Sergio Borgonovo
> wrote:

> > > > aggregate_name (DISTINCT expression [, expression] )

> > > In 8.4, you'll be able to do:

> > > WITH d AS (
> > > SELECT DISTINCT c1, c2 FROM table1
> > > )
> > > SELECT count(*) FROM d;

> > Nice, but what will be the difference from
> > select count(*) from (select distinct c1, c2 from t);
> > ?
> > Optimisation?

> None especially.

So what would be the advantage compared to subselect?

> > Furthermore... I was actually looking at docs because I needed to
> > find a way supported by both postgresql and mysql
>
> Generally, it's *not* a good idea to try to support more than one
> back-end. You wind up maintaining several disparate code bases,

Not really my main target... I was just investigating if it could
come for free ;)

--
Ivan Sergio Borgonovo
http://www.webthatworks.it