pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

Point and function help
3+ messages / 2 participants
[nested] [flat]

* Point and function help
@ 2003-12-25 07:05  Andy Lewis <jumboc@comcast.net>
  0 siblings, 1 reply; 3+ messages in thread

From: Andy Lewis @ 2003-12-25 07:05 UTC (permalink / raw)
  To: 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

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Point and function help
@ 2003-12-25 16:43  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Andy Lewis <jumboc@comcast.net>
  0 siblings, 1 reply; 3+ messages in thread

From: Tom Lane @ 2003-12-25 16:43 UTC (permalink / raw)
  To: Andy Lewis <jumboc@comcast.net>; +Cc: pgsql-sql

"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



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Point and function help
@ 2003-12-25 18:39  Andy Lewis <jumboc@comcast.net>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 3+ messages in thread

From: Andy Lewis @ 2003-12-25 18:39 UTC (permalink / raw)
  To: 'Tom Lane' <tgl@sss.pgh.pa.us>; +Cc: 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@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




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

end of thread, other threads:[~2003-12-25 18:39 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-12-25 07:05 Point and function help Andy Lewis <jumboc@comcast.net>
2003-12-25 16:43 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-12-25 18:39   ` Andy Lewis <jumboc@comcast.net>

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