1-- 2-- Test domains. 3-- 4 5-- Test Comment / Drop 6create domain domaindroptest int4; 7comment on domain domaindroptest is 'About to drop this..'; 8 9create domain dependenttypetest domaindroptest; 10 11-- fail because of dependent type 12drop domain domaindroptest; 13 14drop domain domaindroptest cascade; 15 16-- this should fail because already gone 17drop domain domaindroptest cascade; 18 19 20-- Test domain input. 21 22-- Note: the point of checking both INSERT and COPY FROM is that INSERT 23-- exercises CoerceToDomain while COPY exercises domain_in. 24 25create domain domainvarchar varchar(5); 26create domain domainnumeric numeric(8,2); 27create domain domainint4 int4; 28create domain domaintext text; 29 30-- Test explicit coercions --- these should succeed (and truncate) 31SELECT cast('123456' as domainvarchar); 32SELECT cast('12345' as domainvarchar); 33 34-- Test tables using domains 35create table basictest 36 ( testint4 domainint4 37 , testtext domaintext 38 , testvarchar domainvarchar 39 , testnumeric domainnumeric 40 ); 41 42INSERT INTO basictest values ('88', 'haha', 'short', '123.12'); -- Good 43INSERT INTO basictest values ('88', 'haha', 'short text', '123.12'); -- Bad varchar 44INSERT INTO basictest values ('88', 'haha', 'short', '123.1212'); -- Truncate numeric 45 46-- Test copy 47COPY basictest (testvarchar) FROM stdin; -- fail 48notsoshorttext 49\. 50 51COPY basictest (testvarchar) FROM stdin; 52short 53\. 54 55select * from basictest; 56 57-- check that domains inherit operations from base types 58select testtext || testvarchar as concat, testnumeric + 42 as sum 59from basictest; 60 61-- check that union/case/coalesce type resolution handles domains properly 62select coalesce(4::domainint4, 7) is of (int4) as t; 63select coalesce(4::domainint4, 7) is of (domainint4) as f; 64select coalesce(4::domainint4, 7::domainint4) is of (domainint4) as t; 65 66drop table basictest; 67drop domain domainvarchar restrict; 68drop domain domainnumeric restrict; 69drop domain domainint4 restrict; 70drop domain domaintext; 71 72 73-- Test domains over array types 74 75create domain domainint4arr int4[1]; 76create domain domainchar4arr varchar(4)[2][3]; 77 78create table domarrtest 79 ( testint4arr domainint4arr 80 , testchar4arr domainchar4arr 81 ); 82INSERT INTO domarrtest values ('{2,2}', '{{"a","b"},{"c","d"}}'); 83INSERT INTO domarrtest values ('{{2,2},{2,2}}', '{{"a","b"}}'); 84INSERT INTO domarrtest values ('{2,2}', '{{"a","b"},{"c","d"},{"e","f"}}'); 85INSERT INTO domarrtest values ('{2,2}', '{{"a"},{"c"}}'); 86INSERT INTO domarrtest values (NULL, '{{"a","b","c"},{"d","e","f"}}'); 87INSERT INTO domarrtest values (NULL, '{{"toolong","b","c"},{"d","e","f"}}'); 88INSERT INTO domarrtest (testint4arr[1], testint4arr[3]) values (11,22); 89select * from domarrtest; 90select testint4arr[1], testchar4arr[2:2] from domarrtest; 91select array_dims(testint4arr), array_dims(testchar4arr) from domarrtest; 92 93COPY domarrtest FROM stdin; 94{3,4} {q,w,e} 95\N \N 96\. 97 98COPY domarrtest FROM stdin; -- fail 99{3,4} {qwerty,w,e} 100\. 101 102select * from domarrtest; 103 104update domarrtest set 105 testint4arr[1] = testint4arr[1] + 1, 106 testint4arr[3] = testint4arr[3] - 1 107where testchar4arr is null; 108 109select * from domarrtest where testchar4arr is null; 110 111drop table domarrtest; 112drop domain domainint4arr restrict; 113drop domain domainchar4arr restrict; 114 115create domain dia as int[]; 116select '{1,2,3}'::dia; 117select array_dims('{1,2,3}'::dia); 118select pg_typeof('{1,2,3}'::dia); 119select pg_typeof('{1,2,3}'::dia || 42); -- should be int[] not dia 120drop domain dia; 121 122 123-- Test domains over arrays of composite 124 125create type comptype as (r float8, i float8); 126create domain dcomptypea as comptype[]; 127create table dcomptable (d1 dcomptypea unique); 128 129insert into dcomptable values (array[row(1,2)]::dcomptypea); 130insert into dcomptable values (array[row(3,4), row(5,6)]::comptype[]); 131insert into dcomptable values (array[row(7,8)::comptype, row(9,10)::comptype]); 132insert into dcomptable values (array[row(1,2)]::dcomptypea); -- fail on uniqueness 133insert into dcomptable (d1[1]) values(row(9,10)); 134insert into dcomptable (d1[1].r) values(11); 135 136select * from dcomptable; 137select d1[2], d1[1].r, d1[1].i from dcomptable; 138update dcomptable set d1[2] = row(d1[2].i, d1[2].r); 139select * from dcomptable; 140update dcomptable set d1[1].r = d1[1].r + 1 where d1[1].i > 0; 141select * from dcomptable; 142 143alter domain dcomptypea add constraint c1 check (value[1].r <= value[1].i); 144alter domain dcomptypea add constraint c2 check (value[1].r > value[1].i); -- fail 145 146select array[row(2,1)]::dcomptypea; -- fail 147insert into dcomptable values (array[row(1,2)]::comptype[]); 148insert into dcomptable values (array[row(2,1)]::comptype[]); -- fail 149insert into dcomptable (d1[1].r) values(99); 150insert into dcomptable (d1[1].r, d1[1].i) values(99, 100); 151insert into dcomptable (d1[1].r, d1[1].i) values(100, 99); -- fail 152update dcomptable set d1[1].r = d1[1].r + 1 where d1[1].i > 0; -- fail 153update dcomptable set d1[1].r = d1[1].r - 1, d1[1].i = d1[1].i + 1 154 where d1[1].i > 0; 155select * from dcomptable; 156 157explain (verbose, costs off) 158 update dcomptable set d1[1].r = d1[1].r - 1, d1[1].i = d1[1].i + 1 159 where d1[1].i > 0; 160create rule silly as on delete to dcomptable do instead 161 update dcomptable set d1[1].r = d1[1].r - 1, d1[1].i = d1[1].i + 1 162 where d1[1].i > 0; 163\d+ dcomptable 164 165drop table dcomptable; 166drop type comptype cascade; 167 168 169-- Test not-null restrictions 170 171create domain dnotnull varchar(15) NOT NULL; 172create domain dnull varchar(15); 173create domain dcheck varchar(15) NOT NULL CHECK (VALUE = 'a' OR VALUE = 'c' OR VALUE = 'd'); 174 175create table nulltest 176 ( col1 dnotnull 177 , col2 dnotnull NULL -- NOT NULL in the domain cannot be overridden 178 , col3 dnull NOT NULL 179 , col4 dnull 180 , col5 dcheck CHECK (col5 IN ('c', 'd')) 181 ); 182INSERT INTO nulltest DEFAULT VALUES; 183INSERT INTO nulltest values ('a', 'b', 'c', 'd', 'c'); -- Good 184insert into nulltest values ('a', 'b', 'c', 'd', NULL); 185insert into nulltest values ('a', 'b', 'c', 'd', 'a'); 186INSERT INTO nulltest values (NULL, 'b', 'c', 'd', 'd'); 187INSERT INTO nulltest values ('a', NULL, 'c', 'd', 'c'); 188INSERT INTO nulltest values ('a', 'b', NULL, 'd', 'c'); 189INSERT INTO nulltest values ('a', 'b', 'c', NULL, 'd'); -- Good 190 191-- Test copy 192COPY nulltest FROM stdin; --fail 193a b \N d d 194\. 195 196COPY nulltest FROM stdin; --fail 197a b c d \N 198\. 199 200-- Last row is bad 201COPY nulltest FROM stdin; 202a b c \N c 203a b c \N d 204a b c \N a 205\. 206 207select * from nulltest; 208 209-- Test out coerced (casted) constraints 210SELECT cast('1' as dnotnull); 211SELECT cast(NULL as dnotnull); -- fail 212SELECT cast(cast(NULL as dnull) as dnotnull); -- fail 213SELECT cast(col4 as dnotnull) from nulltest; -- fail 214 215-- cleanup 216drop table nulltest; 217drop domain dnotnull restrict; 218drop domain dnull restrict; 219drop domain dcheck restrict; 220 221 222create domain ddef1 int4 DEFAULT 3; 223create domain ddef2 oid DEFAULT '12'; 224-- Type mixing, function returns int8 225create domain ddef3 text DEFAULT 5; 226create sequence ddef4_seq; 227create domain ddef4 int4 DEFAULT nextval('ddef4_seq'); 228create domain ddef5 numeric(8,2) NOT NULL DEFAULT '12.12'; 229 230create table defaulttest 231 ( col1 ddef1 232 , col2 ddef2 233 , col3 ddef3 234 , col4 ddef4 PRIMARY KEY 235 , col5 ddef1 NOT NULL DEFAULT NULL 236 , col6 ddef2 DEFAULT '88' 237 , col7 ddef4 DEFAULT 8000 238 , col8 ddef5 239 ); 240insert into defaulttest(col4) values(0); -- fails, col5 defaults to null 241alter table defaulttest alter column col5 drop default; 242insert into defaulttest default values; -- succeeds, inserts domain default 243-- We used to treat SET DEFAULT NULL as equivalent to DROP DEFAULT; wrong 244alter table defaulttest alter column col5 set default null; 245insert into defaulttest(col4) values(0); -- fails 246alter table defaulttest alter column col5 drop default; 247insert into defaulttest default values; 248insert into defaulttest default values; 249 250-- Test defaults with copy 251COPY defaulttest(col5) FROM stdin; 25242 253\. 254 255select * from defaulttest; 256 257drop table defaulttest cascade; 258 259-- Test ALTER DOMAIN .. NOT NULL 260create domain dnotnulltest integer; 261create table domnotnull 262( col1 dnotnulltest 263, col2 dnotnulltest 264); 265 266insert into domnotnull default values; 267alter domain dnotnulltest set not null; -- fails 268 269update domnotnull set col1 = 5; 270alter domain dnotnulltest set not null; -- fails 271 272update domnotnull set col2 = 6; 273 274alter domain dnotnulltest set not null; 275 276update domnotnull set col1 = null; -- fails 277 278alter domain dnotnulltest drop not null; 279 280update domnotnull set col1 = null; 281 282drop domain dnotnulltest cascade; 283 284-- Test ALTER DOMAIN .. DEFAULT .. 285create table domdeftest (col1 ddef1); 286 287insert into domdeftest default values; 288select * from domdeftest; 289 290alter domain ddef1 set default '42'; 291insert into domdeftest default values; 292select * from domdeftest; 293 294alter domain ddef1 drop default; 295insert into domdeftest default values; 296select * from domdeftest; 297 298drop table domdeftest; 299 300-- Test ALTER DOMAIN .. CONSTRAINT .. 301create domain con as integer; 302create table domcontest (col1 con); 303 304insert into domcontest values (1); 305insert into domcontest values (2); 306alter domain con add constraint t check (VALUE < 1); -- fails 307 308alter domain con add constraint t check (VALUE < 34); 309alter domain con add check (VALUE > 0); 310 311insert into domcontest values (-5); -- fails 312insert into domcontest values (42); -- fails 313insert into domcontest values (5); 314 315alter domain con drop constraint t; 316insert into domcontest values (-5); --fails 317insert into domcontest values (42); 318 319alter domain con drop constraint nonexistent; 320alter domain con drop constraint if exists nonexistent; 321 322-- Test ALTER DOMAIN .. CONSTRAINT .. NOT VALID 323create domain things AS INT; 324CREATE TABLE thethings (stuff things); 325INSERT INTO thethings (stuff) VALUES (55); 326ALTER DOMAIN things ADD CONSTRAINT meow CHECK (VALUE < 11); 327ALTER DOMAIN things ADD CONSTRAINT meow CHECK (VALUE < 11) NOT VALID; 328ALTER DOMAIN things VALIDATE CONSTRAINT meow; 329UPDATE thethings SET stuff = 10; 330ALTER DOMAIN things VALIDATE CONSTRAINT meow; 331 332-- Confirm ALTER DOMAIN with RULES. 333create table domtab (col1 integer); 334create domain dom as integer; 335create view domview as select cast(col1 as dom) from domtab; 336insert into domtab (col1) values (null); 337insert into domtab (col1) values (5); 338select * from domview; 339 340alter domain dom set not null; 341select * from domview; -- fail 342 343alter domain dom drop not null; 344select * from domview; 345 346alter domain dom add constraint domchkgt6 check(value > 6); 347select * from domview; --fail 348 349alter domain dom drop constraint domchkgt6 restrict; 350select * from domview; 351 352-- cleanup 353drop domain ddef1 restrict; 354drop domain ddef2 restrict; 355drop domain ddef3 restrict; 356drop domain ddef4 restrict; 357drop domain ddef5 restrict; 358drop sequence ddef4_seq; 359 360-- Test domains over domains 361create domain vchar4 varchar(4); 362create domain dinter vchar4 check (substring(VALUE, 1, 1) = 'x'); 363create domain dtop dinter check (substring(VALUE, 2, 1) = '1'); 364 365select 'x123'::dtop; 366select 'x1234'::dtop; -- explicit coercion should truncate 367select 'y1234'::dtop; -- fail 368select 'y123'::dtop; -- fail 369select 'yz23'::dtop; -- fail 370select 'xz23'::dtop; -- fail 371 372create temp table dtest(f1 dtop); 373 374insert into dtest values('x123'); 375insert into dtest values('x1234'); -- fail, implicit coercion 376insert into dtest values('y1234'); -- fail, implicit coercion 377insert into dtest values('y123'); -- fail 378insert into dtest values('yz23'); -- fail 379insert into dtest values('xz23'); -- fail 380 381drop table dtest; 382drop domain vchar4 cascade; 383 384-- Make sure that constraints of newly-added domain columns are 385-- enforced correctly, even if there's no default value for the new 386-- column. Per bug #1433 387create domain str_domain as text not null; 388 389create table domain_test (a int, b int); 390 391insert into domain_test values (1, 2); 392insert into domain_test values (1, 2); 393 394-- should fail 395alter table domain_test add column c str_domain; 396 397create domain str_domain2 as text check (value <> 'foo') default 'foo'; 398 399-- should fail 400alter table domain_test add column d str_domain2; 401 402-- Check that domain constraints on prepared statement parameters of 403-- unknown type are enforced correctly. 404create domain pos_int as int4 check (value > 0) not null; 405prepare s1 as select $1::pos_int = 10 as "is_ten"; 406 407execute s1(10); 408execute s1(0); -- should fail 409execute s1(NULL); -- should fail 410 411-- Check that domain constraints on plpgsql function parameters, results, 412-- and local variables are enforced correctly. 413 414create function doubledecrement(p1 pos_int) returns pos_int as $$ 415declare v pos_int; 416begin 417 return p1; 418end$$ language plpgsql; 419 420select doubledecrement(3); -- fail because of implicit null assignment 421 422create or replace function doubledecrement(p1 pos_int) returns pos_int as $$ 423declare v pos_int := 0; 424begin 425 return p1; 426end$$ language plpgsql; 427 428select doubledecrement(3); -- fail at initialization assignment 429 430create or replace function doubledecrement(p1 pos_int) returns pos_int as $$ 431declare v pos_int := 1; 432begin 433 v := p1 - 1; 434 return v - 1; 435end$$ language plpgsql; 436 437select doubledecrement(null); -- fail before call 438select doubledecrement(0); -- fail before call 439select doubledecrement(1); -- fail at assignment to v 440select doubledecrement(2); -- fail at return 441select doubledecrement(3); -- good 442 443-- Check that ALTER DOMAIN tests columns of derived types 444 445create domain posint as int4; 446 447-- Currently, this doesn't work for composite types, but verify it complains 448create type ddtest1 as (f1 posint); 449create table ddtest2(f1 ddtest1); 450insert into ddtest2 values(row(-1)); 451alter domain posint add constraint c1 check(value >= 0); 452drop table ddtest2; 453 454-- Likewise for domains within arrays of composite 455create table ddtest2(f1 ddtest1[]); 456insert into ddtest2 values('{(-1)}'); 457alter domain posint add constraint c1 check(value >= 0); 458drop table ddtest2; 459 460-- Likewise for domains within domains over array of composite 461create domain ddtest1d as ddtest1[]; 462create table ddtest2(f1 ddtest1d); 463insert into ddtest2 values('{(-1)}'); 464alter domain posint add constraint c1 check(value >= 0); 465drop table ddtest2; 466drop domain ddtest1d; 467 468-- Doesn't work for ranges, either 469create type rposint as range (subtype = posint); 470create table ddtest2(f1 rposint); 471insert into ddtest2 values('(-1,3]'); 472alter domain posint add constraint c1 check(value >= 0); 473drop table ddtest2; 474drop type rposint; 475 476alter domain posint add constraint c1 check(value >= 0); 477 478create domain posint2 as posint check (value % 2 = 0); 479create table ddtest2(f1 posint2); 480insert into ddtest2 values(11); -- fail 481insert into ddtest2 values(-2); -- fail 482insert into ddtest2 values(2); 483 484alter domain posint add constraint c2 check(value >= 10); -- fail 485alter domain posint add constraint c2 check(value > 0); -- OK 486 487drop table ddtest2; 488drop type ddtest1; 489drop domain posint cascade; 490 491-- 492-- Check enforcement of domain-related typmod in plpgsql (bug #5717) 493-- 494 495create or replace function array_elem_check(numeric) returns numeric as $$ 496declare 497 x numeric(4,2)[1]; 498begin 499 x[1] := $1; 500 return x[1]; 501end$$ language plpgsql; 502 503select array_elem_check(121.00); 504select array_elem_check(1.23456); 505 506create domain mynums as numeric(4,2)[1]; 507 508create or replace function array_elem_check(numeric) returns numeric as $$ 509declare 510 x mynums; 511begin 512 x[1] := $1; 513 return x[1]; 514end$$ language plpgsql; 515 516select array_elem_check(121.00); 517select array_elem_check(1.23456); 518 519create domain mynums2 as mynums; 520 521create or replace function array_elem_check(numeric) returns numeric as $$ 522declare 523 x mynums2; 524begin 525 x[1] := $1; 526 return x[1]; 527end$$ language plpgsql; 528 529select array_elem_check(121.00); 530select array_elem_check(1.23456); 531 532drop function array_elem_check(numeric); 533 534-- 535-- Check enforcement of array-level domain constraints 536-- 537 538create domain orderedpair as int[2] check (value[1] < value[2]); 539 540select array[1,2]::orderedpair; 541select array[2,1]::orderedpair; -- fail 542 543create temp table op (f1 orderedpair); 544insert into op values (array[1,2]); 545insert into op values (array[2,1]); -- fail 546 547update op set f1[2] = 3; 548update op set f1[2] = 0; -- fail 549select * from op; 550 551create or replace function array_elem_check(int) returns int as $$ 552declare 553 x orderedpair := '{1,2}'; 554begin 555 x[2] := $1; 556 return x[2]; 557end$$ language plpgsql; 558 559select array_elem_check(3); 560select array_elem_check(-1); 561 562drop function array_elem_check(int); 563 564-- 565-- Check enforcement of changing constraints in plpgsql 566-- 567 568create domain di as int; 569 570create function dom_check(int) returns di as $$ 571declare d di; 572begin 573 d := $1; 574 return d; 575end 576$$ language plpgsql immutable; 577 578select dom_check(0); 579 580alter domain di add constraint pos check (value > 0); 581 582select dom_check(0); -- fail 583 584alter domain di drop constraint pos; 585 586select dom_check(0); 587 588drop function dom_check(int); 589 590drop domain di; 591 592-- 593-- Check use of a (non-inline-able) SQL function in a domain constraint; 594-- this has caused issues in the past 595-- 596 597create function sql_is_distinct_from(anyelement, anyelement) 598returns boolean language sql 599as 'select $1 is distinct from $2 limit 1'; 600 601create domain inotnull int 602 check (sql_is_distinct_from(value, null)); 603 604select 1::inotnull; 605select null::inotnull; 606 607create table dom_table (x inotnull); 608insert into dom_table values ('1'); 609insert into dom_table values (1); 610insert into dom_table values (null); 611 612drop table dom_table; 613drop domain inotnull; 614drop function sql_is_distinct_from(anyelement, anyelement); 615 616-- 617-- Renaming 618-- 619 620create domain testdomain1 as int; 621alter domain testdomain1 rename to testdomain2; 622alter type testdomain2 rename to testdomain3; -- alter type also works 623drop domain testdomain3; 624 625 626-- 627-- Renaming domain constraints 628-- 629 630create domain testdomain1 as int constraint unsigned check (value > 0); 631alter domain testdomain1 rename constraint unsigned to unsigned_foo; 632alter domain testdomain1 drop constraint unsigned_foo; 633drop domain testdomain1; 634