src/test/regress/sql/domain.sql

   1 --
   2 -- Test domains.
   3 --
   4
   5 -- Test Comment / Drop
   6 create domain domaindroptest int4;
   7 comment on domain domaindroptest is 'About to drop this..';
   8
   9 create domain dependenttypetest domaindroptest;
  10
  11 -- fail because of dependent type
  12 drop domain domaindroptest;
  13
  14 drop domain domaindroptest cascade;
  15
  16 -- this should fail because already gone
  17 drop 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
  25 create domain domainvarchar varchar(5);
  26 create domain domainnumeric numeric(8,2);
  27 create domain domainint4 int4;
  28 create domain domaintext text;
  29
  30 -- Test explicit coercions --- these should succeed (and truncate)
  31 SELECT cast('123456' as domainvarchar);
  32 SELECT cast('12345' as domainvarchar);
  33
  34 -- Test tables using domains
  35 create table basictest
  36            ( testint4 domainint4
  37            , testtext domaintext
  38            , testvarchar domainvarchar
  39            , testnumeric domainnumeric
  40            );
  41
  42 INSERT INTO basictest values ('88', 'haha', 'short', '123.12');      -- Good
  43 INSERT INTO basictest values ('88', 'haha', 'short text', '123.12'); -- Bad varchar
  44 INSERT INTO basictest values ('88', 'haha', 'short', '123.1212');    -- Truncate numeric
  45
  46 -- Test copy
  47 COPY basictest (testvarchar) FROM stdin; -- fail
  48 notsoshorttext
  49 \.
  50
  51 COPY basictest (testvarchar) FROM stdin;
  52 short
  53 \.
  54
  55 select * from basictest;
  56
  57 -- check that domains inherit operations from base types
  58 select testtext || testvarchar as concat, testnumeric + 42 as sum
  59 from basictest;
  60
  61 -- check that union/case/coalesce type resolution handles domains properly
  62 select coalesce(4::domainint4, 7) is of (int4) as t;
  63 select coalesce(4::domainint4, 7) is of (domainint4) as f;
  64 select coalesce(4::domainint4, 7::domainint4) is of (domainint4) as t;
  65
  66 drop table basictest;
  67 drop domain domainvarchar restrict;
  68 drop domain domainnumeric restrict;
  69 drop domain domainint4 restrict;
  70 drop domain domaintext;
  71
  72
  73 -- Test domains over array types
  74
  75 create domain domainint4arr int4[1];
  76 create domain domainchar4arr varchar(4)[2][3];
  77
  78 create table domarrtest
  79            ( testint4arr domainint4arr
  80            , testchar4arr domainchar4arr
  81             );
  82 INSERT INTO domarrtest values ('{2,2}', '{{"a","b"},{"c","d"}}');
  83 INSERT INTO domarrtest values ('{{2,2},{2,2}}', '{{"a","b"}}');
  84 INSERT INTO domarrtest values ('{2,2}', '{{"a","b"},{"c","d"},{"e","f"}}');
  85 INSERT INTO domarrtest values ('{2,2}', '{{"a"},{"c"}}');
  86 INSERT INTO domarrtest values (NULL, '{{"a","b","c"},{"d","e","f"}}');
  87 INSERT INTO domarrtest values (NULL, '{{"toolong","b","c"},{"d","e","f"}}');
  88 select * from domarrtest;
  89 select testint4arr[1], testchar4arr[2:2] from domarrtest;
  90
  91 COPY domarrtest FROM stdin;
  92 {3,4}   {q,w,e}
  93 \N      \N
  94 \.
  95
  96 COPY domarrtest FROM stdin;     -- fail
  97 {3,4}   {qwerty,w,e}
  98 \.
  99
 100 select * from domarrtest;
 101
 102 drop table domarrtest;
 103 drop domain domainint4arr restrict;
 104 drop domain domainchar4arr restrict;
 105
 106
 107 create domain dnotnull varchar(15) NOT NULL;
 108 create domain dnull    varchar(15);
 109 create domain dcheck   varchar(15) NOT NULL CHECK (VALUE = 'a' OR VALUE = 'c' OR VALUE = 'd');
 110
 111 create table nulltest
 112            ( col1 dnotnull
 113            , col2 dnotnull NULL  -- NOT NULL in the domain cannot be overridden
 114            , col3 dnull    NOT NULL
 115            , col4 dnull
 116            , col5 dcheck CHECK (col5 IN ('c', 'd'))
 117            );
 118 INSERT INTO nulltest DEFAULT VALUES;
 119 INSERT INTO nulltest values ('a', 'b', 'c', 'd', 'c');  -- Good
 120 insert into nulltest values ('a', 'b', 'c', 'd', NULL);
 121 insert into nulltest values ('a', 'b', 'c', 'd', 'a');
 122 INSERT INTO nulltest values (NULL, 'b', 'c', 'd', 'd');
 123 INSERT INTO nulltest values ('a', NULL, 'c', 'd', 'c');
 124 INSERT INTO nulltest values ('a', 'b', NULL, 'd', 'c');
 125 INSERT INTO nulltest values ('a', 'b', 'c', NULL, 'd'); -- Good
 126
 127 -- Test copy
 128 COPY nulltest FROM stdin; --fail
 129 a       b       \N      d       d
 130 \.
 131
 132 COPY nulltest FROM stdin; --fail
 133 a       b       c       d       \N
 134 \.
 135
 136 -- Last row is bad
 137 COPY nulltest FROM stdin;
 138 a       b       c       \N      c
 139 a       b       c       \N      d
 140 a       b       c       \N      a
 141 \.
 142
 143 select * from nulltest;
 144
 145 -- Test out coerced (casted) constraints
 146 SELECT cast('1' as dnotnull);
 147 SELECT cast(NULL as dnotnull); -- fail
 148 SELECT cast(cast(NULL as dnull) as dnotnull); -- fail
 149 SELECT cast(col4 as dnotnull) from nulltest; -- fail
 150
 151 -- cleanup
 152 drop table nulltest;
 153 drop domain dnotnull restrict;
 154 drop domain dnull restrict;
 155 drop domain dcheck restrict;
 156
 157
 158 create domain ddef1 int4 DEFAULT 3;
 159 create domain ddef2 oid DEFAULT '12';
 160 -- Type mixing, function returns int8
 161 create domain ddef3 text DEFAULT 5;
 162 create sequence ddef4_seq;
 163 create domain ddef4 int4 DEFAULT nextval('ddef4_seq');
 164 create domain ddef5 numeric(8,2) NOT NULL DEFAULT '12.12';
 165
 166 create table defaulttest
 167             ( col1 ddef1
 168             , col2 ddef2
 169             , col3 ddef3
 170             , col4 ddef4 PRIMARY KEY
 171             , col5 ddef1 NOT NULL DEFAULT NULL
 172             , col6 ddef2 DEFAULT '88'
 173             , col7 ddef4 DEFAULT 8000
 174             , col8 ddef5
 175             );
 176 insert into defaulttest(col4) values(0); -- fails, col5 defaults to null
 177 alter table defaulttest alter column col5 drop default;
 178 insert into defaulttest default values; -- succeeds, inserts domain default
 179 -- We used to treat SET DEFAULT NULL as equivalent to DROP DEFAULT; wrong
 180 alter table defaulttest alter column col5 set default null;
 181 insert into defaulttest(col4) values(0); -- fails
 182 alter table defaulttest alter column col5 drop default;
 183 insert into defaulttest default values;
 184 insert into defaulttest default values;
 185
 186 -- Test defaults with copy
 187 COPY defaulttest(col5) FROM stdin;
 188 42
 189 \.
 190
 191 select * from defaulttest;
 192
 193 drop table defaulttest cascade;
 194
 195 -- Test ALTER DOMAIN .. NOT NULL
 196 create domain dnotnulltest integer;
 197 create table domnotnull
 198 ( col1 dnotnulltest
 199 , col2 dnotnulltest
 200 );
 201
 202 insert into domnotnull default values;
 203 alter domain dnotnulltest set not null; -- fails
 204
 205 update domnotnull set col1 = 5;
 206 alter domain dnotnulltest set not null; -- fails
 207
 208 update domnotnull set col2 = 6;
 209
 210 alter domain dnotnulltest set not null;
 211
 212 update domnotnull set col1 = null; -- fails
 213
 214 alter domain dnotnulltest drop not null;
 215
 216 update domnotnull set col1 = null;
 217
 218 drop domain dnotnulltest cascade;
 219
 220 -- Test ALTER DOMAIN .. DEFAULT ..
 221 create table domdeftest (col1 ddef1);
 222
 223 insert into domdeftest default values;
 224 select * from domdeftest;
 225
 226 alter domain ddef1 set default '42';
 227 insert into domdeftest default values;
 228 select * from domdeftest;
 229
 230 alter domain ddef1 drop default;
 231 insert into domdeftest default values;
 232 select * from domdeftest;
 233
 234 drop table domdeftest;
 235
 236 -- Test ALTER DOMAIN .. CONSTRAINT ..
 237 create domain con as integer;
 238 create table domcontest (col1 con);
 239
 240 insert into domcontest values (1);
 241 insert into domcontest values (2);
 242 alter domain con add constraint t check (VALUE < 1); -- fails
 243
 244 alter domain con add constraint t check (VALUE < 34);
 245 alter domain con add check (VALUE > 0);
 246
 247 insert into domcontest values (-5); -- fails
 248 insert into domcontest values (42); -- fails
 249 insert into domcontest values (5);
 250
 251 alter domain con drop constraint t;
 252 insert into domcontest values (-5); --fails
 253 insert into domcontest values (42);
 254
 255 -- Confirm ALTER DOMAIN with RULES.
 256 create table domtab (col1 integer);
 257 create domain dom as integer;
 258 create view domview as select cast(col1 as dom) from domtab;
 259 insert into domtab (col1) values (null);
 260 insert into domtab (col1) values (5);
 261 select * from domview;
 262
 263 alter domain dom set not null;
 264 select * from domview; -- fail
 265
 266 alter domain dom drop not null;
 267 select * from domview;
 268
 269 alter domain dom add constraint domchkgt6 check(value > 6);
 270 select * from domview; --fail
 271
 272 alter domain dom drop constraint domchkgt6 restrict;
 273 select * from domview;
 274
 275 -- cleanup
 276 drop domain ddef1 restrict;
 277 drop domain ddef2 restrict;
 278 drop domain ddef3 restrict;
 279 drop domain ddef4 restrict;
 280 drop domain ddef5 restrict;
 281 drop sequence ddef4_seq;
 282
 283 -- Test domains over domains
 284 create domain vchar4 varchar(4);
 285 create domain dinter vchar4 check (substring(VALUE, 1, 1) = 'x');
 286 create domain dtop dinter check (substring(VALUE, 2, 1) = '1');
 287
 288 select 'x123'::dtop;
 289 select 'x1234'::dtop; -- explicit coercion should truncate
 290 select 'y1234'::dtop; -- fail
 291 select 'y123'::dtop; -- fail
 292 select 'yz23'::dtop; -- fail
 293 select 'xz23'::dtop; -- fail
 294
 295 create temp table dtest(f1 dtop);
 296
 297 insert into dtest values('x123');
 298 insert into dtest values('x1234'); -- fail, implicit coercion
 299 insert into dtest values('y1234'); -- fail, implicit coercion
 300 insert into dtest values('y123'); -- fail
 301 insert into dtest values('yz23'); -- fail
 302 insert into dtest values('xz23'); -- fail
 303
 304 drop table dtest;
 305 drop domain vchar4 cascade;
 306
 307 -- Make sure that constraints of newly-added domain columns are
 308 -- enforced correctly, even if there's no default value for the new
 309 -- column. Per bug #1433
 310 create domain str_domain as text not null;
 311
 312 create table domain_test (a int, b int);
 313
 314 insert into domain_test values (1, 2);
 315 insert into domain_test values (1, 2);
 316
 317 -- should fail
 318 alter table domain_test add column c str_domain;
 319
 320 create domain str_domain2 as text check (value <> 'foo') default 'foo';
 321
 322 -- should fail
 323 alter table domain_test add column d str_domain2;
 324
 325 -- Check that domain constraints on prepared statement parameters of
 326 -- unknown type are enforced correctly.
 327 create domain pos_int as int4 check (value > 0) not null;
 328 prepare s1 as select $1::pos_int = 10 as "is_ten";
 329
 330 execute s1(10);
 331 execute s1(0); -- should fail
 332 execute s1(NULL); -- should fail
 333
 334 -- Check that domain constraints on plpgsql function parameters, results,
 335 -- and local variables are enforced correctly.
 336
 337 create function doubledecrement(p1 pos_int) returns pos_int as $$
 338 declare v pos_int;
 339 begin
 340     return p1;
 341 end$$ language plpgsql;
 342
 343 select doubledecrement(3); -- fail because of implicit null assignment
 344
 345 create or replace function doubledecrement(p1 pos_int) returns pos_int as $$
 346 declare v pos_int := 0;
 347 begin
 348     return p1;
 349 end$$ language plpgsql;
 350
 351 select doubledecrement(3); -- fail at initialization assignment
 352
 353 create or replace function doubledecrement(p1 pos_int) returns pos_int as $$
 354 declare v pos_int := 1;
 355 begin
 356     v := p1 - 1;
 357     return v - 1;
 358 end$$ language plpgsql;
 359
 360 select doubledecrement(null); -- fail before call
 361 select doubledecrement(0); -- fail before call
 362 select doubledecrement(1); -- fail at assignment to v
 363 select doubledecrement(2); -- fail at return
 364 select doubledecrement(3); -- good
 365
 366 -- Check that ALTER DOMAIN tests columns of derived types
 367
 368 create domain posint as int4;
 369
 370 -- Currently, this doesn't work for composite types, but verify it complains
 371 create type ddtest1 as (f1 posint);
 372 create table ddtest2(f1 ddtest1);
 373 insert into ddtest2 values(row(-1));
 374 alter domain posint add constraint c1 check(value >= 0);
 375 drop table ddtest2;
 376
 377 create table ddtest2(f1 ddtest1[]);
 378 insert into ddtest2 values('{(-1)}');
 379 alter domain posint add constraint c1 check(value >= 0);
 380 drop table ddtest2;
 381
 382 alter domain posint add constraint c1 check(value >= 0);
 383
 384 create domain posint2 as posint check (value % 2 = 0);
 385 create table ddtest2(f1 posint2);
 386 insert into ddtest2 values(11); -- fail
 387 insert into ddtest2 values(-2); -- fail
 388 insert into ddtest2 values(2);
 389
 390 alter domain posint add constraint c2 check(value >= 10); -- fail
 391 alter domain posint add constraint c2 check(value > 0); -- OK
 392
 393 drop table ddtest2;
 394 drop type ddtest1;
 395 drop domain posint cascade;