summaryrefslogtreecommitdiffstats
path: root/src/test/regress/sql/domain.sql
blob: 1291d550d687ef7d2c7b10196a95e31bd1fe65d6 (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
--
-- Test domains.
--

-- Test Comment / Drop
create domain domaindroptest int4;
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;


-- Test domain input.

-- Note: the point of checking both INSERT and COPY FROM is that INSERT
-- exercises CoerceToDomain while COPY exercises domain_in.

create domain domainvarchar varchar(5);
create domain domainnumeric numeric(8,2);
create domain domainint4 int4;
create domain domaintext text;

-- Test explicit coercions --- these should succeed (and truncate)
SELECT cast('123456' as domainvarchar);
SELECT cast('12345' as domainvarchar);

-- Test tables using domains
create table basictest
           ( testint4 domainint4
           , testtext domaintext
           , testvarchar domainvarchar
           , testnumeric domainnumeric
           );

INSERT INTO basictest values ('88', 'haha', 'short', '123.12');      -- Good
INSERT INTO basictest values ('88', 'haha', 'short text', '123.12'); -- Bad varchar
INSERT INTO basictest values ('88', 'haha', 'short', '123.1212');    -- Truncate numeric

-- Test copy
COPY basictest (testvarchar) FROM stdin; -- fail
notsoshorttext
\.

COPY basictest (testvarchar) FROM stdin;
short
\.

select * from basictest;

-- check that domains inherit operations from base types
select testtext || testvarchar as concat, testnumeric + 42 as sum
from basictest;

-- check that union/case/coalesce type resolution handles domains properly
select coalesce(4::domainint4, 7) is of (int4) as t;
select coalesce(4::domainint4, 7) is of (domainint4) as f;
select coalesce(4::domainint4, 7::domainint4) is of (domainint4) as t;

drop table basictest;
drop domain domainvarchar restrict;
drop domain domainnumeric restrict;
drop domain domainint4 restrict;
drop domain domaintext;


-- Test domains over array types

create domain domainint4arr int4[1];
create domain domainchar4arr varchar(4)[2][3];

create table domarrtest
           ( testint4arr domainint4arr
           , testchar4arr domainchar4arr
            );
INSERT INTO domarrtest values ('{2,2}', '{{"a","b"},{"c","d"}}');
INSERT INTO domarrtest values ('{{2,2},{2,2}}', '{{"a","b"}}');
INSERT INTO domarrtest values ('{2,2}', '{{"a","b"},{"c","d"},{"e","f"}}');
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;
{3,4}	{q,w,e}
\N	\N
\.

COPY domarrtest FROM stdin;	-- fail
{3,4}	{qwerty,w,e}
\.

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;

create domain dia as int[];
select '{1,2,3}'::dia;
select array_dims('{1,2,3}'::dia);
select pg_typeof('{1,2,3}'::dia);
select pg_typeof('{1,2,3}'::dia || 42); -- should be int[] not dia
drop domain dia;


-- Test domains over composites

create type comptype as (r float8, i float8);
create domain dcomptype as comptype;
create table dcomptable (d1 dcomptype unique);

insert into dcomptable values (row(1,2)::dcomptype);
insert into dcomptable values (row(3,4)::comptype);
insert into dcomptable values (row(1,2)::dcomptype);  -- fail on uniqueness
insert into dcomptable (d1.r) values(11);

select * from dcomptable;
select (d1).r, (d1).i, (d1).* from dcomptable;
update dcomptable set d1.r = (d1).r + 1 where (d1).i > 0;
select * from dcomptable;

alter domain dcomptype add constraint c1 check ((value).r <= (value).i);
alter domain dcomptype add constraint c2 check ((value).r > (value).i);  -- fail

select row(2,1)::dcomptype;  -- fail
insert into dcomptable values (row(1,2)::comptype);
insert into dcomptable values (row(2,1)::comptype);  -- fail
insert into dcomptable (d1.r) values(99);
insert into dcomptable (d1.r, d1.i) values(99, 100);
insert into dcomptable (d1.r, d1.i) values(100, 99);  -- fail
update dcomptable set d1.r = (d1).r + 1 where (d1).i > 0;  -- fail
update dcomptable set d1.r = (d1).r - 1, d1.i = (d1).i + 1 where (d1).i > 0;
select * from dcomptable;

explain (verbose, costs off)
  update dcomptable set d1.r = (d1).r - 1, d1.i = (d1).i + 1 where (d1).i > 0;
create rule silly as on delete to dcomptable do instead
  update dcomptable set d1.r = (d1).r - 1, d1.i = (d1).i + 1 where (d1).i > 0;
\d+ dcomptable

drop table dcomptable;
drop type comptype cascade;


-- check altering and dropping columns used by domain constraints
create type comptype as (r float8, i float8);
create domain dcomptype as comptype;
alter domain dcomptype add constraint c1 check ((value).r > 0);
comment on constraint c1 on domain dcomptype is 'random commentary';

select row(0,1)::dcomptype;  -- fail

alter type comptype alter attribute r type varchar;  -- fail
alter type comptype alter attribute r type bigint;

alter type comptype drop attribute r;  -- fail
alter type comptype drop attribute i;

select conname, obj_description(oid, 'pg_constraint') from pg_constraint
  where contypid = 'dcomptype'::regtype;  -- check comment is still there

drop type comptype cascade;


-- 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, d1[1].i = d1[1].i + 1
  where d1[1].i > 0;
select * from dcomptable;

explain (verbose, costs off)
  update dcomptable set d1[1].r = d1[1].r - 1, d1[1].i = d1[1].i + 1
    where d1[1].i > 0;
create rule silly as on delete to dcomptable do instead
  update dcomptable set d1[1].r = d1[1].r - 1, d1[1].i = d1[1].i + 1
    where d1[1].i > 0;
\d+ dcomptable

drop table dcomptable;
drop type comptype cascade;


-- Test arrays over domains

create domain posint as int check (value > 0);

create table pitable (f1 posint[]);
insert into pitable values(array[42]);
insert into pitable values(array[-1]);  -- fail
insert into pitable values('{0}');  -- fail
update pitable set f1[1] = f1[1] + 1;
update pitable set f1[1] = 0;  -- fail
select * from pitable;
drop table pitable;

create domain vc4 as varchar(4);
create table vc4table (f1 vc4[]);
insert into vc4table values(array['too long']);  -- fail
insert into vc4table values(array['too long']::vc4[]);  -- cast truncates
select * from vc4table;
drop table vc4table;
drop type vc4;

-- You can sort of fake arrays-of-arrays by putting a domain in between
create domain dposinta as posint[];
create table dposintatable (f1 dposinta[]);
insert into dposintatable values(array[array[42]]);  -- fail
insert into dposintatable values(array[array[42]::posint[]]); -- still fail
insert into dposintatable values(array[array[42]::dposinta]); -- but this works
select f1, f1[1], (f1[1])[1] from dposintatable;
select pg_typeof(f1) from dposintatable;
select pg_typeof(f1[1]) from dposintatable;
select pg_typeof(f1[1][1]) from dposintatable;
select pg_typeof((f1[1])[1]) from dposintatable;
update dposintatable set f1[2] = array[99];
select f1, f1[1], (f1[2])[1] from dposintatable;
-- it'd be nice if you could do something like this, but for now you can't:
update dposintatable set f1[2][1] = array[97];
-- maybe someday we can make this syntax work:
update dposintatable set (f1[2])[1] = array[98];

drop table dposintatable;
drop domain posint 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');

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 IN ('c', 'd'))
           );
