Re: Disabling case sensitivity

On Wed, Jul 10, 2002 at 03:22:00PM +0200,
Michael Meskes <meskes(at)postgresql(dot)org> wrote
a message of 31 lines which said:

> Can't you use something like
>
> SELECT from table where tolower(username)='good';

If you want sometimes case-sensitive comparison and sometimes not, it
is a good solution (the PostgreSQL extension ILIKE is another). But if
you want to "disable case-sensitivity", you risk that some
applications forget the call to tolower(). Therefore, I prefer to
create a trigger which will force the field to lowercase before
INSERTing it. (The problem of my solution is that it is no longer
case-preserving.)

CREATE FUNCTION force_lower_case() RETURNS OPAQUE
AS 'BEGIN
NEW.name = lower(NEW.name);
RETURN NEW;
END;'
LANGUAGE PLPGSQL;

-- Domain names are only in US-ASCII (so no locale problems) and are
-- case-insensitive. If you want to record the original case, add a
-- new field.
CREATE TRIGGER force_lower_case
BEFORE INSERT ON Domains
FOR EACH ROW
EXECUTE PROCEDURE force_lower_case();