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.