Message-ID: <44B3C761.3060903@obviously.com>
Date: Tue, 11 Jul 2006 08:44:33 -0700
From: Bryce Nesbitt <bryce1@obviously.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.7.13) Gecko/20060411
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Re: Can function results be used in WHERE?
References: <44B29376.2030107@obviously.com>
	<bf05e51c0607101302w271c7773tdec47e76b192f2c9@mail.gmail.com>
	<44B2CFC7.7060101@obviously.com> <8088.1152589020@sss.pgh.pa.us>
In-Reply-To: <8088.1152589020@sss.pgh.pa.us>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

Tom Lane wrote:
> But as far as the underlying misconception goes, you seem to think that
> "4" in the WHERE clause might somehow be taken as referring to the
> fourth SELECT result column (why you don't think that the "1" would
> likewise refer to the first result column isn't clear).  This is not so.
> "4" means the numeric value four.  There is a special case in ORDER BY
> and GROUP BY that an argument consisting of a simple integer literal
> constant will be taken as a reference to an output column.  This is an
> ugly kluge IMHO...
Yeah, it was a longshot.  I only tried it because the column label did
NOT work, and I had some gut reaction to repeating the same function twice:


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 calculate_distance(lat,lon, 37.789629,-122.422082) < 1 ORDER BY
dist desc limit 5;
 pod_code |    lat    |     lon     |       dist
----------+-----------+-------------+-------------------
        5 | 37.792022 | -122.404247 | 0.988808031847045
       62 | 37.780166 | -122.409615 | 0.944907273102541
        4 | 37.798528 | -122.409582 | 0.919592583879426
       86 | 37.777529 | -122.417982 | 0.866416010967029
       68 | 37.789915 | -122.406926 |  0.82867104307647
(5 rows)
stage=# select * from
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)


The asymetry between HAVING/WHILE and ORDER BY seems odd.  Is there more
to that story?


-- 
----
Visit http://www.obviously.com/