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
