Re: Point and function help

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