From: Bryce Nesbitt <bryce1@obviously.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Can function results be used in WHERE?
Date: Mon, 10 Jul 2006 15:08:07 -0700
Message-ID: <44B2CFC7.7060101@obviously.com> (raw)
In-Reply-To: <bf05e51c0607101302w271c7773tdec47e76b192f2c9@mail.gmail.com>
References: <44B29376.2030107@obviously.com>
<bf05e51c0607101302w271c7773tdec47e76b192f2c9@mail.gmail.com>
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/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: bryce1@obviously.com
Subject: Re: Can function results be used in WHERE?
In-Reply-To: <44B2CFC7.7060101@obviously.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox