From: Bryce Nesbitt <bryce1@obviously.com>
To: pgsql-sql@postgresql.org
Subject: Re: Can function results be used in WHERE?
Date: Tue, 11 Jul 2006 08:44:33 -0700
Message-ID: <44B3C761.3060903@obviously.com> (raw)
In-Reply-To: <8088.1152589020@sss.pgh.pa.us>
References: <44B29376.2030107@obviously.com>
<bf05e51c0607101302w271c7773tdec47e76b192f2c9@mail.gmail.com>
<44B2CFC7.7060101@obviously.com>
<8088.1152589020@sss.pgh.pa.us>
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/
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: <44B3C761.3060903@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