From: | elein <elein(at)varlena(dot)com> |
---|---|
To: | Michael Paesold <mpaesold(at)gmx(dot)at> |
Cc: | elein <elein(at)varlena(dot)com>, pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: Domains and supporting functions |
Date: | 2006-02-20 20:47:42 |
Message-ID: | 20060220204742.GQ15582@varlena.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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(at)varlena(dot)com
>
> Hope this is useful.
>
> Best Regards,
> Michael Paesold
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Peter Eisentraut | 2006-02-20 22:08:24 | Re: Generating config stuff from single source |
Previous Message | Sergey E. Koposov | 2006-02-20 20:34:02 | dropped columns, tupDesc |