X-Original-To: pgsql-sql@postgresql.org
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 74B0C4759BD
	for <pgsql-sql@postgresql.org>; Wed, 12 Feb 2003 19:59:41 -0500 (EST)
Received: from davinci.ethosmedia.com (unknown [209.10.40.251])
	by postgresql.org (Postfix) with ESMTP id C5D254758C9
	for <pgsql-sql@postgresql.org>; Wed, 12 Feb 2003 19:59:39 -0500 (EST)
Received: from [63.195.55.98] (HELO spooky)
	by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.0.2)
	with ESMTP id 2837295; Wed, 12 Feb 2003 16:59:48 -0800
Content-Type: text/plain;
  charset="iso-8859-1"
From: Josh Berkus <josh@agliodbs.com>
Organization: Aglio Database Solutions
To: "James Cooper" <jim@luckydigital.com>
Subject: Re: anal about my syntax
Date: Wed, 12 Feb 2003 16:59:26 -0800
User-Agent: KMail/1.4.3
References: <web-2835260@davinci.ethosmedia.com>
	<019301c2d236$325678f0$c900a8c0@jax>
In-Reply-To: <019301c2d236$325678f0$c900a8c0@jax>
Cc: "sql" <pgsql-sql@postgresql.org>
MIME-Version: 1.0
Message-Id: <200302121659.26956.josh@agliodbs.com>
Content-Transfer-Encoding: quoted-printable
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200302/269
X-Sequence-Number: 11667

James,

A small efficiency improvement, and a syntax fix:

CREATE FUNCTION get_people_letter_total( text, integer )  RETURNS integer
AS '
   select count(person_id) as total from person where person_email like
 ($1 || ''%'') and EXISTS
 (
 select cp.person_id from cluster_person cp, cluster c where cp.cluster_id =
=3D
 c.cluster_id and c.c_id =3D $2 and cp.person_id =3D person.person_id);
 ' LANGUAGE SQL;

 > I tried the above but my result is always 0;

Yes,  that's because of your syntax mistake in your "like" expression.

One more comment:  the above function will only work case-sensitively; i.e.=
,=20
it won't return a count for SMITHERS@yahoo.com is you search on "smithers".=
=20=20
You may want to consider putting a functional index on=20
lower(person.person_email), and changing the above to:

CREATE FUNCTION get_people_letter_total( text, integer )  RETURNS integer
AS '
   select count(person_id) as total from person where lower(person_email) l=
ike=20
lower($1 || ''%'') and EXISTS
 (
 select cp.person_id from cluster_person cp, cluster c where cp.cluster_id =
=3D
 c.cluster_id and c.c_id =3D $2 and cp.person_id =3D person.person_id);
 ' LANGUAGE SQL;


--=20
Josh Berkus
Aglio Database Solutions
San Francisco