Re: CREATE TYPE for case insensitive text and varchar

Tom Lane wrote:

> Daniel Migowski <nur-gueltig-bis-2003-12-01@Mig-O.de> writes:
>> I miss the possibility to code something like a userdifined
>> varchar(n).
>
> You're out of luck on that.  The data types that can have precision
> parameters attached to them are hard-wired into the parser.

Maybe you don't need to invent a whole new data type but the existing
varchar can serve just fine?

The attached script for version 7.3.4 (does not work with 7.4)
demonstrates how to add case insensitive operators *=, *> and so on
including an operator class for btree to the existing varchar.

All one has to do is to use *= instead of = in queries. Indexes, even
unique, based on case insensitive comparision are possible too and well
supported. The only thing I think wouldn't work are IN and NOT IN
constructs.


Jan

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #
#!/bin/sh

DBNAME=ci_testdb
export DBNAME

dropdb ${DBNAME}
createdb ${DBNAME}

psql ${DBNAME} <<_EOF_

--
-- Case insensitive comparision functions
--
create function varchar_cieq(varchar, varchar) returns bool
as '
begin
    return varchareq(lower(\$1), lower(\$2));
end;
' language plpgsql;

create function varchar_cine(varchar, varchar) returns bool
as '
begin
    return varcharne(lower(\$1), lower(\$2));
end;
' language plpgsql;

create function varchar_cilt(varchar, varchar) returns bool
as '
begin
    return varcharlt(lower(\$1), lower(\$2));
end;
' language plpgsql;

create function varchar_cile(varchar, varchar) returns bool
as '
begin
    return varcharle(lower(\$1), lower(\$2));
end;
' language plpgsql;

create function varchar_cigt(varchar, varchar) returns bool
as '
begin
    return varchargt(lower(\$1), lower(\$2));
end;
' language plpgsql;

create function varchar_cige(varchar, varchar) returns bool
as '
begin
    return varcharge(lower(\$1), lower(\$2));
end;
' language plpgsql;

create function varchar_cicmp(varchar, varchar) returns int4
as '
begin
    return varcharcmp(lower(\$1), lower(\$2));
end;
' language plpgsql;


--
-- Case insensitive operators
--
create operator *< (
    procedure = varchar_cilt,
    leftarg = varchar, rightarg = varchar,
    commutator = *>, negator = *>=,
    restrict = scalarltsel, join = scalarltjoinsel
);
create operator *= (
    procedure = varchar_cieq,
    leftarg = varchar, rightarg = varchar,
    commutator = *=, negator = *<>,
    restrict = eqsel, join = eqjoinsel,
    sort1 = *<, sort2 = *<,
    hashes
);
create operator *<> (
    procedure = varchar_cine,
    leftarg = varchar, rightarg = varchar,
    commutator = *<>, negator = *=,
    restrict = neqsel, join = neqjoinsel
);
create operator *!= (
    procedure = varchar_cine,
    leftarg = varchar, rightarg = varchar,
    commutator = *<>, negator = *=,
    restrict = neqsel, join = neqjoinsel
);
create operator *> (
    procedure = varchar_cigt,
    leftarg = varchar, rightarg = varchar,
    commutator = *<, negator = *<=,
    restrict = scalargtsel, join = scalargtjoinsel
);
create operator *<= (
    procedure = varchar_cile,
    leftarg = varchar, rightarg = varchar,
    commutator = *>=, negator = *>,
    restrict = scalarltsel, join = scalarltjoinsel
);
create operator *>= (
    procedure = varchar_cige,
    leftarg = varchar, rightarg = varchar,
    commutator = *<=, negator = *<,
    restrict = scalargtsel, join = scalargtjoinsel
);

--
-- And the operator class for case insensitive indexes
--
create operator class varchar_ciops
    for type varchar using btree as
    operator 1 *<  (varchar, varchar),
    operator 2 *<= (varchar, varchar),
    operator 3 *=  (varchar, varchar),
    operator 4 *>= (varchar, varchar),
    operator 5 *>  (varchar, varchar),
    function 1 varchar_cicmp(varchar, varchar);

create table citest_t1 (
    id        varchar(10),
    data    text
);
create unique index citest_t1_idx on citest_t1 (id varchar_ciops);

insert into citest_t1 values ('hello', 'world');
insert into citest_t1 values ('goodbye', 'world');
insert into citest_t1 values ('Hello', 'World');

set enable_seqscan to off;
set enable_indexscan to on;

explain select * from citest_t1 where id *= 'hello';
explain select * from citest_t1 where id = 'hello';

select * from citest_t1 where id *= 'HELLO';
select * from citest_t1 where id *!= 'HELLO';
select * from citest_t1 where id = 'HELLO';
_EOF_