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 [EMAIL PROTECTED] > > Hope this is useful. > > Best Regards, > Michael Paesold > > ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
