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 E166BD1B4AD for ; Thu, 25 Dec 2003 18:40:03 +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 98220-10 for ; Thu, 25 Dec 2003 14:39:14 -0400 (AST) Received: from rwcrmhc12.comcast.net (rwcrmhc12.comcast.net [216.148.227.85]) by svr1.postgresql.org (Postfix) with ESMTP id 64DB0D1B4AF for ; Thu, 25 Dec 2003 14:39:13 -0400 (AST) Received: from andy2 (c-24-1-39-92.client.comcast.net[24.1.39.92]) by comcast.net (rwcrmhc12) with SMTP id <20031225183908014000sv42e>; Thu, 25 Dec 2003 18:39:08 +0000 From: "Andy Lewis" To: "'Tom Lane'" Cc: Subject: Re: Point and function help Date: Thu, 25 Dec 2003 12:39:11 -0600 Message-ID: <000501c3cb16$63d30a40$0201a8c0@andy2> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.2627 In-reply-to: <20664.1072370632@sss.pgh.pa.us> Importance: Normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200312/179 X-Sequence-Number: 16345 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" 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