Re: Can function results be used in WHERE? - Mailing list pgsql-sql
| From | Aaron Bono |
|---|---|
| Subject | Re: Can function results be used in WHERE? |
| Date | |
| Msg-id | bf05e51c0607101302w271c7773tdec47e76b192f2c9@mail.gmail.com Whole thread Raw |
| In response to | Can function results be used in WHERE? (Bryce Nesbitt <bryce1@obviously.com>) |
| Responses |
Re: Can function results be used in WHERE?
|
| List | pgsql-sql |
On 7/10/06, Bryce Nesbitt <bryce1@obviously.com> 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. 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.
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.
-Aaron
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)
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. 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.
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.
-Aaron