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
[EMAIL PROTECTED]
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 ('[EMAIL PROTECTED]', 'PK');
insert into aliases values ('[EMAIL PROTECTED]', 'PK');
--------------------------------------------------------------
PostgreSQL Consulting, Support & Training
--------------------------------------------------------------
[EMAIL PROTECTED] Varlena, LLC www.varlena.com
PostgreSQL General Bits http://www.varlena.com/GeneralBits/
--------------------------------------------------------------
I have always depended on the [QA] of strangers.
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
