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
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
