X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 05617D1B444 for ; Thu, 25 Dec 2003 16:44:44 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 97301-03 for ; Thu, 25 Dec 2003 12:43:55 -0400 (AST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id 722BED1B455 for ; Thu, 25 Dec 2003 12:43:54 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.10/8.12.10) with ESMTP id hBPGhq19020665; Thu, 25 Dec 2003 11:43:53 -0500 (EST) To: "Andy Lewis" Cc: pgsql-sql@postgresql.org Subject: Re: Point and function help In-reply-to: <000f01c3cab5$7ba83b60$0201a8c0@andy2> References: <000f01c3cab5$7ba83b60$0201a8c0@andy2> Comments: In-reply-to "Andy Lewis" message dated "Thu, 25 Dec 2003 01:05:30 -0600" Date: Thu, 25 Dec 2003 11:43:52 -0500 Message-ID: <20664.1072370632@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200312/178 X-Sequence-Number: 16344 "Andy Lewis" 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