Re: anal about my syntax
От | Josh Berkus |
---|---|
Тема | Re: anal about my syntax |
Дата | |
Msg-id | 200302121659.26956.josh@agliodbs.com обсуждение исходный текст |
Ответ на | Re: anal about my syntax ("Josh Berkus" <josh@agliodbs.com>) |
Список | pgsql-sql |
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
В списке pgsql-sql по дате отправления: