On Mon, Feb 20, 2006 at 09:03:29AM +0100, Michael Paesold wrote:
> 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.
The point of my article is to create the sub type using domains.
This technique inherits the input/output routines of the parent type.
>
> 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.
Unfortunately this does not work for domains. A bug, IMHO. One should
be able to override ALL operators for domains.
--elein
elein@varlena.com
>
> Hope this is useful.
>
> Best Regards,
> Michael Paesold
>
>