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.12 on 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/
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
