X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 1C11153CD5 for ; Sat, 9 Apr 2005 02:48:00 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 02882-09 for ; Sat, 9 Apr 2005 01:47:58 +0000 (GMT) Received: from fed1rmmtao09.cox.net (fed1rmmtao09.cox.net [68.230.241.30]) by svr1.postgresql.org (Postfix) with ESMTP id C94B753CB2 for ; Sat, 9 Apr 2005 02:47:57 +0100 (BST) Received: from KARENSLAPTOP ([70.176.123.3]) by fed1rmmtao09.cox.net (InterMail vM.6.01.04.00 201-2131-118-20041027) with SMTP id <20050409014801.XJIH19936.fed1rmmtao09.cox.net@KARENSLAPTOP> for ; Fri, 8 Apr 2005 21:48:01 -0400 From: "Bill Lawrence" To: Subject: Getting the output of a function used in a where clause Date: Fri, 8 Apr 2005 18:43:39 -0700 Message-ID: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0030_01C53C6A.E15CEA50" X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2911.0) Importance: Normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE X-Spam-Level: X-Archive-Number: 200504/144 X-Sequence-Number: 21036 This is a multi-part message in MIME format. ------=_NextPart_000_0030_01C53C6A.E15CEA50 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 8bit 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 ------=_NextPart_000_0030_01C53C6A.E15CEA50 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

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 =3D “SELECT =
zipcode from zipcodes where zipdist($lat1d,$lon1d,lat,long) <=3D =
$dist;”;

<=
font
size=3D2 color=3Dblack face=3DArial>What I want to write is =
something like:

<=
span
class=3DEmailStyle15>$sql =3D “SELECT zipcode, distance from =
zipcodes where distance <=3D $dist =
order by distance;”;

<=
font
size=3D2 color=3Dblack face=3DArial>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?

<=
font
size=3D2 color=3Dblack face=3DArial>Thanks in =
advance!

<=
font
size=3D2 color=3Dblack face=3DArial>Bill

------=_NextPart_000_0030_01C53C6A.E15CEA50--