Re: Can function results be used in WHERE?

On 7/11/06, Bryce Nesbitt <bryce1(at)obviously(dot)com> wrote:
>
> Tom Lane 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...
> Yeah, it was a longshot. I only tried it because the column label did
> NOT work, and I had some gut reaction to repeating the same function
> twice:

As I mentioned before: the only difference between HAVING and WHERE is that
WHERE occurs before a GROUP BY and HAVING occurs after.

stage=# SELECT pod_code, lat, lon,
> calculate_distance(lat,lon,37.789629,-122.422082) as dist FROM eg_pod
> WHERE dist < 1 ORDER BY dist desc limit 10;
> ERROR: column "dist" does not exist

You cannot use an alias from the select column list in your WHERE clause
because the where is the criteria done BEFORE your columns are pulled out.
This is especially noticable when doing a GROUP BY since the WHERE is done
before the GROUP BY and the returned column values are gathered after the
GROUP BY. If you want to use an alias, do a subquery and then put your
where in the outer query.

I believe the ORDER BY is done last but that may be dependent on the
database implementation. It does make sense to think of ORDER BY to be done
last though. For that reason it can use the alias.

stage=# SELECT pod_code, lat, lon,
> calculate_distance(lat,lon,37.789629,-122.422082) as dist FROM eg_pod
> WHERE calculate_distance(lat,lon, 37.789629,-122.422082) < 1 ORDER BY
> dist desc limit 5;
> pod_code | lat | lon | dist
> ----------+-----------+-------------+-------------------
> 5 | 37.792022 | -122.404247 | 0.988808031847045
> 62 | 37.780166 | -122.409615 | 0.944907273102541
> 4 | 37.798528 | -122.409582 | 0.919592583879426
> 86 | 37.777529 | -122.417982 | 0.866416010967029
> 68 | 37.789915 | -122.406926 | 0.82867104307647
> (5 rows)

==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
==================================================================