Обсуждение: Domains and supporting functions

I've got a domain based on a text type.
I've overridden the equal operator with
lower(text) = lower(text).

I created a table containing my new domain type
and can see that the equals operator is not
being used to determine uniqueness.

What do I need to do to force the UNIQUE constraint
to use the equals function?  Is sort going to ignore 
the > and < I've defined for this type, too?

Must I create an opclass and create the UNIQUE index
separately from the table creation?  
This seems extreme when what I really want to do is to 
override the basic comparing functions.

If this is the way domains really are, I would strongly suggest
expanding create domain to merge with create type (under) and
allow us to list the basic functions. 

--elein
elein@varlena.com


Example;

--
-- check constraint isemail for email base type
--
create or replace function isemail(text) returns boolean as
$$  if ( $_[0] =~ m/^([A-Z0-9]+[._]?){1,}[A-Z0-9]+\@(([A-Z0-9]+[-]?){1,}[A-Z0-9]+\.){1,}[A-Z]{2,4}$/i ) {     return
TRUE; }  else {     return FALSE;  }
 
$$ language 'plperl';

--
-- create type email under text
--
create domain email as text check ( isemail( value) );

--
-- Equals: lower(text) = lower(text)
--
create or replace function email_eq (email, email) returns boolean as
$$  select case when lower($1) = lower($2) then TRUE else FALSE end;
$$ language 'sql';

create operator = (  PROCEDURE = email_eq,  LEFTARG = email,  RIGHTARG = email
);
create table aliases (  email email UNIQUE PRIMARY KEY,  lname text
);

\echo expect PK ERROR
insert into aliases values ('pk@email.com', 'PK');
insert into aliases values ('PK@email.com', 'PK');

--------------------------------------------------------------         PostgreSQL Consulting, Support & Training   
--------------------------------------------------------------
elein@varlena.com        Varlena, LLC        www.varlena.com

PostgreSQL General Bits   http://www.varlena.com/GeneralBits/
--------------------------------------------------------------
I have always depended on the [QA] of strangers.

elein <elein@varlena.com> writes:
> I've got a domain based on a text type.
> I've overridden the equal operator with
> lower(text) = lower(text).

This won't work, you need to make a type instead.

> If this is the way domains really are, I would strongly suggest
> expanding create domain to merge with create type (under) and
> allow us to list the basic functions. 

IMHO, the exact difference between a domain and a type is you get to
choose your own definitions of the basic operations on a type.  There's
no free lunch: as soon as you start substituting operations the
complexity involved goes up by an order of magnitude.
        regards, tom lane

On Sat, Feb 18, 2006 at 09:27:47PM -0800, elein wrote:
> I've got a domain based on a text type.
> I've overridden the equal operator with
> lower(text) = lower(text).
>
> I created a table containing my new domain type
> and can see that the equals operator is not
> being used to determine uniqueness.

What you want is citext.

http://gborg.postgresql.org/project/citext/projdisplay.php

It is a case-insensetive type with indexing and conparison support.

Domains arn't going to do what you want...

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

On Sun, Feb 19, 2006 at 01:26:31AM -0500, Tom Lane wrote:
> elein <elein@varlena.com> writes:
> > I've got a domain based on a text type.
> > I've overridden the equal operator with
> > lower(text) = lower(text).
> 
> This won't work, you need to make a type instead.
> 

Actually I can do and have done this.  It is being tested now.
I did create an opclass.  It creates a UNIQUE index just fine
for the type using the lower() functionality.  *If* it passes 
all of my testing I'll publish it tomorrow on general bits.  
Perhaps folks can help try to break it when I publish (or
if you are around today Sunday, you can test in advance--email me
directly.)

If it doesn't pass the tests, well, I'm hoping
it will be functionally close enough for common use.  This 
implementation is all in SQL and plperl--no C code.

Now my only complaint is that ORDER BY requires the USING op
when it should recognize the information in the opclass for
btree > and < for the type of the sort column.  I can explain 
why it doesn't recognize the opclass information, but I think 
it should.


> > If this is the way domains really are, I would strongly suggest
> > expanding create domain to merge with create type (under) and
> > allow us to list the basic functions. 
> 
> IMHO, the exact difference between a domain and a type is you get to
> choose your own definitions of the basic operations on a type.  There's
> no free lunch: as soon as you start substituting operations the
> complexity involved goes up by an order of magnitude.

