X-Original-To: pgsql-sql-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id A378C9FB278 for ; Mon, 10 Jul 2006 17:02:16 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 65152-05 for ; Mon, 10 Jul 2006 17:02:13 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from nf-out-0910.google.com (nf-out-0910.google.com [64.233.182.185]) by postgresql.org (Postfix) with ESMTP id 18C149FB274 for ; Mon, 10 Jul 2006 17:02:12 -0300 (ADT) Received: by nf-out-0910.google.com with SMTP id l37so566532nfc for ; Mon, 10 Jul 2006 13:02:11 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:references:x-google-sender-auth; b=N+ApWBPqiSxddwWo4ivGTBqWvRFEhKdWbdRHeTyytTOM0ICP4vKpR7yl1UZ0ZPimO1QlqpZoD8c3dEtN7ujYRJEAmk1dzZMYOnhugs6rTThCF5Nad1dgc6y6F1JlaWxdPDTTPHd0PC8YdARQ9g5+FdS1J4K/IUOULZAz7hTbVMw= Received: by 10.78.159.7 with SMTP id h7mr1850291hue; Mon, 10 Jul 2006 13:02:11 -0700 (PDT) Received: by 10.78.29.2 with HTTP; Mon, 10 Jul 2006 13:02:11 -0700 (PDT) Message-ID: Date: Mon, 10 Jul 2006 15:02:11 -0500 From: "Aaron Bono" To: "Bryce Nesbitt" Subject: Re: Can function results be used in WHERE? Cc: pgsql-sql@postgresql.org In-Reply-To: <44B29376.2030107@obviously.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_38488_25606268.1152561731148" References: <44B29376.2030107@obviously.com> X-Google-Sender-Auth: 0d3b0c29473abe74 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200607/158 X-Sequence-Number: 25732 ------=_Part_38488_25606268.1152561731148 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline On 7/10/06, Bryce Nesbitt 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 ------=_Part_38488_25606268.1152561731148 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline On 7/10/06, Bryce Nesbitt <bryce1@obviously.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

------=_Part_38488_25606268.1152561731148--