Domains and supporting functions - Mailing list pgsql-hackers

From elein
Subject Domains and supporting functions
Date
Msg-id 20060219052747.GF15582@varlena.com
Whole thread Raw
Responses Re: Domains and supporting functions
Re: Domains and supporting functions
List pgsql-hackers
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.


pgsql-hackers by date:

Previous
From: "Marc G. Fournier"
Date:
Subject: Re: Pgfoundry and gborg: shut one down
Next
From: Tatsuo Ishii
Date:
Subject: possible design bug with PQescapeString()