Re: Getting the output of a function used in a where clause - Mailing list pgsql-sql

On Tue, 2005-04-12 at 23:08 -0700, Bill Lawrence wrote:
> Thanks,
> 
> Unfortunately, I think that solution requires the distance calculation to be
> executed twice for each record in the table. There are ~70K records in the
> table. Is the postgres query optimizer smart enough to only perform the
> calculation once?

It is in some places, but possibly not in that one.

You can force it with a subselect though:              SELECT * FROM (SELECT zipcode, zipdist($lat1d,$lon1d,lat,long)
   as distance       from zipcodes) AS tab where distance <= $dist;
 

> Bill
> 
> 
> 
> -----Original Message-----
> From: Scott Marlowe [mailto:smarlowe@g2switchworks.com]
> Sent: Tuesday, April 12, 2005 6:55 AM
> To: Bill Lawrence
> Cc: PFC; pgsql-sql@postgresql.org
> Subject: Re: [SQL] Getting the output of a function used in a where clause
> 
> Why not just do:
> 
> SELECT zipcode, zipdist($lat1d,$lon1d,lat,long) as distance from
> zipcodes where zipdist($lat1d,$lon1d,lat,long) <= $dist;";
> 
> 
> On Mon, 2005-04-11 at 20:25, Bill Lawrence wrote:
> > Boy I sure thought that would work... I received the following from
> postgres:
> >
> > ERROR:  Attribute "distance" not found.
> >
> > Started looking into gist.... Looks complex.
> >
> > Any other ideas?
> >
> >
> > -----Original Message-----
> > From: PFC [mailto:lists@boutiquenumerique.com]
> > Sent: Monday, April 11, 2005 1:51 AM
> > To: Bill Lawrence; pgsql-sql@postgresql.org
> > Subject: Re: [SQL] Getting the output of a function used in a where clause
> >
> >
> > try:
> >
> > SELECT zipcode, zipdist($lat1d,$lon1d,lat,long) as distance from zipcodes
> > where distance <= $dist;";
> >
> > OR you could use a gist index with a geometric datatype to get it a lot
> > faster.
> >
> >
> > On Sat, 09 Apr 2005 03:43:39 +0200, Bill Lawrence <bill.lawrence@cox.net>
> > wrote:
> >
> > > 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:
> > >
> > >
> > > 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
> > >
> > >
> > >
> >
> >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 7: don't forget to increase your free space map settings
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
> 
--