From: Bill Lawrence <bill.lawrence@cox.net>
To: pgsql-sql@postgresql.org
Subject: Getting the output of a function used in a where clause
Date: Fri, 8 Apr 2005 18:43:39 -0700
Message-ID: <NEBBJBFOALCOMIDOAMHCCELJDAAA.bill.lawrence@cox.net> (raw)
HI,
I’m a newbie so please bear with me. I have a function defined (got it from
one of your threads… thanks Joe Conway) which calculates the distance
between 2 zip code centeroids (in lat,long). This thing works great.
However, I want to sort my results by distance without incurring the
additional burden of executing the function twice. A simplified version of
my current SQL (written in a perl cgi) that returns a set of zip codes
within a given radius is:
$sql = “SELECT zipcode from zipcodes where zipdist($lat1d,$lon1d,lat,long)
<= $dist;”;
What I want to write is something like:
$sql = “SELECT zipcode, distance from zipcodes where distance <= $dist order
by distance;”;
But I don’t the magic SQL phrase to populate the distance variable using my
nifty function. Do I need to create an output type for distance?
Thanks in advance!
Bill
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: bill.lawrence@cox.net
Subject: Re: Getting the output of a function used in a where clause
In-Reply-To: <NEBBJBFOALCOMIDOAMHCCELJDAAA.bill.lawrence@cox.net>
* 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