Thread: anal about my syntax

James,

> FOR rec IN EXECUTE
> ''select count(person_id) as total from person where person_email
> like '''''' || $1 || ''%'''' and person_id IN
> (
> select cp.person_id from cluster_person cp, cluster c where
> cp.cluster_id = c.cluster_id and c.c_id = '' || $2 || ''group by
> cp.person_id
> ) ''
> LOOP
> tot = rec.total;
> END LOOP;
>
> It works fine - I was just wondering if you can:
> 1. execute this sql without a loop being used as its not required.

Not as written, no.

> 2. could this be a straight SQL function instead( I dont think you
> can append strings together  in SQL functions )

Yes, it could.   If I have time later, I'll draft you an example.

-Josh

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
fromcluster_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
andc.c_id = $2 and cp.person_id = person.person_id);' LANGUAGE SQL; 


--
Josh Berkus
Aglio Database Solutions
San Francisco