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