From: Andy Lewis <jumboc@comcast.net>
To: pgsql-sql@postgresql.org
Subject: Point and function help
Date: Thu, 25 Dec 2003 01:05:30 -0600
Message-ID: <000f01c3cab5$7ba83b60$0201a8c0@andy2> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jumboc@comcast.net
Subject: Re: Point and function help
In-Reply-To: <000f01c3cab5$7ba83b60$0201a8c0@andy2>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox