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 737579FB2F4 for ; Mon, 10 Jul 2006 15:19:40 -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 27720-07 for ; Mon, 10 Jul 2006 15:19:34 -0300 (ADT) X-Greylist: delayed 00:28:33.804512 by SQLgrey- Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by postgresql.org (Postfix) with ESMTP id 3FF8C9FB341 for ; Mon, 10 Jul 2006 15:19:30 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.3 Received: from outboundG.internet-mail-service.net (outboundG.internet-mail-service.net [216.240.47.207]) by svr4.postgresql.org (Postfix) with ESMTP id 45D1D5AF025 for ; Mon, 10 Jul 2006 17:50:56 +0000 (GMT) Received: from idiom.com (idiom.com [216.240.32.1]) by outbound.internet-mail-service.net (Postfix) with ESMTP id F3BDE3105DA for ; Mon, 10 Jul 2006 10:50:53 -0700 (PDT) Received: from [192.168.1.109] (adsl-71-135-172-180.dsl.pltn13.pacbell.net [71.135.172.180]) by idiom.com (8.12.11/8.12.11) with ESMTP id k6AHorMQ048256 for ; Mon, 10 Jul 2006 10:50:53 -0700 (PDT) (envelope-from bryce1@obviously.com) Message-ID: <44B29376.2030107@obviously.com> Date: Mon, 10 Jul 2006 10:50:46 -0700 From: Bryce Nesbitt User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.7.13) Gecko/20060411 X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: Can function results be used in WHERE? Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200607/153 X-Sequence-Number: 25727 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/