Re: Can function results be used in WHERE?

On 7/10/06, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
>
> But as far as the underlying misconception goes, you seem to think that
> "4" in the WHERE clause might somehow be taken as referring to the
> fourth SELECT result column (why you don't think that the "1" would
> likewise refer to the first result column isn't clear). This is not so.
> "4" means the numeric value four. There is a special case in ORDER BY
> and GROUP BY that an argument consisting of a simple integer literal
> constant will be taken as a reference to an output column. This is an
> ugly kluge IMHO, but it's somewhat defensible on the grounds that
> neither ordering nor grouping by a simple constant has any possible
> real-world use; so the special case doesn't break anything of interest.
> This would certainly not be so if we were to randomly replace integer
> constants in general WHERE conditions with non-constant values.

I agree whole heartedly with Tom, using the number in the ORDER BY is ugly
and not recommended. Using column names is much easier to read and is much
more maintainable by team members. I have to admit the 4 < 1 did confuse me
at first.

-Aaron Bono