Type inheritance for base types should work by inheriting all of the
parent's definitions and overriding them only as necessary.  What I'm
trying to do is to demonstrate that we can weasle this functionality
using domains.

--elein
elein@varlena.com

> 
>             regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
> 
>                http://archives.postgresql.org
> 

On Sun, Feb 19, 2006 at 10:34:02AM -0800, elein wrote:
> Actually I can do and have done this.  It is being tested now.
> I did create an opclass.  It creates a UNIQUE index just fine
> for the type using the lower() functionality.  *If* it passes
> all of my testing I'll publish it tomorrow on general bits.
> Perhaps folks can help try to break it when I publish (or
> if you are around today Sunday, you can test in advance--email me
> directly.)

How is this different from the citext module I suggested?

> Now my only complaint is that ORDER BY requires the USING op
> when it should recognize the information in the opclass for
> btree > and < for the type of the sort column.  I can explain
> why it doesn't recognize the opclass information, but I think
> it should.

ORDER BY x ASC is a synonym for ORDER BY x USING >. That's the way it
is currently. To use ORDER BY by itself you need to call your operators
< and >.

> Type inheritance for base types should work by inheriting all of the
> parent's definitions and overriding them only as necessary.  What I'm
> trying to do is to demonstrate that we can weasle this functionality
> using domains.

Well, you can kind of do this by creating an implicit cast from your
type to text. Then you can use your type anywhere where text can appear
(like strpos, length, etc).

Hope this helps,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

On Sun, Feb 19, 2006 at 07:57:42PM +0100, Martijn van Oosterhout wrote:
> On Sun, Feb 19, 2006 at 10:34:02AM -0800, elein wrote:
> > Actually I can do and have done this.  It is being tested now.
> > I did create an opclass.  It creates a UNIQUE index just fine
> > for the type using the lower() functionality.  *If* it passes 
> > all of my testing I'll publish it tomorrow on general bits.  
> > Perhaps folks can help try to break it when I publish (or
> > if you are around today Sunday, you can test in advance--email me
> > directly.)
> 
> How is this different from the citext module I suggested?
> 

My implementation is in SQL and plperl only.  Also, the lower
case comparisons are only one aspect of the datatype.


> > Now my only complaint is that ORDER BY requires the USING op
> > when it should recognize the information in the opclass for
> > btree > and < for the type of the sort column.  I can explain 
> > why it doesn't recognize the opclass information, but I think 
> > it should.
> 
> ORDER BY x ASC is a synonym for ORDER BY x USING >. That's the way it
> is currently. To use ORDER BY by itself you need to call your operators
> < and >.
> 

This does not work where x is datatype foo with opclass foo_ops.
In this case, it uses the text > instead of the foo >.

> > Type inheritance for base types should work by inheriting all of the
> > parent's definitions and overriding them only as necessary.  What I'm
> > trying to do is to demonstrate that we can weasle this functionality
> > using domains.
> 
> Well, you can kind of do this by creating an implicit cast from your
> type to text. Then you can use your type anywhere where text can appear
> (like strpos, length, etc).
> 

Yes, I'm leveraging most of the text functions.

> Hope this helps,
> -- 
> Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> > tool for doing 5% of the work and then sitting around waiting for someone
> > else to do the other 95% so you can sue them.

On Sun, Feb 19, 2006 at 12:59:35PM -0800, elein wrote:
> On Sun, Feb 19, 2006 at 07:57:42PM +0100, Martijn van Oosterhout wrote:
> > ORDER BY x ASC is a synonym for ORDER BY x USING >. That's the way it
> > is currently. To use ORDER BY by itself you need to call your operators
> > < and >.
> >
>
> This does not work where x is datatype foo with opclass foo_ops.
> In this case, it uses the text > instead of the foo >.

Huh? You must be doing something unusual because it does work normally.
Did you specify the opclass as the default for the type?

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

Martijn van Oosterhout wrote:

