typecmds.c says:
"Domains over composite types might be made to work in the future, but
not today."
Attached is a patch that allows domains over composite types, together
with test cases in domaincomp.sql. A domain over a composite type has
typtype TYPTYPE_DOMAIN, but typrelid and typrelkind are empty: that
information is only available in the pg_type record of the base type.
The remainder of the patch follows from that choice. While parsing a
record expression into a row type, an extra coercion node had to be
inserted to ensure that the domain checks are called.
All regression tests are ok, comments are highly appreciated.
--
Yeb Havinga
http://www.mgrid.net/
Mastering Medical Data
diff --git a/src/backend/access/heap/tuptoaster.c b/src/backend/access/heap/tuptoaster.c
new file mode 100644
index 4f4dd69..1c8dc64
*** a/src/backend/access/heap/tuptoaster.c
--- b/src/backend/access/heap/tuptoaster.c
***************
*** 36,41 ****
--- 36,42 ----
#include "access/xact.h"
#include "catalog/catalog.h"
#include "utils/fmgroids.h"
+ #include "utils/lsyscache.h"
#include "utils/pg_lzcompress.h"
#include "utils/rel.h"
#include "utils/typcache.h"
*************** toast_flatten_tuple_attribute(Datum valu
*** 965,971 ****
* Break down the tuple into fields.
*/
olddata = DatumGetHeapTupleHeader(value);
! Assert(typeId == HeapTupleHeaderGetTypeId(olddata));
Assert(typeMod == HeapTupleHeaderGetTypMod(olddata));
/* Build a temporary HeapTuple control structure */
tmptup.t_len = HeapTupleHeaderGetDatumLength(olddata);
--- 966,973 ----
* Break down the tuple into fields.
*/
olddata = DatumGetHeapTupleHeader(value);
! Assert((typeId == HeapTupleHeaderGetTypeId(olddata)
! || (getBaseType(typeId) == HeapTupleHeaderGetTypeId(olddata))));
Assert(typeMod == HeapTupleHeaderGetTypMod(olddata));
/* Build a temporary HeapTuple control structure */
tmptup.t_len = HeapTupleHeaderGetDatumLength(olddata);
diff --git a/src/backend/commands/typecmds.c b/src/backend/commands/typecmds.c
new file mode 100644
index f8eb5bc..c9aef53
*** a/src/backend/commands/typecmds.c
--- b/src/backend/commands/typecmds.c
*************** DefineDomain(CreateDomainStmt *stmt)
*** 814,827 ****
basetypeoid = HeapTupleGetOid(typeTup);
/*
! * Base type must be a plain base type, another domain or an enum. Domains
! * over pseudotypes would create a security hole. Domains over composite
! * types might be made to work in the future, but not today.
*/
typtype = baseType->typtype;
if (typtype != TYPTYPE_BASE &&
typtype != TYPTYPE_DOMAIN &&
! typtype != TYPTYPE_ENUM)
ereport(ERROR,
(errcode(ERRCODE_DATATYPE_MISMATCH),
errmsg("\"%s\" is not a valid base type for a domain",
--- 814,827 ----
basetypeoid = HeapTupleGetOid(typeTup);
/*
! * Base type must be a plain base type, composite type, another domain or
! * an enum. Domains over pseudotypes would create a security hole.
*/
typtype = baseType->typtype;
if (typtype != TYPTYPE_BASE &&
typtype != TYPTYPE_DOMAIN &&
! typtype != TYPTYPE_ENUM &&
! typtype != TYPTYPE_COMPOSITE)
ereport(ERROR,
(errcode(ERRCODE_DATATYPE_MISMATCH),
errmsg("\"%s\" is not a valid base type for a domain",
diff --git a/src/backend/parser/parse_coerce.c b/src/backend/parser/parse_coerce.c
new file mode 100644
index 0418972..39a1835
*** a/src/backend/parser/parse_coerce.c
--- b/src/backend/parser/parse_coerce.c
*************** coerce_type(ParseState *pstate, Node *no
*** 369,382 ****
return result;
}
if (inputTypeId == RECORDOID &&
! ISCOMPLEX(targetTypeId))
{
/* Coerce a RECORD to a specific complex type */
return coerce_record_to_complex(pstate, node, targetTypeId,
ccontext, cformat, location);
}
if (targetTypeId == RECORDOID &&
! ISCOMPLEX(inputTypeId))
{
/* Coerce a specific complex type to RECORD */
/* NB: we do NOT want a RelabelType here */
--- 369,382 ----
return result;
}
if (inputTypeId == RECORDOID &&
! isComplex(targetTypeId))
{
/* Coerce a RECORD to a specific complex type */
return coerce_record_to_complex(pstate, node, targetTypeId,
ccontext, cformat, location);
}
if (targetTypeId == RECORDOID &&
! isComplex(inputTypeId))
{
/* Coerce a specific complex type to RECORD */
/* NB: we do NOT want a RelabelType here */
*************** can_coerce_type(int nargs, Oid *input_ty
*** 478,491 ****
* coerce (may need tighter checking here)
*/
if (inputTypeId == RECORDOID &&
! ISCOMPLEX(targetTypeId))
continue;
/*
* If input is a composite type and target is RECORD, accept
*/
if (targetTypeId == RECORDOID &&
! ISCOMPLEX(inputTypeId))
continue;
#ifdef NOT_USED /* not implemented yet */
--- 478,491 ----
* coerce (may need tighter checking here)
*/
if (inputTypeId == RECORDOID &&
! isComplex(targetTypeId))
continue;
/*
* If input is a composite type and target is RECORD, accept
*/
if (targetTypeId == RECORDOID &&
! isComplex(inputTypeId))
continue;
#ifdef NOT_USED /* not implemented yet */
*************** coerce_record_to_complex(ParseState *pst
*** 841,846 ****
--- 841,847 ----
CoercionForm cformat,
int location)
{
+ Node *result;
RowExpr *rowexpr;
TupleDesc tupdesc;
List *args = NIL;
*************** coerce_record_to_complex(ParseState *pst
*** 949,955 ****
rowexpr->row_format = cformat;
rowexpr->colnames = NIL; /* not needed for named target type */
rowexpr->location = location;
! return (Node *) rowexpr;
}
/*
--- 950,965 ----
rowexpr->row_format = cformat;
rowexpr->colnames = NIL; /* not needed for named target type */
rowexpr->location = location;
!
! result = (Node *) rowexpr;
!
! if (get_typtype(targetTypeId) == TYPTYPE_DOMAIN)
! {
! result = coerce_to_domain(result, InvalidOid, -1, targetTypeId,
! cformat, location, true, false);
! }
!
! return (Node *) result;
}
/*
*************** IsBinaryCoercible(Oid srctype, Oid targe
*** 1811,1817 ****
/* Also accept any composite type as coercible to RECORD */
if (targettype == RECORDOID)
! if (ISCOMPLEX(srctype))
return true;
/* Also accept any composite array type as coercible to RECORD[] */
--- 1821,1827 ----
/* Also accept any composite type as coercible to RECORD */
if (targettype == RECORDOID)
! if (isComplex(srctype))
return true;
/* Also accept any composite array type as coercible to RECORD[] */
*************** is_complex_array(Oid typid)
*** 2079,2085 ****
{
Oid elemtype = get_element_type(typid);
! return (OidIsValid(elemtype) && ISCOMPLEX(elemtype));
}
--- 2089,2095 ----
{
Oid elemtype = get_element_type(typid);
! return (OidIsValid(elemtype) && isComplex(elemtype));
}
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
new file mode 100644
index 08f0439..86a4b81
*** a/src/backend/parser/parse_expr.c
--- b/src/backend/parser/parse_expr.c
*************** unknown_attribute(ParseState *pstate, No
*** 370,376 ****
/* Have to do it by reference to the type of the expression */
Oid relTypeId = exprType(relref);
! if (ISCOMPLEX(relTypeId))
ereport(ERROR,
(errcode(ERRCODE_UNDEFINED_COLUMN),
errmsg("column \"%s\" not found in data type %s",
--- 370,376 ----
/* Have to do it by reference to the type of the expression */
Oid relTypeId = exprType(relref);
! if (isComplex(relTypeId))
ereport(ERROR,
(errcode(ERRCODE_UNDEFINED_COLUMN),
errmsg("column \"%s\" not found in data type %s",
diff --git a/src/backend/parser/parse_func.c b/src/backend/parser/parse_func.c
new file mode 100644
index 75f1e20..172291e
*** a/src/backend/parser/parse_func.c
--- b/src/backend/parser/parse_func.c
*************** ParseFuncOrColumn(ParseState *pstate, Li
*** 178,184 ****
{
Oid argtype = actual_arg_types[0];
! if (argtype == RECORDOID || ISCOMPLEX(argtype))
{
retval = ParseComplexProjection(pstate,
strVal(linitial(funcname)),
--- 178,184 ----
{
Oid argtype = actual_arg_types[0];
! if (argtype == RECORDOID || isComplex(argtype))
{
retval = ParseComplexProjection(pstate,
strVal(linitial(funcname)),
*************** func_get_detail(List *funcname,
*** 1033,1039 ****
break;
case COERCION_PATH_COERCEVIAIO:
if ((sourceType == RECORDOID ||
! ISCOMPLEX(sourceType)) &&
TypeCategory(targetType) == TYPCATEGORY_STRING)
iscoercion = false;
else
--- 1033,1039 ----
break;
case COERCION_PATH_COERCEVIAIO:
if ((sourceType == RECORDOID ||
! isComplex(sourceType)) &&
TypeCategory(targetType) == TYPCATEGORY_STRING)
iscoercion = false;
else
diff --git a/src/backend/parser/parse_type.c b/src/backend/parser/parse_type.c
new file mode 100644
index ac62cbc..1fbe958
*** a/src/backend/parser/parse_type.c
--- b/src/backend/parser/parse_type.c
*************** typeidTypeRelid(Oid type_id)
*** 630,635 ****
--- 630,648 ----
return result;
}
+ bool
+ isComplex(Oid typeid)
+ {
+ Oid basetype;
+
+ if (typeidTypeRelid(typeid) != InvalidOid)
+ return true;
+
+ basetype = getBaseType(typeid);
+
+ return (typeidTypeRelid(basetype) != InvalidOid);
+ }
+
/*
* error context callback for parse failure during parseTypeString()
*/
diff --git a/src/backend/utils/cache/lsyscache.c b/src/backend/utils/cache/lsyscache.c
new file mode 100644
index d3b2a5a..de6789d
*** a/src/backend/utils/cache/lsyscache.c
--- b/src/backend/utils/cache/lsyscache.c
*************** getBaseTypeAndTypmod(Oid typid, int32 *t
*** 2132,2137 ****
--- 2132,2175 ----
}
/*
+ * getBaseTypeAndTypeAndRelid
+ * If the given type is a domain, return its base type and relid.
+ */
+ Oid
+ getBaseTypeAndTypeAndRelid(Oid typid, char *type, Oid *relid)
+ {
+ /*
+ * We loop to find the bottom base type in a stack of domains.
+ */
+ for (;;)
+ {
+ HeapTuple tup;
+ Form_pg_type typTup;
+
+ tup = SearchSysCache1(TYPEOID, ObjectIdGetDatum(typid));
+ if (!HeapTupleIsValid(tup))
+ elog(ERROR, "cache lookup failed for type %u", typid);
+ typTup = (Form_pg_type) GETSTRUCT(tup);
+ if (typTup->typtype != TYPTYPE_DOMAIN)
+ {
+ *relid = typTup->typrelid;
+ *type = typTup->typtype;
+
+ /* Not a domain, so done */
+ ReleaseSysCache(tup);
+ break;
+ }
+
+ typid = typTup->typbasetype;
+
+ ReleaseSysCache(tup);
+ }
+
+ return typid;
+ }
+
+
+ /*
* get_typavgwidth
*
* Given a type OID and a typmod value (pass -1 if typmod is unknown),
diff --git a/src/backend/utils/cache/typcache.c b/src/backend/utils/cache/typcache.c
new file mode 100644
index 2769a30..a2c1e15
*** a/src/backend/utils/cache/typcache.c
--- b/src/backend/utils/cache/typcache.c
*************** lookup_type_cache(Oid type_id, int flags
*** 131,136 ****
--- 131,139 ----
{
TypeCacheEntry *typentry;
bool found;
+ char basetyptype = 0;
+ Oid basetyprelid = InvalidOid;
+ Oid baseoid = InvalidOid;
if (TypeCacheHash == NULL)
{
*************** lookup_type_cache(Oid type_id, int flags
*** 190,195 ****
--- 193,204 ----
typentry->typtype = typtup->typtype;
typentry->typrelid = typtup->typrelid;
+ /*
+ * If it's a domain type of a composite type, get the base type and relid
+ */
+ if (typentry->typtype == TYPTYPE_DOMAIN)
+ baseoid = getBaseTypeAndTypeAndRelid(typtup->typbasetype, &basetyptype, &basetyprelid);
+
ReleaseSysCache(tp);
}
*************** lookup_type_cache(Oid type_id, int flags
*** 359,373 ****
*/
if ((flags & TYPECACHE_TUPDESC) &&
typentry->tupDesc == NULL &&
! typentry->typtype == TYPTYPE_COMPOSITE)
{
Relation rel;
! if (!OidIsValid(typentry->typrelid)) /* should not happen */
elog(ERROR, "invalid typrelid for composite type %u",
! typentry->type_id);
! rel = relation_open(typentry->typrelid, AccessShareLock);
! Assert(rel->rd_rel->reltype == typentry->type_id);
/*
* Link to the tupdesc and increment its refcount (we assert it's a
--- 368,386 ----
*/
if ((flags & TYPECACHE_TUPDESC) &&
typentry->tupDesc == NULL &&
! ((typentry->typtype == TYPTYPE_COMPOSITE) ||
! ((typentry->typtype == TYPTYPE_DOMAIN) && (basetyptype == TYPTYPE_COMPOSITE))))
{
Relation rel;
! Oid relid = typentry->typtype == TYPTYPE_COMPOSITE ? typentry->typrelid : basetyprelid;
! Oid type_id = typentry->typtype == TYPTYPE_COMPOSITE ? typentry->type_id : baseoid;
!
! if (!OidIsValid(relid)) /* should not happen */
elog(ERROR, "invalid typrelid for composite type %u",
! type_id);
! rel = relation_open(relid, AccessShareLock);
! Assert(rel->rd_rel->reltype == type_id);
/*
* Link to the tupdesc and increment its refcount (we assert it's a
diff --git a/src/backend/utils/fmgr/funcapi.c b/src/backend/utils/fmgr/funcapi.c
new file mode 100644
index aa249fa..a854600
*** a/src/backend/utils/fmgr/funcapi.c
--- b/src/backend/utils/fmgr/funcapi.c
***************
*** 20,25 ****
--- 20,26 ----
#include "funcapi.h"
#include "nodes/nodeFuncs.h"
#include "parser/parse_coerce.h"
+ #include "parser/parse_type.h"
#include "utils/array.h"
#include "utils/builtins.h"
#include "utils/lsyscache.h"
*************** get_type_func_class(Oid typid)
*** 660,667 ****
{
case TYPTYPE_COMPOSITE:
return TYPEFUNC_COMPOSITE;
- case TYPTYPE_BASE:
case TYPTYPE_DOMAIN:
case TYPTYPE_ENUM:
return TYPEFUNC_SCALAR;
case TYPTYPE_PSEUDO:
--- 661,672 ----
{
case TYPTYPE_COMPOSITE:
return TYPEFUNC_COMPOSITE;
case TYPTYPE_DOMAIN:
+ if (isComplex(typid))
+ return TYPEFUNC_COMPOSITE;
+ else
+ return TYPEFUNC_SCALAR;
+ case TYPTYPE_BASE:
case TYPTYPE_ENUM:
return TYPEFUNC_SCALAR;
case TYPTYPE_PSEUDO:
diff --git a/src/include/parser/parse_type.h b/src/include/parser/parse_type.h
new file mode 100644
index 373acd8..d0c15cf
*** a/src/include/parser/parse_type.h
--- b/src/include/parser/parse_type.h
*************** extern Oid typeidTypeRelid(Oid type_id);
*** 47,52 ****
extern void parseTypeString(const char *str, Oid *typeid_p, int32 *typmod_p);
! #define ISCOMPLEX(typeid) (typeidTypeRelid(typeid) != InvalidOid)
#endif /* PARSE_TYPE_H */
--- 47,53 ----
extern void parseTypeString(const char *str, Oid *typeid_p, int32 *typmod_p);
! extern bool isComplex(Oid typeid);
!
#endif /* PARSE_TYPE_H */
diff --git a/src/include/utils/lsyscache.h b/src/include/utils/lsyscache.h
new file mode 100644
index 0a419dc..750cdd4
*** a/src/include/utils/lsyscache.h
--- b/src/include/utils/lsyscache.h
*************** extern Oid get_typcollation(Oid typid);
*** 129,134 ****
--- 129,135 ----
extern bool type_is_collatable(Oid typid);
extern Oid getBaseType(Oid typid);
extern Oid getBaseTypeAndTypmod(Oid typid, int32 *typmod);
+ extern Oid getBaseTypeAndTypeAndRelid(Oid typid, char *type, Oid *relid);
extern int32 get_typavgwidth(Oid typid, int32 typmod);
extern int32 get_attavgwidth(Oid relid, AttrNumber attnum);
extern bool get_attstatsslot(HeapTuple statstuple,
diff --git a/src/test/regress/expected/domaincomp.out b/src/test/regress/expected/domaincomp.out
new file mode 100644
index ...191fa69
*** a/src/test/regress/expected/domaincomp.out
--- b/src/test/regress/expected/domaincomp.out
***************
*** 0 ****
--- 1,207 ----
+ --
+ -- Test domains on composite types.
+ --
+ create type droptest as (
+ r double precision,
+ i double precision
+ );
+ -- Test Comment / Drop
+ create domain domaindroptest droptest;
+ comment on domain domaindroptest is 'About to drop this..';
+ create domain dependenttypetest domaindroptest;
+ -- fail because of dependent type
+ drop domain domaindroptest;
+ ERROR: cannot drop type domaindroptest because other objects depend on it
+ DETAIL: type dependenttypetest depends on type domaindroptest
+ HINT: Use DROP ... CASCADE to drop the dependent objects too.
+ drop domain domaindroptest cascade;
+ NOTICE: drop cascades to type dependenttypetest
+ -- this should fail because already gone
+ drop domain domaindroptest cascade;
+ ERROR: type "domaindroptest" does not exist
+ drop type droptest;
+ -- test table type
+ create table inventory_item (
+ name text,
+ supplier_id integer,
+ price numeric
+ );
+ create domain item_d as inventory_item
+ check (VALUE IS NULL OR (VALUE).price IS NOT NULL);
+ create domain cheap_d as inventory_item
+ check ((VALUE).price < 1);
+ create domain expensive_d as inventory_item
+ check ((VALUE).price > 10);
+ -- Test domain input.
+ -- Note: the point of checking both INSERT and COPY FROM is that INSERT
+ -- exercises CoerceToDomain while COPY exercises domain_in.
+ -- Test explicit coercions --- these should succeed
+ SELECT cast(ROW('fuzzy dice', 42, 1.99) as item_d);
+ row
+ ------------------------
+ ("fuzzy dice",42,1.99)
+ (1 row)
+
+ -- Test tables using domains
+ create table basictest
+ ( test item_d,
+ cheap cheap_d,
+ expensive expensive_d
+ );
+ INSERT INTO basictest values (NULL, ROW('fuzzy dice', 42, 1.99), ROW('fuzzy dice', 42, 11.99)); -- bad cheap_d
+ ERROR: value for domain cheap_d violates check constraint "cheap_d_check"
+ INSERT INTO basictest values (ROW('normal dice', 42, NULL), NULL, NULL); -- Bad item_d with NULL price
+ ERROR: value for domain item_d violates check constraint "item_d_check"
+ INSERT INTO basictest values (NULL, ROW('fuzzy dice', 42, 0.99), ROW('fuzzy dice', 42, 11.99)); -- good
+ -- Test copy
+ COPY basictest (cheap) FROM stdin; -- fail
+ ERROR: value for domain cheap_d violates check constraint "cheap_d_check"
+ CONTEXT: COPY basictest, line 1, column cheap: "("fuzzy dice",42, 999)"
+ COPY basictest (cheap) FROM stdin;
+ select * from basictest;
+ test | cheap | expensive
+ ------+------------------------+-------------------------
+ | ("fuzzy dice",42,0.99) | ("fuzzy dice",42,11.99)
+ | ("fuzzy dice",42,0.9) |
+ (2 rows)
+
+ -- check that domains inherit operations from base types
+ select cheap = cast(ROW('fuzzy dice',42, 0.99) as inventory_item)
+ from basictest;
+ ?column?
+ ----------
+ t
+ f
+ (2 rows)
+
+ drop table basictest;
+ drop domain item_d;
+ drop domain cheap_d;
+ drop domain expensive_d;
+ create domain dnotnull inventory_item NOT NULL;
+ create domain dnull inventory_item;
+ create domain dcheck inventory_item NOT NULL CHECK ((VALUE).name = 'a' OR (VALUE).name = 'c' OR (VALUE).name = 'd');
+ create table nulltest
+ ( col1 dnotnull
+ , col2 dnotnull NULL -- NOT NULL in the domain cannot be overridden
+ , col3 dnull NOT NULL
+ , col4 dnull
+ , col5 dcheck CHECK ((col5).name IN ('c', 'd'))
+ );
+ INSERT INTO nulltest DEFAULT VALUES;
+ ERROR: domain dnotnull does not allow null values
+ INSERT INTO nulltest values (ROW('a',NULL,NULL), ROW('b',NULL,NULL), ROW('c',NULL,NULL), ROW('d',NULL,NULL), ROW('c',NULL,NULL)); -- Good
+ insert into nulltest values (ROW('a',NULL,NULL), ROW('b',NULL,NULL), ROW('c',NULL,NULL), ROW('d',NULL,NULL), NULL);
+ ERROR: domain dcheck does not allow null values
+ insert into nulltest values (ROW('a',NULL,NULL), ROW('b',NULL,NULL), ROW('c',NULL,NULL), ROW('d',NULL,NULL), ROW('a',NULL,NULL));
+ ERROR: new row for relation "nulltest" violates check constraint "nulltest_col5_check"
+ INSERT INTO nulltest values (NULL, ROW('b',NULL,NULL), ROW('c',NULL,NULL), ROW('d',NULL,NULL), ROW('d',NULL,NULL));
+ ERROR: domain dnotnull does not allow null values
+ INSERT INTO nulltest values (ROW('a',NULL,NULL), NULL, ROW('c',NULL,NULL), ROW('d',NULL,NULL), ROW('c',NULL,NULL));
+ ERROR: domain dnotnull does not allow null values
+ INSERT INTO nulltest values (ROW('a',NULL,NULL), ROW('b',NULL,NULL), NULL, ROW('d',NULL,NULL), ROW('c',NULL,NULL));
+ ERROR: null value in column "col3" violates not-null constraint
+ INSERT INTO nulltest values (ROW('a',NULL,NULL), ROW('b',NULL,NULL), ROW('c',NULL,NULL), NULL, ROW('d',NULL,NULL)); -- Good
+ -- Test copy
+ COPY nulltest FROM stdin; --fail
+ ERROR: null value in column "col3" violates not-null constraint
+ CONTEXT: COPY nulltest, line 1: "(a,,) (b,,) \N (d,,) (d,,)"
+ COPY nulltest FROM stdin; --fail
+ ERROR: domain dcheck does not allow null values
+ CONTEXT: COPY nulltest, line 1, column col5: null input
+ -- Last row is bad
+ COPY nulltest FROM stdin;
+ ERROR: new row for relation "nulltest" violates check constraint "nulltest_col5_check"
+ CONTEXT: COPY nulltest, line 3: "(a,,) (b,,) (c,,) \N (a,,)"
+ select * from nulltest;
+ col1 | col2 | col3 | col4 | col5
+ -------+-------+-------+-------+-------
+ (a,,) | (b,,) | (c,,) | (d,,) | (c,,)
+ (a,,) | (b,,) | (c,,) | | (d,,)
+ (2 rows)
+
+ -- Test out coerced (casted) constraints
+ SELECT cast(ROW('1',NULL,NULL) as dnotnull);
+ row
+ -------
+ (1,,)
+ (1 row)
+
+ SELECT cast(NULL as dnotnull); -- fail
+ ERROR: domain dnotnull does not allow null values
+ SELECT cast(cast(NULL as dnull) as dnotnull); -- fail
+ ERROR: domain dnotnull does not allow null values
+ SELECT cast(col4 as dnotnull) from nulltest; -- fail
+ ERROR: domain dnotnull does not allow null values
+ -- cleanup
+ drop table nulltest;
+ drop domain dnotnull restrict;
+ drop domain dnull restrict;
+ drop domain dcheck restrict;
+ create domain ddef1 inventory_item DEFAULT ROW('f',1,2);
+ -- Type mixing, function returns int8
+ create domain ddef3 inventory_item DEFAULT ROW(5,NULL,NULL);
+ create sequence ddef4_seq;
+ create domain ddef4 inventory_item DEFAULT ROW(nextval('ddef4_seq'),nextval('ddef4_seq'),nextval('ddef4_seq'));
+ create domain ddef5 inventory_item NOT NULL DEFAULT ROW(NULL,12.12,NULL);
+ create table defaulttest
+ ( col1 ddef1
+ , col3 ddef3
+ , col4 ddef4 PRIMARY KEY
+ , col5 ddef1 NOT NULL DEFAULT NULL
+ , col7 ddef4 DEFAULT ROW(8000,8000,8000)
+ , col8 ddef5
+ );
+ NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "defaulttest_pkey" for table "defaulttest"
+ insert into defaulttest(col4) values(ROW(0,0,0)); -- fails, col5 defaults to null
+ ERROR: null value in column "col5" violates not-null constraint
+ alter table defaulttest alter column col5 drop default;
+ insert into defaulttest default values; -- succeeds, inserts domain default
+ -- We used to treat SET DEFAULT NULL as equivalent to DROP DEFAULT; wrong
+ alter table defaulttest alter column col5 set default null;
+ insert into defaulttest(col4) values(ROW(0,0,0)); -- fails
+ ERROR: null value in column "col5" violates not-null constraint
+ alter table defaulttest alter column col5 drop default;
+ insert into defaulttest default values;
+ insert into defaulttest default values;
+ -- Test defaults with copy
+ COPY defaulttest(col5) FROM stdin;
+ select * from defaulttest;
+ col1 | col3 | col4 | col5 | col7 | col8
+ ---------+-------+------------+---------+------------------+--------
+ (f,1,2) | (5,,) | (1,2,3) | (f,1,2) | (8000,8000,8000) | (,12,)
+ (f,1,2) | (5,,) | (4,5,6) | (f,1,2) | (8000,8000,8000) | (,12,)
+ (f,1,2) | (5,,) | (7,8,9) | (f,1,2) | (8000,8000,8000) | (,12,)
+ (f,1,2) | (5,,) | (10,11,12) | (a,,) | (8000,8000,8000) | (,12,)
+ (4 rows)
+
+ drop table defaulttest cascade;
+ -- Test domains over domains
+ create domain level1 inventory_item;
+ create domain level2 level1 check ((VALUE).price > 10);
+ create domain level3 level2 check ((VALUE).price > 100);
+ select ROW(NULL,NULL,1)::level1;
+ row
+ -------
+ (,,1)
+ (1 row)
+
+ select ROW(NULL,NULL,1)::level2; --fail
+ ERROR: value for domain level2 violates check constraint "level2_check"
+ select ROW(NULL,NULL,20)::level3; --fail
+ ERROR: value for domain level3 violates check constraint "level3_check"
+ create temp table dtest(f1 level3);
+ insert into dtest values(ROW(NULL,NULL,200));
+ insert into dtest values(ROW(NULL,NULL,20)); -- fail
+ ERROR: value for domain level3 violates check constraint "level3_check"
+ drop table dtest;
+ drop domain level1 cascade;
+ NOTICE: drop cascades to 2 other objects
+ DETAIL: drop cascades to type level2
+ drop cascades to type level3
+ drop table inventory_item cascade;
+ NOTICE: drop cascades to 4 other objects
+ DETAIL: drop cascades to type ddef1
+ drop cascades to type ddef3
+ drop cascades to type ddef4
+ drop cascades to type ddef5
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
new file mode 100644
index 376f28d..1aec114
*** a/src/test/regress/parallel_schedule
--- b/src/test/regress/parallel_schedule
*************** test: select_views portals_p2 foreign_ke
*** 97,101 ****
--- 97,103 ----
# ----------
test: plancache limit plpgsql copy2 temp domain rangefuncs prepare without_oid conversion truncate alter_table sequence polymorphism rowtypes returning largeobject with xml
+ test: domaincomp
+
# run stats by itself because its delay may be insufficient under heavy load
test: stats
diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule
new file mode 100644
index bb654f9..fcfe75f
*** a/src/test/regress/serial_schedule
--- b/src/test/regress/serial_schedule
*************** test: plpgsql
*** 113,118 ****
--- 113,119 ----
test: copy2
test: temp
test: domain
+ test: domaincomp
test: rangefuncs
test: prepare
test: without_oid
diff --git a/src/test/regress/sql/domaincomp.sql b/src/test/regress/sql/domaincomp.sql
new file mode 100644
index ...00f55c9
*** a/src/test/regress/sql/domaincomp.sql
--- b/src/test/regress/sql/domaincomp.sql
***************
*** 0 ****
--- 1,182 ----
+ --
+ -- Test domains on composite types.
+ --
+
+ create type droptest as (
+ r double precision,
+ i double precision
+ );
+
+ -- Test Comment / Drop
+ create domain domaindroptest droptest;
+ comment on domain domaindroptest is 'About to drop this..';
+
+ create domain dependenttypetest domaindroptest;
+
+ -- fail because of dependent type
+ drop domain domaindroptest;
+
+ drop domain domaindroptest cascade;
+
+ -- this should fail because already gone
+ drop domain domaindroptest cascade;
+
+ drop type droptest;
+
+ -- test table type
+ create table inventory_item (
+ name text,
+ supplier_id integer,
+ price numeric
+ );
+
+ create domain item_d as inventory_item
+ check (VALUE IS NULL OR (VALUE).price IS NOT NULL);
+ create domain cheap_d as inventory_item
+ check ((VALUE).price < 1);
+ create domain expensive_d as inventory_item
+ check ((VALUE).price > 10);
+
+ -- Test domain input.
+
+ -- Note: the point of checking both INSERT and COPY FROM is that INSERT
+ -- exercises CoerceToDomain while COPY exercises domain_in.
+
+ -- Test explicit coercions --- these should succeed
+ SELECT cast(ROW('fuzzy dice', 42, 1.99) as item_d);
+
+ -- Test tables using domains
+ create table basictest
+ ( test item_d,
+ cheap cheap_d,
+ expensive expensive_d
+ );
+
+ INSERT INTO basictest values (NULL, ROW('fuzzy dice', 42, 1.99), ROW('fuzzy dice', 42, 11.99)); -- bad cheap_d
+ INSERT INTO basictest values (ROW('normal dice', 42, NULL), NULL, NULL); -- Bad item_d with NULL price
+ INSERT INTO basictest values (NULL, ROW('fuzzy dice', 42, 0.99), ROW('fuzzy dice', 42, 11.99)); -- good
+
+ -- Test copy
+ COPY basictest (cheap) FROM stdin; -- fail
+ ("fuzzy dice",42, 999)
+ \.
+
+ COPY basictest (cheap) FROM stdin;
+ ("fuzzy dice",42, 0.9)
+ \.
+
+ select * from basictest;
+
+ -- check that domains inherit operations from base types
+ select cheap = cast(ROW('fuzzy dice',42, 0.99) as inventory_item)
+ from basictest;
+
+ drop table basictest;
+ drop domain item_d;
+ drop domain cheap_d;
+ drop domain expensive_d;
+
+ create domain dnotnull inventory_item NOT NULL;
+ create domain dnull inventory_item;
+ create domain dcheck inventory_item NOT NULL CHECK ((VALUE).name = 'a' OR (VALUE).name = 'c' OR (VALUE).name = 'd');
+
+ create table nulltest
+ ( col1 dnotnull
+ , col2 dnotnull NULL -- NOT NULL in the domain cannot be overridden
+ , col3 dnull NOT NULL
+ , col4 dnull
+ , col5 dcheck CHECK ((col5).name IN ('c', 'd'))
+ );
+ INSERT INTO nulltest DEFAULT VALUES;
+ INSERT INTO nulltest values (ROW('a',NULL,NULL), ROW('b',NULL,NULL), ROW('c',NULL,NULL), ROW('d',NULL,NULL), ROW('c',NULL,NULL)); -- Good
+ insert into nulltest values (ROW('a',NULL,NULL), ROW('b',NULL,NULL), ROW('c',NULL,NULL), ROW('d',NULL,NULL), NULL);
+ insert into nulltest values (ROW('a',NULL,NULL), ROW('b',NULL,NULL), ROW('c',NULL,NULL), ROW('d',NULL,NULL), ROW('a',NULL,NULL));
+ INSERT INTO nulltest values (NULL, ROW('b',NULL,NULL), ROW('c',NULL,NULL), ROW('d',NULL,NULL), ROW('d',NULL,NULL));
+ INSERT INTO nulltest values (ROW('a',NULL,NULL), NULL, ROW('c',NULL,NULL), ROW('d',NULL,NULL), ROW('c',NULL,NULL));
+ INSERT INTO nulltest values (ROW('a',NULL,NULL), ROW('b',NULL,NULL), NULL, ROW('d',NULL,NULL), ROW('c',NULL,NULL));
+ INSERT INTO nulltest values (ROW('a',NULL,NULL), ROW('b',NULL,NULL), ROW('c',NULL,NULL), NULL, ROW('d',NULL,NULL)); -- Good
+
+ -- Test copy
+ COPY nulltest FROM stdin; --fail
+ (a,,) (b,,) \N (d,,) (d,,)
+ \.
+
+ COPY nulltest FROM stdin; --fail
+ (a,,) (b,,) (c,,) (d,,) \N
+ \.
+
+ -- Last row is bad
+ COPY nulltest FROM stdin;
+ (a,,) (b,,) (c,,) \N (c,,)
+ (a,,) (b,,) (c,,) \N (d,,)
+ (a,,) (b,,) (c,,) \N (a,,)
+ \.
+
+ select * from nulltest;
+
+ -- Test out coerced (casted) constraints
+ SELECT cast(ROW('1',NULL,NULL) as dnotnull);
+ SELECT cast(NULL as dnotnull); -- fail
+ SELECT cast(cast(NULL as dnull) as dnotnull); -- fail
+ SELECT cast(col4 as dnotnull) from nulltest; -- fail
+
+ -- cleanup
+ drop table nulltest;
+ drop domain dnotnull restrict;
+ drop domain dnull restrict;
+ drop domain dcheck restrict;
+
+
+ create domain ddef1 inventory_item DEFAULT ROW('f',1,2);
+ -- Type mixing, function returns int8
+ create domain ddef3 inventory_item DEFAULT ROW(5,NULL,NULL);
+ create sequence ddef4_seq;
+ create domain ddef4 inventory_item DEFAULT ROW(nextval('ddef4_seq'),nextval('ddef4_seq'),nextval('ddef4_seq'));
+ create domain ddef5 inventory_item NOT NULL DEFAULT ROW(NULL,12.12,NULL);
+
+ create table defaulttest
+ ( col1 ddef1
+ , col3 ddef3
+ , col4 ddef4 PRIMARY KEY
+ , col5 ddef1 NOT NULL DEFAULT NULL
+ , col7 ddef4 DEFAULT ROW(8000,8000,8000)
+ , col8 ddef5
+ );
+ insert into defaulttest(col4) values(ROW(0,0,0)); -- fails, col5 defaults to null
+ alter table defaulttest alter column col5 drop default;
+ insert into defaulttest default values; -- succeeds, inserts domain default
+ -- We used to treat SET DEFAULT NULL as equivalent to DROP DEFAULT; wrong
+ alter table defaulttest alter column col5 set default null;
+ insert into defaulttest(col4) values(ROW(0,0,0)); -- fails
+ alter table defaulttest alter column col5 drop default;
+ insert into defaulttest default values;
+ insert into defaulttest default values;
+
+ -- Test defaults with copy
+ COPY defaulttest(col5) FROM stdin;
+ (a,,)
+ \.
+
+ select * from defaulttest;
+
+ drop table defaulttest cascade;
+
+ -- Test domains over domains
+ create domain level1 inventory_item;
+ create domain level2 level1 check ((VALUE).price > 10);
+ create domain level3 level2 check ((VALUE).price > 100);
+
+ select ROW(NULL,NULL,1)::level1;
+ select ROW(NULL,NULL,1)::level2; --fail
+ select ROW(NULL,NULL,20)::level3; --fail
+
+ create temp table dtest(f1 level3);
+
+ insert into dtest values(ROW(NULL,NULL,200));
+ insert into dtest values(ROW(NULL,NULL,20)); -- fail
+
+ drop table dtest;
+ drop domain level1 cascade;
+
+ drop table inventory_item cascade;
+
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
