Elein wrote:
> http://www.varlena.com/GeneralBits/128.php
>
> Known Problems and Issues:
>
> * Creating the table with an email PRIMARY KEY did not use our
> comparison function. It was necessary to create a unique index which
> explicitly used the email operator class.
> * ORDER BY requires USING op clause.
> * LIKE does not work. Use defined operator % instead.
>
> There are convincing arguments for and against this behavior. Feel free to
> argue one way or the other.
I once created a case-insensitive "ivarchar" type based just reusing the
varcharin/out functions and some pl/pgsql functions. I can send you the
complete .sql file, if you want.
I have not looked at your type, but when I saw "LIKE does not work", I
thought I'd send you this part of the ivarchar type, which should explain
how I got the LIKE functionality to work.
-- Support case insensitive LIKE operations
-- Support functions
CREATE FUNCTION ivarcharlike( ivarchar, text ) RETURNS boolean AS 'BEGIN
RETURN texticlike($1::text,$2); END' LANGUAGE PLpgSQL IMMUTABLE STRICT;
CREATE FUNCTION ivarcharnlike( ivarchar, text ) RETURNS boolean AS 'BEGIN
RETURN texticnlike($1::text,$2); END' LANGUAGE PLpgSQL IMMUTABLE STRICT;
-- Operators used by LIKE and NOT LIKE
CREATE OPERATOR ~~ ( PROCEDURE=ivarcharlike, LEFTARG=ivarchar,
RIGHTARG=text, NEGATOR= !~~, RESTRICT=iclikesel, JOIN=iclikejoinsel );
CREATE OPERATOR !~~ ( PROCEDURE=ivarcharnlike, LEFTARG=ivarchar,
RIGHTARG=text, NEGATOR= ~~, RESTRICT=icnlikesel, JOIN=icnlikejoinsel );
LIKE is really not much more than syntactic sugar for the ~~ operator.
Hope this is useful.
Best Regards,
Michael Paesold