Postgres Pro
Downloads
Home   >   mailing lists

Thread: Point and function help

Point and function help

From
"Andy Lewis"
Date:
Hello all merry XMAS!
 
I'm trying to create a function that will return a point and having little luck in returning results.
Basically I have a zip code DB complete with city, state and zip pre-populated.
 
What I would like to do is create this function:
 
CREATE OR REPLACE FUNCTION public.map_point(pg_catalog.varchar, pg_catalog.varchar, pg_catalog.varchar)
  RETURNS point AS
'SELECT map_loc from zip_code where zip = \'$3\' and lower(state) = lower(\'$2\') and lower(city) = lower(\'$1\')'
  LANGUAGE 'sql' VOLATILE;
 
And I have no problems creating this function however, I can't get it to return any thing from my zip_code table.
 
Am I doing something wrong?
 
Here's a snippet of the zip_code table:
 
       Table "public.zip_code"
Column   |          Type          | Modifiers
-----------+------------------------+-----------
 city      | character varying(100) |
 state     | character varying(2)   |
 zip       | character varying(10)  |
 area_code | character varying(3)   |
 map_loc   | point                  |
 
    city    | state |  zip  | area_code |      map_loc     
------------+-------+-------+-----------+-------------------
 portsmouth | nh    | 00210 | 603       | (43.0718,70.7634)
 portsmouth | nh    | 00211 | 603       | (43.0718,70.7634)
 portsmouth | nh    | 00212 | 603       | (43.0718,70.7634)
 portsmouth | nh    | 00213 | 603       | (43.0718,70.7634)
 
And nothing returned but an empty row:
 
my_db=# select public.map_point('portsmouth','nh','00211');
 map_point
-----------
 
(1 row)
 
 
I'm running 7.3.x on Slackware.
 
Any ideas why this is happening?
 
Thanks,
 
Andy

Re: Point and function help

From
Tom Lane
Date:
"Andy Lewis" <jumboc@comcast.net> writes:
> CREATE OR REPLACE FUNCTION public.map_point(pg_catalog.varchar,
> pg_catalog.varchar, pg_catalog.varchar)
>   RETURNS point AS
> 'SELECT map_loc from zip_code where zip = \'$3\' and lower(state) =
> lower(\'$2\') and lower(city) = lower(\'$1\')'
>   LANGUAGE 'sql' VOLATILE;

You don't want to quote the parameter references --- what you've got
there is simple literal constants '$3' etc.  Try

CREATE OR REPLACE FUNCTION public.map_point(pg_catalog.varchar,
pg_catalog.varchar, pg_catalog.varchar) RETURNS point AS
'SELECT map_loc from zip_code where zip = $3 and lower(state) =
lower($2) and lower(city) = lower($1)' LANGUAGE 'sql' VOLATILE;

Also, I can't see any reason why this function needs to be VOLATILE;
STABLE should be enough, no?
        regards, tom lane

Re: Point and function help

From
"Andy Lewis"
Date:
Thanks Tom, worked like a charm.

Appreciate your time on Christmas day!

Best Regards and Merry Christmas to all.

Andy

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us] 
Sent: Thursday, December 25, 2003 10:44 AM
To: Andy Lewis
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Point and function help 


"Andy Lewis" <jumboc@comcast.net> writes:
> CREATE OR REPLACE FUNCTION public.map_point(pg_catalog.varchar,
> pg_catalog.varchar, pg_catalog.varchar)
>   RETURNS point AS
> 'SELECT map_loc from zip_code where zip = \'$3\' and lower(state) =
> lower(\'$2\') and lower(city) = lower(\'$1\')'
>   LANGUAGE 'sql' VOLATILE;

You don't want to quote the parameter references --- what you've got
there is simple literal constants '$3' etc.  Try

CREATE OR REPLACE FUNCTION public.map_point(pg_catalog.varchar,
pg_catalog.varchar, pg_catalog.varchar) RETURNS point AS
'SELECT map_loc from zip_code where zip = $3 and lower(state) =
lower($2) and lower(city) = lower($1)' LANGUAGE 'sql' VOLATILE;

Also, I can't see any reason why this function needs to be VOLATILE;
STABLE should be enough, no?
        regards, tom lane