From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | David Fetter <david(at)fetter(dot)org> |
Cc: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: aliases, &c in HAVING clause? |
Date: | 2004-02-24 00:09:22 |
Message-ID: | 11233.1077581362@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
David Fetter <david(at)fetter(dot)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 ...) ss
GROUP 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
From | Date | Subject | |
---|---|---|---|
Next Message | Simon Riggs | 2004-02-24 00:17:28 | Re: Progress Report on Materialized Views |
Previous Message | David Fetter | 2004-02-23 23:57:13 | Re: aliases, &c in HAVING clause? |