Re: aliases, &c in HAVING clause?

David Fetter <david@fetter.org> writes:
> Just in general, isn't it better to write a piece of code (here, a
> possibly-complicated aggregate) just once and refer to it elsewhere
> rather than have to write a separate copy of it everywhere it's used?

In general, you do that with subselects.  Having inconsistent scoping
rules for ORDER BY is a much less desirable way to attack it.
SELECT * FROM  (SELECT foobar(baz) AS x FROM ...) ssGROUP BY x HAVING x > 44;

>> But the real reason why this is bogus is that it violates the
>> fundamental conceptual model of how SELECT works.  The SELECT output
>> list is not supposed to be computed until after all the other steps
>> are complete, and therefore it's improper to assume its results are
>> available in GROUP BY or HAVING.

> but I'm pretty certain that PostgreSQL doesn't do things that way at
> the implementation level.

It does anywhere that you can tell the difference.  Try a SELECT with
side-effect-producing output expressions.  As an example, would you be
happy if the following were prone to getting divide-by-zero errors?
SELECT x, 1.0 / sum(x) FROM t GROUP BY x HAVING sum(x) != 0;
        regards, tom lane