Re: Can function results be used in WHERE?

Aaron Bono wrote:
> First I recommend making your function IMMUTABLE since, given the same
> arguments, it gives the same result - this will allow PostgreSQL to
> optimize the function call and cache the results. 
Will do!

> Then, don't use "4", use "calculate_distance(lat,lon,
> 37.789629,-122.422082)".  That use is very ambiguous and subject to
> breaking if you change the columns in your select.  It may also be the
> reason you have a problem though I don't use that syntax so cannot be
> sure.
I think it is ugly also, but no other syntax seems to work:

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 4 < 1 order by dist desc limit 10;
pod_code |    lat    |     lon     |       dist
----------+-----------+-------------+------------------      20 |         1 |           1 | 7962.56837300854      36 |
37.39424| -122.077673 | 33.2296275931303      45 | 37.426929 | -122.161922 | 28.8542985664155      44 | 37.422813 |
-122.172403| 28.8253772580912      22 | 37.444638 | -122.156875 | 27.9378660315883      34 | 37.875915 | -122.257427 |
10.7947710258918     81 | 37.903325 |  -122.29963 |  10.323500058406      33 | 37.868001 | -122.261818 |
10.2977353566856     17 | 37.873002 |  -122.26968 | 10.1277713471574      14 | 37.869574 | -122.267937 |
10.0742861708283
(10 rows)


> The only difference between HAVING and WHERE is that WHERE occurs
> before a GROUP BY and HAVING occurs after.  Since you have no GROUP BY
> there should be no difference in the queries.  The only other
> difference is the "4 > 5::double precision" so that is where I would
> start.
WHERE does nothing in my example.
HAVING filters the results according to distance.
So there's got to be more to it.


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