From: Aaron Bono <postgresql@aranya.com>
To: Bryce Nesbitt <bryce1@obviously.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Can function results be used in WHERE?
Date: Wed, 12 Jul 2006 14:22:03 -0500
Message-ID: <bf05e51c0607121222t63f779c2ie5ae327fa0fef3a1@mail.gmail.com> (raw)
In-Reply-To: <44B3C761.3060903@obviously.com>
References: <44B29376.2030107@obviously.com>
<bf05e51c0607101302w271c7773tdec47e76b192f2c9@mail.gmail.com>
<44B2CFC7.7060101@obviously.com>
<8088.1152589020@sss.pgh.pa.us>
<44B3C761.3060903@obviously.com>
On 7/11/06, Bryce Nesbitt <bryce1@obviously.com> wrote:
>
> 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:
As I mentioned before: the only difference between HAVING and WHERE is that
WHERE occurs before a GROUP BY and HAVING occurs after.
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
You cannot use an alias from the select column list in your WHERE clause
because the where is the criteria done BEFORE your columns are pulled out.
This is especially noticable when doing a GROUP BY since the WHERE is done
before the GROUP BY and the returned column values are gathered after the
GROUP BY. If you want to use an alias, do a subquery and then put your
where in the outer query.
I believe the ORDER BY is done last but that may be dependent on the
database implementation. It does make sense to think of ORDER BY to be done
last though. For that reason it can use the alias.
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)
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.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: postgresql@aranya.com, bryce1@obviously.com
Subject: Re: Can function results be used in WHERE?
In-Reply-To: <bf05e51c0607121222t63f779c2ie5ae327fa0fef3a1@mail.gmail.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