Re: Can function results be used in WHERE?

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:


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

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)
stage=# select * from
version();                                                   version
-----------------------------------------------------------------------------------------------------------PostgreSQL
7.4.12on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.3
 
20030502 (Red Hat Linux 3.2.3-20)
(1 row)


The asymetry between HAVING/WHILE and ORDER BY seems odd.  Is there more
to that story?


-- 
----
Visit http://www.obviously.com/