From: Andy Lewis <jumboc@comcast.net>
To: 'Tom Lane' <tgl@sss.pgh.pa.us>
Cc: pgsql-sql@postgresql.org
Subject: Re: Point and function help
Date: Thu, 25 Dec 2003 12:39:11 -0600
Message-ID: <000501c3cb16$63d30a40$0201a8c0@andy2> (raw)
In-Reply-To: <20664.1072370632@sss.pgh.pa.us>
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
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, tgl@sss.pgh.pa.us
Subject: Re: Point and function help
In-Reply-To: <000501c3cb16$63d30a40$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