Lists: | pgsql-sql |
---|
From: | "Andy Lewis" <jumboc(at)comcast(dot)net> |
---|---|
To: | <pgsql-sql(at)postgresql(dot)org> |
Subject: | Point and function help |
Date: | 2003-12-25 07:05:30 |
Message-ID: | 000f01c3cab5$7ba83b60$0201a8c0@andy2 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-sql |
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
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "Andy Lewis" <jumboc(at)comcast(dot)net> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Point and function help |
Date: | 2003-12-25 16:43:52 |
Message-ID: | 20664.1072370632@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-sql |
"Andy Lewis" <jumboc(at)comcast(dot)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
From: | "Andy Lewis" <jumboc(at)comcast(dot)net> |
---|---|
To: | "'Tom Lane'" <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: Point and function help |
Date: | 2003-12-25 18:39:11 |
Message-ID: | 000501c3cb16$63d30a40$0201a8c0@andy2 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-sql |
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(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Thursday, December 25, 2003 10:44 AM
To: Andy Lewis
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: [SQL] Point and function help
"Andy Lewis" <jumboc(at)comcast(dot)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