Message

Basically= I have a=20 zip code DB complete with city, state and zip pre-populated.<= /DIV>

What I wo= uld like to=20 do is create this function:

CREATE OR= REPLACE=20 FUNCTION public.map_point(pg_catalog.varchar, pg_catalog.varchar,=20 pg_catalog.varchar)
RETURNS point AS
'SELECT map_loc from zip_= code=20 where zip =3D \'$3\' and lower(state) =3D lower(\'$2\') and lower(city) =3D= =20 lower(\'$1\')'
LANGUAGE 'sql' VOLATILE;

And I hav= e no=20 problems creating this function however, I can't get it to return any thing= from=20 my zip_code table.

Am I doin= g something=20 wrong?

Here's a = snippet of=20 the zip_code table:

&nb= sp; =20 city    | state | zip | area_code=20 |      map_loc     =20
------------+-------+-------+-----------+-------------------
p= ortsmouth=20 | nh    | 00210 | 603       |= =20 (43.0718,70.7634)
portsmouth | nh    | 00211 |=20 603       | (43.0718,70.7634)
portsm= outh=20 | nh    | 00212 | 603       |= =20 (43.0718,70.7634)
portsmouth | nh    | 00213 |=20 603       | (43.0718,70.7634)

And nothi= ng returned=20 but an empty row:

my_db=3D#= select=20 public.map_point('portsmouth','nh','00211');
map_point=20
-----------

(1 row)

I'm runni= ng 7.3.x on=20 Slackware.

Any ideas= why this=20 is happening?

Thanks,

Andy

------=_NextPart_000_0010_01C3CA83.310DCB60-- From tgl@sss.pgh.pa.us Thu Dec 25 16:44:44 2003 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 From jumboc@comcast.net Thu Dec 25 18:40:03 2003 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