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