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 composites 124 125create type comptype as (r float8, i float8); 126create domain dcomptype as comptype; 127create table dcomptable (d1 dcomptype unique); 128 129insert into dcomptable values (row(1,2)::dcomptype); 130insert into dcomptable values (row(3,4)::comptype); 131insert into dcomptable values (row(1,2)::dcomptype); -- fail on uniqueness 132insert into dcomptable (d1.r) values(11); 133 134select * from dcomptable; 135select (d1).r, (d1).i, (d1).* from dcomptable; 136update dcomptable set d1.r = (d1).r + 1 where (d1).i > 0; 137select * from dcomptable; 138 139alter domain dcomptype add constraint c1 check ((value).r <= (value).i); 140alter domain dcomptype add constraint c2 check ((value).r > (value).i); -- fail 141 142select row(2,1)::dcomptype; -- fail 143insert into dcomptable values (row(1,2)::comptype); 144insert into dcomptable values (row(2,1)::comptype); -- fail 145insert into dcomptable (d1.r) values(99); 146insert into dcomptable (d1.r, d1.i) values(99, 100); 147insert into dcomptable (d1.r, d1.i) values(100, 99); -- fail 148update dcomptable set d1.r = (d1).r + 1 where (d1).i > 0; -- fail 149update dcomptable set d1.r = (d1).r - 1, d1.i = (d1).i + 1 where (d1).i > 0; 150select * from dcomptable; 151 152explain (verbose, costs off) 153 update dcomptable set d1.r = (d1).r - 1, d1.i = (d1).i + 1 where (d1).i > 0; 154create rule silly as on delete to dcomptable do instead 155 update dcomptable set d1.r = (d1).r - 1, d1.i = (d1).i + 1 where (d1).i > 0; 156\d+ dcomptable 157 158drop table dcomptable; 159drop type comptype cascade; 160 161 162-- check altering and dropping columns used by domain constraints 163create type comptype as (r float8, i float8); 164create domain dcomptype as comptype; 165alter domain dcomptype add constraint c1 check ((value).r > 0); 166comment on constraint c1 on domain dcomptype is 'random commentary'; 167 168select row(0,1)::dcomptype; -- fail 169 170alter type comptype alter attribute r type varchar; -- fail 171alter type comptype alter attribute r type bigint; 172 173alter type comptype drop attribute r; -- fail 174alter type comptype drop attribute i; 175 176select conname, obj_description(oid, 'pg_constraint') from pg_constraint 177 where contypid = 'dcomptype'::regtype; -- check comment is still there 178 179drop type comptype cascade; 180 181 182-- Test domains over arrays of composite 183 184create type comptype as (r float8, i float8); 185create domain dcomptypea as comptype[]; 186create table dcomptable (d1 dcomptypea unique); 187 188insert into dcomptable values (array[row(1,2)]::dcomptypea); 189insert into dcomptable values (array[row(3,4), row(5,6)]::comptype[]); 190insert into dcomptable values (array[row(7,8)::comptype, row(9,10)::comptype]); 191insert into dcomptable values (array[row(1,2)]::dcomptypea); -- fail on uniqueness 192insert into dcomptable (d1[1]) values(row(9,10)); 193insert into dcomptable (d1[1].r) values(11); 194 195select * from dcomptable; 196select d1[2], d1[1].r, d1[1].i from dcomptable; 197update dcomptable set d1[2] = row(d1[2].i, d1[2].r); 198select * from dcomptable; 199update dcomptable set d1[1].r = d1[1].r + 1 where d1[1].i > 0; 200select * from dcomptable; 201 202alter domain dcomptypea add constraint c1 check (value[1].r <= value[1].i); 203alter domain dcomptypea add constraint c2 check (value[1].r > value[1].i); -- fail 204 205select array[row(2,1)]::dcomptypea; -- fail 206insert into dcomptable values (array[row(1,2)]::comptype[]); 207insert into dcomptable values (array[row(2,1)]::comptype[]); -- fail 208insert into dcomptable (d1[1].r) values(99); 209insert into dcomptable (d1[1].r, d1[1].i) values(99, 100); 210insert into dcomptable (d1[1].r, d1[1].i) values(100, 99); -- fail 211update dcomptable set d1[1].r = d1[1].r + 1 where d1[1].i > 0; -- fail 212update dcomptable set d1[1].r = d1[1].r - 1, d1[1].i = d1[1].i + 1 213 where d1[1].i > 0; 214select * from dcomptable; 215 216explain (verbose, costs off) 217 update dcomptable set d1[1].r = d1[1].r - 1, d1[1].i = d1[1].i + 1 218 where d1[1].i > 0; 219create rule silly as on delete to dcomptable do instead 220 update dcomptable set d1[1].r = d1[1].r - 1, d1[1].i = d1[1].i + 1 221 where d1[1].i > 0; 222\d+ dcomptable 223 224drop table dcomptable; 225drop type comptype cascade; 226 227 228-- Test arrays over domains 229 230create domain posint as int check (value > 0); 231 232create table pitable (f1 posint[]); 233insert into pitable values(array[42]); 234insert into pitable values(array[-1]); -- fail 235insert into pitable values('{0}'); -- fail 236update pitable set f1[1] = f1[1] + 1; 237update pitable set f1[1] = 0; -- fail 238select * from pitable; 239drop table pitable; 240 241create domain vc4 as varchar(4); 242create table vc4table (f1 vc4[]); 243insert into vc4table values(array['too long']); -- fail 244insert into vc4table values(array['too long']::vc4[]); -- cast truncates 245select * from vc4table; 246drop table vc4table; 247drop type vc4; 248 249-- You can sort of fake arrays-of-arrays by putting a domain in between 250create domain dposinta as posint[]; 251create table dposintatable (f1 dposinta[]); 252insert into dposintatable values(array[array[42]]); -- fail 253insert into dposintatable values(array[array[42]::posint[]]); -- still fail 254insert into dposintatable values(array[array[42]::dposinta]); -- but this works 255select f1, f1[1], (f1[1])[1] from dposintatable; 256select pg_typeof(f1) from dposintatable; 257select pg_typeof(f1[1]) from dposintatable; 258select pg_typeof(f1[1][1]) from dposintatable; 259select pg_typeof((f1[1])[1]) from dposintatable; 260update dposintatable set f1[2] = array[99]; 261select f1, f1[1], (f1[2])[1] from dposintatable; 262-- it'd be nice if you could do something like this, but for now you can't: 263update dposintatable set f1[2][1] = array[97]; 264-- maybe someday we can make this syntax work: 265update dposintatable set (f1[2])[1] = array[98]; 266 267drop table dposintatable; 268drop domain posint cascade; 269 270 271-- Test arrays over domains of composite 272 273create type comptype as (cf1 int, cf2 int); 274create domain dcomptype as comptype check ((value).cf1 > 0); 275 276create table dcomptable (f1 dcomptype[]); 277insert into dcomptable values (null); 278update dcomptable set f1[1].cf2 = 5; 279table dcomptable; 280update dcomptable set f1[1].cf1 = -1; -- fail 281update dcomptable set f1[1].cf1 = 1; 282table dcomptable; 283 284drop table dcomptable; 285drop type comptype cascade; 286 287 288-- Test not-null restrictions 289 290create domain dnotnull varchar(15) NOT NULL; 291create domain dnull varchar(15); 292create domain dcheck varchar(15) NOT NULL CHECK (VALUE = 'a' OR VALUE = 'c' OR VALUE = 'd'); 293 294create table nulltest 295 ( col1 dnotnull 296 , col2 dnotnull NULL -- NOT NULL in the domain cannot be overridden 297 , col3 dnull NOT NULL 298 , col4 dnull 299 , col5 dcheck CHECK (col5 IN ('c', 'd')) 300 ); 301INSERT INTO nulltest DEFAULT VALUES; 302INSERT INTO nulltest values ('a', 'b', 'c', 'd', 'c'); -- Good 303insert into nulltest values ('a', 'b', 'c', 'd', NULL); 304insert into nulltest values ('a', 'b', 'c', 'd', 'a'); 305INSERT INTO nulltest values (NULL, 'b', 'c', 'd', 'd'); 306INSERT INTO nulltest values ('a', NULL, 'c', 'd', 'c'); 307INSERT INTO nulltest values ('a', 'b', NULL, 'd', 'c'); 308INSERT INTO nulltest values ('a', 'b', 'c', NULL, 'd'); -- Good 309 310-- Test copy 311COPY nulltest FROM stdin; --fail 312a b \N d d 313\. 314 315COPY nulltest FROM stdin; --fail 316a b c d \N 317\. 318 319-- Last row is bad 320COPY nulltest FROM stdin; 321a b c \N c 322a b c \N d 323a b c \N a 324\. 325 326select * from nulltest; 327 328-- Test out coerced (casted) constraints 329SELECT cast('1' as dnotnull); 330SELECT cast(NULL as dnotnull); -- fail 331SELECT cast(cast(NULL as dnull) as dnotnull); -- fail 332SELECT cast(col4 as dnotnull) from nulltest; -- fail 333 334-- cleanup 335drop table nulltest; 336drop domain dnotnull restrict; 337drop domain dnull restrict; 338drop domain dcheck restrict; 339 340 341create domain ddef1 int4 DEFAULT 3; 342create domain ddef2 oid DEFAULT '12'; 343-- Type mixing, function returns int8 344create domain ddef3 text DEFAULT 5; 345create sequence ddef4_seq; 346create domain ddef4 int4 DEFAULT nextval('ddef4_seq'); 347create domain ddef5 numeric(8,2) NOT NULL DEFAULT '12.12'; 348 349create table defaulttest 350 ( col1 ddef1 351 , col2 ddef2 352 , col3 ddef3 353 , col4 ddef4 PRIMARY KEY 354 , col5 ddef1 NOT NULL DEFAULT NULL 355 , col6 ddef2 DEFAULT '88' 356 , col7 ddef4 DEFAULT 8000 357 , col8 ddef5 358 ); 359insert into defaulttest(col4) values(0); -- fails, col5 defaults to null 360alter table defaulttest alter column col5 drop default; 361insert into defaulttest default values; -- succeeds, inserts domain default 362-- We used to treat SET DEFAULT NULL as equivalent to DROP DEFAULT; wrong 363alter table defaulttest alter column col5 set default null; 364insert into defaulttest(col4) values(0); -- fails 365alter table defaulttest alter column col5 drop default; 366insert into defaulttest default values; 367insert into defaulttest default values; 368 369-- Test defaults with copy 370COPY defaulttest(col5) FROM stdin; 37142 372\. 373 374select * from defaulttest; 375 376drop table defaulttest cascade; 377 378-- Test ALTER DOMAIN .. NOT NULL 379create domain dnotnulltest integer; 380create table domnotnull 381( col1 dnotnulltest 382, col2 dnotnulltest 383); 384 385insert into domnotnull default values; 386alter domain dnotnulltest set not null; -- fails 387 388update domnotnull set col1 = 5; 389alter domain dnotnulltest set not null; -- fails 390 391update domnotnull set col2 = 6; 392 393alter domain dnotnulltest set not null; 394 395update domnotnull set col1 = null; -- fails 396 397alter domain dnotnulltest drop not null; 398 399update domnotnull set col1 = null; 400 401drop domain dnotnulltest cascade; 402 403-- Test ALTER DOMAIN .. DEFAULT .. 404create table domdeftest (col1 ddef1); 405 406insert into domdeftest default values; 407select * from domdeftest; 408 409alter domain ddef1 set default '42'; 410insert into domdeftest default values; 411select * from domdeftest; 412 413alter domain ddef1 drop default; 414insert into domdeftest default values; 415select * from domdeftest; 416 417drop table domdeftest; 418 419-- Test ALTER DOMAIN .. CONSTRAINT .. 420create domain con as integer; 421create table domcontest (col1 con); 422 423insert into domcontest values (1); 424insert into domcontest values (2); 425alter domain con add constraint t check (VALUE < 1); -- fails 426 427alter domain con add constraint t check (VALUE < 34); 428alter domain con add check (VALUE > 0); 429 430insert into domcontest values (-5); -- fails 431insert into domcontest values (42); -- fails 432insert into domcontest values (5); 433 434alter domain con drop constraint t; 435insert into domcontest values (-5); --fails 436insert into domcontest values (42); 437 438alter domain con drop constraint nonexistent; 439alter domain con drop constraint if exists nonexistent; 440 441-- Test ALTER DOMAIN .. CONSTRAINT .. NOT VALID 442create domain things AS INT; 443CREATE TABLE thethings (stuff things); 444INSERT INTO thethings (stuff) VALUES (55); 445ALTER DOMAIN things ADD CONSTRAINT meow CHECK (VALUE < 11); 446ALTER DOMAIN things ADD CONSTRAINT meow CHECK (VALUE < 11) NOT VALID; 447ALTER DOMAIN things VALIDATE CONSTRAINT meow; 448UPDATE thethings SET stuff = 10; 449ALTER DOMAIN things VALIDATE CONSTRAINT meow; 450 451-- Confirm ALTER DOMAIN with RULES. 452create table domtab (col1 integer); 453create domain dom as integer; 454create view domview as select cast(col1 as dom) from domtab; 455insert into domtab (col1) values (null); 456insert into domtab (col1) values (5); 457select * from domview; 458 459alter domain dom set not null; 460select * from domview; -- fail 461 462alter domain dom drop not null; 463select * from domview; 464 465alter domain dom add constraint domchkgt6 check(value > 6); 466select * from domview; --fail 467 468alter domain dom drop constraint domchkgt6 restrict; 469select * from domview; 470 471-- cleanup 472drop domain ddef1 restrict; 473drop domain ddef2 restrict; 474drop domain ddef3 restrict; 475drop domain ddef4 restrict; 476drop domain ddef5 restrict; 477drop sequence ddef4_seq; 478 479-- Test domains over domains 480create domain vchar4 varchar(4); 481create domain dinter vchar4 check (substring(VALUE, 1, 1) = 'x'); 482create domain dtop dinter check (substring(VALUE, 2, 1) = '1'); 483 484select 'x123'::dtop; 485select 'x1234'::dtop; -- explicit coercion should truncate 486select 'y1234'::dtop; -- fail 487select 'y123'::dtop; -- fail 488select 'yz23'::dtop; -- fail 489select 'xz23'::dtop; -- fail 490 491create temp table dtest(f1 dtop); 492 493insert into dtest values('x123'); 494insert into dtest values('x1234'); -- fail, implicit coercion 495insert into dtest values('y1234'); -- fail, implicit coercion 496insert into dtest values('y123'); -- fail 497insert into dtest values('yz23'); -- fail 498insert into dtest values('xz23'); -- fail 499 500drop table dtest; 501drop domain vchar4 cascade; 502 503-- Make sure that constraints of newly-added domain columns are 504-- enforced correctly, even if there's no default value for the new 505-- column. Per bug #1433 506create domain str_domain as text not null; 507 508create table domain_test (a int, b int); 509 510insert into domain_test values (1, 2); 511insert into domain_test values (1, 2); 512 513-- should fail 514alter table domain_test add column c str_domain; 515 516create domain str_domain2 as text check (value <> 'foo') default 'foo'; 517 518-- should fail 519alter table domain_test add column d str_domain2; 520 521-- Check that domain constraints on prepared statement parameters of 522-- unknown type are enforced correctly. 523create domain pos_int as int4 check (value > 0) not null; 524prepare s1 as select $1::pos_int = 10 as "is_ten"; 525 526execute s1(10); 527execute s1(0); -- should fail 528execute s1(NULL); -- should fail 529 530-- Check that domain constraints on plpgsql function parameters, results, 531-- and local variables are enforced correctly. 532 533create function doubledecrement(p1 pos_int) returns pos_int as $$ 534declare v pos_int; 535begin 536 return p1; 537end$$ language plpgsql; 538 539select doubledecrement(3); -- fail because of implicit null assignment 540 541create or replace function doubledecrement(p1 pos_int) returns pos_int as $$ 542declare v pos_int := 0; 543begin 544 return p1; 545end$$ language plpgsql; 546 547select doubledecrement(3); -- fail at initialization assignment 548 549create or replace function doubledecrement(p1 pos_int) returns pos_int as $$ 550declare v pos_int := 1; 551begin 552 v := p1 - 1; 553 return v - 1; 554end$$ language plpgsql; 555 556select doubledecrement(null); -- fail before call 557select doubledecrement(0); -- fail before call 558select doubledecrement(1); -- fail at assignment to v 559select doubledecrement(2); -- fail at return 560select doubledecrement(3); -- good 561 562-- Check that ALTER DOMAIN tests columns of derived types 563 564create domain posint as int4; 565 566-- Currently, this doesn't work for composite types, but verify it complains 567create type ddtest1 as (f1 posint); 568create table ddtest2(f1 ddtest1); 569insert into ddtest2 values(row(-1)); 570alter domain posint add constraint c1 check(value >= 0); 571drop table ddtest2; 572 573-- Likewise for domains within arrays of composite 574create table ddtest2(f1 ddtest1[]); 575insert into ddtest2 values('{(-1)}'); 576alter domain posint add constraint c1 check(value >= 0); 577drop table ddtest2; 578 579-- Likewise for domains within domains over composite 580create domain ddtest1d as ddtest1; 581create table ddtest2(f1 ddtest1d); 582insert into ddtest2 values('(-1)'); 583alter domain posint add constraint c1 check(value >= 0); 584drop table ddtest2; 585drop domain ddtest1d; 586 587-- Likewise for domains within domains over array of composite 588create domain ddtest1d as ddtest1[]; 589create table ddtest2(f1 ddtest1d); 590insert into ddtest2 values('{(-1)}'); 591alter domain posint add constraint c1 check(value >= 0); 592drop table ddtest2; 593drop domain ddtest1d; 594 595-- Doesn't work for ranges, either 596create type rposint as range (subtype = posint); 597create table ddtest2(f1 rposint); 598insert into ddtest2 values('(-1,3]'); 599alter domain posint add constraint c1 check(value >= 0); 600drop table ddtest2; 601drop type rposint; 602 603alter domain posint add constraint c1 check(value >= 0); 604 605create domain posint2 as posint check (value % 2 = 0); 606create table ddtest2(f1 posint2); 607insert into ddtest2 values(11); -- fail 608insert into ddtest2 values(-2); -- fail 609insert into ddtest2 values(2); 610 611alter domain posint add constraint c2 check(value >= 10); -- fail 612alter domain posint add constraint c2 check(value > 0); -- OK 613 614drop table ddtest2; 615drop type ddtest1; 616drop domain posint cascade; 617 618-- 619-- Check enforcement of domain-related typmod in plpgsql (bug #5717) 620-- 621 622create or replace function array_elem_check(numeric) returns numeric as $$ 623declare 624 x numeric(4,2)[1]; 625begin 626 x[1] := $1; 627 return x[1]; 628end$$ language plpgsql; 629 630select array_elem_check(121.00); 631select array_elem_check(1.23456); 632 633create domain mynums as numeric(4,2)[1]; 634 635create or replace function array_elem_check(numeric) returns numeric as $$ 636declare 637 x mynums; 638begin 639 x[1] := $1; 640 return x[1]; 641end$$ language plpgsql; 642 643select array_elem_check(121.00); 644select array_elem_check(1.23456); 645 646create domain mynums2 as mynums; 647 648create or replace function array_elem_check(numeric) returns numeric as $$ 649declare 650 x mynums2; 651begin 652 x[1] := $1; 653 return x[1]; 654end$$ language plpgsql; 655 656select array_elem_check(121.00); 657select array_elem_check(1.23456); 658 659drop function array_elem_check(numeric); 660 661-- 662-- Check enforcement of array-level domain constraints 663-- 664 665create domain orderedpair as int[2] check (value[1] < value[2]); 666 667select array[1,2]::orderedpair; 668select array[2,1]::orderedpair; -- fail 669 670create temp table op (f1 orderedpair); 671insert into op values (array[1,2]); 672insert into op values (array[2,1]); -- fail 673 674update op set f1[2] = 3; 675update op set f1[2] = 0; -- fail 676select * from op; 677 678create or replace function array_elem_check(int) returns int as $$ 679declare 680 x orderedpair := '{1,2}'; 681begin 682 x[2] := $1; 683 return x[2]; 684end$$ language plpgsql; 685 686select array_elem_check(3); 687select array_elem_check(-1); 688 689drop function array_elem_check(int); 690 691-- 692-- Check enforcement of changing constraints in plpgsql 693-- 694 695create domain di as int; 696 697create function dom_check(int) returns di as $$ 698declare d di; 699begin 700 d := $1::di; 701 return d; 702end 703$$ language plpgsql immutable; 704 705select dom_check(0); 706 707alter domain di add constraint pos check (value > 0); 708 709select dom_check(0); -- fail 710 711alter domain di drop constraint pos; 712 713select dom_check(0); 714 715-- implicit cast during assignment is a separate code path, test that too 716 717create or replace function dom_check(int) returns di as $$ 718declare d di; 719begin 720 d := $1; 721 return d; 722end 723$$ language plpgsql immutable; 724 725select dom_check(0); 726 727alter domain di add constraint pos check (value > 0); 728 729select dom_check(0); -- fail 730 731alter domain di drop constraint pos; 732 733select dom_check(0); 734 735drop function dom_check(int); 736 737drop domain di; 738 739-- 740-- Check use of a (non-inline-able) SQL function in a domain constraint; 741-- this has caused issues in the past 742-- 743 744create function sql_is_distinct_from(anyelement, anyelement) 745returns boolean language sql 746as 'select $1 is distinct from $2 limit 1'; 747 748create domain inotnull int 749 check (sql_is_distinct_from(value, null)); 750 751select 1::inotnull; 752select null::inotnull; 753 754create table dom_table (x inotnull); 755insert into dom_table values ('1'); 756insert into dom_table values (1); 757insert into dom_table values (null); 758 759drop table dom_table; 760drop domain inotnull; 761drop function sql_is_distinct_from(anyelement, anyelement); 762 763-- 764-- Renaming 765-- 766 767create domain testdomain1 as int; 768alter domain testdomain1 rename to testdomain2; 769alter type testdomain2 rename to testdomain3; -- alter type also works 770drop domain testdomain3; 771 772 773-- 774-- Renaming domain constraints 775-- 776 777create domain testdomain1 as int constraint unsigned check (value > 0); 778alter domain testdomain1 rename constraint unsigned to unsigned_foo; 779alter domain testdomain1 drop constraint unsigned_foo; 780drop domain testdomain1; 781