Re: Domains and supporting functions - Mailing list pgsql-hackers

From Michael Paesold
Subject Re: Domains and supporting functions
Date
Msg-id 019f01c635f4$23a11750$9001a8c0@zaphod
Whole thread Raw
In response to Domains and supporting functions  (elein <elein@varlena.com>)
Responses Re: Domains and supporting functions
List pgsql-hackers
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 




pgsql-hackers by date:

Previous
From: "Jeroen T. Vermeulen"
Date:
Subject: Re: Pgfoundry and gborg: shut one down
Next
From: "Rafaqat Ali"
Date:
Subject: Memory profiling in postgreSQL