I started to look into allowing domains over composite types, which is
another never-implemented case that there's no very good reason not to
allow. Well, other than the argument that the SQL standard only allows
domains over "predefined" (built-in) types ... but we blew past that
restriction ages ago.
Any thought that there might be some fundamental problem with that was
soon dispelled when I noticed that we allow domains over arrays of
composite types. Ahem.
They even work, mostly. I wrote a few test cases and couldn't find
anything that failed except for attempts to insert or update multiple
subfields of the same base column; that's because process_matched_tle()
fails to look through CoerceToDomain nodes. But that turns out to be a
bug even for the simpler case of domains over arrays of scalars, which
is certainly supported. That is, given
create domain domainint4arr int4[];
create table domarrtest (testint4arr domainint4arr);
this ought to work:
insert into domarrtest (testint4arr[1], testint4arr[3]) values (11,22);
It would work with a plain-array target column, but with the domain
it fails with
ERROR: multiple assignments to same column "testint4arr"
Hence, the attached proposed patch, which fixes that oversight and
adds some relevant test cases. (I've not yet looked to see if any
documentation changes would be appropriate.)
I think this is a bug fix and should be back-patched. Any objections?
regards, tom lane
diff --git a/src/backend/rewrite/rewriteHandler.c b/src/backend/rewrite/rewriteHandler.c
index f3c7526..0a6f4f3 100644
*** a/src/backend/rewrite/rewriteHandler.c
--- b/src/backend/rewrite/rewriteHandler.c
*************** process_matched_tle(TargetEntry *src_tle
*** 934,939 ****
--- 934,940 ----
const char *attrName)
{
TargetEntry *result;
+ CoerceToDomain *coerce_expr = NULL;
Node *src_expr;
Node *prior_expr;
Node *src_input;
*************** process_matched_tle(TargetEntry *src_tle
*** 970,979 ****
--- 971,1000 ----
* For FieldStore, instead of nesting we can generate a single
* FieldStore with multiple target fields. We must nest when
* ArrayRefs are involved though.
+ *
+ * As a further complication, the destination column might be a domain,
+ * resulting in each assignment containing a CoerceToDomain node over a
+ * FieldStore or ArrayRef. These should have matching target domains, so
+ * we strip them and reconstitute a single CoerceToDomain over the
+ * combined FieldStore/ArrayRef nodes. (Notice that this has the result
+ * that the domain's checks are applied only after we do all the field or
+ * element updates, not after each one. This is arguably desirable.)
*----------
*/
src_expr = (Node *) src_tle->expr;
prior_expr = (Node *) prior_tle->expr;
+
+ if (src_expr && IsA(src_expr, CoerceToDomain) &&
+ prior_expr && IsA(prior_expr, CoerceToDomain) &&
+ ((CoerceToDomain *) src_expr)->resulttype ==
+ ((CoerceToDomain *) prior_expr)->resulttype)
+ {
+ /* we assume without checking that resulttypmod/resultcollid match */
+ coerce_expr = (CoerceToDomain *) src_expr;
+ src_expr = (Node *) ((CoerceToDomain *) src_expr)->arg;
+ prior_expr = (Node *) ((CoerceToDomain *) prior_expr)->arg;
+ }
+
src_input = get_assignment_input(src_expr);
prior_input = get_assignment_input(prior_expr);
if (src_input == NULL ||
*************** process_matched_tle(TargetEntry *src_tle
*** 1042,1047 ****
--- 1063,1078 ----
newexpr = NULL;
}
+ if (coerce_expr)
+ {
+ /* put back the CoerceToDomain */
+ CoerceToDomain *newcoerce = makeNode(CoerceToDomain);
+
+ memcpy(newcoerce, coerce_expr, sizeof(CoerceToDomain));
+ newcoerce->arg = (Expr *) newexpr;
+ newexpr = (Node *) newcoerce;
+ }
+
result = flatCopyTargetEntry(src_tle);
result->expr = (Expr *) newexpr;
return result;
diff --git a/src/test/regress/expected/domain.out b/src/test/regress/expected/domain.out
index 41b70e2..9fb750d 100644
*** a/src/test/regress/expected/domain.out
--- b/src/test/regress/expected/domain.out
*************** INSERT INTO domarrtest values ('{2,2}',
*** 107,112 ****
--- 107,113 ----
INSERT INTO domarrtest values (NULL, '{{"a","b","c"},{"d","e","f"}}');
INSERT INTO domarrtest values (NULL, '{{"toolong","b","c"},{"d","e","f"}}');
ERROR: value too long for type character varying(4)
+ INSERT INTO domarrtest (testint4arr[1], testint4arr[3]) values (11,22);
select * from domarrtest;
testint4arr | testchar4arr
---------------+---------------------
*************** select * from domarrtest;
*** 115,121 ****
{2,2} | {{a,b},{c,d},{e,f}}
{2,2} | {{a},{c}}
| {{a,b,c},{d,e,f}}
! (5 rows)
select testint4arr[1], testchar4arr[2:2] from domarrtest;
testint4arr | testchar4arr
--- 116,123 ----
{2,2} | {{a,b},{c,d},{e,f}}
{2,2} | {{a},{c}}
| {{a,b,c},{d,e,f}}
! {11,NULL,22} |
! (6 rows)
select testint4arr[1], testchar4arr[2:2] from domarrtest;
testint4arr | testchar4arr
*************** select testint4arr[1], testchar4arr[2:2]
*** 125,131 ****
2 | {{c,d}}
2 | {{c}}
| {{d,e,f}}
! (5 rows)
select array_dims(testint4arr), array_dims(testchar4arr) from domarrtest;
array_dims | array_dims
--- 127,134 ----
2 | {{c,d}}
2 | {{c}}
| {{d,e,f}}
! 11 |
! (6 rows)
select array_dims(testint4arr), array_dims(testchar4arr) from domarrtest;
array_dims | array_dims
*************** select array_dims(testint4arr), array_di
*** 135,141 ****
[1:2] | [1:3][1:2]
[1:2] | [1:2][1:1]
| [1:2][1:3]
! (5 rows)
COPY domarrtest FROM stdin;
COPY domarrtest FROM stdin; -- fail
--- 138,145 ----
[1:2] | [1:3][1:2]
[1:2] | [1:2][1:1]
| [1:2][1:3]
! [1:3] |
! (6 rows)
COPY domarrtest FROM stdin;
COPY domarrtest FROM stdin; -- fail
*************** select * from domarrtest;
*** 149,157 ****
{2,2} | {{a,b},{c,d},{e,f}}
{2,2} | {{a},{c}}
| {{a,b,c},{d,e,f}}
{3,4} | {q,w,e}
|
! (7 rows)
drop table domarrtest;
drop domain domainint4arr restrict;
--- 153,173 ----
{2,2} | {{a,b},{c,d},{e,f}}
{2,2} | {{a},{c}}
| {{a,b,c},{d,e,f}}
+ {11,NULL,22} |
{3,4} | {q,w,e}
|
! (8 rows)
!
! update domarrtest set
! testint4arr[1] = testint4arr[1] + 1,
! testint4arr[3] = testint4arr[3] - 1
! where testchar4arr is null;
! select * from domarrtest where testchar4arr is null;
! testint4arr | testchar4arr
! ------------------+--------------
! {12,NULL,21} |
! {NULL,NULL,NULL} |
! (2 rows)
drop table domarrtest;
drop domain domainint4arr restrict;
*************** select pg_typeof('{1,2,3}'::dia || 42);
*** 182,187 ****
--- 198,289 ----
(1 row)
drop domain dia;
+ -- Test domains over arrays of composite
+ create type comptype as (r float8, i float8);
+ create domain dcomptypea as comptype[];
+ create table dcomptable (d1 dcomptypea unique);
+ insert into dcomptable values (array[row(1,2)]::dcomptypea);
+ insert into dcomptable values (array[row(3,4), row(5,6)]::comptype[]);
+ insert into dcomptable values (array[row(7,8)::comptype, row(9,10)::comptype]);
+ insert into dcomptable values (array[row(1,2)]::dcomptypea); -- fail on uniqueness
+ ERROR: duplicate key value violates unique constraint "dcomptable_d1_key"
+ DETAIL: Key (d1)=({"(1,2)"}) already exists.
+ insert into dcomptable (d1[1]) values(row(9,10));
+ insert into dcomptable (d1[1].r) values(11);
+ select * from dcomptable;
+ d1
+ --------------------
+ {"(1,2)"}
+ {"(3,4)","(5,6)"}
+ {"(7,8)","(9,10)"}
+ {"(9,10)"}
+ {"(11,)"}
+ (5 rows)
+
+ select d1[2], d1[1].r, d1[1].i from dcomptable;
+ d1 | r | i
+ --------+----+----
+ | 1 | 2
+ (5,6) | 3 | 4
+ (9,10) | 7 | 8
+ | 9 | 10
+ | 11 |
+ (5 rows)
+
+ update dcomptable set d1[2] = row(d1[2].i, d1[2].r);
+ select * from dcomptable;
+ d1
+ --------------------
+ {"(1,2)","(,)"}
+ {"(3,4)","(6,5)"}
+ {"(7,8)","(10,9)"}
+ {"(9,10)","(,)"}
+ {"(11,)","(,)"}
+ (5 rows)
+
+ update dcomptable set d1[1].r = d1[1].r + 1 where d1[1].i > 0;
+ select * from dcomptable;
+ d1
+ --------------------
+ {"(11,)","(,)"}
+ {"(2,2)","(,)"}
+ {"(4,4)","(6,5)"}
+ {"(8,8)","(10,9)"}
+ {"(10,10)","(,)"}
+ (5 rows)
+
+ alter domain dcomptypea add constraint c1 check (value[1].r <= value[1].i);
+ alter domain dcomptypea add constraint c2 check (value[1].r > value[1].i); -- fail
+ ERROR: column "d1" of table "dcomptable" contains values that violate the new constraint
+ select array[row(2,1)]::dcomptypea; -- fail
+ ERROR: value for domain dcomptypea violates check constraint "c1"
+ insert into dcomptable values (array[row(1,2)]::comptype[]);
+ insert into dcomptable values (array[row(2,1)]::comptype[]); -- fail
+ ERROR: value for domain dcomptypea violates check constraint "c1"
+ insert into dcomptable (d1[1].r) values(99);
+ insert into dcomptable (d1[1].r, d1[1].i) values(99, 100);
+ insert into dcomptable (d1[1].r, d1[1].i) values(100, 99); -- fail
+ ERROR: value for domain dcomptypea violates check constraint "c1"
+ update dcomptable set d1[1].r = d1[1].r + 1 where d1[1].i > 0; -- fail
+ ERROR: value for domain dcomptypea violates check constraint "c1"
+ update dcomptable set d1[1].r = d1[1].r - 1 where d1[1].i > 0;
+ select * from dcomptable;
+ d1
+ --------------------
+ {"(11,)","(,)"}
+ {"(99,)"}
+ {"(1,2)","(,)"}
+ {"(3,4)","(6,5)"}
+ {"(7,8)","(10,9)"}
+ {"(9,10)","(,)"}
+ {"(0,2)"}
+ {"(98,100)"}
+ (8 rows)
+
+ drop table dcomptable;
+ drop type comptype cascade;
+ NOTICE: drop cascades to type dcomptypea
+ -- Test not-null restrictions
create domain dnotnull varchar(15) NOT NULL;
create domain dnull varchar(15);
create domain dcheck varchar(15) NOT NULL CHECK (VALUE = 'a' OR VALUE = 'c' OR VALUE = 'd');
diff --git a/src/test/regress/sql/domain.sql b/src/test/regress/sql/domain.sql
index 407d3ef..1fd7b11 100644
*** a/src/test/regress/sql/domain.sql
--- b/src/test/regress/sql/domain.sql
*************** INSERT INTO domarrtest values ('{2,2}',
*** 85,90 ****
--- 85,91 ----
INSERT INTO domarrtest values ('{2,2}', '{{"a"},{"c"}}');
INSERT INTO domarrtest values (NULL, '{{"a","b","c"},{"d","e","f"}}');
INSERT INTO domarrtest values (NULL, '{{"toolong","b","c"},{"d","e","f"}}');
+ INSERT INTO domarrtest (testint4arr[1], testint4arr[3]) values (11,22);
select * from domarrtest;
select testint4arr[1], testchar4arr[2:2] from domarrtest;
select array_dims(testint4arr), array_dims(testchar4arr) from domarrtest;
*************** COPY domarrtest FROM stdin; -- fail
*** 100,105 ****
--- 101,113 ----
select * from domarrtest;
+ update domarrtest set
+ testint4arr[1] = testint4arr[1] + 1,
+ testint4arr[3] = testint4arr[3] - 1
+ where testchar4arr is null;
+
+ select * from domarrtest where testchar4arr is null;
+
drop table domarrtest;
drop domain domainint4arr restrict;
drop domain domainchar4arr restrict;
*************** select pg_typeof('{1,2,3}'::dia);
*** 111,116 ****
--- 119,164 ----
select pg_typeof('{1,2,3}'::dia || 42); -- should be int[] not dia
drop domain dia;
+
+ -- Test domains over arrays of composite
+
+ create type comptype as (r float8, i float8);
+ create domain dcomptypea as comptype[];
+ create table dcomptable (d1 dcomptypea unique);
+
+ insert into dcomptable values (array[row(1,2)]::dcomptypea);
+ insert into dcomptable values (array[row(3,4), row(5,6)]::comptype[]);
+ insert into dcomptable values (array[row(7,8)::comptype, row(9,10)::comptype]);
+ insert into dcomptable values (array[row(1,2)]::dcomptypea); -- fail on uniqueness
+ insert into dcomptable (d1[1]) values(row(9,10));
+ insert into dcomptable (d1[1].r) values(11);
+
+ select * from dcomptable;
+ select d1[2], d1[1].r, d1[1].i from dcomptable;
+ update dcomptable set d1[2] = row(d1[2].i, d1[2].r);
+ select * from dcomptable;
+ update dcomptable set d1[1].r = d1[1].r + 1 where d1[1].i > 0;
+ select * from dcomptable;
+
+ alter domain dcomptypea add constraint c1 check (value[1].r <= value[1].i);
+ alter domain dcomptypea add constraint c2 check (value[1].r > value[1].i); -- fail
+
+ select array[row(2,1)]::dcomptypea; -- fail
+ insert into dcomptable values (array[row(1,2)]::comptype[]);
+ insert into dcomptable values (array[row(2,1)]::comptype[]); -- fail
+ insert into dcomptable (d1[1].r) values(99);
+ insert into dcomptable (d1[1].r, d1[1].i) values(99, 100);
+ insert into dcomptable (d1[1].r, d1[1].i) values(100, 99); -- fail
+ update dcomptable set d1[1].r = d1[1].r + 1 where d1[1].i > 0; -- fail
+ update dcomptable set d1[1].r = d1[1].r - 1 where d1[1].i > 0;
+ select * from dcomptable;
+
+ drop table dcomptable;
+ drop type comptype cascade;
+
+
+ -- Test not-null restrictions
+
create domain dnotnull varchar(15) NOT NULL;
create domain dnull varchar(15);
create domain dcheck varchar(15) NOT NULL CHECK (VALUE = 'a' OR VALUE = 'c' OR VALUE = 'd');
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
