Re: anal about my syntax

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(at)yahoo(dot)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