[SQL] Point and function help

[Andy Lewis]

Title: Message



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: [SQL] not in vs not exists - vastly diferent performance

[Tom Lane]

"Iain" <[EMAIL PROTECTED]> writes:
> I found this interesting and thought I'd offer it up for comment.

You didn't say what PG version you are using, but I'd venture to bet
it is pre-7.4.

regards, tom lane

---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [SQL] Point and function help

[Tom Lane]

"Andy Lewis" <[EMAIL PROTECTED]> 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

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [SQL] Point and function help

[Andy Lewis]

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:[EMAIL PROTECTED] 
Sent: Thursday, December 25, 2003 10:44 AM
To: Andy Lewis
Cc: [EMAIL PROTECTED]
Subject: Re: [SQL] Point and function help 


"Andy Lewis" <[EMAIL PROTECTED]> 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


---(end of broadcast)---
TIP 8: explain analyze is your friend

[SQL] Multiple DB servers on a single machine

[Kumar]

Dear Friends,
 
I am having an RH Linux 7.3 box which is already running an 
Postgres 7.3.4 server. I want to install Postgres 7.4 on the same machine. Is it 
possible to install different versions of Postgres in a single RH Linux 7.3 box 
and the postmaster up and running for both (7.3.4 & 7.4 database 
servers).
 
Please shed some light with a white paper or an article to do 
it.
 
Regards
Kumar