1-- 2-- UPDATABLE VIEWS 3-- 4 5-- avoid bit-exact output here because operations may not be bit-exact. 6SET extra_float_digits = 0; 7 8-- check that non-updatable views and columns are rejected with useful error 9-- messages 10 11CREATE TABLE base_tbl (a int PRIMARY KEY, b text DEFAULT 'Unspecified'); 12INSERT INTO base_tbl SELECT i, 'Row ' || i FROM generate_series(-2, 2) g(i); 13 14CREATE VIEW ro_view1 AS SELECT DISTINCT a, b FROM base_tbl; -- DISTINCT not supported 15CREATE VIEW ro_view2 AS SELECT a, b FROM base_tbl GROUP BY a, b; -- GROUP BY not supported 16CREATE VIEW ro_view3 AS SELECT 1 FROM base_tbl HAVING max(a) > 0; -- HAVING not supported 17CREATE VIEW ro_view4 AS SELECT count(*) FROM base_tbl; -- Aggregate functions not supported 18CREATE VIEW ro_view5 AS SELECT a, rank() OVER() FROM base_tbl; -- Window functions not supported 19CREATE VIEW ro_view6 AS SELECT a, b FROM base_tbl UNION SELECT -a, b FROM base_tbl; -- Set ops not supported 20CREATE VIEW ro_view7 AS WITH t AS (SELECT a, b FROM base_tbl) SELECT * FROM t; -- WITH not supported 21CREATE VIEW ro_view8 AS SELECT a, b FROM base_tbl ORDER BY a OFFSET 1; -- OFFSET not supported 22CREATE VIEW ro_view9 AS SELECT a, b FROM base_tbl ORDER BY a LIMIT 1; -- LIMIT not supported 23CREATE VIEW ro_view10 AS SELECT 1 AS a; -- No base relations 24CREATE VIEW ro_view11 AS SELECT b1.a, b2.b FROM base_tbl b1, base_tbl b2; -- Multiple base relations 25CREATE VIEW ro_view12 AS SELECT * FROM generate_series(1, 10) AS g(a); -- SRF in rangetable 26CREATE VIEW ro_view13 AS SELECT a, b FROM (SELECT * FROM base_tbl) AS t; -- Subselect in rangetable 27CREATE VIEW rw_view14 AS SELECT ctid, a, b FROM base_tbl; -- System columns may be part of an updatable view 28CREATE VIEW rw_view15 AS SELECT a, upper(b) FROM base_tbl; -- Expression/function may be part of an updatable view 29CREATE VIEW rw_view16 AS SELECT a, b, a AS aa FROM base_tbl; -- Repeated column may be part of an updatable view 30CREATE VIEW ro_view17 AS SELECT * FROM ro_view1; -- Base relation not updatable 31CREATE VIEW ro_view18 AS SELECT * FROM (VALUES(1)) AS tmp(a); -- VALUES in rangetable 32CREATE SEQUENCE uv_seq; 33CREATE VIEW ro_view19 AS SELECT * FROM uv_seq; -- View based on a sequence 34CREATE VIEW ro_view20 AS SELECT a, b, generate_series(1, a) g FROM base_tbl; -- SRF in targetlist not supported 35 36SELECT table_name, is_insertable_into 37 FROM information_schema.tables 38 WHERE table_name LIKE E'r_\\_view%' 39 ORDER BY table_name; 40 41SELECT table_name, is_updatable, is_insertable_into 42 FROM information_schema.views 43 WHERE table_name LIKE E'r_\\_view%' 44 ORDER BY table_name; 45 46SELECT table_name, column_name, is_updatable 47 FROM information_schema.columns 48 WHERE table_name LIKE E'r_\\_view%' 49 ORDER BY table_name, ordinal_position; 50 51-- Read-only views 52DELETE FROM ro_view1; 53DELETE FROM ro_view2; 54DELETE FROM ro_view3; 55DELETE FROM ro_view4; 56DELETE FROM ro_view5; 57DELETE FROM ro_view6; 58UPDATE ro_view7 SET a=a+1; 59UPDATE ro_view8 SET a=a+1; 60UPDATE ro_view9 SET a=a+1; 61UPDATE ro_view10 SET a=a+1; 62UPDATE ro_view11 SET a=a+1; 63UPDATE ro_view12 SET a=a+1; 64INSERT INTO ro_view13 VALUES (3, 'Row 3'); 65-- Partially updatable view 66INSERT INTO rw_view14 VALUES (null, 3, 'Row 3'); -- should fail 67INSERT INTO rw_view14 (a, b) VALUES (3, 'Row 3'); -- should be OK 68UPDATE rw_view14 SET ctid=null WHERE a=3; -- should fail 69UPDATE rw_view14 SET b='ROW 3' WHERE a=3; -- should be OK 70SELECT * FROM base_tbl; 71DELETE FROM rw_view14 WHERE a=3; -- should be OK 72-- Partially updatable view 73INSERT INTO rw_view15 VALUES (3, 'ROW 3'); -- should fail 74INSERT INTO rw_view15 (a) VALUES (3); -- should be OK 75INSERT INTO rw_view15 (a) VALUES (3) ON CONFLICT DO NOTHING; -- succeeds 76SELECT * FROM rw_view15; 77INSERT INTO rw_view15 (a) VALUES (3) ON CONFLICT (a) DO NOTHING; -- succeeds 78SELECT * FROM rw_view15; 79INSERT INTO rw_view15 (a) VALUES (3) ON CONFLICT (a) DO UPDATE set a = excluded.a; -- succeeds 80SELECT * FROM rw_view15; 81INSERT INTO rw_view15 (a) VALUES (3) ON CONFLICT (a) DO UPDATE set upper = 'blarg'; -- fails 82SELECT * FROM rw_view15; 83SELECT * FROM rw_view15; 84ALTER VIEW rw_view15 ALTER COLUMN upper SET DEFAULT 'NOT SET'; 85INSERT INTO rw_view15 (a) VALUES (4); -- should fail 86UPDATE rw_view15 SET upper='ROW 3' WHERE a=3; -- should fail 87UPDATE rw_view15 SET upper=DEFAULT WHERE a=3; -- should fail 88UPDATE rw_view15 SET a=4 WHERE a=3; -- should be OK 89SELECT * FROM base_tbl; 90DELETE FROM rw_view15 WHERE a=4; -- should be OK 91-- Partially updatable view 92INSERT INTO rw_view16 VALUES (3, 'Row 3', 3); -- should fail 93INSERT INTO rw_view16 (a, b) VALUES (3, 'Row 3'); -- should be OK 94UPDATE rw_view16 SET a=3, aa=-3 WHERE a=3; -- should fail 95UPDATE rw_view16 SET aa=-3 WHERE a=3; -- should be OK 96SELECT * FROM base_tbl; 97DELETE FROM rw_view16 WHERE a=-3; -- should be OK 98-- Read-only views 99INSERT INTO ro_view17 VALUES (3, 'ROW 3'); 100DELETE FROM ro_view18; 101UPDATE ro_view19 SET last_value=1000; 102UPDATE ro_view20 SET b=upper(b); 103 104-- A view with a conditional INSTEAD rule but no unconditional INSTEAD rules 105-- or INSTEAD OF triggers should be non-updatable and generate useful error 106-- messages with appropriate detail 107CREATE RULE rw_view16_ins_rule AS ON INSERT TO rw_view16 108 WHERE NEW.a > 0 DO INSTEAD INSERT INTO base_tbl VALUES (NEW.a, NEW.b); 109CREATE RULE rw_view16_upd_rule AS ON UPDATE TO rw_view16 110 WHERE OLD.a > 0 DO INSTEAD UPDATE base_tbl SET b=NEW.b WHERE a=OLD.a; 111CREATE RULE rw_view16_del_rule AS ON DELETE TO rw_view16 112 WHERE OLD.a > 0 DO INSTEAD DELETE FROM base_tbl WHERE a=OLD.a; 113 114INSERT INTO rw_view16 (a, b) VALUES (3, 'Row 3'); -- should fail 115UPDATE rw_view16 SET b='ROW 2' WHERE a=2; -- should fail 116DELETE FROM rw_view16 WHERE a=2; -- should fail 117 118DROP TABLE base_tbl CASCADE; 119DROP VIEW ro_view10, ro_view12, ro_view18; 120DROP SEQUENCE uv_seq CASCADE; 121 122-- simple updatable view 123 124CREATE TABLE base_tbl (a int PRIMARY KEY, b text DEFAULT 'Unspecified'); 125INSERT INTO base_tbl SELECT i, 'Row ' || i FROM generate_series(-2, 2) g(i); 126 127CREATE VIEW rw_view1 AS SELECT * FROM base_tbl WHERE a>0; 128 129SELECT table_name, is_insertable_into 130 FROM information_schema.tables 131 WHERE table_name = 'rw_view1'; 132 133SELECT table_name, is_updatable, is_insertable_into 134 FROM information_schema.views 135 WHERE table_name = 'rw_view1'; 136 137SELECT table_name, column_name, is_updatable 138 FROM information_schema.columns 139 WHERE table_name = 'rw_view1' 140 ORDER BY ordinal_position; 141 142INSERT INTO rw_view1 VALUES (3, 'Row 3'); 143INSERT INTO rw_view1 (a) VALUES (4); 144UPDATE rw_view1 SET a=5 WHERE a=4; 145DELETE FROM rw_view1 WHERE b='Row 2'; 146SELECT * FROM base_tbl; 147 148EXPLAIN (costs off) UPDATE rw_view1 SET a=6 WHERE a=5; 149EXPLAIN (costs off) DELETE FROM rw_view1 WHERE a=5; 150 151DROP TABLE base_tbl CASCADE; 152 153-- view on top of view 154 155CREATE TABLE base_tbl (a int PRIMARY KEY, b text DEFAULT 'Unspecified'); 156INSERT INTO base_tbl SELECT i, 'Row ' || i FROM generate_series(-2, 2) g(i); 157 158CREATE VIEW rw_view1 AS SELECT b AS bb, a AS aa FROM base_tbl WHERE a>0; 159CREATE VIEW rw_view2 AS SELECT aa AS aaa, bb AS bbb FROM rw_view1 WHERE aa<10; 160 161SELECT table_name, is_insertable_into 162 FROM information_schema.tables 163 WHERE table_name = 'rw_view2'; 164 165SELECT table_name, is_updatable, is_insertable_into 166 FROM information_schema.views 167 WHERE table_name = 'rw_view2'; 168 169SELECT table_name, column_name, is_updatable 170 FROM information_schema.columns 171 WHERE table_name = 'rw_view2' 172 ORDER BY ordinal_position; 173 174INSERT INTO rw_view2 VALUES (3, 'Row 3'); 175INSERT INTO rw_view2 (aaa) VALUES (4); 176SELECT * FROM rw_view2; 177UPDATE rw_view2 SET bbb='Row 4' WHERE aaa=4; 178DELETE FROM rw_view2 WHERE aaa=2; 179SELECT * FROM rw_view2; 180 181EXPLAIN (costs off) UPDATE rw_view2 SET aaa=5 WHERE aaa=4; 182EXPLAIN (costs off) DELETE FROM rw_view2 WHERE aaa=4; 183 184DROP TABLE base_tbl CASCADE; 185 186-- view on top of view with rules 187 188CREATE TABLE base_tbl (a int PRIMARY KEY, b text DEFAULT 'Unspecified'); 189INSERT INTO base_tbl SELECT i, 'Row ' || i FROM generate_series(-2, 2) g(i); 190 191CREATE VIEW rw_view1 AS SELECT * FROM base_tbl WHERE a>0 OFFSET 0; -- not updatable without rules/triggers 192CREATE VIEW rw_view2 AS SELECT * FROM rw_view1 WHERE a<10; 193 194SELECT table_name, is_insertable_into 195 FROM information_schema.tables 196 WHERE table_name LIKE 'rw_view%' 197 ORDER BY table_name; 198 199SELECT table_name, is_updatable, is_insertable_into 200 FROM information_schema.views 201 WHERE table_name LIKE 'rw_view%' 202 ORDER BY table_name; 203 204SELECT table_name, column_name, is_updatable 205 FROM information_schema.columns 206 WHERE table_name LIKE 'rw_view%' 207 ORDER BY table_name, ordinal_position; 208 209CREATE RULE rw_view1_ins_rule AS ON INSERT TO rw_view1 210 DO INSTEAD INSERT INTO base_tbl VALUES (NEW.a, NEW.b) RETURNING *; 211 212SELECT table_name, is_insertable_into 213 FROM information_schema.tables 214 WHERE table_name LIKE 'rw_view%' 215 ORDER BY table_name; 216 217SELECT table_name, is_updatable, is_insertable_into 218 FROM information_schema.views 219 WHERE table_name LIKE 'rw_view%' 220 ORDER BY table_name; 221 222SELECT table_name, column_name, is_updatable 223 FROM information_schema.columns 224 WHERE table_name LIKE 'rw_view%' 225 ORDER BY table_name, ordinal_position; 226 227CREATE RULE rw_view1_upd_rule AS ON UPDATE TO rw_view1 228 DO INSTEAD UPDATE base_tbl SET b=NEW.b WHERE a=OLD.a RETURNING NEW.*; 229 230SELECT table_name, is_insertable_into 231 FROM information_schema.tables 232 WHERE table_name LIKE 'rw_view%' 233 ORDER BY table_name; 234 235SELECT table_name, is_updatable, is_insertable_into 236 FROM information_schema.views 237 WHERE table_name LIKE 'rw_view%' 238 ORDER BY table_name; 239 240SELECT table_name, column_name, is_updatable 241 FROM information_schema.columns 242 WHERE table_name LIKE 'rw_view%' 243 ORDER BY table_name, ordinal_position; 244 245CREATE RULE rw_view1_del_rule AS ON DELETE TO rw_view1 246 DO INSTEAD DELETE FROM base_tbl WHERE a=OLD.a RETURNING OLD.*; 247 248SELECT table_name, is_insertable_into 249 FROM information_schema.tables 250 WHERE table_name LIKE 'rw_view%' 251 ORDER BY table_name; 252 253SELECT table_name, is_updatable, is_insertable_into 254 FROM information_schema.views 255 WHERE table_name LIKE 'rw_view%' 256 ORDER BY table_name; 257 258SELECT table_name, column_name, is_updatable 259 FROM information_schema.columns 260 WHERE table_name LIKE 'rw_view%' 261 ORDER BY table_name, ordinal_position; 262 263INSERT INTO rw_view2 VALUES (3, 'Row 3') RETURNING *; 264UPDATE rw_view2 SET b='Row three' WHERE a=3 RETURNING *; 265SELECT * FROM rw_view2; 266DELETE FROM rw_view2 WHERE a=3 RETURNING *; 267SELECT * FROM rw_view2; 268 269EXPLAIN (costs off) UPDATE rw_view2 SET a=3 WHERE a=2; 270EXPLAIN (costs off) DELETE FROM rw_view2 WHERE a=2; 271 272DROP TABLE base_tbl CASCADE; 273 274-- view on top of view with triggers 275 276CREATE TABLE base_tbl (a int PRIMARY KEY, b text DEFAULT 'Unspecified'); 277INSERT INTO base_tbl SELECT i, 'Row ' || i FROM generate_series(-2, 2) g(i); 278 279CREATE VIEW rw_view1 AS SELECT * FROM base_tbl WHERE a>0 OFFSET 0; -- not updatable without rules/triggers 280CREATE VIEW rw_view2 AS SELECT * FROM rw_view1 WHERE a<10; 281 282SELECT table_name, is_insertable_into 283 FROM information_schema.tables 284 WHERE table_name LIKE 'rw_view%' 285 ORDER BY table_name; 286 287SELECT table_name, is_updatable, is_insertable_into, 288 is_trigger_updatable, is_trigger_deletable, 289 is_trigger_insertable_into 290 FROM information_schema.views 291 WHERE table_name LIKE 'rw_view%' 292 ORDER BY table_name; 293 294SELECT table_name, column_name, is_updatable 295 FROM information_schema.columns 296 WHERE table_name LIKE 'rw_view%' 297 ORDER BY table_name, ordinal_position; 298 299CREATE FUNCTION rw_view1_trig_fn() 300RETURNS trigger AS 301$$ 302BEGIN 303 IF TG_OP = 'INSERT' THEN 304 INSERT INTO base_tbl VALUES (NEW.a, NEW.b); 305 RETURN NEW; 306 ELSIF TG_OP = 'UPDATE' THEN 307 UPDATE base_tbl SET b=NEW.b WHERE a=OLD.a; 308 RETURN NEW; 309 ELSIF TG_OP = 'DELETE' THEN 310 DELETE FROM base_tbl WHERE a=OLD.a; 311 RETURN OLD; 312 END IF; 313END; 314$$ 315LANGUAGE plpgsql; 316 317CREATE TRIGGER rw_view1_ins_trig INSTEAD OF INSERT ON rw_view1 318 FOR EACH ROW EXECUTE PROCEDURE rw_view1_trig_fn(); 319 320SELECT table_name, is_insertable_into 321 FROM information_schema.tables 322 WHERE table_name LIKE 'rw_view%' 323 ORDER BY table_name; 324 325SELECT table_name, is_updatable, is_insertable_into, 326 is_trigger_updatable, is_trigger_deletable, 327 is_trigger_insertable_into 328 FROM information_schema.views 329 WHERE table_name LIKE 'rw_view%' 330 ORDER BY table_name; 331 332SELECT table_name, column_name, is_updatable 333 FROM information_schema.columns 334 WHERE table_name LIKE 'rw_view%' 335 ORDER BY table_name, ordinal_position; 336 337CREATE TRIGGER rw_view1_upd_trig INSTEAD OF UPDATE ON rw_view1 338 FOR EACH ROW EXECUTE PROCEDURE rw_view1_trig_fn(); 339 340SELECT table_name, is_insertable_into 341 FROM information_schema.tables 342 WHERE table_name LIKE 'rw_view%' 343 ORDER BY table_name; 344 345SELECT table_name, is_updatable, is_insertable_into, 346 is_trigger_updatable, is_trigger_deletable, 347 is_trigger_insertable_into 348 FROM information_schema.views 349 WHERE table_name LIKE 'rw_view%' 350 ORDER BY table_name; 351 352SELECT table_name, column_name, is_updatable 353 FROM information_schema.columns 354 WHERE table_name LIKE 'rw_view%' 355 ORDER BY table_name, ordinal_position; 356 357CREATE TRIGGER rw_view1_del_trig INSTEAD OF DELETE ON rw_view1 358 FOR EACH ROW EXECUTE PROCEDURE rw_view1_trig_fn(); 359 360SELECT table_name, is_insertable_into 361 FROM information_schema.tables 362 WHERE table_name LIKE 'rw_view%' 363 ORDER BY table_name; 364 365SELECT table_name, is_updatable, is_insertable_into, 366 is_trigger_updatable, is_trigger_deletable, 367 is_trigger_insertable_into 368 FROM information_schema.views 369 WHERE table_name LIKE 'rw_view%' 370 ORDER BY table_name; 371 372SELECT table_name, column_name, is_updatable 373 FROM information_schema.columns 374 WHERE table_name LIKE 'rw_view%' 375 ORDER BY table_name, ordinal_position; 376 377INSERT INTO rw_view2 VALUES (3, 'Row 3') RETURNING *; 378UPDATE rw_view2 SET b='Row three' WHERE a=3 RETURNING *; 379SELECT * FROM rw_view2; 380DELETE FROM rw_view2 WHERE a=3 RETURNING *; 381SELECT * FROM rw_view2; 382 383EXPLAIN (costs off) UPDATE rw_view2 SET a=3 WHERE a=2; 384EXPLAIN (costs off) DELETE FROM rw_view2 WHERE a=2; 385 386DROP TABLE base_tbl CASCADE; 387DROP FUNCTION rw_view1_trig_fn(); 388 389-- update using whole row from view 390 391CREATE TABLE base_tbl (a int PRIMARY KEY, b text DEFAULT 'Unspecified'); 392INSERT INTO base_tbl SELECT i, 'Row ' || i FROM generate_series(-2, 2) g(i); 393 394CREATE VIEW rw_view1 AS SELECT b AS bb, a AS aa FROM base_tbl; 395 396CREATE FUNCTION rw_view1_aa(x rw_view1) 397 RETURNS int AS $$ SELECT x.aa $$ LANGUAGE sql; 398 399UPDATE rw_view1 v SET bb='Updated row 2' WHERE rw_view1_aa(v)=2 400 RETURNING rw_view1_aa(v), v.bb; 401SELECT * FROM base_tbl; 402 403EXPLAIN (costs off) 404UPDATE rw_view1 v SET bb='Updated row 2' WHERE rw_view1_aa(v)=2 405 RETURNING rw_view1_aa(v), v.bb; 406 407DROP TABLE base_tbl CASCADE; 408 409-- permissions checks 410 411CREATE USER regress_view_user1; 412CREATE USER regress_view_user2; 413 414SET SESSION AUTHORIZATION regress_view_user1; 415CREATE TABLE base_tbl(a int, b text, c float); 416INSERT INTO base_tbl VALUES (1, 'Row 1', 1.0); 417CREATE VIEW rw_view1 AS SELECT b AS bb, c AS cc, a AS aa FROM base_tbl; 418INSERT INTO rw_view1 VALUES ('Row 2', 2.0, 2); 419 420GRANT SELECT ON base_tbl TO regress_view_user2; 421GRANT SELECT ON rw_view1 TO regress_view_user2; 422GRANT UPDATE (a,c) ON base_tbl TO regress_view_user2; 423GRANT UPDATE (bb,cc) ON rw_view1 TO regress_view_user2; 424RESET SESSION AUTHORIZATION; 425 426SET SESSION AUTHORIZATION regress_view_user2; 427CREATE VIEW rw_view2 AS SELECT b AS bb, c AS cc, a AS aa FROM base_tbl; 428SELECT * FROM base_tbl; -- ok 429SELECT * FROM rw_view1; -- ok 430SELECT * FROM rw_view2; -- ok 431 432INSERT INTO base_tbl VALUES (3, 'Row 3', 3.0); -- not allowed 433INSERT INTO rw_view1 VALUES ('Row 3', 3.0, 3); -- not allowed 434INSERT INTO rw_view2 VALUES ('Row 3', 3.0, 3); -- not allowed 435 436UPDATE base_tbl SET a=a, c=c; -- ok 437UPDATE base_tbl SET b=b; -- not allowed 438UPDATE rw_view1 SET bb=bb, cc=cc; -- ok 439UPDATE rw_view1 SET aa=aa; -- not allowed 440UPDATE rw_view2 SET aa=aa, cc=cc; -- ok 441UPDATE rw_view2 SET bb=bb; -- not allowed 442 443DELETE FROM base_tbl; -- not allowed 444DELETE FROM rw_view1; -- not allowed 445DELETE FROM rw_view2; -- not allowed 446RESET SESSION AUTHORIZATION; 447 448SET SESSION AUTHORIZATION regress_view_user1; 449GRANT INSERT, DELETE ON base_tbl TO regress_view_user2; 450RESET SESSION AUTHORIZATION; 451 452SET SESSION AUTHORIZATION regress_view_user2; 453INSERT INTO base_tbl VALUES (3, 'Row 3', 3.0); -- ok 454INSERT INTO rw_view1 VALUES ('Row 4', 4.0, 4); -- not allowed 455INSERT INTO rw_view2 VALUES ('Row 4', 4.0, 4); -- ok 456DELETE FROM base_tbl WHERE a=1; -- ok 457DELETE FROM rw_view1 WHERE aa=2; -- not allowed 458DELETE FROM rw_view2 WHERE aa=2; -- ok 459SELECT * FROM base_tbl; 460RESET SESSION AUTHORIZATION; 461 462SET SESSION AUTHORIZATION regress_view_user1; 463REVOKE INSERT, DELETE ON base_tbl FROM regress_view_user2; 464GRANT INSERT, DELETE ON rw_view1 TO regress_view_user2; 465RESET SESSION AUTHORIZATION; 466 467SET SESSION AUTHORIZATION regress_view_user2; 468INSERT INTO base_tbl VALUES (5, 'Row 5', 5.0); -- not allowed 469INSERT INTO rw_view1 VALUES ('Row 5', 5.0, 5); -- ok 470INSERT INTO rw_view2 VALUES ('Row 6', 6.0, 6); -- not allowed 471DELETE FROM base_tbl WHERE a=3; -- not allowed 472DELETE FROM rw_view1 WHERE aa=3; -- ok 473DELETE FROM rw_view2 WHERE aa=4; -- not allowed 474SELECT * FROM base_tbl; 475RESET SESSION AUTHORIZATION; 476 477DROP TABLE base_tbl CASCADE; 478 479-- nested-view permissions 480 481CREATE TABLE base_tbl(a int, b text, c float); 482INSERT INTO base_tbl VALUES (1, 'Row 1', 1.0); 483 484SET SESSION AUTHORIZATION regress_view_user1; 485CREATE VIEW rw_view1 AS SELECT * FROM base_tbl; 486SELECT * FROM rw_view1; -- not allowed 487SELECT * FROM rw_view1 FOR UPDATE; -- not allowed 488UPDATE rw_view1 SET b = 'foo' WHERE a = 1; -- not allowed 489 490SET SESSION AUTHORIZATION regress_view_user2; 491CREATE VIEW rw_view2 AS SELECT * FROM rw_view1; 492SELECT * FROM rw_view2; -- not allowed 493SELECT * FROM rw_view2 FOR UPDATE; -- not allowed 494UPDATE rw_view2 SET b = 'bar' WHERE a = 1; -- not allowed 495 496RESET SESSION AUTHORIZATION; 497GRANT SELECT ON base_tbl TO regress_view_user1; 498 499SET SESSION AUTHORIZATION regress_view_user1; 500SELECT * FROM rw_view1; 501SELECT * FROM rw_view1 FOR UPDATE; -- not allowed 502UPDATE rw_view1 SET b = 'foo' WHERE a = 1; -- not allowed 503 504SET SESSION AUTHORIZATION regress_view_user2; 505SELECT * FROM rw_view2; -- not allowed 506SELECT * FROM rw_view2 FOR UPDATE; -- not allowed 507UPDATE rw_view2 SET b = 'bar' WHERE a = 1; -- not allowed 508 509SET SESSION AUTHORIZATION regress_view_user1; 510GRANT SELECT ON rw_view1 TO regress_view_user2; 511 512SET SESSION AUTHORIZATION regress_view_user2; 513SELECT * FROM rw_view2; 514SELECT * FROM rw_view2 FOR UPDATE; -- not allowed 515UPDATE rw_view2 SET b = 'bar' WHERE a = 1; -- not allowed 516 517RESET SESSION AUTHORIZATION; 518GRANT UPDATE ON base_tbl TO regress_view_user1; 519 520SET SESSION AUTHORIZATION regress_view_user1; 521SELECT * FROM rw_view1; 522SELECT * FROM rw_view1 FOR UPDATE; 523UPDATE rw_view1 SET b = 'foo' WHERE a = 1; 524 525SET SESSION AUTHORIZATION regress_view_user2; 526SELECT * FROM rw_view2; 527SELECT * FROM rw_view2 FOR UPDATE; -- not allowed 528UPDATE rw_view2 SET b = 'bar' WHERE a = 1; -- not allowed 529 530SET SESSION AUTHORIZATION regress_view_user1; 531GRANT UPDATE ON rw_view1 TO regress_view_user2; 532 533SET SESSION AUTHORIZATION regress_view_user2; 534SELECT * FROM rw_view2; 535SELECT * FROM rw_view2 FOR UPDATE; 536UPDATE rw_view2 SET b = 'bar' WHERE a = 1; 537 538RESET SESSION AUTHORIZATION; 539REVOKE UPDATE ON base_tbl FROM regress_view_user1; 540 541SET SESSION AUTHORIZATION regress_view_user1; 542SELECT * FROM rw_view1; 543SELECT * FROM rw_view1 FOR UPDATE; -- not allowed 544UPDATE rw_view1 SET b = 'foo' WHERE a = 1; -- not allowed 545 546SET SESSION AUTHORIZATION regress_view_user2; 547SELECT * FROM rw_view2; 548SELECT * FROM rw_view2 FOR UPDATE; -- not allowed 549UPDATE rw_view2 SET b = 'bar' WHERE a = 1; -- not allowed 550 551RESET SESSION AUTHORIZATION; 552 553DROP TABLE base_tbl CASCADE; 554 555DROP USER regress_view_user1; 556DROP USER regress_view_user2; 557 558-- column defaults 559 560CREATE TABLE base_tbl (a int PRIMARY KEY, b text DEFAULT 'Unspecified', c serial); 561INSERT INTO base_tbl VALUES (1, 'Row 1'); 562INSERT INTO base_tbl VALUES (2, 'Row 2'); 563INSERT INTO base_tbl VALUES (3); 564 565CREATE VIEW rw_view1 AS SELECT a AS aa, b AS bb FROM base_tbl; 566ALTER VIEW rw_view1 ALTER COLUMN bb SET DEFAULT 'View default'; 567 568INSERT INTO rw_view1 VALUES (4, 'Row 4'); 569INSERT INTO rw_view1 (aa) VALUES (5); 570 571SELECT * FROM base_tbl; 572 573DROP TABLE base_tbl CASCADE; 574 575-- Table having triggers 576 577CREATE TABLE base_tbl (a int PRIMARY KEY, b text DEFAULT 'Unspecified'); 578INSERT INTO base_tbl VALUES (1, 'Row 1'); 579INSERT INTO base_tbl VALUES (2, 'Row 2'); 580 581CREATE FUNCTION rw_view1_trig_fn() 582RETURNS trigger AS 583$$ 584BEGIN 585 IF TG_OP = 'INSERT' THEN 586 UPDATE base_tbl SET b=NEW.b WHERE a=1; 587 RETURN NULL; 588 END IF; 589 RETURN NULL; 590END; 591$$ 592LANGUAGE plpgsql; 593 594CREATE TRIGGER rw_view1_ins_trig AFTER INSERT ON base_tbl 595 FOR EACH ROW EXECUTE PROCEDURE rw_view1_trig_fn(); 596 597CREATE VIEW rw_view1 AS SELECT a AS aa, b AS bb FROM base_tbl; 598 599INSERT INTO rw_view1 VALUES (3, 'Row 3'); 600select * from base_tbl; 601 602DROP VIEW rw_view1; 603DROP TRIGGER rw_view1_ins_trig on base_tbl; 604DROP FUNCTION rw_view1_trig_fn(); 605DROP TABLE base_tbl; 606 607-- view with ORDER BY 608 609CREATE TABLE base_tbl (a int, b int); 610INSERT INTO base_tbl VALUES (1,2), (4,5), (3,-3); 611 612CREATE VIEW rw_view1 AS SELECT * FROM base_tbl ORDER BY a+b; 613 614SELECT * FROM rw_view1; 615 616INSERT INTO rw_view1 VALUES (7,-8); 617SELECT * FROM rw_view1; 618 619EXPLAIN (verbose, costs off) UPDATE rw_view1 SET b = b + 1 RETURNING *; 620UPDATE rw_view1 SET b = b + 1 RETURNING *; 621SELECT * FROM rw_view1; 622 623DROP TABLE base_tbl CASCADE; 624 625-- multiple array-column updates 626 627CREATE TABLE base_tbl (a int, arr int[]); 628INSERT INTO base_tbl VALUES (1,ARRAY[2]), (3,ARRAY[4]); 629 630CREATE VIEW rw_view1 AS SELECT * FROM base_tbl; 631 632UPDATE rw_view1 SET arr[1] = 42, arr[2] = 77 WHERE a = 3; 633 634SELECT * FROM rw_view1; 635 636DROP TABLE base_tbl CASCADE; 637 638-- views with updatable and non-updatable columns 639 640CREATE TABLE base_tbl(a float); 641INSERT INTO base_tbl SELECT i/10.0 FROM generate_series(1,10) g(i); 642 643CREATE VIEW rw_view1 AS 644 SELECT ctid, sin(a) s, a, cos(a) c 645 FROM base_tbl 646 WHERE a != 0 647 ORDER BY abs(a); 648 649INSERT INTO rw_view1 VALUES (null, null, 1.1, null); -- should fail 650INSERT INTO rw_view1 (s, c, a) VALUES (null, null, 1.1); -- should fail 651INSERT INTO rw_view1 (a) VALUES (1.1) RETURNING a, s, c; -- OK 652UPDATE rw_view1 SET s = s WHERE a = 1.1; -- should fail 653UPDATE rw_view1 SET a = 1.05 WHERE a = 1.1 RETURNING s; -- OK 654DELETE FROM rw_view1 WHERE a = 1.05; -- OK 655 656CREATE VIEW rw_view2 AS 657 SELECT s, c, s/c t, a base_a, ctid 658 FROM rw_view1; 659 660INSERT INTO rw_view2 VALUES (null, null, null, 1.1, null); -- should fail 661INSERT INTO rw_view2(s, c, base_a) VALUES (null, null, 1.1); -- should fail 662INSERT INTO rw_view2(base_a) VALUES (1.1) RETURNING t; -- OK 663UPDATE rw_view2 SET s = s WHERE base_a = 1.1; -- should fail 664UPDATE rw_view2 SET t = t WHERE base_a = 1.1; -- should fail 665UPDATE rw_view2 SET base_a = 1.05 WHERE base_a = 1.1; -- OK 666DELETE FROM rw_view2 WHERE base_a = 1.05 RETURNING base_a, s, c, t; -- OK 667 668CREATE VIEW rw_view3 AS 669 SELECT s, c, s/c t, ctid 670 FROM rw_view1; 671 672INSERT INTO rw_view3 VALUES (null, null, null, null); -- should fail 673INSERT INTO rw_view3(s) VALUES (null); -- should fail 674UPDATE rw_view3 SET s = s; -- should fail 675DELETE FROM rw_view3 WHERE s = sin(0.1); -- should be OK 676SELECT * FROM base_tbl ORDER BY a; 677 678SELECT table_name, is_insertable_into 679 FROM information_schema.tables 680 WHERE table_name LIKE E'r_\\_view%' 681 ORDER BY table_name; 682 683SELECT table_name, is_updatable, is_insertable_into 684 FROM information_schema.views 685 WHERE table_name LIKE E'r_\\_view%' 686 ORDER BY table_name; 687 688SELECT table_name, column_name, is_updatable 689 FROM information_schema.columns 690 WHERE table_name LIKE E'r_\\_view%' 691 ORDER BY table_name, ordinal_position; 692 693SELECT events & 4 != 0 AS upd, 694 events & 8 != 0 AS ins, 695 events & 16 != 0 AS del 696 FROM pg_catalog.pg_relation_is_updatable('rw_view3'::regclass, false) t(events); 697 698DROP TABLE base_tbl CASCADE; 699 700-- view on table with GENERATED columns 701 702CREATE TABLE base_tbl (id int, idplus1 int GENERATED ALWAYS AS (id + 1) STORED); 703CREATE VIEW rw_view1 AS SELECT * FROM base_tbl; 704 705INSERT INTO base_tbl (id) VALUES (1); 706INSERT INTO rw_view1 (id) VALUES (2); 707INSERT INTO base_tbl (id, idplus1) VALUES (3, DEFAULT); 708INSERT INTO rw_view1 (id, idplus1) VALUES (4, DEFAULT); 709INSERT INTO base_tbl (id, idplus1) VALUES (5, 6); -- error 710INSERT INTO rw_view1 (id, idplus1) VALUES (6, 7); -- error 711 712SELECT * FROM base_tbl; 713 714UPDATE base_tbl SET id = 2000 WHERE id = 2; 715UPDATE rw_view1 SET id = 3000 WHERE id = 3; 716 717SELECT * FROM base_tbl; 718 719DROP TABLE base_tbl CASCADE; 720 721-- inheritance tests 722 723CREATE TABLE base_tbl_parent (a int); 724CREATE TABLE base_tbl_child (CHECK (a > 0)) INHERITS (base_tbl_parent); 725INSERT INTO base_tbl_parent SELECT * FROM generate_series(-8, -1); 726INSERT INTO base_tbl_child SELECT * FROM generate_series(1, 8); 727 728CREATE VIEW rw_view1 AS SELECT * FROM base_tbl_parent; 729CREATE VIEW rw_view2 AS SELECT * FROM ONLY base_tbl_parent; 730 731SELECT * FROM rw_view1 ORDER BY a; 732SELECT * FROM ONLY rw_view1 ORDER BY a; 733SELECT * FROM rw_view2 ORDER BY a; 734 735INSERT INTO rw_view1 VALUES (-100), (100); 736INSERT INTO rw_view2 VALUES (-200), (200); 737 738UPDATE rw_view1 SET a = a*10 WHERE a IN (-1, 1); -- Should produce -10 and 10 739UPDATE ONLY rw_view1 SET a = a*10 WHERE a IN (-2, 2); -- Should produce -20 and 20 740UPDATE rw_view2 SET a = a*10 WHERE a IN (-3, 3); -- Should produce -30 only 741UPDATE ONLY rw_view2 SET a = a*10 WHERE a IN (-4, 4); -- Should produce -40 only 742 743DELETE FROM rw_view1 WHERE a IN (-5, 5); -- Should delete -5 and 5 744DELETE FROM ONLY rw_view1 WHERE a IN (-6, 6); -- Should delete -6 and 6 745DELETE FROM rw_view2 WHERE a IN (-7, 7); -- Should delete -7 only 746DELETE FROM ONLY rw_view2 WHERE a IN (-8, 8); -- Should delete -8 only 747 748SELECT * FROM ONLY base_tbl_parent ORDER BY a; 749SELECT * FROM base_tbl_child ORDER BY a; 750 751CREATE TABLE other_tbl_parent (id int); 752CREATE TABLE other_tbl_child () INHERITS (other_tbl_parent); 753INSERT INTO other_tbl_parent VALUES (7),(200); 754INSERT INTO other_tbl_child VALUES (8),(100); 755 756EXPLAIN (costs off) 757UPDATE rw_view1 SET a = a + 1000 FROM other_tbl_parent WHERE a = id; 758UPDATE rw_view1 SET a = a + 1000 FROM other_tbl_parent WHERE a = id; 759 760SELECT * FROM ONLY base_tbl_parent ORDER BY a; 761SELECT * FROM base_tbl_child ORDER BY a; 762 763DROP TABLE base_tbl_parent, base_tbl_child CASCADE; 764DROP TABLE other_tbl_parent CASCADE; 765 766-- simple WITH CHECK OPTION 767 768CREATE TABLE base_tbl (a int, b int DEFAULT 10); 769INSERT INTO base_tbl VALUES (1,2), (2,3), (1,-1); 770 771CREATE VIEW rw_view1 AS SELECT * FROM base_tbl WHERE a < b 772 WITH LOCAL CHECK OPTION; 773\d+ rw_view1 774SELECT * FROM information_schema.views WHERE table_name = 'rw_view1'; 775 776INSERT INTO rw_view1 VALUES(3,4); -- ok 777INSERT INTO rw_view1 VALUES(4,3); -- should fail 778INSERT INTO rw_view1 VALUES(5,null); -- should fail 779UPDATE rw_view1 SET b = 5 WHERE a = 3; -- ok 780UPDATE rw_view1 SET b = -5 WHERE a = 3; -- should fail 781INSERT INTO rw_view1(a) VALUES (9); -- ok 782INSERT INTO rw_view1(a) VALUES (10); -- should fail 783SELECT * FROM base_tbl; 784 785DROP TABLE base_tbl CASCADE; 786 787-- WITH LOCAL/CASCADED CHECK OPTION 788 789CREATE TABLE base_tbl (a int); 790 791CREATE VIEW rw_view1 AS SELECT * FROM base_tbl WHERE a > 0; 792CREATE VIEW rw_view2 AS SELECT * FROM rw_view1 WHERE a < 10 793 WITH CHECK OPTION; -- implicitly cascaded 794\d+ rw_view2 795SELECT * FROM information_schema.views WHERE table_name = 'rw_view2'; 796 797INSERT INTO rw_view2 VALUES (-5); -- should fail 798INSERT INTO rw_view2 VALUES (5); -- ok 799INSERT INTO rw_view2 VALUES (15); -- should fail 800SELECT * FROM base_tbl; 801 802UPDATE rw_view2 SET a = a - 10; -- should fail 803UPDATE rw_view2 SET a = a + 10; -- should fail 804 805CREATE OR REPLACE VIEW rw_view2 AS SELECT * FROM rw_view1 WHERE a < 10 806 WITH LOCAL CHECK OPTION; 807\d+ rw_view2 808SELECT * FROM information_schema.views WHERE table_name = 'rw_view2'; 809 810INSERT INTO rw_view2 VALUES (-10); -- ok, but not in view 811INSERT INTO rw_view2 VALUES (20); -- should fail 812SELECT * FROM base_tbl; 813 814ALTER VIEW rw_view1 SET (check_option=here); -- invalid 815ALTER VIEW rw_view1 SET (check_option=local); 816 817INSERT INTO rw_view2 VALUES (-20); -- should fail 818INSERT INTO rw_view2 VALUES (30); -- should fail 819 820ALTER VIEW rw_view2 RESET (check_option); 821\d+ rw_view2 822SELECT * FROM information_schema.views WHERE table_name = 'rw_view2'; 823INSERT INTO rw_view2 VALUES (30); -- ok, but not in view 824SELECT * FROM base_tbl; 825 826DROP TABLE base_tbl CASCADE; 827 828-- WITH CHECK OPTION with no local view qual 829 830CREATE TABLE base_tbl (a int); 831 832CREATE VIEW rw_view1 AS SELECT * FROM base_tbl WITH CHECK OPTION; 833CREATE VIEW rw_view2 AS SELECT * FROM rw_view1 WHERE a > 0; 834CREATE VIEW rw_view3 AS SELECT * FROM rw_view2 WITH CHECK OPTION; 835SELECT * FROM information_schema.views WHERE table_name LIKE E'rw\\_view_' ORDER BY table_name; 836 837INSERT INTO rw_view1 VALUES (-1); -- ok 838INSERT INTO rw_view1 VALUES (1); -- ok 839INSERT INTO rw_view2 VALUES (-2); -- ok, but not in view 840INSERT INTO rw_view2 VALUES (2); -- ok 841INSERT INTO rw_view3 VALUES (-3); -- should fail 842INSERT INTO rw_view3 VALUES (3); -- ok 843 844DROP TABLE base_tbl CASCADE; 845 846-- WITH CHECK OPTION with scalar array ops 847 848CREATE TABLE base_tbl (a int, b int[]); 849CREATE VIEW rw_view1 AS SELECT * FROM base_tbl WHERE a = ANY (b) 850 WITH CHECK OPTION; 851 852INSERT INTO rw_view1 VALUES (1, ARRAY[1,2,3]); -- ok 853INSERT INTO rw_view1 VALUES (10, ARRAY[4,5]); -- should fail 854 855UPDATE rw_view1 SET b[2] = -b[2] WHERE a = 1; -- ok 856UPDATE rw_view1 SET b[1] = -b[1] WHERE a = 1; -- should fail 857 858PREPARE ins(int, int[]) AS INSERT INTO rw_view1 VALUES($1, $2); 859EXECUTE ins(2, ARRAY[1,2,3]); -- ok 860EXECUTE ins(10, ARRAY[4,5]); -- should fail 861DEALLOCATE PREPARE ins; 862 863DROP TABLE base_tbl CASCADE; 864 865-- WITH CHECK OPTION with subquery 866 867CREATE TABLE base_tbl (a int); 868CREATE TABLE ref_tbl (a int PRIMARY KEY); 869INSERT INTO ref_tbl SELECT * FROM generate_series(1,10); 870 871CREATE VIEW rw_view1 AS 872 SELECT * FROM base_tbl b 873 WHERE EXISTS(SELECT 1 FROM ref_tbl r WHERE r.a = b.a) 874 WITH CHECK OPTION; 875 876INSERT INTO rw_view1 VALUES (5); -- ok 877INSERT INTO rw_view1 VALUES (15); -- should fail 878 879UPDATE rw_view1 SET a = a + 5; -- ok 880UPDATE rw_view1 SET a = a + 5; -- should fail 881 882EXPLAIN (costs off) INSERT INTO rw_view1 VALUES (5); 883EXPLAIN (costs off) UPDATE rw_view1 SET a = a + 5; 884 885DROP TABLE base_tbl, ref_tbl CASCADE; 886 887-- WITH CHECK OPTION with BEFORE trigger on base table 888 889CREATE TABLE base_tbl (a int, b int); 890 891CREATE FUNCTION base_tbl_trig_fn() 892RETURNS trigger AS 893$$ 894BEGIN 895 NEW.b := 10; 896 RETURN NEW; 897END; 898$$ 899LANGUAGE plpgsql; 900 901CREATE TRIGGER base_tbl_trig BEFORE INSERT OR UPDATE ON base_tbl 902 FOR EACH ROW EXECUTE PROCEDURE base_tbl_trig_fn(); 903 904CREATE VIEW rw_view1 AS SELECT * FROM base_tbl WHERE a < b WITH CHECK OPTION; 905 906INSERT INTO rw_view1 VALUES (5,0); -- ok 907INSERT INTO rw_view1 VALUES (15, 20); -- should fail 908UPDATE rw_view1 SET a = 20, b = 30; -- should fail 909 910DROP TABLE base_tbl CASCADE; 911DROP FUNCTION base_tbl_trig_fn(); 912 913-- WITH LOCAL CHECK OPTION with INSTEAD OF trigger on base view 914 915CREATE TABLE base_tbl (a int, b int); 916 917CREATE VIEW rw_view1 AS SELECT a FROM base_tbl WHERE a < b; 918 919CREATE FUNCTION rw_view1_trig_fn() 920RETURNS trigger AS 921$$ 922BEGIN 923 IF TG_OP = 'INSERT' THEN 924 INSERT INTO base_tbl VALUES (NEW.a, 10); 925 RETURN NEW; 926 ELSIF TG_OP = 'UPDATE' THEN 927 UPDATE base_tbl SET a=NEW.a WHERE a=OLD.a; 928 RETURN NEW; 929 ELSIF TG_OP = 'DELETE' THEN 930 DELETE FROM base_tbl WHERE a=OLD.a; 931 RETURN OLD; 932 END IF; 933END; 934$$ 935LANGUAGE plpgsql; 936 937CREATE TRIGGER rw_view1_trig 938 INSTEAD OF INSERT OR UPDATE OR DELETE ON rw_view1 939 FOR EACH ROW EXECUTE PROCEDURE rw_view1_trig_fn(); 940 941CREATE VIEW rw_view2 AS 942 SELECT * FROM rw_view1 WHERE a > 0 WITH LOCAL CHECK OPTION; 943 944INSERT INTO rw_view2 VALUES (-5); -- should fail 945INSERT INTO rw_view2 VALUES (5); -- ok 946INSERT INTO rw_view2 VALUES (50); -- ok, but not in view 947UPDATE rw_view2 SET a = a - 10; -- should fail 948SELECT * FROM base_tbl; 949 950-- Check option won't cascade down to base view with INSTEAD OF triggers 951 952ALTER VIEW rw_view2 SET (check_option=cascaded); 953INSERT INTO rw_view2 VALUES (100); -- ok, but not in view (doesn't fail rw_view1's check) 954UPDATE rw_view2 SET a = 200 WHERE a = 5; -- ok, but not in view (doesn't fail rw_view1's check) 955SELECT * FROM base_tbl; 956 957-- Neither local nor cascaded check options work with INSTEAD rules 958 959DROP TRIGGER rw_view1_trig ON rw_view1; 960CREATE RULE rw_view1_ins_rule AS ON INSERT TO rw_view1 961 DO INSTEAD INSERT INTO base_tbl VALUES (NEW.a, 10); 962CREATE RULE rw_view1_upd_rule AS ON UPDATE TO rw_view1 963 DO INSTEAD UPDATE base_tbl SET a=NEW.a WHERE a=OLD.a; 964INSERT INTO rw_view2 VALUES (-10); -- ok, but not in view (doesn't fail rw_view2's check) 965INSERT INTO rw_view2 VALUES (5); -- ok 966INSERT INTO rw_view2 VALUES (20); -- ok, but not in view (doesn't fail rw_view1's check) 967UPDATE rw_view2 SET a = 30 WHERE a = 5; -- ok, but not in view (doesn't fail rw_view1's check) 968INSERT INTO rw_view2 VALUES (5); -- ok 969UPDATE rw_view2 SET a = -5 WHERE a = 5; -- ok, but not in view (doesn't fail rw_view2's check) 970SELECT * FROM base_tbl; 971 972DROP TABLE base_tbl CASCADE; 973DROP FUNCTION rw_view1_trig_fn(); 974 975CREATE TABLE base_tbl (a int); 976CREATE VIEW rw_view1 AS SELECT a,10 AS b FROM base_tbl; 977CREATE RULE rw_view1_ins_rule AS ON INSERT TO rw_view1 978 DO INSTEAD INSERT INTO base_tbl VALUES (NEW.a); 979CREATE VIEW rw_view2 AS 980 SELECT * FROM rw_view1 WHERE a > b WITH LOCAL CHECK OPTION; 981INSERT INTO rw_view2 VALUES (2,3); -- ok, but not in view (doesn't fail rw_view2's check) 982DROP TABLE base_tbl CASCADE; 983 984-- security barrier view 985 986CREATE TABLE base_tbl (person text, visibility text); 987INSERT INTO base_tbl VALUES ('Tom', 'public'), 988 ('Dick', 'private'), 989 ('Harry', 'public'); 990 991CREATE VIEW rw_view1 AS 992 SELECT person FROM base_tbl WHERE visibility = 'public'; 993 994CREATE FUNCTION snoop(anyelement) 995RETURNS boolean AS 996$$ 997BEGIN 998 RAISE NOTICE 'snooped value: %', $1; 999 RETURN true; 1000END; 1001$$ 1002LANGUAGE plpgsql COST 0.000001; 1003 1004CREATE OR REPLACE FUNCTION leakproof(anyelement) 1005RETURNS boolean AS 1006$$ 1007BEGIN 1008 RETURN true; 1009END; 1010$$ 1011LANGUAGE plpgsql STRICT IMMUTABLE LEAKPROOF; 1012 1013SELECT * FROM rw_view1 WHERE snoop(person); 1014UPDATE rw_view1 SET person=person WHERE snoop(person); 1015DELETE FROM rw_view1 WHERE NOT snoop(person); 1016 1017ALTER VIEW rw_view1 SET (security_barrier = true); 1018 1019SELECT table_name, is_insertable_into 1020 FROM information_schema.tables 1021 WHERE table_name = 'rw_view1'; 1022 1023SELECT table_name, is_updatable, is_insertable_into 1024 FROM information_schema.views 1025 WHERE table_name = 'rw_view1'; 1026 1027SELECT table_name, column_name, is_updatable 1028 FROM information_schema.columns 1029 WHERE table_name = 'rw_view1' 1030 ORDER BY ordinal_position; 1031 1032SELECT * FROM rw_view1 WHERE snoop(person); 1033UPDATE rw_view1 SET person=person WHERE snoop(person); 1034DELETE FROM rw_view1 WHERE NOT snoop(person); 1035 1036EXPLAIN (costs off) SELECT * FROM rw_view1 WHERE snoop(person); 1037EXPLAIN (costs off) UPDATE rw_view1 SET person=person WHERE snoop(person); 1038EXPLAIN (costs off) DELETE FROM rw_view1 WHERE NOT snoop(person); 1039 1040-- security barrier view on top of security barrier view 1041 1042CREATE VIEW rw_view2 WITH (security_barrier = true) AS 1043 SELECT * FROM rw_view1 WHERE snoop(person); 1044 1045SELECT table_name, is_insertable_into 1046 FROM information_schema.tables 1047 WHERE table_name = 'rw_view2'; 1048 1049SELECT table_name, is_updatable, is_insertable_into 1050 FROM information_schema.views 1051 WHERE table_name = 'rw_view2'; 1052 1053SELECT table_name, column_name, is_updatable 1054 FROM information_schema.columns 1055 WHERE table_name = 'rw_view2' 1056 ORDER BY ordinal_position; 1057 1058SELECT * FROM rw_view2 WHERE snoop(person); 1059UPDATE rw_view2 SET person=person WHERE snoop(person); 1060DELETE FROM rw_view2 WHERE NOT snoop(person); 1061 1062EXPLAIN (costs off) SELECT * FROM rw_view2 WHERE snoop(person); 1063EXPLAIN (costs off) UPDATE rw_view2 SET person=person WHERE snoop(person); 1064EXPLAIN (costs off) DELETE FROM rw_view2 WHERE NOT snoop(person); 1065 1066DROP TABLE base_tbl CASCADE; 1067 1068-- security barrier view on top of table with rules 1069 1070CREATE TABLE base_tbl(id int PRIMARY KEY, data text, deleted boolean); 1071INSERT INTO base_tbl VALUES (1, 'Row 1', false), (2, 'Row 2', true); 1072 1073CREATE RULE base_tbl_ins_rule AS ON INSERT TO base_tbl 1074 WHERE EXISTS (SELECT 1 FROM base_tbl t WHERE t.id = new.id) 1075 DO INSTEAD 1076 UPDATE base_tbl SET data = new.data, deleted = false WHERE id = new.id; 1077 1078CREATE RULE base_tbl_del_rule AS ON DELETE TO base_tbl 1079 DO INSTEAD 1080 UPDATE base_tbl SET deleted = true WHERE id = old.id; 1081 1082CREATE VIEW rw_view1 WITH (security_barrier=true) AS 1083 SELECT id, data FROM base_tbl WHERE NOT deleted; 1084 1085SELECT * FROM rw_view1; 1086 1087EXPLAIN (costs off) DELETE FROM rw_view1 WHERE id = 1 AND snoop(data); 1088DELETE FROM rw_view1 WHERE id = 1 AND snoop(data); 1089 1090EXPLAIN (costs off) INSERT INTO rw_view1 VALUES (2, 'New row 2'); 1091INSERT INTO rw_view1 VALUES (2, 'New row 2'); 1092 1093SELECT * FROM base_tbl; 1094 1095DROP TABLE base_tbl CASCADE; 1096 1097-- security barrier view based on inheritance set 1098CREATE TABLE t1 (a int, b float, c text); 1099CREATE INDEX t1_a_idx ON t1(a); 1100INSERT INTO t1 1101SELECT i,i,'t1' FROM generate_series(1,10) g(i); 1102ANALYZE t1; 1103 1104CREATE TABLE t11 (d text) INHERITS (t1); 1105CREATE INDEX t11_a_idx ON t11(a); 1106INSERT INTO t11 1107SELECT i,i,'t11','t11d' FROM generate_series(1,10) g(i); 1108ANALYZE t11; 1109 1110CREATE TABLE t12 (e int[]) INHERITS (t1); 1111CREATE INDEX t12_a_idx ON t12(a); 1112INSERT INTO t12 1113SELECT i,i,'t12','{1,2}'::int[] FROM generate_series(1,10) g(i); 1114ANALYZE t12; 1115 1116CREATE TABLE t111 () INHERITS (t11, t12); 1117CREATE INDEX t111_a_idx ON t111(a); 1118INSERT INTO t111 1119SELECT i,i,'t111','t111d','{1,1,1}'::int[] FROM generate_series(1,10) g(i); 1120ANALYZE t111; 1121 1122CREATE VIEW v1 WITH (security_barrier=true) AS 1123SELECT *, (SELECT d FROM t11 WHERE t11.a = t1.a LIMIT 1) AS d 1124FROM t1 1125WHERE a > 5 AND EXISTS(SELECT 1 FROM t12 WHERE t12.a = t1.a); 1126 1127SELECT * FROM v1 WHERE a=3; -- should not see anything 1128SELECT * FROM v1 WHERE a=8; 1129 1130EXPLAIN (VERBOSE, COSTS OFF) 1131UPDATE v1 SET a=100 WHERE snoop(a) AND leakproof(a) AND a < 7 AND a != 6; 1132UPDATE v1 SET a=100 WHERE snoop(a) AND leakproof(a) AND a < 7 AND a != 6; 1133 1134SELECT * FROM v1 WHERE a=100; -- Nothing should have been changed to 100 1135SELECT * FROM t1 WHERE a=100; -- Nothing should have been changed to 100 1136 1137EXPLAIN (VERBOSE, COSTS OFF) 1138UPDATE v1 SET a=a+1 WHERE snoop(a) AND leakproof(a) AND a = 8; 1139UPDATE v1 SET a=a+1 WHERE snoop(a) AND leakproof(a) AND a = 8; 1140 1141SELECT * FROM v1 WHERE b=8; 1142 1143DELETE FROM v1 WHERE snoop(a) AND leakproof(a); -- should not delete everything, just where a>5 1144 1145TABLE t1; -- verify all a<=5 are intact 1146 1147DROP TABLE t1, t11, t12, t111 CASCADE; 1148DROP FUNCTION snoop(anyelement); 1149DROP FUNCTION leakproof(anyelement); 1150 1151CREATE TABLE tx1 (a integer); 1152CREATE TABLE tx2 (b integer); 1153CREATE TABLE tx3 (c integer); 1154CREATE VIEW vx1 AS SELECT a FROM tx1 WHERE EXISTS(SELECT 1 FROM tx2 JOIN tx3 ON b=c); 1155INSERT INTO vx1 values (1); 1156SELECT * FROM tx1; 1157SELECT * FROM vx1; 1158 1159DROP VIEW vx1; 1160DROP TABLE tx1; 1161DROP TABLE tx2; 1162DROP TABLE tx3; 1163 1164CREATE TABLE tx1 (a integer); 1165CREATE TABLE tx2 (b integer); 1166CREATE TABLE tx3 (c integer); 1167CREATE VIEW vx1 AS SELECT a FROM tx1 WHERE EXISTS(SELECT 1 FROM tx2 JOIN tx3 ON b=c); 1168INSERT INTO vx1 VALUES (1); 1169INSERT INTO vx1 VALUES (1); 1170SELECT * FROM tx1; 1171SELECT * FROM vx1; 1172 1173DROP VIEW vx1; 1174DROP TABLE tx1; 1175DROP TABLE tx2; 1176DROP TABLE tx3; 1177 1178CREATE TABLE tx1 (a integer, b integer); 1179CREATE TABLE tx2 (b integer, c integer); 1180CREATE TABLE tx3 (c integer, d integer); 1181ALTER TABLE tx1 DROP COLUMN b; 1182ALTER TABLE tx2 DROP COLUMN c; 1183ALTER TABLE tx3 DROP COLUMN d; 1184CREATE VIEW vx1 AS SELECT a FROM tx1 WHERE EXISTS(SELECT 1 FROM tx2 JOIN tx3 ON b=c); 1185INSERT INTO vx1 VALUES (1); 1186INSERT INTO vx1 VALUES (1); 1187SELECT * FROM tx1; 1188SELECT * FROM vx1; 1189 1190DROP VIEW vx1; 1191DROP TABLE tx1; 1192DROP TABLE tx2; 1193DROP TABLE tx3; 1194 1195-- 1196-- Test handling of vars from correlated subqueries in quals from outer 1197-- security barrier views, per bug #13988 1198-- 1199CREATE TABLE t1 (a int, b text, c int); 1200INSERT INTO t1 VALUES (1, 'one', 10); 1201 1202CREATE TABLE t2 (cc int); 1203INSERT INTO t2 VALUES (10), (20); 1204 1205CREATE VIEW v1 WITH (security_barrier = true) AS 1206 SELECT * FROM t1 WHERE (a > 0) 1207 WITH CHECK OPTION; 1208 1209CREATE VIEW v2 WITH (security_barrier = true) AS 1210 SELECT * FROM v1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.cc = v1.c) 1211 WITH CHECK OPTION; 1212 1213INSERT INTO v2 VALUES (2, 'two', 20); -- ok 1214INSERT INTO v2 VALUES (-2, 'minus two', 20); -- not allowed 1215INSERT INTO v2 VALUES (3, 'three', 30); -- not allowed 1216 1217UPDATE v2 SET b = 'ONE' WHERE a = 1; -- ok 1218UPDATE v2 SET a = -1 WHERE a = 1; -- not allowed 1219UPDATE v2 SET c = 30 WHERE a = 1; -- not allowed 1220 1221DELETE FROM v2 WHERE a = 2; -- ok 1222SELECT * FROM v2; 1223 1224DROP VIEW v2; 1225DROP VIEW v1; 1226DROP TABLE t2; 1227DROP TABLE t1; 1228 1229-- 1230-- Test CREATE OR REPLACE VIEW turning a non-updatable view into an 1231-- auto-updatable view and adding check options in a single step 1232-- 1233CREATE TABLE t1 (a int, b text); 1234CREATE VIEW v1 AS SELECT null::int AS a; 1235CREATE OR REPLACE VIEW v1 AS SELECT * FROM t1 WHERE a > 0 WITH CHECK OPTION; 1236 1237INSERT INTO v1 VALUES (1, 'ok'); -- ok 1238INSERT INTO v1 VALUES (-1, 'invalid'); -- should fail 1239 1240DROP VIEW v1; 1241DROP TABLE t1; 1242 1243-- check that an auto-updatable view on a partitioned table works correctly 1244create table uv_pt (a int, b int, v varchar) partition by range (a, b); 1245create table uv_pt1 (b int not null, v varchar, a int not null) partition by range (b); 1246create table uv_pt11 (like uv_pt1); 1247alter table uv_pt11 drop a; 1248alter table uv_pt11 add a int; 1249alter table uv_pt11 drop a; 1250alter table uv_pt11 add a int not null; 1251alter table uv_pt1 attach partition uv_pt11 for values from (2) to (5); 1252alter table uv_pt attach partition uv_pt1 for values from (1, 2) to (1, 10); 1253 1254create view uv_ptv as select * from uv_pt; 1255select events & 4 != 0 AS upd, 1256 events & 8 != 0 AS ins, 1257 events & 16 != 0 AS del 1258 from pg_catalog.pg_relation_is_updatable('uv_pt'::regclass, false) t(events); 1259select pg_catalog.pg_column_is_updatable('uv_pt'::regclass, 1::smallint, false); 1260select pg_catalog.pg_column_is_updatable('uv_pt'::regclass, 2::smallint, false); 1261select table_name, is_updatable, is_insertable_into 1262 from information_schema.views where table_name = 'uv_ptv'; 1263select table_name, column_name, is_updatable 1264 from information_schema.columns where table_name = 'uv_ptv' order by column_name; 1265insert into uv_ptv values (1, 2); 1266select tableoid::regclass, * from uv_pt; 1267create view uv_ptv_wco as select * from uv_pt where a = 0 with check option; 1268insert into uv_ptv_wco values (1, 2); 1269drop view uv_ptv, uv_ptv_wco; 1270drop table uv_pt, uv_pt1, uv_pt11; 1271 1272-- check that wholerow vars appearing in WITH CHECK OPTION constraint expressions 1273-- work fine with partitioned tables 1274create table wcowrtest (a int) partition by list (a); 1275create table wcowrtest1 partition of wcowrtest for values in (1); 1276create view wcowrtest_v as select * from wcowrtest where wcowrtest = '(2)'::wcowrtest with check option; 1277insert into wcowrtest_v values (1); 1278 1279alter table wcowrtest add b text; 1280create table wcowrtest2 (b text, c int, a int); 1281alter table wcowrtest2 drop c; 1282alter table wcowrtest attach partition wcowrtest2 for values in (2); 1283 1284create table sometable (a int, b text); 1285insert into sometable values (1, 'a'), (2, 'b'); 1286create view wcowrtest_v2 as 1287 select * 1288 from wcowrtest r 1289 where r in (select s from sometable s where r.a = s.a) 1290with check option; 1291 1292-- WITH CHECK qual will be processed with wcowrtest2's 1293-- rowtype after tuple-routing 1294insert into wcowrtest_v2 values (2, 'no such row in sometable'); 1295 1296drop view wcowrtest_v, wcowrtest_v2; 1297drop table wcowrtest, sometable; 1298 1299-- Check INSERT .. ON CONFLICT DO UPDATE works correctly when the view's 1300-- columns are named and ordered differently than the underlying table's. 1301create table uv_iocu_tab (a text unique, b float); 1302insert into uv_iocu_tab values ('xyxyxy', 0); 1303create view uv_iocu_view as 1304 select b, b+1 as c, a, '2.0'::text as two from uv_iocu_tab; 1305 1306insert into uv_iocu_view (a, b) values ('xyxyxy', 1) 1307 on conflict (a) do update set b = uv_iocu_view.b; 1308select * from uv_iocu_tab; 1309insert into uv_iocu_view (a, b) values ('xyxyxy', 1) 1310 on conflict (a) do update set b = excluded.b; 1311select * from uv_iocu_tab; 1312 1313-- OK to access view columns that are not present in underlying base 1314-- relation in the ON CONFLICT portion of the query 1315insert into uv_iocu_view (a, b) values ('xyxyxy', 3) 1316 on conflict (a) do update set b = cast(excluded.two as float); 1317select * from uv_iocu_tab; 1318 1319explain (costs off) 1320insert into uv_iocu_view (a, b) values ('xyxyxy', 3) 1321 on conflict (a) do update set b = excluded.b where excluded.c > 0; 1322 1323insert into uv_iocu_view (a, b) values ('xyxyxy', 3) 1324 on conflict (a) do update set b = excluded.b where excluded.c > 0; 1325select * from uv_iocu_tab; 1326 1327drop view uv_iocu_view; 1328drop table uv_iocu_tab; 1329 1330-- Test whole-row references to the view 1331create table uv_iocu_tab (a int unique, b text); 1332create view uv_iocu_view as 1333 select b as bb, a as aa, uv_iocu_tab::text as cc from uv_iocu_tab; 1334 1335insert into uv_iocu_view (aa,bb) values (1,'x'); 1336explain (costs off) 1337insert into uv_iocu_view (aa,bb) values (1,'y') 1338 on conflict (aa) do update set bb = 'Rejected: '||excluded.* 1339 where excluded.aa > 0 1340 and excluded.bb != '' 1341 and excluded.cc is not null; 1342insert into uv_iocu_view (aa,bb) values (1,'y') 1343 on conflict (aa) do update set bb = 'Rejected: '||excluded.* 1344 where excluded.aa > 0 1345 and excluded.bb != '' 1346 and excluded.cc is not null; 1347select * from uv_iocu_view; 1348 1349-- Test omitting a column of the base relation 1350delete from uv_iocu_view; 1351insert into uv_iocu_view (aa,bb) values (1,'x'); 1352insert into uv_iocu_view (aa) values (1) 1353 on conflict (aa) do update set bb = 'Rejected: '||excluded.*; 1354select * from uv_iocu_view; 1355 1356alter table uv_iocu_tab alter column b set default 'table default'; 1357insert into uv_iocu_view (aa) values (1) 1358 on conflict (aa) do update set bb = 'Rejected: '||excluded.*; 1359select * from uv_iocu_view; 1360 1361alter view uv_iocu_view alter column bb set default 'view default'; 1362insert into uv_iocu_view (aa) values (1) 1363 on conflict (aa) do update set bb = 'Rejected: '||excluded.*; 1364select * from uv_iocu_view; 1365 1366-- Should fail to update non-updatable columns 1367insert into uv_iocu_view (aa) values (1) 1368 on conflict (aa) do update set cc = 'XXX'; 1369 1370drop view uv_iocu_view; 1371drop table uv_iocu_tab; 1372 1373-- ON CONFLICT DO UPDATE permissions checks 1374create user regress_view_user1; 1375create user regress_view_user2; 1376 1377set session authorization regress_view_user1; 1378create table base_tbl(a int unique, b text, c float); 1379insert into base_tbl values (1,'xxx',1.0); 1380create view rw_view1 as select b as bb, c as cc, a as aa from base_tbl; 1381 1382grant select (aa,bb) on rw_view1 to regress_view_user2; 1383grant insert on rw_view1 to regress_view_user2; 1384grant update (bb) on rw_view1 to regress_view_user2; 1385 1386set session authorization regress_view_user2; 1387insert into rw_view1 values ('yyy',2.0,1) 1388 on conflict (aa) do update set bb = excluded.cc; -- Not allowed 1389insert into rw_view1 values ('yyy',2.0,1) 1390 on conflict (aa) do update set bb = rw_view1.cc; -- Not allowed 1391insert into rw_view1 values ('yyy',2.0,1) 1392 on conflict (aa) do update set bb = excluded.bb; -- OK 1393insert into rw_view1 values ('zzz',2.0,1) 1394 on conflict (aa) do update set bb = rw_view1.bb||'xxx'; -- OK 1395insert into rw_view1 values ('zzz',2.0,1) 1396 on conflict (aa) do update set cc = 3.0; -- Not allowed 1397reset session authorization; 1398select * from base_tbl; 1399 1400set session authorization regress_view_user1; 1401grant select (a,b) on base_tbl to regress_view_user2; 1402grant insert (a,b) on base_tbl to regress_view_user2; 1403grant update (a,b) on base_tbl to regress_view_user2; 1404 1405set session authorization regress_view_user2; 1406create view rw_view2 as select b as bb, c as cc, a as aa from base_tbl; 1407insert into rw_view2 (aa,bb) values (1,'xxx') 1408 on conflict (aa) do update set bb = excluded.bb; -- Not allowed 1409create view rw_view3 as select b as bb, a as aa from base_tbl; 1410insert into rw_view3 (aa,bb) values (1,'xxx') 1411 on conflict (aa) do update set bb = excluded.bb; -- OK 1412reset session authorization; 1413select * from base_tbl; 1414 1415set session authorization regress_view_user2; 1416create view rw_view4 as select aa, bb, cc FROM rw_view1; 1417insert into rw_view4 (aa,bb) values (1,'yyy') 1418 on conflict (aa) do update set bb = excluded.bb; -- Not allowed 1419create view rw_view5 as select aa, bb FROM rw_view1; 1420insert into rw_view5 (aa,bb) values (1,'yyy') 1421 on conflict (aa) do update set bb = excluded.bb; -- OK 1422reset session authorization; 1423select * from base_tbl; 1424 1425drop view rw_view5; 1426drop view rw_view4; 1427drop view rw_view3; 1428drop view rw_view2; 1429drop view rw_view1; 1430drop table base_tbl; 1431drop user regress_view_user1; 1432drop user regress_view_user2; 1433 1434-- Test single- and multi-row inserts with table and view defaults. 1435-- Table defaults should be used, unless overridden by view defaults. 1436create table base_tab_def (a int, b text default 'Table default', 1437 c text default 'Table default', d text, e text); 1438create view base_tab_def_view as select * from base_tab_def; 1439alter view base_tab_def_view alter b set default 'View default'; 1440alter view base_tab_def_view alter d set default 'View default'; 1441insert into base_tab_def values (1); 1442insert into base_tab_def values (2), (3); 1443insert into base_tab_def values (4, default, default, default, default); 1444insert into base_tab_def values (5, default, default, default, default), 1445 (6, default, default, default, default); 1446insert into base_tab_def_view values (11); 1447insert into base_tab_def_view values (12), (13); 1448insert into base_tab_def_view values (14, default, default, default, default); 1449insert into base_tab_def_view values (15, default, default, default, default), 1450 (16, default, default, default, default); 1451insert into base_tab_def_view values (17), (default); 1452select * from base_tab_def order by a; 1453 1454-- Adding an INSTEAD OF trigger should cause NULLs to be inserted instead of 1455-- table defaults, where there are no view defaults. 1456create function base_tab_def_view_instrig_func() returns trigger 1457as 1458$$ 1459begin 1460 insert into base_tab_def values (new.a, new.b, new.c, new.d, new.e); 1461 return new; 1462end; 1463$$ 1464language plpgsql; 1465create trigger base_tab_def_view_instrig instead of insert on base_tab_def_view 1466 for each row execute function base_tab_def_view_instrig_func(); 1467truncate base_tab_def; 1468insert into base_tab_def values (1); 1469insert into base_tab_def values (2), (3); 1470insert into base_tab_def values (4, default, default, default, default); 1471insert into base_tab_def values (5, default, default, default, default), 1472 (6, default, default, default, default); 1473insert into base_tab_def_view values (11); 1474insert into base_tab_def_view values (12), (13); 1475insert into base_tab_def_view values (14, default, default, default, default); 1476insert into base_tab_def_view values (15, default, default, default, default), 1477 (16, default, default, default, default); 1478insert into base_tab_def_view values (17), (default); 1479select * from base_tab_def order by a; 1480 1481-- Using an unconditional DO INSTEAD rule should also cause NULLs to be 1482-- inserted where there are no view defaults. 1483drop trigger base_tab_def_view_instrig on base_tab_def_view; 1484drop function base_tab_def_view_instrig_func; 1485create rule base_tab_def_view_ins_rule as on insert to base_tab_def_view 1486 do instead insert into base_tab_def values (new.a, new.b, new.c, new.d, new.e); 1487truncate base_tab_def; 1488insert into base_tab_def values (1); 1489insert into base_tab_def values (2), (3); 1490insert into base_tab_def values (4, default, default, default, default); 1491insert into base_tab_def values (5, default, default, default, default), 1492 (6, default, default, default, default); 1493insert into base_tab_def_view values (11); 1494insert into base_tab_def_view values (12), (13); 1495insert into base_tab_def_view values (14, default, default, default, default); 1496insert into base_tab_def_view values (15, default, default, default, default), 1497 (16, default, default, default, default); 1498insert into base_tab_def_view values (17), (default); 1499select * from base_tab_def order by a; 1500 1501-- A DO ALSO rule should cause each row to be inserted twice. The first 1502-- insert should behave the same as an auto-updatable view (using table 1503-- defaults, unless overridden by view defaults). The second insert should 1504-- behave the same as a rule-updatable view (inserting NULLs where there are 1505-- no view defaults). 1506drop rule base_tab_def_view_ins_rule on base_tab_def_view; 1507create rule base_tab_def_view_ins_rule as on insert to base_tab_def_view 1508 do also insert into base_tab_def values (new.a, new.b, new.c, new.d, new.e); 1509truncate base_tab_def; 1510insert into base_tab_def values (1); 1511insert into base_tab_def values (2), (3); 1512insert into base_tab_def values (4, default, default, default, default); 1513insert into base_tab_def values (5, default, default, default, default), 1514 (6, default, default, default, default); 1515insert into base_tab_def_view values (11); 1516insert into base_tab_def_view values (12), (13); 1517insert into base_tab_def_view values (14, default, default, default, default); 1518insert into base_tab_def_view values (15, default, default, default, default), 1519 (16, default, default, default, default); 1520insert into base_tab_def_view values (17), (default); 1521select * from base_tab_def order by a, c NULLS LAST; 1522 1523drop view base_tab_def_view; 1524drop table base_tab_def; 1525 1526-- Test defaults with array assignments 1527create table base_tab (a serial, b int[], c text, d text default 'Table default'); 1528create view base_tab_view as select c, a, b from base_tab; 1529alter view base_tab_view alter column c set default 'View default'; 1530insert into base_tab_view (b[1], b[2], c, b[5], b[4], a, b[3]) 1531values (1, 2, default, 5, 4, default, 3), (10, 11, 'C value', 14, 13, 100, 12); 1532select * from base_tab order by a; 1533drop view base_tab_view; 1534drop table base_tab; 1535