>On Sun, Feb 19, 2006 at 10:34:02AM -0800, elein wrote:
>  
>
>>Actually I can do and have done this.  It is being tested now.
>>I did create an opclass.  It creates a UNIQUE index just fine
>>for the type using the lower() functionality.  *If* it passes 
>>all of my testing I'll publish it tomorrow on general bits.  
>>Perhaps folks can help try to break it when I publish (or
>>if you are around today Sunday, you can test in advance--email me
>>directly.)
>>    
>>
>
>How is this different from the citext module I suggested?
>  
>


Have you looked at the code of citext? Unless I'm misreading, it creates 
a lowercase copy of each string for each comparison. And it doesn't look 
to me like it's encoding/locale aware.

No doubt it serves the author's needs, but I'd be very careful of using 
or recommending it for general use.

I'm not sure how hard a text type with efficient, encoding and locale 
aware, case-insensitive comparison would be to create , but it would be 
a Good Thing (tm) to have available.

cheers

andrew

On Sun, Feb 19, 2006 at 10:29:35PM +0100, Martijn van Oosterhout wrote:
> On Sun, Feb 19, 2006 at 12:59:35PM -0800, elein wrote:
> > On Sun, Feb 19, 2006 at 07:57:42PM +0100, Martijn van Oosterhout wrote:
> > > ORDER BY x ASC is a synonym for ORDER BY x USING >. That's the way it
> > > is currently. To use ORDER BY by itself you need to call your operators
> > > < and >.
> > > 
> > 
> > This does not work where x is datatype foo with opclass foo_ops.
> > In this case, it uses the text > instead of the foo >.
> 
> Huh? You must be doing something unusual because it does work normally.
> Did you specify the opclass as the default for the type?
> 

I'll show you my test case if you'll show me yours :)

~e


> Have a nice day,
> -- 
> Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> > tool for doing 5% of the work and then sitting around waiting for someone
> > else to do the other 95% so you can sue them.

On Sun, Feb 19, 2006 at 04:35:56PM -0500, Andrew Dunstan wrote:
> Have you looked at the code of citext? Unless I'm misreading, it creates
> a lowercase copy of each string for each comparison. And it doesn't look
> to me like it's encoding/locale aware.

Its cilower function isn't terribly great and could probably do with
some work. toupper/tolower() are encoding/locale sensetive, but the
code used doesn't really handle multibyte encodings. But it's an
excellent starting point for creating new types because almost all the
hard work is done.

> I'm not sure how hard a text type with efficient, encoding and locale
> aware, case-insensitive comparison would be to create , but it would be
> a Good Thing (tm) to have available.

Hmm, "case-insensetive match" is a terribly badly defined concept.
There's a reason why there's a strcasecmp() but no strcasecoll(). The
code currently uses tolower, but if you changed it to do toupper it
would be equally valid yet produce different results.

If/when we ever get to use a real internationalisation library like
ICU, we can do things like convert strings to Normal Form D so we can
compare character seperate from their accents, ie accent-insensetive
comparison. In any case ICU contains mappings for things like
title-case and all the different kinds of space and hyphens so people
can specify their own mapping to get whatever they're happy with.

Until then, people will just have to rely on their system's support for
tolower().

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

On Sun, Feb 19, 2006 at 01:36:41PM -0800, elein wrote:
> On Sun, Feb 19, 2006 at 10:29:35PM +0100, Martijn van Oosterhout wrote:
> > On Sun, Feb 19, 2006 at 12:59:35PM -0800, elein wrote:
> > > On Sun, Feb 19, 2006 at 07:57:42PM +0100, Martijn van Oosterhout wrote:
> > > > ORDER BY x ASC is a synonym for ORDER BY x USING >. That's the way it
> > > > is currently. To use ORDER BY by itself you need to call your operators
> > > > < and >.
> > > >
> > >
> > > This does not work where x is datatype foo with opclass foo_ops.
> > > In this case, it uses the text > instead of the foo >.
> >
> > Huh? You must be doing something unusual because it does work normally.
> > Did you specify the opclass as the default for the type?
> >
>
> I'll show you my test case if you'll show me yours :)

Ok, here's a quick example I whipped up and if you run it it clearly
shows it's using the comparison function from the operator class.

http://svana.org/kleptog/temp/text2.example

It basically replicates the entire infrastructure for the text type as
a new type, "text2" so there's planty of scope for confusion, but
postgresql correctly picks the right function.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

