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