From: Josh Berkus <josh@agliodbs.com>
To: James Cooper <jim@luckydigital.com>
Cc: sql <pgsql-sql@postgresql.org>
Subject: Re: anal about my syntax
Date: Wed, 12 Feb 2003 16:59:26 -0800
Message-ID: <200302121659.26956.josh@agliodbs.com> (raw)
In-Reply-To: <019301c2d236$325678f0$c900a8c0@jax>
References: <web-2835260@davinci.ethosmedia.com>
<019301c2d236$325678f0$c900a8c0@jax>
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 =
c.cluster_id and c.c_id = $2 and cp.person_id = 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.,
it won't return a count for SMITHERS@yahoo.com is you search on "smithers".
You may want to consider putting a functional index on
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) like
lower($1 || ''%'') and EXISTS
(
select cp.person_id from cluster_person cp, cluster c where cp.cluster_id =
c.cluster_id and c.c_id = $2 and cp.person_id = person.person_id);
' LANGUAGE SQL;
--
Josh Berkus
Aglio Database Solutions
San Francisco
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: josh@agliodbs.com, jim@luckydigital.com
Subject: Re: anal about my syntax
In-Reply-To: <200302121659.26956.josh@agliodbs.com>
* 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