plpgsql locate provider by zip code - Mailing list pgsql-novice

I spent the last 15 hours writing this function.  It took me all day to
figure it out.  This is my first postgresql function.

I have a zip code database (the free one).  I wanted to locate providers
by zip code, and also have the distance to each provider.  I had a PHP
script that did this well, but I needed to move it into the database to
make it easy for the IVR system.  This is what I did:

/* My database of zips */
CREATE TABLE "zips" (
   "zip" int8 NOT NULL,
   "state" char(2) DEFAULT 'NA' NOT NULL,
   "city" varchar(50) DEFAULT 'NA' NOT NULL,
   "location" point DEFAULT '(0,0)' NOT NULL -- (longitude,latitude)
);

/* Earthdistance from Postgresql contrib */
CREATE FUNCTION geo_distance (point, point) RETURNS float8
  AS '/usr/local/pgsql/share/contrib/earthdistance.so' LANGUAGE 'c';

CREATE OPERATOR <@> (
  leftarg = point,
  rightarg = point,
  procedure = geo_distance,
  commutator = <@>
);


I figured out this query to select my providers:

/* get my location by zip */
select location from zips where zip=95661;

/* using my location get 10 providers */
select p.name, p.city, p.phone1, '(121.234,38.7346)'::point <@> location as distance
from zips z, providers p
where '(121.234,38.7346)'::point <@> location < 50
and z.zip = p.zip
order by distance
limit 10;

This worked well, now time to move it into the database:

create function locate_providers (int8,int2) returns text as '
declare
 source_zip alias for $1;
 offset alias for $2;
 source_point point;
 result record;
begin
  -- Get the source point by zip
  select into source_point location from zips where zip = source_zip;

  if not found
  then return ''Error: ZIP not found'';
  end if;

  -- Get the providers one at a time by offset
  select into result
    trim(p.name)::text as name, trim(p.address1)::text as address,
    p.phone1::text as phone, trim(p.city)::text as city,
    round(source_point <@> location)::text as distance
  from zips z, providers p
  where source_point <@> location < 50 and z.zip = p.zip
  order by distance
  limit 1, offset;

  -- Return
  if found
    then return result.name || '', '' || result.address || '', '' ||
                result.city || '', '' || result.phone || '', '' ||
                result.distance || '' Miles'';
  end if;

  -- if nothing found
  return ''Error: Provider Not Found'';
end;'
language 'plpgsql';


Now I could issue:

    select locate_providers(95661,0);
    select locate_providers(95661,1);
    select locate_providers(95661,2);
    ...
    select locate_providers(95661,10);

and get back each provider by distance.

It works well for me, probably a few things wrong with it, but I'm a
novice! 8-)  I hope this helps some of the people out there trying to
figure out this stuff.

--
 _   |       _
(_()(|('.|)('||.|()|`|(