Re: Can function results be used in WHERE?

On 7/10/06, Bryce Nesbitt <bryce1(at)obviously(dot)com> wrote:
>
> 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