I have a function, the results of which seem to apply to ORDER BY and
HAVING, but not to WHERE. Is this expected?
-- Return distance in some mystery units (TODO: convert to miles or
kilometers)
CREATE FUNCTION calculate_distance(double precision, double precision,
double precision, double precision) RETURNS double precision AS '
BEGIN RETURN (3963 * acos( sin($1/57.2958) * sin($3/57.2958) +
cos($1/57.2958) * cos($3/57.2958) * cos($4/57.2958 - $2/57.2958) ));
END;
' LANGUAGE plpgsql;
demo=# select
pod_code,lat,lon,calculate_distance(lat,lon,37.789629,-122.422082) from
eg_pod where 4 > 5::double precision order by 4 limit 10;pod_code | lat | lon | calculate_distance
----------+-----------+-------------+-------------------- 44 | 0 | 0 | 0
45| 0 | 0 | 0 69 | 37.789629 | -122.422082 | 0 51 |
37.788166| -122.421488 | 0.106273303754946 71 | 37.794228 | -122.421382 | 0.320393524437476 73 | 37.787878
|-122.411644 | 0.583267064983836 37 | 37.791736 | -122.411604 | 0.590977027054446 46 | 37.784929 |
-122.412782| 0.603416307249032 50 | 37.780329 | -122.418482 | 0.672685350683496 30 | 37.780419 |
-122.417764| 0.679355355047995
(10 rows)
sdemo=# select
pod_code,lat,lon,calculate_distance(lat,lon,37.789629,-122.422082) from
eg_pod having calculate_distance(lat,lon,37.789629,-122.422082) > 5
order by 4;pod_code | lat | lon | calculate_distance
----------+-----------+-------------+-------------------- 21 | 37.710581 | -122.468864 | 6.03655070159813
77| 37.805427 | -122.29528 | 7.01595024232628 29 | 37.802684 | -122.275976 | 8.0364304687727 12 |
37.806133| -122.273827 | 8.18282157050301 23 | 37.797327 | -122.26598 | 8.54878571904839 57 | 37.829592
|-122.266347 | 8.94791199923289 35 | 37.809327 | -122.25448 | 9.26077996779577 47 | 37.851957 |
-122.270376| 9.34292370436932
demo=# select 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)
--
----
Visit http://www.obviously.com/
