Re: Domains and supporting functions

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 
> 
>