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 2608E9FB2E2 for ; Tue, 11 Jul 2006 00:21:44 -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 55638-04 for ; Tue, 11 Jul 2006 00:21:36 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from nf-out-0910.google.com (nf-out-0910.google.com [64.233.182.186]) by postgresql.org (Postfix) with ESMTP id E55AB9FB2A6 for ; Tue, 11 Jul 2006 00:21:35 -0300 (ADT) Received: by nf-out-0910.google.com with SMTP id n15so106797nfc for ; Mon, 10 Jul 2006 20:21:34 -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=Ym79n6RNvtMIPL7AtFstQrUyj6RmietiTvha5a4PZPELzQBYHBETwt5Qj+d4uPQXdZ83jL4N6pZF4xtYbNGD7mBx7bx2fxwSeS6j5VpQMuF5ZzyNXGkSfryuaf9kLyUDOXAFS8vM9lOcpwwlc1jGtQebTKiQNVIbPvaBMTUN5Bs= Received: by 10.78.151.15 with SMTP id y15mr1974532hud; Mon, 10 Jul 2006 20:21:34 -0700 (PDT) Received: by 10.78.29.2 with HTTP; Mon, 10 Jul 2006 20:21:34 -0700 (PDT) Message-ID: Date: Mon, 10 Jul 2006 22:21:34 -0500 From: "Aaron Bono" To: "Bryce Nesbitt" Subject: Re: Can function results be used in WHERE? Cc: pgsql-sql@postgresql.org In-Reply-To: <44B2EFBC.1090201@obviously.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_43997_10648922.1152588094408" References: <44B29376.2030107@obviously.com> <44B2CFC7.7060101@obviously.com> <44B2EFBC.1090201@obviously.com> X-Google-Sender-Auth: c5e3b365bf26aead X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200607/167 X-Sequence-Number: 25741 ------=_Part_43997_10648922.1152588094408 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline On 7/10/06, Bryce Nesbitt wrote: > > Aaron Bono wrote: > > > > > > On 7/10/06, *Bryce Nesbitt* > > wrote: > > > > > > I think it is ugly also, but no other syntax seems to work: > > > > 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 > > > > > > 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 calculate_distance(lat,lon,37.789629,-122.422082) desc limit > 10; > Yep, that works. I guess with IMMUTABLE it's even effecient. > But I have to pass 6 arguments, not 2. Is there a way to make it look > cleaner? > I had expected using the column label (e.g. "dist") to work with WHERE, > just as it does with ORDER BY. You can also try SELECT pod_code, lat, lon, dist FROM ( SELECT pod_code, lat, lon, calculate_distance(lat,lon,37.789629,-122.422082) as dist FROM eg_pod ) eg_prod WHERE dist < 1 ORDER BY dist desc limit 10; If the 37.789629 and -122.422082 are static values you can create a view for it. Otherwise you can create a function or stored procedure that takes 2 arguments and returns the results of the subquery. Just some options. Not sure which you would prefer. I am sure there are more ways to do it. -Aaron Bono ------=_Part_43997_10648922.1152588094408 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:
Aaron Bono wrote:
>
>
> On 7/10/06, *Bryce Nesbitt* <bryce1@obviously.com
> <mailto:bryce1@obviously.com>> wrote:
>
>
>     I think it is ugly also, but no other syntax seems to work:
>
>     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
>
>
> 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 calculate_distance(lat,lon,37.789629,-122.422082) desc limit 10;
Yep, that works.  I guess with IMMUTABLE it's even effecient.
But I have to pass 6 arguments, not 2.  Is there a way to make it look
cleaner?
I had expected using the column label (e.g. "dist") to work with WHERE,
just as it does with ORDER BY.

You can also try

SELECT
   pod_code,
   lat,
   lon,
   dist
FROM (
   SELECT
      pod_code,
      lat,
      lon,
      calculate_distance(lat,lon,37.789629,-122.422082) as dist
   FROM eg_pod
) eg_prod
WHERE dist < 1
ORDER BY dist desc limit 10;

If the 37.789629 and -122.422082 are static values you can create a view for it.  Otherwise you can create a function or stored procedure that takes 2 arguments and returns the results of the subquery.

Just some options.  Not sure which you would prefer.  I am sure there are more ways to do it.

-Aaron Bono
 

------=_Part_43997_10648922.1152588094408--