INSERT INTO nulltest DEFAULT VALUES;
INSERT INTO nulltest values ('a', 'b', 'c', 'd', 'c');  -- Good
insert into nulltest values ('a', 'b', 'c', 'd', NULL);
insert into nulltest values ('a', 'b', 'c', 'd', 'a');
INSERT INTO nulltest values (NULL, 'b', 'c', 'd', 'd');
INSERT INTO nulltest values ('a', NULL, 'c', 'd', 'c');
INSERT INTO nulltest values ('a', 'b', NULL, 'd', 'c');
INSERT INTO nulltest values ('a', 'b', 'c', NULL, 'd'); -- 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('1' 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 int4 DEFAULT 3;
create domain ddef2 oid DEFAULT '12';
-- Type mixing, function returns int8
create domain ddef3 text DEFAULT 5;
create sequence ddef4_seq;
create domain ddef4 int4 DEFAULT nextval('ddef4_seq');
create domain ddef5 numeric(8,2) NOT NULL DEFAULT '12.12';

create table defaulttest
            ( col1 ddef1
            , col2 ddef2
            , col3 ddef3
            , col4 ddef4 PRIMARY KEY
            , col5 ddef1 NOT NULL DEFAULT NULL
            , col6 ddef2 DEFAULT '88'
            , col7 ddef4 DEFAULT 8000
            , col8 ddef5
            );
insert into defaulttest(col4) values(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(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;
42
\.

select * from defaulttest;

drop table defaulttest cascade;

-- Test ALTER DOMAIN .. NOT NULL
create domain dnotnulltest integer;
create table domnotnull
( col1 dnotnulltest
, col2 dnotnulltest
);

insert into domnotnull default values;
alter domain dnotnulltest set not null; -- fails

update domnotnull set col1 = 5;
alter domain dnotnulltest set not null; -- fails

update domnotnull set col2 = 6;

alter domain dnotnulltest set not null;

update domnotnull set col1 = null; -- fails

alter domain dnotnulltest drop not null;

update domnotnull set col1 = null;

drop domain dnotnulltest cascade;

-- Test ALTER DOMAIN .. DEFAULT ..
create table domdeftest (col1 ddef1);

insert into domdeftest default values;
select * from domdeftest;

alter domain ddef1 set default '42';
insert into domdeftest default values;
select * from domdeftest;

alter domain ddef1 drop default;
insert into domdeftest default values;
select * from domdeftest;

drop table domdeftest;

-- Test ALTER DOMAIN .. CONSTRAINT ..
create domain con as integer;
create table domcontest (col1 con);

insert into domcontest values (1);
insert into domcontest values (2);
alter domain con add constraint t check (VALUE < 1); -- fails

alter domain con add constraint t check (VALUE < 34);
alter domain con add check (VALUE > 0);

insert into domcontest values (-5); -- fails
insert into domcontest values (42); -- fails
insert into domcontest values (5);

alter domain con drop constraint t;
insert into domcontest values (-5); --fails
insert into domcontest values (42);

alter domain con drop constraint nonexistent;
alter domain con drop constraint if exists nonexistent;

-- Test ALTER DOMAIN .. CONSTRAINT .. NOT VALID
create domain things AS INT;
CREATE TABLE thethings (stuff things);
INSERT INTO thethings (stuff) VALUES (55);
ALTER DOMAIN things ADD CONSTRAINT meow CHECK (VALUE < 11);
ALTER DOMAIN things ADD CONSTRAINT meow CHECK (VALUE < 11) NOT VALID;
ALTER DOMAIN things VALIDATE CONSTRAINT meow;
UPDATE thethings SET stuff = 10;
ALTER DOMAIN things VALIDATE CONSTRAINT meow;

-- Confirm ALTER DOMAIN with RULES.
create table domtab (col1 integer);
create domain dom as integer;
create view domview as select cast(col1 as dom) from domtab;
insert into domtab (col1) values (null);
insert into domtab (col1) values (5);
select * from domview;

alter domain dom set not null;
select * from domview; -- fail

alter domain dom drop not null;
select * from domview;

alter domain dom add constraint domchkgt6 check(value > 6);
select * from domview; --fail

alter domain dom drop constraint domchkgt6 restrict;
select * from domview;

-- cleanup
drop domain ddef1 restrict;
drop domain ddef2 restrict;
drop domain ddef3 restrict;
drop domain ddef4 restrict;
drop domain ddef5 restrict;
drop sequence ddef4_seq;

-- Test domains over domains
create domain vchar4 varchar(4);
create domain dinter vchar4 check (substring(VALUE, 1, 1) = 'x');
create domain dtop dinter check (substring(VALUE, 2, 1) = '1');

select 'x123'::dtop;
select 'x1234'::dtop; -- explicit coercion should truncate
select 'y1234'::dtop; -- fail
select 'y123'::dtop; -- fail
select 'yz23'::dtop; -- fail
select 'xz23'::dtop; -- fail

create temp table dtest(f1 dtop);

insert into dtest values('x123');
insert into dtest values('x1234'); -- fail, implicit coercion
insert into dtest values('y1234'); -- fail, implicit coercion
insert into dtest values('y123'); -- fail
insert into dtest values('yz23'); -- fail
insert into dtest values('xz23'); -- fail

drop table dtest;
drop domain vchar4 cascade;

-- Make sure that constraints of newly-added domain columns are
-- enforced correctly, even if there's no default value for the new
-- column. Per bug #1433
create domain str_domain as text not null;

create table domain_test (a int, b int);

insert into domain_test values (1, 2);
insert into domain_test values (1, 2);

-- should fail
alter table domain_test add column c str_domain;

create domain str_domain2 as text check (value <> 'foo') default 'foo';

-- should fail
alter table domain_test add column d str_domain2;

-- Check that domain constraints on prepared statement parameters of
-- unknown type are enforced correctly.
create domain pos_int as int4 check (value > 0) not null;
prepare s1 as select $1::pos_int = 10 as "is_ten";

execute s1(10);
execute s1(0); -- should fail
execute s1(NULL); -- should fail

-- Check that domain constraints on plpgsql function parameters, results,
-- and local variables are enforced correctly.

create function doubledecrement(p1 pos_int) returns pos_int as $$
declare v pos_int;
begin
    return p1;
end$$ language plpgsql;

select doubledecrement(3); -- fail because of implicit null assignment

create or replace function doubledecrement(p1 pos_int) returns pos_int as $$
declare v pos_int := 0;
begin
    return p1;
end$$ language plpgsql;

select doubledecrement(3); -- fail at initialization assignment

create or replace function doubledecrement(p1 pos_int) returns pos_int as $$
declare v pos_int := 1;
begin
    v := p1 - 1;
    return v - 1;
end$$ language plpgsql;

select doubledecrement(null); -- fail before call
select doubledecrement(0); -- fail before call
select doubledecrement(1); -- fail at assignment to v
select doubledecrement(2); -- fail at return
select doubledecrement(3); -- good

-- Check that ALTER DOMAIN tests columns of derived types

create domain posint as int4;

-- Currently, this doesn't work for composite types, but verify it complains
create type ddtest1 as (f1 posint);
create table ddtest2(f1 ddtest1);
insert into ddtest2 values(row(-1));
alter domain posint add constraint c1 check(value >= 0);
drop table ddtest2;

-- Likewise for domains within arrays of composite
create table ddtest2(f1 ddtest1[]);
insert into ddtest2 values('{(-1)}');
alter domain posint add constraint c1 check(value >= 0);
drop table ddtest2;

-- Likewise for domains within domains over composite
create domain ddtest1d as ddtest1;
create table ddtest2(f1 ddtest1d);
insert into ddtest2 values('(-1)');
alter domain posint add constraint c1 check(value >= 0);
drop table ddtest2;
drop domain ddtest1d;

-- Likewise for domains within domains over array of composite
create domain ddtest1d as ddtest1[];
create table ddtest2(f1 ddtest1d);
insert into ddtest2 values('{(-1)}');
alter domain posint add constraint c1 check(value >= 0);
drop table ddtest2;
drop domain ddtest1d;

-- Doesn't work for ranges, either
create type rposint as range (subtype = posint);
create table ddtest2(f1 rposint);
insert into ddtest2 values('(-1,3]');
alter domain posint add constraint c1 check(value >= 0);
drop table ddtest2;
drop type rposint;

alter domain posint add constraint c1 check(value >= 0);

create domain posint2 as posint check (value % 2 = 0);
create table ddtest2(f1 posint2);
insert into ddtest2 values(11); -- fail
insert into ddtest2 values(-2); -- fail
insert into ddtest2 values(2);

alter domain posint add constraint c2 check(value >= 10); -- fail
alter domain posint add constraint c2 check(value > 0); -- OK

drop table ddtest2;
drop type ddtest1;
drop domain posint cascade;

--
-- Check enforcement of domain-related typmod in plpgsql (bug #5717)
--

create or replace function array_elem_check(numeric) returns numeric as $$
declare
  x numeric(4,2)[1];
begin
  x[1] := $1;
  return x[1];
end$$ language plpgsql;

select array_elem_check(121.00);
select array_elem_check(1.23456);

create domain mynums as numeric(4,2)[1];

create or replace function array_elem_check(numeric) returns numeric as $$
declare
  x mynums;
begin
  x[1] := $1;
  return x[1];
end$$ language plpgsql;

select array_elem_check(121.00);
select array_elem_check(1.23456);

create domain mynums2 as mynums;

create or replace function array_elem_check(numeric) returns numeric as $$
declare
  x mynums2;
begin
  x[1] := $1;
  return x[1];
end$$ language plpgsql;

select array_elem_check(121.00);
select array_elem_check(1.23456);

drop function array_elem_check(numeric);

--
-- Check enforcement of array-level domain constraints
--

create domain orderedpair as int[2] check (value[1] < value[2]);

select array[1,2]::orderedpair;
select array[2,1]::orderedpair;  -- fail

create temp table op (f1 orderedpair);
insert into op values (array[1,2]);
insert into op values (array[2,1]);  -- fail

update op set f1[2] = 3;
update op set f1[2] = 0;  -- fail
select * from op;

create or replace function array_elem_check(int) returns int as $$
declare
  x orderedpair := '{1,2}';
begin
  x[2] := $1;
  return x[2];
end$$ language plpgsql;

select array_elem_check(3);
select array_elem_check(-1);

drop function array_elem_check(int);

--
-- Check enforcement of changing constraints in plpgsql
--

create domain di as int;

create function dom_check(int) returns di as $$
declare d di;
begin
  d := $1::di;
  return d;
end
$$ language plpgsql immutable;

select dom_check(0);

alter domain di add constraint pos check (value > 0);

select dom_check(0); -- fail

alter domain di drop constraint pos;

select dom_check(0);

-- implicit cast during assignment is a separate code path, test that too

create or replace function dom_check(int) returns di as $$
declare d di;
begin
  d := $1;
  return d;
end
$$ language plpgsql immutable;

select dom_check(0);

alter domain di add constraint pos check (value > 0);

select dom_check(0); -- fail

alter domain di drop constraint pos;

select dom_check(0);

drop function dom_check(int);

drop domain di;

--
-- Check use of a (non-inline-able) SQL function in a domain constraint;
-- this has caused issues in the past
--

create function sql_is_distinct_from(anyelement, anyelement)
returns boolean language sql
as 'select $1 is distinct from $2 limit 1';

create domain inotnull int
  check (sql_is_distinct_from(value, null));

select 1::inotnull;
select null::inotnull;

create table dom_table (x inotnull);
insert into dom_table values ('1');
insert into dom_table values (1);
insert into dom_table values (null);

drop table dom_table;
drop domain inotnull;
drop function sql_is_distinct_from(anyelement, anyelement);

--
-- Renaming
--

create domain testdomain1 as int;
alter domain testdomain1 rename to testdomain2;
alter type testdomain2 rename to testdomain3;  -- alter type also works
drop domain testdomain3;


--
-- Renaming domain constraints
--

create domain testdomain1 as int constraint unsigned check (value > 0);
alter domain testdomain1 rename constraint unsigned to unsigned_foo;
alter domain testdomain1 drop constraint unsigned_foo;
drop domain testdomain1;