Background:
Domains lay the groundwork for inherited basetypes
or subtypes. By defining a domain and overriding
operators and possibly creating an operator class, then a domain
can be created which inherits the storage method
and all of the functions of a basetype. The domain
constraint enables a psuedo input function for
the subtype.
Domains enable people to create basetype subtypes using SQL
and procedural languages only. Current belief is that
this "doesn't work." However, all of this has worked
since domains were implemented with three exceptions.
1. PRIMARY KEY uses a type's base type opclass indiscriminantly.
Workaround: for this is to create an opclass for
the subtype and create a UNIQUE index on that column.
2. Operators which take two different types are not found.
The key example for this is a LIKE or ~~ type for a
subtype of text which has as its operands (basetype, parenttype).
Workaround: use the function instead of the operator.
3. ORDER BY uses the parent type's sort operators.
Workaround: Use ORDER BY ... USING 'operator'
Proposal:
This proposal only addresses issue #2. I will look into the
other two as time permits. But there are sensible workarounds
to #1 and #3 in the meanwhile.
This patch passes regression as well as my domain tests.
Attached is a patch to parse_oper.c which essentially does the
following. The major change is in binary_oper_exact().
Instead of checking only one level of the basetype it checks
all possible combinations of type and parent types for
an exact match (only). This favors first the passed in type
and then the basetype for exact matches. The second part of
this change is to lightly loosen the assumption that any operator
has same type operands. If an exact match is not made, the UNKNOWNOID
setting for the original operator is re-instated so that
the function selection mechanism can give it a try.
Tests:
Also attached are two files to create a domain/subtype and
to test it.
Issues/Questions:
1) Code review please.
2) Are there any test cases that were not covered by regression
and my tests?
3) Should I add my test case to the regression tests?
4) Eventually this feature should be added to the docs. Should
I wait until all of the pieces are in place or go ahead and
document the feature as it stands?
elein
--------------------------------------------------------------
[EMAIL PROTECTED] Varlena, LLC www.varlena.com
PostgreSQL Consulting, Support & Training
PostgreSQL General Bits http://www.varlena.com/GeneralBits/
--------------------------------------------------------------
I have always depended on the [QA] of strangers.
--
-- create type email under text;
--
-- Elein Mustain, [EMAIL PROTECTED], http://www.varlena.com
-- Published 19-Feb-2006 at http://www.varlena.com/GeneralBits/128.php
-- License: BSD, Share and enjoy
--
drop domain email cascade;
--
-- Check constraint isemail for email base type
-- Using "pretty good" but not strict rfc compliant regex
-- cf: http://aspn.activestate.com/ASPN/Cookbook/Rx/Recipe/59886
--
CREATE OR REPLACE FUNCTION isemail(text)
RETURNS BOOLEAN
AS $$
if ( $_[0] =~ m/
^ # Start.
( # Start of $1
[A-Z0-9]+ # Must start with some A-Z or 0-9s.
[._]? # May have a . or an _ after that
) # End of $1
{1,} # One or more of $1.
[A-Z0-9]+ # More alphanumerics
\@ # Literal @ sign
( # Start of $2
( # Start of $3
[A-Z0-9]+ # More alphanumerics
[-]? # At most one - sign.
) # End of $3
{1,} # One or more of $3
[A-Z0-9]+\. # More alphanumerics
) # End of $2
{1,} # One or more of $2.
[A-Z]{2,4} # 2-4 letters
$ # End.
/ix ) {
return TRUE;
}
else {
return FALSE;
}
$$ LANGUAGE 'plperl' STRICT IMMUTABLE;
--
-- create type email under text
--
CREATE DOMAIN email AS text CHECK ( isemail( VALUE) );
--
-- Comparison and Equality Functions
--
-- cmp: 0 if equal, 1 if greater than, -1 if less than
CREATE OR REPLACE FUNCTION email_cmp(email,email)
RETURNS INTEGER AS
$$
SELECT CASE WHEN lower($1) = lower($2) THEN 0
WHEN lower($1) > lower($2) THEN 1
ELSE -1
END;
$$ LANGUAGE 'SQL' IMMUTABLE STRICT;
-- Equals: lower(text) = lower(text)
CREATE OR REPLACE FUNCTION email_eq (email, email) RETURNS boolean AS
$$
SELECT CASE WHEN email_cmp($1, $2) = 0
THEN TRUE ELSE FALSE END;
$$ LANGUAGE 'SQL' IMMUTABLE STRICT;
-- Greater than: lower(text) > lower(text)
CREATE OR REPLACE FUNCTION email_gt (email, email) RETURNS boolean AS
$$
SELECT CASE WHEN email_cmp($1, $2) > 0
THEN TRUE ELSE FALSE END;
$$ LANGUAGE 'SQL' IMMUTABLE STRICT;
-- Less than: lower(text) < lower(text)
CREATE OR REPLACE FUNCTION email_lt (email, email) RETURNS boolean AS
$$
SELECT CASE WHEN email_cmp($1, $2) < 0
THEN TRUE ELSE FALSE END;
$$ LANGUAGE 'SQL' IMMUTABLE STRICT;
--Greater than or Equals
CREATE OR REPLACE FUNCTION email_gte (email, email) RETURNS boolean AS
$$
SELECT CASE WHEN email_cmp($1,$2) = 0 OR email_cmp($1,$2) = 1
THEN TRUE ELSE FALSE end;
$$ LANGUAGE 'SQL' IMMUTABLE STRICT;
--Less than or Equals
CREATE OR REPLACE FUNCTION email_lte (email, email) RETURNS boolean AS
$$
SELECT CASE WHEN email_cmp($1,$2) = 0 OR email_cmp($1,$2) = -1
THEN TRUE ELSE FALSE END;
$$ LANGUAGE 'SQL' IMMUTABLE STRICT;
--Like
CREATE OR REPLACE FUNCTION email_like(email, text) RETURNS boolean AS
$$
SELECT lower($1) LIKE lower($2);
$$ LANGUAGE 'SQL' IMMUTABLE STRICT;
--
-- Operators
--
CREATE OPERATOR = ( PROCEDURE = email_eq, LEFTARG = email, RIGHTARG = email);
CREATE OPERATOR > ( PROCEDURE = email_gt, LEFTARG = email, RIGHTARG = email);
CREATE OPERATOR >= ( PROCEDURE = email_gte, LEFTARG = email, RIGHTARG = email);
CREATE OPERATOR < ( PROCEDURE = email_lt, LEFTARG = email, RIGHTARG = email);
CREATE OPERATOR <= ( PROCEDURE = email_lte, LEFTARG = email, RIGHTARG = email);
CREATE OPERATOR ~~ ( PROCEDURE = email_like, LEFTARG = email, RIGHTARG = text);
--
-- Operator Class
--
CREATE OPERATOR CLASS email_ops
DEFAULT FOR TYPE email USING BTREE AS
OPERATOR 1 <,
OPERATOR 2 <=,
OPERATOR 3 =,
OPERATOR 4 >=,
OPERATOR 5 >,
FUNCTION 1 email_cmp(email,email)
;
--
-- Accessor functions
--
-- login: before the @ sign
CREATE OR REPLACE FUNCTION email_login (email) RETURNS text AS
$$
return (split(/\@/, $_[0]))[0];
$$ LANGUAGE 'plperl' IMMUTABLE STRICT;
-- Full Domain: after the @ sign
CREATE OR REPLACE FUNCTION email_fdomain (email) RETURNS text AS
$$
return (split /\@/, $_[0])[-1];
$$ LANGUAGE 'plperl' IMMUTABLE STRICT;
-- Top Level Domain:: after the last dot
CREATE OR REPLACE FUNCTION email_domain_type (email) RETURNS text AS
$$
return (split( # Split the output of split below
/\./, # Split the following on "."
(
split(
/\@/, # Split whole input on "@"
$_[0]
)
)[-1] # last part of inner split
))[-1]; # last part of outer split
$$ LANGUAGE 'plperl' IMMUTABLE STRICT;
-- Reverse Domain
CREATE OR REPLACE FUNCTION email_reverse_domain(email) RETURNS text AS
$$
return join(".", reverse split( # Split the output of split below
/\./, # Split the following on "."
(
split(
/\@/, # Split whole input on "@"
$_[0]
)
)[-1] # last part of inner split
));
$$ LANGUAGE 'plperl' IMMUTABLE STRICT;
-- Nonsense function to ensure func select will choose this one
CREATE OR REPLACE FUNCTION email_n(email, integer) returns text as
$$
select $1::text || '-' || $2::numeric
$$ LANGUAGE 'SQL' IMMUTABLE STRICT;
create operator * ( PROCEDURE= email_n, LEFTARG=email, RIGHTARG=integer);
--
-- Test create type email under text;
--
-- Elein Mustain, [EMAIL PROTECTED], http://www.varlena.com
-- Published 19-Feb-2006 at http://www.varlena.com/GeneralBits/128.php
-- License: BSD, Share and enjoy
--
drop table aliases;
Create table aliases (
email email UNIQUE PRIMARY KEY, -- broken: use unique index with
btree (email email_ops)
lname text
);
create unique index aliases_email on aliases using btree (email email_ops);
\echo [Expect check_email ERROR]
insert into aliases values ('[EMAIL PROTECTED]', 'Bad');
\echo
\echo [Expect PK ERROR]
insert into aliases values ('[EMAIL PROTECTED]', 'PK');
insert into aliases values ('[EMAIL PROTECTED]', 'PK');
\echo
\echo [No errors]
insert into aliases values ('[EMAIL PROTECTED]', 'Boz');
insert into aliases values ('[EMAIL PROTECTED]', 'Abe');
insert into aliases values ('[EMAIL PROTECTED]', 'Carolina');
insert into aliases values ('[EMAIL PROTECTED]', 'Donald');
insert into aliases values ('[EMAIL PROTECTED]', 'ET');
insert into aliases values ('[EMAIL PROTECTED]', 'Frodo');
insert into aliases values ('[EMAIL PROTECTED]', 'Sir');
--
\echo [Accessor functions]
select email, email_login(email) from aliases limit 2;
select email, email_fdomain(email) from aliases limit 2;
select email, email_domain_type(email) from aliases limit 2;
select email, email_reverse_domain(email) from aliases limit 2;
\echo
\echo [Equality tests]
select '[EMAIL PROTECTED]'::email = '[EMAIL PROTECTED]'::email; --works
select lname, email from aliases where email = '[EMAIL PROTECTED]'; -- works
select a.email, a2.lname from aliases a JOIN aliases a2 USING (email); --works
select a.email, a2.lname from aliases a JOIN aliases a2 ON
(UPPER(a.email)::email = a2.email); --works
\echo [Comparison Operators]
\echo [result should be comparison based on lower()]
\echo [one sided Casting is required for literals.]
select '[EMAIL PROTECTED]'::email < '[EMAIL PROTECTED]'; -- works
select '[EMAIL PROTECTED]'::email > '[EMAIL PROTECTED]'; -- works
select email from aliases where email <= '[EMAIL PROTECTED]'; -- works
select email from aliases where email >= '[EMAIL PROTECTED]'; -- works
\echo [LIKE and ~~ with typed right operand]
\echo select column like value: works
select lname, email from aliases where email like 'BOZO%'::text; --works
select lname, email from aliases where email ~~ 'BOZO%'::text; --works
select lname, email from aliases where email like lname || '%'; --works
\echo [direct function all to email_like works without casts]
select lname, email from aliases where email_like(email, 'BOZO%'); --works
select lname, email from aliases where email_like(email, lname || '%'); --works
-- these now work because email is a child of text.
\echo [Patch Corrected Cases]
\echo [LIKE and ~~ with unknown Right operand untyped work with patch]
select '[EMAIL PROTECTED]'::email like 'ABC%'; --now works-- broken: uses text
select lname, email from aliases where email like 'BOZO%'; --now works --
broken
select lname, email from aliases where email ~~ 'BOZO%'; --now works -- broken
\echo
\echo [ORDER BY]
\echo [Have to add using clause to tell which sort operator to use]
\echo [ASC/DESC: dont change things]
select * from aliases order by email; --broken
\echo Using USING < > works correctly
select * from aliases order by email using <;
select * from aliases order by email using >;
\echo
\echo
\echo MISC Tests
select email ~~ upper(email) from aliases; -- now works uses email ~~
select email || email from aliases; -- works uses text ||
select email_n(email,2) from aliases; -- works
select email * 2 from aliases; -- works: uses email_n
Index: parse_oper.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/parser/parse_oper.c,v
retrieving revision 1.86
diff -c -r1.86 parse_oper.c
*** parse_oper.c 14 Mar 2006 22:48:21 -0000 1.86
--- parse_oper.c 24 Mar 2006 18:59:58 -0000
***************
*** 43,48 ****
--- 43,49 ----
static Expr *make_op_expr(ParseState *pstate, Operator op,
Node *ltree, Node *rtree,
Oid ltypeId, Oid rtypeId);
+ static List * mk_oper_arg_list( Oid arg);
/*
***************
*** 388,431 ****
*/
static Oid
binary_oper_exact(Oid arg1, Oid arg2,
! FuncCandidateList candidates)
{
! FuncCandidateList cand;
! bool was_unknown = false;
/* Unspecified type for one of the arguments? then use the other */
if ((arg1 == UNKNOWNOID) && (arg2 != InvalidOid))
{
arg1 = arg2;
! was_unknown = true;
}
else if ((arg2 == UNKNOWNOID) && (arg1 != InvalidOid))
{
arg2 = arg1;
! was_unknown = true;
}
! for (cand = candidates; cand != NULL; cand = cand->next)
! {
! if (arg1 == cand->args[0] && arg2 == cand->args[1])
! return cand->oid;
! }
! if (was_unknown)
{
! /* arg1 and arg2 are the same here, need only look at arg1 */
! Oid basetype = getBaseType(arg1);
!
! if (basetype != arg1)
{
for (cand = candidates; cand != NULL; cand = cand->next)
{
! if (basetype == cand->args[0] && basetype ==
cand->args[1])
return cand->oid;
}
}
}
return InvalidOid;
}
--- 389,455 ----
*/
static Oid
binary_oper_exact(Oid arg1, Oid arg2,
! FuncCandidateList candidates)
{
! FuncCandidateList cand;
! int was_unknown = 0;
! List * larglist=NIL;
! List * rarglist=NIL;
! ListCell * larg_cell;
! ListCell * rarg_cell;
! Oid larg = InvalidOid;
! Oid rarg = InvalidOid;
+
+ /* Check for both args == UNKNOWNOID is in oper() */
/* Unspecified type for one of the arguments? then use the other */
if ((arg1 == UNKNOWNOID) && (arg2 != InvalidOid))
{
arg1 = arg2;
! was_unknown = 1;
}
else if ((arg2 == UNKNOWNOID) && (arg1 != InvalidOid))
{
arg2 = arg1;
! was_unknown = 2;
}
! /*
! * create argument list of type + basetypes
! */
! larglist = mk_oper_arg_list( arg1);
! rarglist = mk_oper_arg_list( arg2);
! /*
! * Check each basetype combination for exact match
! * This favors current types and basetypes for matches
! */
! foreach ( larg_cell, larglist )
{
! larg = lfirst_oid( larg_cell );
! foreach ( rarg_cell, rarglist )
{
+ rarg = lfirst_oid( rarg_cell );
for (cand = candidates; cand != NULL; cand = cand->next)
{
! if (larg == cand->args[0] && rarg ==
cand->args[1])
return cand->oid;
}
}
}
+ /*
+ * Put back the unknownid to let func sel handle it
+ */
+ if (was_unknown == 1)
+ {
+ arg1 = UNKNOWNOID;
+ }
+ else if (was_unknown == 2)
+ {
+ arg2 = UNKNOWNOID;
+ }
+
return InvalidOid;
}
***************
*** 518,531 ****
/*
* Check for an "exact" match.
*/
! operOid = binary_oper_exact(ltypeId, rtypeId, clist);
if (!OidIsValid(operOid))
{
/*
- * Otherwise, search for the most suitable candidate.
- */
-
- /*
* Unspecified type for one of the arguments? then use
the other
* (XXX this is probably dead code?)
*/
--- 542,554 ----
/*
* Check for an "exact" match.
*/
! if ( ltypeId == UNKNOWNOID && rtypeId == UNKNOWNOID )
! operOid = InvalidOid;
! else
! operOid = binary_oper_exact(ltypeId, rtypeId, clist);
if (!OidIsValid(operOid))
{
/*
* Unspecified type for one of the arguments? then use
the other
* (XXX this is probably dead code?)
*/
***************
*** 533,538 ****
--- 556,565 ----
rtypeId = ltypeId;
else if (ltypeId == InvalidOid)
ltypeId = rtypeId;
+
+ /*
+ * search for the most suitable candidate.
+ */
inputOids[0] = ltypeId;
inputOids[1] = rtypeId;
fdresult = oper_select_candidate(2, inputOids, clist,
&operOid);
***************
*** 1017,1019 ****
--- 1044,1074 ----
return (Expr *) result;
}
+
+ /*
+ * mk_oper_arg_list()
+ * Build type argument list of current type and basetypes.
+ *
+ * Order of list is important. Current type has precedence,
+ * then parent types in order of distance from current type.
+ *
+ * Called by binary_oper_exact to find operators for domains.
+ */
+ List *
+ mk_oper_arg_list( Oid arg)
+ {
+ List *arglist = NIL;
+ Oid basetype = InvalidOid;
+ Oid prevtype = arg;
+
+
+ arglist = lappend_oid( arglist, prevtype );
+ basetype = getBaseType(prevtype);
+ while ( basetype != prevtype ){
+ arglist = lappend_oid( arglist, basetype );
+ prevtype = basetype;
+ basetype = getBaseType(prevtype);
+ }
+ return arglist;
+ }
+
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