On Sun, Feb 19, 2006 at 11:34:21PM +0100, Martijn van Oosterhout wrote:
> On Sun, Feb 19, 2006 at 01:36:41PM -0800, elein wrote:
> > On Sun, Feb 19, 2006 at 10:29:35PM +0100, Martijn van Oosterhout wrote:
> > > On Sun, Feb 19, 2006 at 12:59:35PM -0800, elein wrote:
> > > > On Sun, Feb 19, 2006 at 07:57:42PM +0100, Martijn van Oosterhout wrote:
> > > > > ORDER BY x ASC is a synonym for ORDER BY x USING >. That's the way it
> > > > > is currently. To use ORDER BY by itself you need to call your operators
> > > > > < and >.
> > > > > 
> > > > 
> > > > This does not work where x is datatype foo with opclass foo_ops.
> > > > In this case, it uses the text > instead of the foo >.
> > > 
> > > Huh? You must be doing something unusual because it does work normally.
> > > Did you specify the opclass as the default for the type?
> > > 
> > 
> > I'll show you my test case if you'll show me yours :)
> 
> Ok, here's a quick example I whipped up and if you run it it clearly
> shows it's using the comparison function from the operator class.
> 
> http://svana.org/kleptog/temp/text2.example
> 
> It basically replicates the entire infrastructure for the text type as
> a new type, "text2" so there's planty of scope for confusion, but
> postgresql correctly picks the right function.
> 
> Have a nice day,
> -- 
> Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> > tool for doing 5% of the work and then sitting around waiting for someone
> > else to do the other 95% so you can sue them.

Comparing test cases we found that Martijn was using a true CREATE TYPE while
I am using CREATE DOMAIN.  That was the only difference that mattered.

So far there are only two gotchas with this exercise of making a 
domain based base type. 
1) LIKE doesn't work. Workaround: create and use like-ish operator. Arguably correct behavior.
2) ORDER BY requires the USING op clause. Workaround: use the USING op clause.

--elein

On Sun, Feb 19, 2006 at 10:34:02AM -0800, elein wrote:
> On Sun, Feb 19, 2006 at 01:26:31AM -0500, Tom Lane wrote:
> > elein <elein@varlena.com> writes:
> > > I've got a domain based on a text type.
> > > I've overridden the equal operator with
> > > lower(text) = lower(text).
> > 
> > This won't work, you need to make a type instead.
> > 
> 
> Actually I can do and have done this.  It is being tested now.
> I did create an opclass.  It creates a UNIQUE index just fine
> for the type using the lower() functionality.  *If* it passes 
> all of my testing I'll publish it tomorrow on general bits.  
> Perhaps folks can help try to break it when I publish (or
> if you are around today Sunday, you can test in advance--email me
> directly.)
> 
> If it doesn't pass the tests, well, I'm hoping
> it will be functionally close enough for common use.  This 
> implementation is all in SQL and plperl--no C code.
> 
> Now my only complaint is that ORDER BY requires the USING op
> when it should recognize the information in the opclass for
> btree > and < for the type of the sort column.  I can explain 
> why it doesn't recognize the opclass information, but I think 
> it should.
> 
> 
> > > If this is the way domains really are, I would strongly suggest
> > > expanding create domain to merge with create type (under) and
> > > allow us to list the basic functions. 
> > 
> > IMHO, the exact difference between a domain and a type is you get to
> > choose your own definitions of the basic operations on a type.  There's
> > no free lunch: as soon as you start substituting operations the
> > complexity involved goes up by an order of magnitude.
> 
> Type inheritance for base types should work by inheriting all of the
> parent's definitions and overriding them only as necessary.  What I'm
> trying to do is to demonstrate that we can weasle this functionality
> using domains.
> 
> --elein
> elein@varlena.com
> 
> > 
> >             regards, tom lane
> > 
> > ---------------------------(end of broadcast)---------------------------
> > TIP 4: Have you searched our list archives?
> > 
> >                http://archives.postgresql.org
> > 

OK. My article (and code) is published.  It could probably use some more tries to
find holes in the implementation.

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

eleie@varlena.com
I've always depended on the [QA] of strangers. :)

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 

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