Re: Domains and supporting functions

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