Querybuilder: Make "LIKE" case insensitive on pgsql

It's not case sensitive here. What database backend are you using?

PostreSQL's LIKE is not case insensitive..

Do you think it may be a good idea to use the function LOWER() or UPPER() to make it all as database agnostic as possible?

(I think that finction exists in postgres and mysql, but have no idea about others...)

i would like to avoid using this method as it is extremely slow because using LOWER or UPPER prevents using table indexes

Me too, but we can't avoid it, not in D6. Core is currently doing the same thing and will hopefully add lower-case fields for things like username and taxonomy terms.

user_load() is using lower() anyway, so we should be consistent.

If we are going to support case agnostic solutions then we should start caching people's names in a new table {uid, LOWER(name)} in hook_user_{$register|$edit|...} or something.

is there some way to automagically switch to ILIKE for postgres?

If Mysql could also support ILIKE (which for it would be the same has LIKE...), things would be easier.

While we are working on it, you can easily change it with a simple hook, if you know a bit PHP:

See
http://blog.worldempire.ch/api/group/sql/1
http://blog.worldempire.ch/api/function/hook_privatemsg_sql_autocomplete...
http://blog.worldempire.ch/api/function/privatemsg_sql_autocomplete/1

Create a yourmodule_privatemsg_sql_autocomplete_alter hook and replace the LIKE in the first where part. And please post your hook here so that others can use it too, if they want.

Edit: BTW, PostgreSQL does support LOWER()

would an str_replace work?

Something like:

function mymodule_privatemsg_sql_autocomplete_alter(&$fragments, $search, $names) {
 $fragments = str_replace('LIKE', 'ILIKE', $fragments);

??

It should if you replace it in $fragments['where'][0].

ok, attaching a patch for the query builder that should solve this for pgsql - However I do not have pgsql installed so testing will have to be done by others.

Nice little patch that gets it working without loosing the performance on MySQL.

Aquote of dries' quote from http://lists.drupal.org/pipermail/development/2007-January/021942.html : Removal of ilike searches. Use lower() = lower() it is faster.

Committed. thank you!