| Quellcodebibliothek Statistik Leitseite products/Sources/formale Sprachen/C/Postgres/src/test/regress/expected/ (Postgres Database Version 18.4©) Datei vom 11.4.2026 mit Größe 153 kB |
|
SSL updatable_views.out Interaktion und
|
|
|
Spracherkennung für: .out vermutete Sprache: Unknown {[0] [0] [0]} [Methode: Schwerpunktbildung, einfache Gewichte, sechs Dimensionen] --
-- UPDATABLE VIEWS -- -- avoid bit-exact output here because operations may not be bit-exact. SET extra_float_digits = 0; -- check that non-updatable views and columns are rejected with useful error -- messages CREATE TABLE base_tbl (a int PRIMARY KEY, b text DEFAULT 'Unspecified'); INSERT INTO base_tbl SELECT i, 'Row ' || i FROM generate_series(-2, 2) g(i); CREATE VIEW ro_view1 AS SELECT DISTINCT a, b FROM base_tbl; -- DISTINCT not supported CREATE VIEW ro_view2 AS SELECT a, b FROM base_tbl GROUP BY a, b; -- GROUP BY not supported CREATE VIEW ro_view3 AS SELECT 1 FROM base_tbl HAVING max(a) > 0; -- HAVING not supported CREATE VIEW ro_view4 AS SELECT count(*) FROM base_tbl; -- Aggregate functions not supported CREATE VIEW ro_view5 AS SELECT a, rank() OVER() FROM base_tbl; -- Window functions not support CREATE VIEW ro_view6 AS SELECT a, b FROM base_tbl UNION SELECT -a, b FROM base_tbl; -- Set ops not CREATE VIEW ro_view7 AS WITH t AS (SELECT a, b FROM base_tbl) SELECT * FROM t; -- WITH not supporte CREATE VIEW ro_view8 AS SELECT a, b FROM base_tbl ORDER BY a OFFSET 1; -- OFFSET not supported CREATE VIEW ro_view9 AS SELECT a, b FROM base_tbl ORDER BY a LIMIT 1; -- LIMIT not supported CREATE VIEW ro_view10 AS SELECT 1 AS a; -- No base relations CREATE VIEW ro_view11 AS SELECT b1.a, b2.b FROM base_tbl b1, base_tbl b2; -- Multiple base rela CREATE VIEW ro_view12 AS SELECT * FROM generate_series(1, 10) AS g(a); -- SRF in rangetable CREATE VIEW ro_view13 AS SELECT a, b FROM (SELECT * FROM base_tbl) AS t; -- Subselect in rangetab CREATE VIEW rw_view14 AS SELECT ctid, a, b FROM base_tbl; -- System columns may be part of an upda CREATE VIEW rw_view15 AS SELECT a, upper(b) FROM base_tbl; -- Expression/function may be part CREATE VIEW rw_view16 AS SELECT a, b, a AS aa FROM base_tbl; -- Repeated column may be part of an up CREATE VIEW ro_view17 AS SELECT * FROM ro_view1; -- Base relation not updatable CREATE VIEW ro_view18 AS SELECT * FROM (VALUES(1)) AS tmp(a); -- VALUES in rangetable CREATE SEQUENCE uv_seq; CREATE VIEW ro_view19 AS SELECT * FROM uv_seq; -- View based on a sequence CREATE VIEW ro_view20 AS SELECT a, b, generate_series(1, a) g FROM base_tbl; -- SRF in targetli SELECT table_name, is_insertable_into FROM information_schema.tables WHERE table_name LIKE E'r_\\_view%' ORDER BY table_name; table_name | is_insertable_into ------------+-------------------- ro_view1 | NO ro_view10 | NO ro_view11 | NO ro_view12 | NO ro_view13 | NO ro_view17 | NO ro_view18 | NO ro_view19 | NO ro_view2 | NO ro_view20 | NO ro_view3 | NO ro_view4 | NO ro_view5 | NO ro_view6 | NO ro_view7 | NO ro_view8 | NO ro_view9 | NO rw_view14 | YES rw_view15 | YES rw_view16 | YES (20 rows) SELECT table_name, is_updatable, is_insertable_into FROM information_schema.views WHERE table_name LIKE E'r_\\_view%' ORDER BY table_name; table_name | is_updatable | is_insertable_into ------------+--------------+-------------------- ro_view1 | NO | NO ro_view10 | NO | NO ro_view11 | NO | NO ro_view12 | NO | NO ro_view13 | NO | NO ro_view17 | NO | NO ro_view18 | NO | NO ro_view19 | NO | NO ro_view2 | NO | NO ro_view20 | NO | NO ro_view3 | NO | NO ro_view4 | NO | NO ro_view5 | NO | NO ro_view6 | NO | NO ro_view7 | NO | NO ro_view8 | NO | NO ro_view9 | NO | NO rw_view14 | YES | YES rw_view15 | YES | YES rw_view16 | YES | YES (20 rows) SELECT table_name, column_name, is_updatable FROM information_schema.columns WHERE table_name LIKE E'r_\\_view%' ORDER BY table_name, ordinal_position; table_name | column_name | is_updatable ------------+-------------+-------------- ro_view1 | a | NO ro_view1 | b | NO ro_view10 | a | NO ro_view11 | a | NO ro_view11 | b | NO ro_view12 | a | NO ro_view13 | a | NO ro_view13 | b | NO ro_view17 | a | NO ro_view17 | b | NO ro_view18 | a | NO ro_view19 | last_value | NO ro_view19 | log_cnt | NO ro_view19 | is_called | NO ro_view2 | a | NO ro_view2 | b | NO ro_view20 | a | NO ro_view20 | b | NO ro_view20 | g | NO ro_view3 | ?column? | NO ro_view4 | count | NO ro_view5 | a | NO ro_view5 | rank | NO ro_view6 | a | NO ro_view6 | b | NO ro_view7 | a | NO ro_view7 | b | NO ro_view8 | a | NO ro_view8 | b | NO ro_view9 | a | NO ro_view9 | b | NO rw_view14 | ctid | NO rw_view14 | a | YES rw_view14 | b | YES rw_view15 | a | YES rw_view15 | upper | NO rw_view16 | a | YES rw_view16 | b | YES rw_view16 | aa | YES (39 rows) -- Read-only views DELETE FROM ro_view1; ERROR: cannot delete from view "ro_view1" DETAIL: Views containing DISTINCT are not automatically updatable. HINT: To enable deleting from the view, provide an INSTEAD OF DELETE trigger or an unconditiona DELETE FROM ro_view2; ERROR: cannot delete from view "ro_view2" DETAIL: Views containing GROUP BY are not automatically updatable. HINT: To enable deleting from the view, provide an INSTEAD OF DELETE trigger or an unconditiona DELETE FROM ro_view3; ERROR: cannot delete from view "ro_view3" DETAIL: Views containing HAVING are not automatically updatable. HINT: To enable deleting from the view, provide an INSTEAD OF DELETE trigger or an unconditiona DELETE FROM ro_view4; ERROR: cannot delete from view "ro_view4" DETAIL: Views that return aggregate functions are not automatically updatable. HINT: To enable deleting from the view, provide an INSTEAD OF DELETE trigger or an unconditiona DELETE FROM ro_view5; ERROR: cannot delete from view "ro_view5" DETAIL: Views that return window functions are not automatically updatable. HINT: To enable deleting from the view, provide an INSTEAD OF DELETE trigger or an unconditiona DELETE FROM ro_view6; ERROR: cannot delete from view "ro_view6" DETAIL: Views containing UNION, INTERSECT, or EXCEPT are not automatically updatable. HINT: To enable deleting from the view, provide an INSTEAD OF DELETE trigger or an unconditiona UPDATE ro_view7 SET a=a+1; ERROR: cannot update view "ro_view7" DETAIL: Views containing WITH are not automatically updatable. HINT: To enable updating the view, provide an INSTEAD OF UPDATE trigger or an unconditional ON U UPDATE ro_view8 SET a=a+1; ERROR: cannot update view "ro_view8" DETAIL: Views containing LIMIT or OFFSET are not automatically updatable. HINT: To enable updating the view, provide an INSTEAD OF UPDATE trigger or an unconditional ON U UPDATE ro_view9 SET a=a+1; ERROR: cannot update view "ro_view9" DETAIL: Views containing LIMIT or OFFSET are not automatically updatable. HINT: To enable updating the view, provide an INSTEAD OF UPDATE trigger or an unconditional ON U UPDATE ro_view10 SET a=a+1; ERROR: cannot update view "ro_view10" DETAIL: Views that do not select from a single table or view are not automatically updatable. HINT: To enable updating the view, provide an INSTEAD OF UPDATE trigger or an unconditional ON U UPDATE ro_view11 SET a=a+1; ERROR: cannot update view "ro_view11" DETAIL: Views that do not select from a single table or view are not automatically updatable. HINT: To enable updating the view, provide an INSTEAD OF UPDATE trigger or an unconditional ON U UPDATE ro_view12 SET a=a+1; ERROR: cannot update view "ro_view12" DETAIL: Views that do not select from a single table or view are not automatically updatable. HINT: To enable updating the view, provide an INSTEAD OF UPDATE trigger or an unconditional ON U INSERT INTO ro_view13 VALUES (3, 'Row 3'); ERROR: cannot insert into view "ro_view13" DETAIL: Views that do not select from a single table or view are not automatically updatable. HINT: To enable inserting into the view, provide an INSTEAD OF INSERT trigger or an uncondition MERGE INTO ro_view13 AS t USING (VALUES (1, 'Row 1')) AS v(a,b) ON t.a = v.a WHEN MATCHED THEN DELETE; ERROR: cannot delete from view "ro_view13" DETAIL: Views that do not select from a single table or view are not automatically updatable. HINT: To enable deleting from the view using MERGE, provide an INSTEAD OF DELETE trigger. MERGE INTO ro_view13 AS t USING (VALUES (2, 'Row 2')) AS v(a,b) ON t.a = v.a WHEN MATCHED THEN UPDATE SET b = v.b; ERROR: cannot update view "ro_view13" DETAIL: Views that do not select from a single table or view are not automatically updatable. HINT: To enable updating the view using MERGE, provide an INSTEAD OF UPDATE trigger. MERGE INTO ro_view13 AS t USING (VALUES (3, 'Row 3')) AS v(a,b) ON t.a = v.a WHEN NOT MATCHED THEN INSERT VALUES (v.a, v.b); ERROR: cannot insert into view "ro_view13" DETAIL: Views that do not select from a single table or view are not automatically updatable. HINT: To enable inserting into the view using MERGE, provide an INSTEAD OF INSERT trigger. MERGE INTO ro_view13 AS t USING (VALUES (2, 'Row 2')) AS v(a,b) ON t.a = v.a WHEN MATCHED THEN DO NOTHING WHEN NOT MATCHED THEN DO NOTHING; -- should be OK to do nothing MERGE INTO ro_view13 AS t USING (VALUES (3, 'Row 3')) AS v(a,b) ON t.a = v.a WHEN MATCHED THEN DO NOTHING WHEN NOT MATCHED THEN DO NOTHING; -- should be OK to do nothing -- Partially updatable view INSERT INTO rw_view14 VALUES (null, 3, 'Row 3'); -- should fail ERROR: cannot insert into column "ctid" of view "rw_view14" DETAIL: View columns that refer to system columns are not updatable. INSERT INTO rw_view14 (a, b) VALUES (3, 'Row 3'); -- should be OK UPDATE rw_view14 SET ctid=null WHERE a=3; -- should fail ERROR: cannot update column "ctid" of view "rw_view14" DETAIL: View columns that refer to system columns are not updatable. UPDATE rw_view14 SET b='ROW 3' WHERE a=3; -- should be OK SELECT * FROM base_tbl; a | b ----+-------- -2 | Row -2 -1 | Row -1 0 | Row 0 1 | Row 1 2 | Row 2 3 | ROW 3 (6 rows) DELETE FROM rw_view14 WHERE a=3; -- should be OK MERGE INTO rw_view14 AS t USING (VALUES (2, 'Merged row 2'), (3, 'Merged row 3')) AS v(a,b) ON t.a = v.a WHEN MATCHED THEN UPDATE SET b = v.b -- should be OK, except... WHEN NOT MATCHED THEN INSERT VALUES (null, v.a, v.b); -- should fail ERROR: cannot merge into column "ctid" of view "rw_view14" DETAIL: View columns that refer to system columns are not updatable. MERGE INTO rw_view14 AS t USING (VALUES (2, 'Merged row 2'), (3, 'Merged row 3')) AS v(a,b) ON t.a = v.a WHEN MATCHED THEN UPDATE SET b = v.b -- should be OK WHEN NOT MATCHED THEN INSERT (a,b) VALUES (v.a, v.b); -- should be OK SELECT * FROM base_tbl ORDER BY a; a | b ----+-------------- -2 | Row -2 -1 | Row -1 0 | Row 0 1 | Row 1 2 | Merged row 2 3 | Merged row 3 (6 rows) MERGE INTO rw_view14 AS t USING (VALUES (2, 'Row 2'), (3, 'Row 3')) AS v(a,b) ON t.a = v.a WHEN MATCHED AND t.a = 2 THEN UPDATE SET b = v.b -- should be OK WHEN MATCHED AND t.a = 3 THEN DELETE; -- should be OK SELECT * FROM base_tbl ORDER BY a; a | b ----+-------- -2 | Row -2 -1 | Row -1 0 | Row 0 1 | Row 1 2 | Row 2 (5 rows) -- Partially updatable view INSERT INTO rw_view15 VALUES (3, 'ROW 3'); -- should fail ERROR: cannot insert into column "upper" of view "rw_view15" DETAIL: View columns that are not columns of their base relation are not updatable. INSERT INTO rw_view15 (a) VALUES (3); -- should be OK INSERT INTO rw_view15 (a) VALUES (3) ON CONFLICT DO NOTHING; -- succeeds SELECT * FROM rw_view15; a | upper ----+------------- -2 | ROW -2 -1 | ROW -1 0 | ROW 0 1 | ROW 1 2 | ROW 2 3 | UNSPECIFIED (6 rows) INSERT INTO rw_view15 (a) VALUES (3) ON CONFLICT (a) DO NOTHING; -- succeeds SELECT * FROM rw_view15; a | upper ----+------------- -2 | ROW -2 -1 | ROW -1 0 | ROW 0 1 | ROW 1 2 | ROW 2 3 | UNSPECIFIED (6 rows) INSERT INTO rw_view15 (a) VALUES (3) ON CONFLICT (a) DO UPDATE set a = excluded.a; -- succeeds SELECT * FROM rw_view15; a | upper ----+------------- -2 | ROW -2 -1 | ROW -1 0 | ROW 0 1 | ROW 1 2 | ROW 2 3 | UNSPECIFIED (6 rows) INSERT INTO rw_view15 (a) VALUES (3) ON CONFLICT (a) DO UPDATE set upper = 'blarg'; -- fails ERROR: cannot insert into column "upper" of view "rw_view15" DETAIL: View columns that are not columns of their base relation are not updatable. SELECT * FROM rw_view15; a | upper ----+------------- -2 | ROW -2 -1 | ROW -1 0 | ROW 0 1 | ROW 1 2 | ROW 2 3 | UNSPECIFIED (6 rows) SELECT * FROM rw_view15; a | upper ----+------------- -2 | ROW -2 -1 | ROW -1 0 | ROW 0 1 | ROW 1 2 | ROW 2 3 | UNSPECIFIED (6 rows) ALTER VIEW rw_view15 ALTER COLUMN upper SET DEFAULT 'NOT SET'; INSERT INTO rw_view15 (a) VALUES (4); -- should fail ERROR: cannot insert into column "upper" of view "rw_view15" DETAIL: View columns that are not columns of their base relation are not updatable. UPDATE rw_view15 SET upper='ROW 3' WHERE a=3; -- should fail ERROR: cannot update column "upper" of view "rw_view15" DETAIL: View columns that are not columns of their base relation are not updatable. UPDATE rw_view15 SET upper=DEFAULT WHERE a=3; -- should fail ERROR: cannot update column "upper" of view "rw_view15" DETAIL: View columns that are not columns of their base relation are not updatable. UPDATE rw_view15 SET a=4 WHERE a=3; -- should be OK SELECT * FROM base_tbl; a | b ----+------------- -2 | Row -2 -1 | Row -1 0 | Row 0 1 | Row 1 2 | Row 2 4 | Unspecified (6 rows) DELETE FROM rw_view15 WHERE a=4; -- should be OK -- Partially updatable view INSERT INTO rw_view16 VALUES (3, 'Row 3', 3); -- should fail ERROR: multiple assignments to same column "a" INSERT INTO rw_view16 (a, b) VALUES (3, 'Row 3'); -- should be OK UPDATE rw_view16 SET a=3, aa=-3 WHERE a=3; -- should fail ERROR: multiple assignments to same column "a" UPDATE rw_view16 SET aa=-3 WHERE a=3; -- should be OK SELECT * FROM base_tbl; a | b ----+-------- -2 | Row -2 -1 | Row -1 0 | Row 0 1 | Row 1 2 | Row 2 -3 | Row 3 (6 rows) DELETE FROM rw_view16 WHERE a=-3; -- should be OK -- Read-only views INSERT INTO ro_view17 VALUES (3, 'ROW 3'); ERROR: cannot insert into view "ro_view1" DETAIL: Views containing DISTINCT are not automatically updatable. HINT: To enable inserting into the view, provide an INSTEAD OF INSERT trigger or an uncondition DELETE FROM ro_view18; ERROR: cannot delete from view "ro_view18" DETAIL: Views that do not select from a single table or view are not automatically updatable. HINT: To enable deleting from the view, provide an INSTEAD OF DELETE trigger or an unconditiona MERGE INTO ro_view18 AS t USING (VALUES (1, 'Row 1')) AS v(a,b) ON t.a = v.a WHEN MATCHED THEN DO NOTHING; -- should be OK to do nothing UPDATE ro_view19 SET last_value=1000; ERROR: cannot update view "ro_view19" DETAIL: Views that do not select from a single table or view are not automatically updatable. HINT: To enable updating the view, provide an INSTEAD OF UPDATE trigger or an unconditional ON U UPDATE ro_view20 SET b=upper(b); ERROR: cannot update view "ro_view20" DETAIL: Views that return set-returning functions are not automatically updatable. HINT: To enable updating the view, provide an INSTEAD OF UPDATE trigger or an unconditional ON U -- A view with a conditional INSTEAD rule but no unconditional INSTEAD rules -- or INSTEAD OF triggers should be non-updatable and generate useful error -- messages with appropriate detail CREATE RULE rw_view16_ins_rule AS ON INSERT TO rw_view16 WHERE NEW.a > 0 DO INSTEAD INSERT INTO base_tbl VALUES (NEW.a, NEW.b); CREATE RULE rw_view16_upd_rule AS ON UPDATE TO rw_view16 WHERE OLD.a > 0 DO INSTEAD UPDATE base_tbl SET b=NEW.b WHERE a=OLD.a; CREATE RULE rw_view16_del_rule AS ON DELETE TO rw_view16 WHERE OLD.a > 0 DO INSTEAD DELETE FROM base_tbl WHERE a=OLD.a; INSERT INTO rw_view16 (a, b) VALUES (3, 'Row 3'); -- should fail ERROR: cannot insert into view "rw_view16" DETAIL: Views with conditional DO INSTEAD rules are not automatically updatable. HINT: To enable inserting into the view, provide an INSTEAD OF INSERT trigger or an uncondition UPDATE rw_view16 SET b='ROW 2' WHERE a=2; -- should fail ERROR: cannot update view "rw_view16" DETAIL: Views with conditional DO INSTEAD rules are not automatically updatable. HINT: To enable updating the view, provide an INSTEAD OF UPDATE trigger or an unconditional ON U DELETE FROM rw_view16 WHERE a=2; -- should fail ERROR: cannot delete from view "rw_view16" DETAIL: Views with conditional DO INSTEAD rules are not automatically updatable. HINT: To enable deleting from the view, provide an INSTEAD OF DELETE trigger or an unconditiona MERGE INTO rw_view16 AS t USING (VALUES (3, 'Row 3')) AS v(a,b) ON t.a = v.a WHEN NOT MATCHED THEN INSERT VALUES (v.a, v.b); -- should fail ERROR: cannot execute MERGE on relation "rw_view16" DETAIL: MERGE is not supported for relations with rules. DROP TABLE base_tbl CASCADE; NOTICE: drop cascades to 16 other objects DETAIL: drop cascades to view ro_view1 drop cascades to view ro_view17 drop cascades to view ro_view2 drop cascades to view ro_view3 drop cascades to view ro_view4 drop cascades to view ro_view5 drop cascades to view ro_view6 drop cascades to view ro_view7 drop cascades to view ro_view8 drop cascades to view ro_view9 drop cascades to view ro_view11 drop cascades to view ro_view13 drop cascades to view rw_view14 drop cascades to view rw_view15 drop cascades to view rw_view16 drop cascades to view ro_view20 DROP VIEW ro_view10, ro_view12, ro_view18; DROP SEQUENCE uv_seq CASCADE; NOTICE: drop cascades to view ro_view19 -- simple updatable view CREATE TABLE base_tbl (a int PRIMARY KEY, b text DEFAULT 'Unspecified'); INSERT INTO base_tbl SELECT i, 'Row ' || i FROM generate_series(-2, 2) g(i); CREATE VIEW rw_view1 AS SELECT *, 'Const' AS c, (SELECT concat('b: ', b)) AS d FROM base_tbl WHERE a>0; SELECT table_name, is_insertable_into FROM information_schema.tables WHERE table_name = 'rw_view1'; table_name | is_insertable_into ------------+-------------------- rw_view1 | YES (1 row) SELECT table_name, is_updatable, is_insertable_into FROM information_schema.views WHERE table_name = 'rw_view1'; table_name | is_updatable | is_insertable_into ------------+--------------+-------------------- rw_view1 | YES | YES (1 row) SELECT table_name, column_name, is_updatable FROM information_schema.columns WHERE table_name = 'rw_view1' ORDER BY ordinal_position; table_name | column_name | is_updatable ------------+-------------+-------------- rw_view1 | a | YES rw_view1 | b | YES rw_view1 | c | NO rw_view1 | d | NO (4 rows) INSERT INTO rw_view1 VALUES (3, 'Row 3'); INSERT INTO rw_view1 (a) VALUES (4); UPDATE rw_view1 SET a=5 WHERE a=4; DELETE FROM rw_view1 WHERE b='Row 2'; SELECT * FROM base_tbl; a | b ----+------------- -2 | Row -2 -1 | Row -1 0 | Row 0 1 | Row 1 3 | Row 3 5 | Unspecified (6 rows) SET jit_above_cost = 0; MERGE INTO rw_view1 t USING (VALUES (0, 'ROW 0'), (1, 'ROW 1'), (2, 'ROW 2'), (3, 'ROW 3')) AS v(a,b) ON t.a = v.a WHEN MATCHED AND t.a <= 1 THEN UPDATE SET b = v.b WHEN MATCHED THEN DELETE WHEN NOT MATCHED AND a > 0 THEN INSERT (a) VALUES (v.a) RETURNING merge_action(), v.*, old, new, old.*, new.*, t.*; merge_action | a | b | old | new | a | b | c | d | a | b | c | d | a | b | c | d --------------+---+-------+------------------------------+---------------------- UPDATE | 1 | ROW 1 | (1,"Row 1",Const,"b: Row 1") | (1,"ROW 1",Const,"b: ROW 1") | 1 | Row 1 | Const | b DELETE | 3 | ROW 3 | (3,"Row 3",Const,"b: Row 3") | | 3 | Row 3 | Const | b: Row 3 | | | | | 3 | Row 3 | Const | b: INSERT | 2 | ROW 2 | | (2,Unspecified,Const,"b: Unspecified") | | | | | 2 | Unspecified | Const | b: U (3 rows) SET jit_above_cost TO DEFAULT; SELECT * FROM base_tbl ORDER BY a; a | b ----+------------- -2 | Row -2 -1 | Row -1 0 | Row 0 1 | ROW 1 2 | Unspecified 5 | Unspecified (6 rows) MERGE INTO rw_view1 t USING (VALUES (0, 'R0'), (1, 'R1'), (2, 'R2'), (3, 'R3')) AS v(a,b) ON t.a = v.a WHEN MATCHED AND t.a <= 1 THEN UPDATE SET b = v.b WHEN MATCHED THEN DELETE WHEN NOT MATCHED BY SOURCE THEN DELETE WHEN NOT MATCHED AND a > 0 THEN INSERT (a) VALUES (v.a) RETURNING merge_action(), v.*, old, new, old.*, new.*, t.*; merge_action | a | b | old | new | a | b | c | d | a | b | c | d | a | b | c | d --------------+---+----+----------------------------------------+--------------- UPDATE | 1 | R1 | (1,"ROW 1",Const,"b: ROW 1") | (1,R1,Const,"b: R1") | 1 | ROW 1 | Const | b: ROW 1 | 1 | DELETE | | | (5,Unspecified,Const,"b: Unspecified") | | 5 | Unspecified | Const | b: Unspecifie DELETE | 2 | R2 | (2,Unspecified,Const,"b: Unspecified") | | 2 | Unspecified | Const | b: Unspeci INSERT | 3 | R3 | | (3,Unspecified,Const,"b: Unspecified") | | | | | 3 | Unspecified | Const | b: Uns (4 rows) SELECT * FROM base_tbl ORDER BY a; a | b ----+------------- -2 | Row -2 -1 | Row -1 0 | Row 0 1 | R1 3 | Unspecified (5 rows) EXPLAIN (costs off) UPDATE rw_view1 SET a=6 WHERE a=5; QUERY PLAN -------------------------------------------------- Update on base_tbl -> Index Scan using base_tbl_pkey on base_tbl Index Cond: ((a > 0) AND (a = 5)) (3 rows) EXPLAIN (costs off) DELETE FROM rw_view1 WHERE a=5; QUERY PLAN -------------------------------------------------- Delete on base_tbl -> Index Scan using base_tbl_pkey on base_tbl Index Cond: ((a > 0) AND (a = 5)) (3 rows) EXPLAIN (costs off) MERGE INTO rw_view1 t USING (VALUES (5, 'X')) AS v(a,b) ON t.a = v.a WHEN MATCHED THEN DELETE; QUERY PLAN -------------------------------------------------- Merge on base_tbl -> Index Scan using base_tbl_pkey on base_tbl Index Cond: ((a > 0) AND (a = 5)) (3 rows) EXPLAIN (costs off) MERGE INTO rw_view1 t USING (SELECT * FROM generate_series(1,5)) AS s(a) ON t.a = s.a WHEN MATCHED THEN UPDATE SET b = 'Updated'; QUERY PLAN ------------------------------------------------------------------- Merge on base_tbl -> Hash Join Hash Cond: (base_tbl.a = generate_series.generate_series) -> Bitmap Heap Scan on base_tbl Recheck Cond: (a > 0) -> Bitmap Index Scan on base_tbl_pkey Index Cond: (a > 0) -> Hash -> Function Scan on generate_series (9 rows) EXPLAIN (costs off) MERGE INTO rw_view1 t USING (SELECT * FROM generate_series(1,5)) AS s(a) ON t.a = s.a WHEN NOT MATCHED BY SOURCE THEN DELETE; QUERY PLAN ------------------------------------------------------------------- Merge on base_tbl -> Hash Left Join Hash Cond: (base_tbl.a = generate_series.generate_series) -> Bitmap Heap Scan on base_tbl Recheck Cond: (a > 0) -> Bitmap Index Scan on base_tbl_pkey Index Cond: (a > 0) -> Hash -> Function Scan on generate_series (9 rows) EXPLAIN (costs off) MERGE INTO rw_view1 t USING (SELECT * FROM generate_series(1,5)) AS s(a) ON t.a = s.a WHEN NOT MATCHED THEN INSERT (a) VALUES (s.a); QUERY PLAN ------------------------------------------------------------------- Merge on base_tbl -> Hash Right Join Hash Cond: (base_tbl.a = generate_series.generate_series) -> Bitmap Heap Scan on base_tbl Recheck Cond: (a > 0) -> Bitmap Index Scan on base_tbl_pkey Index Cond: (a > 0) -> Hash -> Function Scan on generate_series (9 rows) -- it's still updatable if we add a DO ALSO rule CREATE TABLE base_tbl_hist(ts timestamptz default now(), a int, b text); CREATE RULE base_tbl_log AS ON INSERT TO rw_view1 DO ALSO INSERT INTO base_tbl_hist(a,b) VALUES(new.a, new.b); SELECT table_name, is_updatable, is_insertable_into FROM information_schema.views WHERE table_name = 'rw_view1'; table_name | is_updatable | is_insertable_into ------------+--------------+-------------------- rw_view1 | YES | YES (1 row) -- Check behavior with DEFAULTs (bug #17633) INSERT INTO rw_view1 VALUES (9, DEFAULT), (10, DEFAULT); SELECT a, b FROM base_tbl_hist; a | b ----+--- 9 | 10 | (2 rows) DROP TABLE base_tbl CASCADE; NOTICE: drop cascades to view rw_view1 DROP TABLE base_tbl_hist; -- view on top of view CREATE TABLE base_tbl (a int PRIMARY KEY, b text DEFAULT 'Unspecified'); INSERT INTO base_tbl SELECT i, 'Row ' || i FROM generate_series(-2, 2) g(i); CREATE VIEW rw_view1 AS SELECT b AS bb, a AS aa, 'Const1' AS c FROM base_tbl WHERE a>0; CREATE VIEW rw_view2 AS SELECT aa AS aaa, bb AS bbb, c AS c1, 'Const2' AS c2 FROM rw_view1 WHERE aa<10; SELECT table_name, is_insertable_into FROM information_schema.tables WHERE table_name = 'rw_view2'; table_name | is_insertable_into ------------+-------------------- rw_view2 | YES (1 row) SELECT table_name, is_updatable, is_insertable_into FROM information_schema.views WHERE table_name = 'rw_view2'; table_name | is_updatable | is_insertable_into ------------+--------------+-------------------- rw_view2 | YES | YES (1 row) SELECT table_name, column_name, is_updatable FROM information_schema.columns WHERE table_name = 'rw_view2' ORDER BY ordinal_position; table_name | column_name | is_updatable ------------+-------------+-------------- rw_view2 | aaa | YES rw_view2 | bbb | YES rw_view2 | c1 | NO rw_view2 | c2 | NO (4 rows) INSERT INTO rw_view2 VALUES (3, 'Row 3'); INSERT INTO rw_view2 (aaa) VALUES (4); SELECT * FROM rw_view2; aaa | bbb | c1 | c2 -----+-------------+--------+-------- 1 | Row 1 | Const1 | Const2 2 | Row 2 | Const1 | Const2 3 | Row 3 | Const1 | Const2 4 | Unspecified | Const1 | Const2 (4 rows) UPDATE rw_view2 SET bbb='Row 4' WHERE aaa=4; DELETE FROM rw_view2 WHERE aaa=2; SELECT * FROM rw_view2; aaa | bbb | c1 | c2 -----+-------+--------+-------- 1 | Row 1 | Const1 | Const2 3 | Row 3 | Const1 | Const2 4 | Row 4 | Const1 | Const2 (3 rows) MERGE INTO rw_view2 t USING (VALUES (3, 'R3'), (4, 'R4'), (5, 'R5')) AS v(a,b) ON aaa = v.a WHEN MATCHED AND aaa = 3 THEN DELETE WHEN MATCHED THEN UPDATE SET bbb = v.b WHEN NOT MATCHED THEN INSERT (aaa) VALUES (v.a) RETURNING merge_action(), v.*, (SELECT old), (SELECT (SELECT new)), t.*; merge_action | a | b | old | new | aaa | bbb | c1 | c2 --------------+---+----+---------------------------+---------------------------- DELETE | 3 | R3 | (3,"Row 3",Const1,Const2) | | 3 | Row 3 | Const1 | Const2 UPDATE | 4 | R4 | (4,"Row 4",Const1,Const2) | (4,R4,Const1,Const2) | 4 | R4 | Const1 | Const2 INSERT | 5 | R5 | | (5,Unspecified,Const1,Const2) | 5 | Unspecified | Const1 | Const2 (3 rows) SELECT * FROM rw_view2 ORDER BY aaa; aaa | bbb | c1 | c2 -----+-------------+--------+-------- 1 | Row 1 | Const1 | Const2 4 | R4 | Const1 | Const2 5 | Unspecified | Const1 | Const2 (3 rows) MERGE INTO rw_view2 t USING (VALUES (4, 'r4'), (5, 'r5'), (6, 'r6')) AS v(a,b) ON aaa = v.a WHEN MATCHED AND aaa = 4 THEN DELETE WHEN MATCHED THEN UPDATE SET bbb = v.b WHEN NOT MATCHED THEN INSERT (aaa) VALUES (v.a) WHEN NOT MATCHED BY SOURCE THEN UPDATE SET bbb = 'Not matched by source' RETURNING merge_action(), v.*, old, (SELECT new FROM (VALUES ((SELECT new)))), t.*; merge_action | a | b | old | new | aaa | bbb | c1 | c2 --------------+---+----+-------------------------------+------------------------ UPDATE | | | (1,"Row 1",Const1,Const2) | (1,"Not matched by source",Const1,Const2) | 1 | Not ma DELETE | 4 | r4 | (4,R4,Const1,Const2) | | 4 | R4 | Const1 | Const2 UPDATE | 5 | r5 | (5,Unspecified,Const1,Const2) | (5,r5,Const1,Const2) | 5 | r5 | Const1 | Const INSERT | 6 | r6 | | (6,Unspecified,Const1,Const2) | 6 | Unspecified | Const1 | Const2 (4 rows) SELECT * FROM rw_view2 ORDER BY aaa; aaa | bbb | c1 | c2 -----+-----------------------+--------+-------- 1 | Not matched by source | Const1 | Const2 5 | r5 | Const1 | Const2 6 | Unspecified | Const1 | Const2 (3 rows) EXPLAIN (costs off) UPDATE rw_view2 SET aaa=5 WHERE aaa=4; QUERY PLAN -------------------------------------------------------- Update on base_tbl -> Index Scan using base_tbl_pkey on base_tbl Index Cond: ((a < 10) AND (a > 0) AND (a = 4)) (3 rows) EXPLAIN (costs off) DELETE FROM rw_view2 WHERE aaa=4; QUERY PLAN -------------------------------------------------------- Delete on base_tbl -> Index Scan using base_tbl_pkey on base_tbl Index Cond: ((a < 10) AND (a > 0) AND (a = 4)) (3 rows) DROP TABLE base_tbl CASCADE; NOTICE: drop cascades to 2 other objects DETAIL: drop cascades to view rw_view1 drop cascades to view rw_view2 -- view on top of view with rules CREATE TABLE base_tbl (a int PRIMARY KEY, b text DEFAULT 'Unspecified'); INSERT INTO base_tbl SELECT i, 'Row ' || i FROM generate_series(-2, 2) g(i); CREATE VIEW rw_view1 AS SELECT * FROM base_tbl WHERE a>0 OFFSET 0; -- not updatable without rules CREATE VIEW rw_view2 AS SELECT * FROM rw_view1 WHERE a<10; SELECT table_name, is_insertable_into FROM information_schema.tables WHERE table_name LIKE 'rw_view%' ORDER BY table_name; table_name | is_insertable_into ------------+-------------------- rw_view1 | NO rw_view2 | NO (2 rows) SELECT table_name, is_updatable, is_insertable_into FROM information_schema.views WHERE table_name LIKE 'rw_view%' ORDER BY table_name; table_name | is_updatable | is_insertable_into ------------+--------------+-------------------- rw_view1 | NO | NO rw_view2 | NO | NO (2 rows) SELECT table_name, column_name, is_updatable FROM information_schema.columns WHERE table_name LIKE 'rw_view%' ORDER BY table_name, ordinal_position; table_name | column_name | is_updatable ------------+-------------+-------------- rw_view1 | a | NO rw_view1 | b | NO rw_view2 | a | NO rw_view2 | b | NO (4 rows) CREATE RULE rw_view1_ins_rule AS ON INSERT TO rw_view1 DO INSTEAD INSERT INTO base_tbl VALUES (NEW.a, NEW.b) RETURNING *; SELECT table_name, is_insertable_into FROM information_schema.tables WHERE table_name LIKE 'rw_view%' ORDER BY table_name; table_name | is_insertable_into ------------+-------------------- rw_view1 | YES rw_view2 | YES (2 rows) SELECT table_name, is_updatable, is_insertable_into FROM information_schema.views WHERE table_name LIKE 'rw_view%' ORDER BY table_name; table_name | is_updatable | is_insertable_into ------------+--------------+-------------------- rw_view1 | NO | YES rw_view2 | NO | YES (2 rows) SELECT table_name, column_name, is_updatable FROM information_schema.columns WHERE table_name LIKE 'rw_view%' ORDER BY table_name, ordinal_position; table_name | column_name | is_updatable ------------+-------------+-------------- rw_view1 | a | NO rw_view1 | b | NO rw_view2 | a | NO rw_view2 | b | NO (4 rows) CREATE RULE rw_view1_upd_rule AS ON UPDATE TO rw_view1 DO INSTEAD UPDATE base_tbl SET b=NEW.b WHERE a=OLD.a RETURNING NEW.*; SELECT table_name, is_insertable_into FROM information_schema.tables WHERE table_name LIKE 'rw_view%' ORDER BY table_name; table_name | is_insertable_into ------------+-------------------- rw_view1 | YES rw_view2 | YES (2 rows) SELECT table_name, is_updatable, is_insertable_into FROM information_schema.views WHERE table_name LIKE 'rw_view%' ORDER BY table_name; table_name | is_updatable | is_insertable_into ------------+--------------+-------------------- rw_view1 | NO | YES rw_view2 | NO | YES (2 rows) SELECT table_name, column_name, is_updatable FROM information_schema.columns WHERE table_name LIKE 'rw_view%' ORDER BY table_name, ordinal_position; table_name | column_name | is_updatable ------------+-------------+-------------- rw_view1 | a | NO rw_view1 | b | NO rw_view2 | a | NO rw_view2 | b | NO (4 rows) CREATE RULE rw_view1_del_rule AS ON DELETE TO rw_view1 DO INSTEAD DELETE FROM base_tbl WHERE a=OLD.a RETURNING OLD.*; SELECT table_name, is_insertable_into FROM information_schema.tables WHERE table_name LIKE 'rw_view%' ORDER BY table_name; table_name | is_insertable_into ------------+-------------------- rw_view1 | YES rw_view2 | YES (2 rows) SELECT table_name, is_updatable, is_insertable_into FROM information_schema.views WHERE table_name LIKE 'rw_view%' ORDER BY table_name; table_name | is_updatable | is_insertable_into ------------+--------------+-------------------- rw_view1 | YES | YES rw_view2 | YES | YES (2 rows) SELECT table_name, column_name, is_updatable FROM information_schema.columns WHERE table_name LIKE 'rw_view%' ORDER BY table_name, ordinal_position; table_name | column_name | is_updatable ------------+-------------+-------------- rw_view1 | a | YES rw_view1 | b | YES rw_view2 | a | YES rw_view2 | b | YES (4 rows) INSERT INTO rw_view2 VALUES (3, 'Row 3') RETURNING old.*, new.*; a | b | a | b ---+---+---+------- | | 3 | Row 3 (1 row) UPDATE rw_view2 SET b='R3' WHERE a=3 RETURNING old.*, new.*; -- rule returns NEW a | b | a | b ---+----+---+---- 3 | R3 | 3 | R3 (1 row) DROP RULE rw_view1_upd_rule ON rw_view1; CREATE RULE rw_view1_upd_rule AS ON UPDATE TO rw_view1 DO INSTEAD UPDATE base_tbl SET b=NEW.b WHERE a=OLD.a RETURNING *; UPDATE rw_view2 SET b='Row three' WHERE a=3 RETURNING old.*, new.*; a | b | a | b ---+----+---+----------- 3 | R3 | 3 | Row three (1 row) SELECT * FROM rw_view2; a | b ---+----------- 1 | Row 1 2 | Row 2 3 | Row three (3 rows) DELETE FROM rw_view2 WHERE a=3 RETURNING old.*, new.*; a | b | a | b ---+-----------+---+--- 3 | Row three | | (1 row) SELECT * FROM rw_view2; a | b ---+------- 1 | Row 1 2 | Row 2 (2 rows) MERGE INTO rw_view2 t USING (VALUES (3, 'Row 3')) AS v(a,b) ON t.a = v.a WHEN NOT MATCHED THEN INSERT VALUES (v.a, v.b); -- should fail ERROR: cannot execute MERGE on relation "rw_view1" DETAIL: MERGE is not supported for relations with rules. EXPLAIN (costs off) UPDATE rw_view2 SET a=3 WHERE a=2; QUERY PLAN ---------------------------------------------------------------- Update on base_tbl -> Nested Loop -> Index Scan using base_tbl_pkey on base_tbl Index Cond: (a = 2) -> Subquery Scan on rw_view1 Filter: ((rw_view1.a < 10) AND (rw_view1.a = 2)) -> Bitmap Heap Scan on base_tbl base_tbl_1 Recheck Cond: (a > 0) -> Bitmap Index Scan on base_tbl_pkey Index Cond: (a > 0) (10 rows) EXPLAIN (costs off) DELETE FROM rw_view2 WHERE a=2; QUERY PLAN ---------------------------------------------------------------- Delete on base_tbl -> Nested Loop -> Index Scan using base_tbl_pkey on base_tbl Index Cond: (a = 2) -> Subquery Scan on rw_view1 Filter: ((rw_view1.a < 10) AND (rw_view1.a = 2)) -> Bitmap Heap Scan on base_tbl base_tbl_1 Recheck Cond: (a > 0) -> Bitmap Index Scan on base_tbl_pkey Index Cond: (a > 0) (10 rows) DROP TABLE base_tbl CASCADE; NOTICE: drop cascades to 2 other objects DETAIL: drop cascades to view rw_view1 drop cascades to view rw_view2 -- view on top of view with triggers CREATE TABLE base_tbl (a int PRIMARY KEY, b text DEFAULT 'Unspecified'); INSERT INTO base_tbl SELECT i, 'Row ' || i FROM generate_series(-2, 2) g(i); CREATE VIEW rw_view1 AS SELECT *, 'Const1' AS c1 FROM base_tbl WHERE a>0 OFFSET 0; -- not updatable without rules/trigge CREATE VIEW rw_view2 AS SELECT *, 'Const2' AS c2 FROM rw_view1 WHERE a<10; SELECT table_name, is_insertable_into FROM information_schema.tables WHERE table_name LIKE 'rw_view%' ORDER BY table_name; table_name | is_insertable_into ------------+-------------------- rw_view1 | NO rw_view2 | NO (2 rows) SELECT table_name, is_updatable, is_insertable_into, is_trigger_updatable, is_trigger_deletable, is_trigger_insertable_into FROM information_schema.views WHERE table_name LIKE 'rw_view%' ORDER BY table_name; table_name | is_updatable | is_insertable_into | is_trigger_updatable | is_trigger_delet ------------+--------------+--------------------+----------------------+-------- rw_view1 | NO | NO | NO | NO | NO rw_view2 | NO | NO | NO | NO | NO (2 rows) SELECT table_name, column_name, is_updatable FROM information_schema.columns WHERE table_name LIKE 'rw_view%' ORDER BY table_name, ordinal_position; table_name | column_name | is_updatable ------------+-------------+-------------- rw_view1 | a | NO rw_view1 | b | NO rw_view1 | c1 | NO rw_view2 | a | NO rw_view2 | b | NO rw_view2 | c1 | NO rw_view2 | c2 | NO (7 rows) CREATE FUNCTION rw_view1_trig_fn() RETURNS trigger AS $$ BEGIN IF TG_OP = 'INSERT' THEN INSERT INTO base_tbl VALUES (NEW.a, NEW.b); NEW.c1 = 'Trigger Const1'; RETURN NEW; ELSIF TG_OP = 'UPDATE' THEN UPDATE base_tbl SET b=NEW.b WHERE a=OLD.a; NEW.c1 = 'Trigger Const1'; RETURN NEW; ELSIF TG_OP = 'DELETE' THEN DELETE FROM base_tbl WHERE a=OLD.a; RETURN OLD; END IF; END; $$ LANGUAGE plpgsql; CREATE TRIGGER rw_view1_ins_trig INSTEAD OF INSERT ON rw_view1 FOR EACH ROW EXECUTE PROCEDURE rw_view1_trig_fn(); SELECT table_name, is_insertable_into FROM information_schema.tables WHERE table_name LIKE 'rw_view%' ORDER BY table_name; table_name | is_insertable_into ------------+-------------------- rw_view1 | NO rw_view2 | NO (2 rows) SELECT table_name, is_updatable, is_insertable_into, is_trigger_updatable, is_trigger_deletable, is_trigger_insertable_into FROM information_schema.views WHERE table_name LIKE 'rw_view%' ORDER BY table_name; table_name | is_updatable | is_insertable_into | is_trigger_updatable | is_trigger_delet ------------+--------------+--------------------+----------------------+-------- rw_view1 | NO | NO | NO | NO | YES rw_view2 | NO | NO | NO | NO | NO (2 rows) SELECT table_name, column_name, is_updatable FROM information_schema.columns WHERE table_name LIKE 'rw_view%' ORDER BY table_name, ordinal_position; table_name | column_name | is_updatable ------------+-------------+-------------- rw_view1 | a | NO rw_view1 | b | NO rw_view1 | c1 | NO rw_view2 | a | NO rw_view2 | b | NO rw_view2 | c1 | NO rw_view2 | c2 | NO (7 rows) CREATE TRIGGER rw_view1_upd_trig INSTEAD OF UPDATE ON rw_view1 FOR EACH ROW EXECUTE PROCEDURE rw_view1_trig_fn(); SELECT table_name, is_insertable_into FROM information_schema.tables WHERE table_name LIKE 'rw_view%' ORDER BY table_name; table_name | is_insertable_into ------------+-------------------- rw_view1 | NO rw_view2 | NO (2 rows) SELECT table_name, is_updatable, is_insertable_into, is_trigger_updatable, is_trigger_deletable, is_trigger_insertable_into FROM information_schema.views WHERE table_name LIKE 'rw_view%' ORDER BY table_name; table_name | is_updatable | is_insertable_into | is_trigger_updatable | is_trigger_delet ------------+--------------+--------------------+----------------------+-------- rw_view1 | NO | NO | YES | NO | YES rw_view2 | NO | NO | NO | NO | NO (2 rows) SELECT table_name, column_name, is_updatable FROM information_schema.columns WHERE table_name LIKE 'rw_view%' ORDER BY table_name, ordinal_position; table_name | column_name | is_updatable ------------+-------------+-------------- rw_view1 | a | NO rw_view1 | b | NO rw_view1 | c1 | NO rw_view2 | a | NO rw_view2 | b | NO rw_view2 | c1 | NO rw_view2 | c2 | NO (7 rows) CREATE TRIGGER rw_view1_del_trig INSTEAD OF DELETE ON rw_view1 FOR EACH ROW EXECUTE PROCEDURE rw_view1_trig_fn(); SELECT table_name, is_insertable_into FROM information_schema.tables WHERE table_name LIKE 'rw_view%' ORDER BY table_name; table_name | is_insertable_into ------------+-------------------- rw_view1 | NO rw_view2 | NO (2 rows) SELECT table_name, is_updatable, is_insertable_into, is_trigger_updatable, is_trigger_deletable, is_trigger_insertable_into FROM information_schema.views WHERE table_name LIKE 'rw_view%' ORDER BY table_name; table_name | is_updatable | is_insertable_into | is_trigger_updatable | is_trigger_delet ------------+--------------+--------------------+----------------------+-------- rw_view1 | NO | NO | YES | YES | YES rw_view2 | NO | NO | NO | NO | NO (2 rows) SELECT table_name, column_name, is_updatable FROM information_schema.columns WHERE table_name LIKE 'rw_view%' ORDER BY table_name, ordinal_position; table_name | column_name | is_updatable ------------+-------------+-------------- rw_view1 | a | NO rw_view1 | b | NO rw_view1 | c1 | NO rw_view2 | a | NO rw_view2 | b | NO rw_view2 | c1 | NO rw_view2 | c2 | NO (7 rows) INSERT INTO rw_view2 VALUES (3, 'Row 3') RETURNING old.*, new.*; a | b | c1 | c2 | a | b | c1 | c2 ---+---+----+----+---+-------+----------------+-------- | | | | 3 | Row 3 | Trigger Const1 | Const2 (1 row) UPDATE rw_view2 SET b='Row three' WHERE a=3 RETURNING old.*, new.*; a | b | c1 | c2 | a | b | c1 | c2 ---+-------+--------+--------+---+-----------+----------------+-------- 3 | Row 3 | Const1 | Const2 | 3 | Row three | Trigger Const1 | Const2 (1 row) SELECT * FROM rw_view2; a | b | c1 | c2 ---+-----------+--------+-------- 1 | Row 1 | Const1 | Const2 2 | Row 2 | Const1 | Const2 3 | Row three | Const1 | Const2 (3 rows) DELETE FROM rw_view2 WHERE a=3 RETURNING old.*, new.*; a | b | c1 | c2 | a | b | c1 | c2 ---+-----------+--------+--------+---+---+----+---- 3 | Row three | Const1 | Const2 | | | | (1 row) SELECT * FROM rw_view2; a | b | c1 | c2 ---+-------+--------+-------- 1 | Row 1 | Const1 | Const2 2 | Row 2 | Const1 | Const2 (2 rows) MERGE INTO rw_view2 t USING (SELECT x, 'R'||x FROM generate_series(0,3) x) AS s(a,b) ON t.a = s.a WHEN MATCHED AND t.a <= 1 THEN DELETE WHEN MATCHED THEN UPDATE SET b = s.b WHEN NOT MATCHED AND s.a > 0 THEN INSERT VALUES (s.a, s.b) RETURNING merge_action(), s.*, old, new, t.*; merge_action | a | b | old | new | a | b | c1 | c2 --------------+---+----+---------------------------+---------------------------- DELETE | 1 | R1 | (1,"Row 1",Const1,Const2) | | 1 | Row 1 | Const1 | Const2 UPDATE | 2 | R2 | (2,"Row 2",Const1,Const2) | (2,R2,"Trigger Const1",Const2) | 2 | R2 | Trigger C INSERT | 3 | R3 | | (3,R3,"Trigger Const1",Const2) | 3 | R3 | Trigger Const1 | Const2 (3 rows) SELECT * FROM base_tbl ORDER BY a; a | b ----+-------- -2 | Row -2 -1 | Row -1 0 | Row 0 2 | R2 3 | R3 (5 rows) MERGE INTO rw_view2 t USING (SELECT x, 'r'||x FROM generate_series(0,2) x) AS s(a,b) ON t.a = s.a WHEN MATCHED THEN UPDATE SET b = s.b WHEN NOT MATCHED AND s.a > 0 THEN INSERT VALUES (s.a, s.b) WHEN NOT MATCHED BY SOURCE THEN UPDATE SET b = 'Not matched by source' RETURNING merge_action(), s.*, old, new, t.*; merge_action | a | b | old | new | a | b | c1 | c2 --------------+---+----+----------------------+--------------------------------- UPDATE | 2 | r2 | (2,R2,Const1,Const2) | (2,r2,"Trigger Const1",Const2) | 2 | r2 | Trigger Const UPDATE | | | (3,R3,Const1,Const2) | (3,"Not matched by source","Trigger Const1",Const2) | 3 | INSERT | 1 | r1 | | (1,r1,"Trigger Const1",Const2) | 1 | r1 | Trigger Const1 | Const2 (3 rows) SELECT * FROM base_tbl ORDER BY a; a | b ----+----------------------- -2 | Row -2 -1 | Row -1 0 | Row 0 1 | r1 2 | r2 3 | Not matched by source (6 rows) EXPLAIN (costs off) UPDATE rw_view2 SET a=3 WHERE a=2; QUERY PLAN ---------------------------------------------------------- Update on rw_view1 rw_view1_1 -> Subquery Scan on rw_view1 Filter: ((rw_view1.a < 10) AND (rw_view1.a = 2)) -> Bitmap Heap Scan on base_tbl Recheck Cond: (a > 0) -> Bitmap Index Scan on base_tbl_pkey Index Cond: (a > 0) (7 rows) EXPLAIN (costs off) DELETE FROM rw_view2 WHERE a=2; QUERY PLAN ---------------------------------------------------------- Delete on rw_view1 rw_view1_1 -> Subquery Scan on rw_view1 Filter: ((rw_view1.a < 10) AND (rw_view1.a = 2)) -> Bitmap Heap Scan on base_tbl Recheck Cond: (a > 0) -> Bitmap Index Scan on base_tbl_pkey Index Cond: (a > 0) (7 rows) EXPLAIN (costs off) MERGE INTO rw_view2 t USING (SELECT x, 'R'||x FROM generate_series(0,3) x) AS s(a,b) ON t.a = s.a WHEN MATCHED AND t.a <= 1 THEN DELETE WHEN MATCHED THEN UPDATE SET b = s.b WHEN NOT MATCHED AND s.a > 0 THEN INSERT VALUES (s.a, s.b); QUERY PLAN ------------------------------------------------------------ Merge on rw_view1 rw_view1_1 -> Hash Right Join Hash Cond: (rw_view1.a = x.x) -> Subquery Scan on rw_view1 Filter: (rw_view1.a < 10) -> Bitmap Heap Scan on base_tbl Recheck Cond: (a > 0) -> Bitmap Index Scan on base_tbl_pkey Index Cond: (a > 0) -> Hash -> Function Scan on generate_series x (11 rows) -- MERGE with incomplete set of INSTEAD OF triggers DROP TRIGGER rw_view1_del_trig ON rw_view1; MERGE INTO rw_view2 t USING (SELECT x, 'R'||x FROM generate_series(0,3) x) AS s(a,b) ON t.a = s.a WHEN MATCHED AND t.a <= 1 THEN DELETE WHEN MATCHED THEN UPDATE SET b = s.b WHEN NOT MATCHED AND s.a > 0 THEN INSERT VALUES (s.a, s.b); -- should fail ERROR: cannot delete from view "rw_view1" DETAIL: Views containing LIMIT or OFFSET are not automatically updatable. HINT: To enable deleting from the view using MERGE, provide an INSTEAD OF DELETE trigger. MERGE INTO rw_view2 t USING (SELECT x, 'R'||x FROM generate_series(0,3) x) AS s(a,b) ON t.a = s.a WHEN MATCHED THEN UPDATE SET b = s.b WHEN NOT MATCHED AND s.a > 0 THEN INSERT VALUES (s.a, s.b); -- ok DROP TRIGGER rw_view1_ins_trig ON rw_view1; MERGE INTO rw_view2 t USING (SELECT x, 'R'||x FROM generate_series(0,3) x) AS s(a,b) ON t.a = s.a WHEN MATCHED THEN UPDATE SET b = s.b WHEN NOT MATCHED AND s.a > 0 THEN INSERT VALUES (s.a, s.b); -- should fail ERROR: cannot insert into view "rw_view1" DETAIL: Views containing LIMIT or OFFSET are not automatically updatable. HINT: To enable inserting into the view using MERGE, provide an INSTEAD OF INSERT trigger. MERGE INTO rw_view2 t USING (SELECT x, 'R'||x FROM generate_series(0,3) x) AS s(a,b) ON t.a = s.a WHEN MATCHED THEN UPDATE SET b = s.b; -- ok -- MERGE with INSTEAD OF triggers on auto-updatable view CREATE TRIGGER rw_view2_upd_trig INSTEAD OF UPDATE ON rw_view2 FOR EACH ROW EXECUTE PROCEDURE rw_view1_trig_fn(); MERGE INTO rw_view2 t USING (SELECT x, 'R'||x FROM generate_series(0,3) x) AS s(a,b) ON t.a = s.a WHEN MATCHED THEN UPDATE SET b = s.b WHEN NOT MATCHED AND s.a > 0 THEN INSERT VALUES (s.a, s.b); -- should fail ERROR: cannot merge into view "rw_view2" DETAIL: MERGE is not supported for views with INSTEAD OF triggers for some actions but not all. HINT: To enable merging into the view, either provide a full set of INSTEAD OF triggers or drop th MERGE INTO rw_view2 t USING (SELECT x, 'R'||x FROM generate_series(0,3) x) AS s(a,b) ON t.a = s.a WHEN MATCHED THEN UPDATE SET b = s.b; -- ok SELECT * FROM base_tbl ORDER BY a; a | b ----+-------- -2 | Row -2 -1 | Row -1 0 | Row 0 1 | R1 2 | R2 3 | R3 (6 rows) DROP TABLE base_tbl CASCADE; NOTICE: drop cascades to 2 other objects DETAIL: drop cascades to view rw_view1 drop cascades to view rw_view2 DROP FUNCTION rw_view1_trig_fn(); -- update using whole row from view CREATE TABLE base_tbl (a int PRIMARY KEY, b text DEFAULT 'Unspecified'); INSERT INTO base_tbl SELECT i, 'Row ' || i FROM generate_series(-2, 2) g(i); CREATE VIEW rw_view1 AS SELECT b AS bb, a AS aa FROM base_tbl; CREATE FUNCTION rw_view1_aa(x rw_view1) RETURNS int AS $$ SELECT x.aa $$ LANGUAGE sql; UPDATE rw_view1 v SET bb='Updated row 2' WHERE rw_view1_aa(v)=2 RETURNING rw_view1_aa(v), v.bb; rw_view1_aa | bb -------------+--------------- 2 | Updated row 2 (1 row) SELECT * FROM base_tbl; a | b ----+--------------- -2 | Row -2 -1 | Row -1 0 | Row 0 1 | Row 1 2 | Updated row 2 (5 rows) EXPLAIN (costs off) UPDATE rw_view1 v SET bb='Updated row 2' WHERE rw_view1_aa(v)=2 RETURNING rw_view1_aa(v), v.bb; QUERY PLAN -------------------------------------------------- Update on base_tbl -> Index Scan using base_tbl_pkey on base_tbl Index Cond: (a = 2) (3 rows) DROP TABLE base_tbl CASCADE; NOTICE: drop cascades to 2 other objects DETAIL: drop cascades to view rw_view1 drop cascades to function rw_view1_aa(rw_view1) -- permissions checks CREATE USER regress_view_user1; CREATE USER regress_view_user2; CREATE USER regress_view_user3; SET SESSION AUTHORIZATION regress_view_user1; CREATE TABLE base_tbl(a int, b text, c float); INSERT INTO base_tbl VALUES (1, 'Row 1', 1.0); CREATE VIEW rw_view1 AS SELECT b AS bb, c AS cc, a AS aa FROM base_tbl; INSERT INTO rw_view1 VALUES ('Row 2', 2.0, 2); GRANT SELECT ON base_tbl TO regress_view_user2; GRANT SELECT ON rw_view1 TO regress_view_user2; GRANT UPDATE (a,c) ON base_tbl TO regress_view_user2; GRANT UPDATE (bb,cc) ON rw_view1 TO regress_view_user2; RESET SESSION AUTHORIZATION; SET SESSION AUTHORIZATION regress_view_user2; CREATE VIEW rw_view2 AS SELECT b AS bb, c AS cc, a AS aa FROM base_tbl; SELECT * FROM base_tbl; -- ok a | b | c ---+-------+--- 1 | Row 1 | 1 2 | Row 2 | 2 (2 rows) SELECT * FROM rw_view1; -- ok bb | cc | aa -------+----+---- Row 1 | 1 | 1 Row 2 | 2 | 2 (2 rows) SELECT * FROM rw_view2; -- ok bb | cc | aa -------+----+---- Row 1 | 1 | 1 Row 2 | 2 | 2 (2 rows) INSERT INTO base_tbl VALUES (3, 'Row 3', 3.0); -- not allowed ERROR: permission denied for table base_tbl INSERT INTO rw_view1 VALUES ('Row 3', 3.0, 3); -- not allowed ERROR: permission denied for view rw_view1 INSERT INTO rw_view2 VALUES ('Row 3', 3.0, 3); -- not allowed ERROR: permission denied for table base_tbl MERGE INTO rw_view1 t USING (VALUES ('Row 3', 3.0, 3)) AS v(b,c,a) ON t.aa = v.a WHEN NOT MATCHED THEN INSERT VALUES (v.b, v.c, v.a); -- not allowed ERROR: permission denied for view rw_view1 MERGE INTO rw_view2 t USING (VALUES ('Row 3', 3.0, 3)) AS v(b,c,a) ON t.aa = v.a WHEN NOT MATCHED THEN INSERT VALUES (v.b, v.c, v.a); -- not allowed ERROR: permission denied for table base_tbl UPDATE base_tbl SET a=a, c=c; -- ok UPDATE base_tbl SET b=b; -- not allowed ERROR: permission denied for table base_tbl UPDATE rw_view1 SET bb=bb, cc=cc; -- ok UPDATE rw_view1 SET aa=aa; -- not allowed ERROR: permission denied for view rw_view1 UPDATE rw_view2 SET aa=aa, cc=cc; -- ok UPDATE rw_view2 SET bb=bb; -- not allowed ERROR: permission denied for table base_tbl MERGE INTO rw_view1 t USING (VALUES (1)) AS v(a) ON t.aa = v.a WHEN MATCHED THEN UPDATE SET bb = bb, cc = cc; -- ok MERGE INTO rw_view1 t USING (VALUES (1)) AS v(a) ON t.aa = v.a WHEN MATCHED THEN UPDATE SET aa = aa; -- not allowed ERROR: permission denied for view rw_view1 MERGE INTO rw_view2 t USING (VALUES (1)) AS v(a) ON t.aa = v.a WHEN MATCHED THEN UPDATE SET aa = aa, cc = cc; -- ok MERGE INTO rw_view2 t USING (VALUES (1)) AS v(a) ON t.aa = v.a WHEN MATCHED THEN UPDATE SET bb = bb; -- not allowed ERROR: permission denied for table base_tbl DELETE FROM base_tbl; -- not allowed ERROR: permission denied for table base_tbl DELETE FROM rw_view1; -- not allowed ERROR: permission denied for view rw_view1 DELETE FROM rw_view2; -- not allowed ERROR: permission denied for table base_tbl RESET SESSION AUTHORIZATION; SET SESSION AUTHORIZATION regress_view_user1; GRANT INSERT, DELETE ON base_tbl TO regress_view_user2; RESET SESSION AUTHORIZATION; SET SESSION AUTHORIZATION regress_view_user2; INSERT INTO base_tbl VALUES (3, 'Row 3', 3.0); -- ok INSERT INTO rw_view1 VALUES ('Row 4', 4.0, 4); -- not allowed ERROR: permission denied for view rw_view1 INSERT INTO rw_view2 VALUES ('Row 4', 4.0, 4); -- ok DELETE FROM base_tbl WHERE a=1; -- ok DELETE FROM rw_view1 WHERE aa=2; -- not allowed ERROR: permission denied for view rw_view1 DELETE FROM rw_view2 WHERE aa=2; -- ok MERGE INTO rw_view1 t USING (VALUES (1)) AS v(a) ON t.aa = v.a WHEN MATCHED AND bb = 'xxx' THEN DELETE; -- not allowed ERROR: permission denied for view rw_view1 MERGE INTO rw_view2 t USING (VALUES (1)) AS v(a) ON t.aa = v.a WHEN MATCHED AND bb = 'xxx' THEN DELETE; -- ok SELECT * FROM base_tbl; a | b | c ---+-------+--- 3 | Row 3 | 3 4 | Row 4 | 4 (2 rows) RESET SESSION AUTHORIZATION; SET SESSION AUTHORIZATION regress_view_user1; REVOKE INSERT, DELETE ON base_tbl FROM regress_view_user2; GRANT INSERT, DELETE ON rw_view1 TO regress_view_user2; RESET SESSION AUTHORIZATION; SET SESSION AUTHORIZATION regress_view_user2; INSERT INTO base_tbl VALUES (5, 'Row 5', 5.0); -- not allowed ERROR: permission denied for table base_tbl INSERT INTO rw_view1 VALUES ('Row 5', 5.0, 5); -- ok INSERT INTO rw_view2 VALUES ('Row 6', 6.0, 6); -- not allowed ERROR: permission denied for table base_tbl DELETE FROM base_tbl WHERE a=3; -- not allowed ERROR: permission denied for table base_tbl DELETE FROM rw_view1 WHERE aa=3; -- ok DELETE FROM rw_view2 WHERE aa=4; -- not allowed ERROR: permission denied for table base_tbl MERGE INTO rw_view1 t USING (VALUES (1)) AS v(a) ON t.aa = v.a WHEN MATCHED AND bb = 'xxx' THEN DELETE; -- ok MERGE INTO rw_view2 t USING (VALUES (1)) AS v(a) ON t.aa = v.a WHEN MATCHED AND bb = 'xxx' THEN DELETE; -- not allowed ERROR: permission denied for table base_tbl SELECT * FROM base_tbl; a | b | c ---+-------+--- 4 | Row 4 | 4 5 | Row 5 | 5 (2 rows) RESET SESSION AUTHORIZATION; DROP TABLE base_tbl CASCADE; NOTICE: drop cascades to 2 other objects DETAIL: drop cascades to view rw_view1 drop cascades to view rw_view2 -- nested-view permissions CREATE TABLE base_tbl(a int, b text, c float); INSERT INTO base_tbl VALUES (1, 'Row 1', 1.0); SET SESSION AUTHORIZATION regress_view_user1; CREATE VIEW rw_view1 AS SELECT * FROM base_tbl; SELECT * FROM rw_view1; -- not allowed ERROR: permission denied for table base_tbl SELECT * FROM rw_view1 FOR UPDATE; -- not allowed ERROR: permission denied for table base_tbl UPDATE rw_view1 SET b = 'foo' WHERE a = 1; -- not allowed ERROR: permission denied for table base_tbl MERGE INTO rw_view1 t USING (VALUES (1)) AS v(a) ON t.a = v.a WHEN MATCHED THEN UPDATE SET b = 'foo'; -- not allowed ERROR: permission denied for table base_tbl SET SESSION AUTHORIZATION regress_view_user2; CREATE VIEW rw_view2 AS SELECT * FROM rw_view1; SELECT * FROM rw_view2; -- not allowed ERROR: permission denied for view rw_view1 SELECT * FROM rw_view2 FOR UPDATE; -- not allowed ERROR: permission denied for view rw_view1 UPDATE rw_view2 SET b = 'bar' WHERE a = 1; -- not allowed ERROR: permission denied for view rw_view1 MERGE INTO rw_view2 t USING (VALUES (1)) AS v(a) ON t.a = v.a WHEN MATCHED THEN UPDATE SET b = 'foo'; -- not allowed ERROR: permission denied for view rw_view1 RESET SESSION AUTHORIZATION; GRANT SELECT ON base_tbl TO regress_view_user1; SET SESSION AUTHORIZATION regress_view_user1; SELECT * FROM rw_view1; a | b | c ---+-------+--- 1 | Row 1 | 1 (1 row) SELECT * FROM rw_view1 FOR UPDATE; -- not allowed ERROR: permission denied for table base_tbl UPDATE rw_view1 SET b = 'foo' WHERE a = 1; -- not allowed ERROR: permission denied for table base_tbl MERGE INTO rw_view1 t USING (VALUES (1)) AS v(a) ON t.a = v.a WHEN MATCHED THEN UPDATE SET b = 'foo'; -- not allowed ERROR: permission denied for table base_tbl SET SESSION AUTHORIZATION regress_view_user2; SELECT * FROM rw_view2; -- not allowed ERROR: permission denied for view rw_view1 SELECT * FROM rw_view2 FOR UPDATE; -- not allowed ERROR: permission denied for view rw_view1 UPDATE rw_view2 SET b = 'bar' WHERE a = 1; -- not allowed ERROR: permission denied for view rw_view1 MERGE INTO rw_view2 t USING (VALUES (1)) AS v(a) ON t.a = v.a WHEN MATCHED THEN UPDATE SET b = 'foo'; -- not allowed ERROR: permission denied for view rw_view1 SET SESSION AUTHORIZATION regress_view_user1; GRANT SELECT ON rw_view1 TO regress_view_user2; SET SESSION AUTHORIZATION regress_view_user2; SELECT * FROM rw_view2; a | b | c ---+-------+--- 1 | Row 1 | 1 (1 row) SELECT * FROM rw_view2 FOR UPDATE; -- not allowed ERROR: permission denied for view rw_view1 UPDATE rw_view2 SET b = 'bar' WHERE a = 1; -- not allowed ERROR: permission denied for view rw_view1 MERGE INTO rw_view2 t USING (VALUES (1)) AS v(a) ON t.a = v.a WHEN MATCHED THEN UPDATE SET b = 'foo'; -- not allowed ERROR: permission denied for view rw_view1 RESET SESSION AUTHORIZATION; GRANT UPDATE ON base_tbl TO regress_view_user1; SET SESSION AUTHORIZATION regress_view_user1; SELECT * FROM rw_view1; a | b | c ---+-------+--- 1 | Row 1 | 1 (1 row) SELECT * FROM rw_view1 FOR UPDATE; a | b | c ---+-------+--- 1 | Row 1 | 1 (1 row) UPDATE rw_view1 SET b = 'foo' WHERE a = 1; MERGE INTO rw_view1 t USING (VALUES (1)) AS v(a) ON t.a = v.a WHEN MATCHED THEN UPDATE SET b = 'foo'; SET SESSION AUTHORIZATION regress_view_user2; SELECT * FROM rw_view2; a | b | c ---+-----+--- 1 | foo | 1 (1 row) SELECT * FROM rw_view2 FOR UPDATE; -- not allowed ERROR: permission denied for view rw_view1 UPDATE rw_view2 SET b = 'bar' WHERE a = 1; -- not allowed ERROR: permission denied for view rw_view1 MERGE INTO rw_view2 t USING (VALUES (1)) AS v(a) ON t.a = v.a WHEN MATCHED THEN UPDATE SET b = 'bar'; -- not allowed ERROR: permission denied for view rw_view1 SET SESSION AUTHORIZATION regress_view_user1; GRANT UPDATE ON rw_view1 TO regress_view_user2; SET SESSION AUTHORIZATION regress_view_user2; SELECT * FROM rw_view2; a | b | c ---+-----+--- 1 | foo | 1 (1 row) SELECT * FROM rw_view2 FOR UPDATE; a | b | c ---+-----+--- 1 | foo | 1 (1 row) UPDATE rw_view2 SET b = 'bar' WHERE a = 1; MERGE INTO rw_view2 t USING (VALUES (1)) AS v(a) ON t.a = v.a WHEN MATCHED THEN UPDATE SET b = 'fud'; RESET SESSION AUTHORIZATION; REVOKE UPDATE ON base_tbl FROM regress_view_user1; SET SESSION AUTHORIZATION regress_view_user1; SELECT * FROM rw_view1; a | b | c ---+-----+--- 1 | fud | 1 (1 row) SELECT * FROM rw_view1 FOR UPDATE; -- not allowed ERROR: permission denied for table base_tbl UPDATE rw_view1 SET b = 'foo' WHERE a = 1; -- not allowed ERROR: permission denied for table base_tbl MERGE INTO rw_view1 t USING (VALUES (1)) AS v(a) ON t.a = v.a WHEN MATCHED THEN UPDATE SET b = 'foo'; -- not allowed ERROR: permission denied for table base_tbl SET SESSION AUTHORIZATION regress_view_user2; SELECT * FROM rw_view2; a | b | c ---+-----+--- 1 | fud | 1 (1 row) SELECT * FROM rw_view2 FOR UPDATE; -- not allowed ERROR: permission denied for table base_tbl UPDATE rw_view2 SET b = 'bar' WHERE a = 1; -- not allowed ERROR: permission denied for table base_tbl MERGE INTO rw_view2 t USING (VALUES (1)) AS v(a) ON t.a = v.a WHEN MATCHED THEN UPDATE SET b = 'foo'; -- not allowed ERROR: permission denied for table base_tbl RESET SESSION AUTHORIZATION; DROP TABLE base_tbl CASCADE; NOTICE: drop cascades to 2 other objects DETAIL: drop cascades to view rw_view1 drop cascades to view rw_view2 -- security invoker view permissions SET SESSION AUTHORIZATION regress_view_user1; CREATE TABLE base_tbl(a int, b text, c float); INSERT INTO base_tbl VALUES (1, 'Row 1', 1.0); CREATE VIEW rw_view1 AS SELECT b AS bb, c AS cc, a AS aa FROM base_tbl; ALTER VIEW rw_view1 SET (security_invoker = true); INSERT INTO rw_view1 VALUES ('Row 2', 2.0, 2); GRANT SELECT ON rw_view1 TO regress_view_user2; GRANT UPDATE (bb,cc) ON rw_view1 TO regress_view_user2; SET SESSION AUTHORIZATION regress_view_user2; SELECT * FROM base_tbl; -- not allowed ERROR: permission denied for table base_tbl SELECT * FROM rw_view1; -- not allowed ERROR: permission denied for table base_tbl INSERT INTO base_tbl VALUES (3, 'Row 3', 3.0); -- not allowed ERROR: permission denied for table base_tbl INSERT INTO rw_view1 VALUES ('Row 3', 3.0, 3); -- not allowed ERROR: permission denied for view rw_view1 UPDATE base_tbl SET a=a; -- not allowed ERROR: permission denied for table base_tbl UPDATE rw_view1 SET bb=bb, cc=cc; -- not allowed ERROR: permission denied for table base_tbl MERGE INTO rw_view1 t USING (VALUES (1)) AS v(a) ON t.aa = v.a WHEN MATCHED THEN UPDATE SET bb = bb; -- not allowed ERROR: permission denied for table base_tbl DELETE FROM base_tbl; -- not allowed ERROR: permission denied for table base_tbl DELETE FROM rw_view1; -- not allowed ERROR: permission denied for view rw_view1 MERGE INTO rw_view1 t USING (VALUES (1)) AS v(a) ON t.aa = v.a WHEN MATCHED THEN DELETE; -- not allowed ERROR: permission denied for view rw_view1 SET SESSION AUTHORIZATION regress_view_user1; GRANT SELECT ON base_tbl TO regress_view_user2; GRANT UPDATE (a,c) ON base_tbl TO regress_view_user2; SET SESSION AUTHORIZATION regress_view_user2; SELECT * FROM base_tbl; -- ok a | b | c ---+-------+--- 1 | Row 1 | 1 2 | Row 2 | 2 (2 rows) SELECT * FROM rw_view1; -- ok bb | cc | aa -------+----+---- Row 1 | 1 | 1 Row 2 | 2 | 2 (2 rows) UPDATE base_tbl SET a=a, c=c; -- ok UPDATE base_tbl SET b=b; -- not allowed ERROR: permission denied for table base_tbl UPDATE rw_view1 SET cc=cc; -- ok MERGE INTO rw_view1 t USING (VALUES (1)) AS v(a) ON t.aa = v.a WHEN MATCHED THEN UPDATE SET cc = cc; -- ok UPDATE rw_view1 SET aa=aa; -- not allowed ERROR: permission denied for view rw_view1 UPDATE rw_view1 SET bb=bb; -- not allowed ERROR: permission denied for table base_tbl MERGE INTO rw_view1 t USING (VALUES (1)) AS v(a) ON t.aa = v.a WHEN MATCHED THEN UPDATE SET aa = aa; -- not allowed ERROR: permission denied for view rw_view1 MERGE INTO rw_view1 t USING (VALUES (1)) AS v(a) ON t.aa = v.a WHEN MATCHED THEN UPDATE SET bb = bb; -- not allowed ERROR: permission denied for table base_tbl SET SESSION AUTHORIZATION regress_view_user1; GRANT INSERT, DELETE ON base_tbl TO regress_view_user2; SET SESSION AUTHORIZATION regress_view_user2; INSERT INTO base_tbl VALUES (3, 'Row 3', 3.0); -- ok INSERT INTO rw_view1 VALUES ('Row 4', 4.0, 4); -- not allowed ERROR: permission denied for view rw_view1 DELETE FROM base_tbl WHERE a=1; -- ok DELETE FROM rw_view1 WHERE aa=2; -- not allowed ERROR: permission denied for view rw_view1 MERGE INTO rw_view1 t USING (VALUES (1)) AS v(a) ON t.aa = v.a WHEN MATCHED THEN DELETE; -- not allowed ERROR: permission denied for view rw_view1 SET SESSION AUTHORIZATION regress_view_user1; REVOKE INSERT, DELETE ON base_tbl FROM regress_view_user2; GRANT INSERT, DELETE ON rw_view1 TO regress_view_user2; SET SESSION AUTHORIZATION regress_view_user2; INSERT INTO rw_view1 VALUES ('Row 4', 4.0, 4); -- not allowed ERROR: permission denied for table base_tbl DELETE FROM rw_view1 WHERE aa=2; -- not allowed ERROR: permission denied for table base_tbl MERGE INTO rw_view1 t USING (VALUES (1)) AS v(a) ON t.aa = v.a WHEN MATCHED THEN DELETE; -- not allowed ERROR: permission denied for table base_tbl SET SESSION AUTHORIZATION regress_view_user1; GRANT INSERT, DELETE ON base_tbl TO regress_view_user2; SET SESSION AUTHORIZATION regress_view_user2; INSERT INTO rw_view1 VALUES ('Row 4', 4.0, 4); -- ok DELETE FROM rw_view1 WHERE aa=2; -- ok MERGE INTO rw_view1 t USING (VALUES (3)) AS v(a) ON t.aa = v.a WHEN MATCHED THEN DELETE; -- ok SELECT * FROM base_tbl; -- ok a | b | c ---+-------+--- 4 | Row 4 | 4 (1 row) RESET SESSION AUTHORIZATION; DROP TABLE base_tbl CASCADE; NOTICE: drop cascades to view rw_view1 -- ordinary view on top of security invoker view permissions CREATE TABLE base_tbl(a int, b text, c float); INSERT INTO base_tbl VALUES (1, 'Row 1', 1.0); SET SESSION AUTHORIZATION regress_view_user1; CREATE VIEW rw_view1 AS SELECT b AS bb, c AS cc, a AS aa FROM base_tbl; ALTER VIEW rw_view1 SET (security_invoker = true); SELECT * FROM rw_view1; -- not allowed ERROR: permission denied for table base_tbl UPDATE rw_view1 SET aa=aa; -- not allowed ERROR: permission denied for table base_tbl MERGE INTO rw_view1 t USING (VALUES (2, 'Row 2', 2.0)) AS v(a,b,c) ON t.aa = v.a WHEN NOT MATCHED THEN INSERT VALUES (v.b, v.c, v.a); -- not allowed ERROR: permission denied for table base_tbl SET SESSION AUTHORIZATION regress_view_user2; CREATE VIEW rw_view2 AS SELECT cc AS ccc, aa AS aaa, bb AS bbb FROM rw_view1; GRANT SELECT, UPDATE ON rw_view2 TO regress_view_user3; SELECT * FROM rw_view2; -- not allowed ERROR: permission denied for view rw_view1 UPDATE rw_view2 SET aaa=aaa; -- not allowed ERROR: permission denied for view rw_view1 MERGE INTO rw_view2 t USING (VALUES (2, 'Row 2', 2.0)) AS v(a,b,c) ON t.aaa = v.a WHEN NOT MATCHED THEN INSERT VALUES (v.c, v.a, v.b); -- not allowed ERROR: permission denied for view rw_view1 RESET SESSION AUTHORIZATION; GRANT SELECT ON base_tbl TO regress_view_user1; GRANT UPDATE (a, b) ON base_tbl TO regress_view_user1; SET SESSION AUTHORIZATION regress_view_user1; SELECT * FROM rw_view1; -- ok bb | cc | aa -------+----+---- Row 1 | 1 | 1 (1 row) UPDATE rw_view1 SET aa=aa, bb=bb; -- ok UPDATE rw_view1 SET cc=cc; -- not allowed ERROR: permission denied for table base_tbl MERGE INTO rw_view1 t USING (VALUES (1)) AS v(a) ON t.aa = v.a WHEN MATCHED THEN UPDATE SET aa = aa, bb = bb; -- ok MERGE INTO rw_view1 t USING (VALUES (1)) AS v(a) ON t.aa = v.a WHEN MATCHED THEN UPDATE SET cc = cc; -- not allowed ERROR: permission denied for table base_tbl SET SESSION AUTHORIZATION regress_view_user2; SELECT * FROM rw_view2; -- not allowed ERROR: permission denied for view rw_view1 UPDATE rw_view2 SET aaa=aaa; -- not allowed ERROR: permission denied for view rw_view1 MERGE INTO rw_view2 t USING (VALUES (1)) AS v(a) ON t.aaa = v.a WHEN MATCHED THEN UPDATE SET aaa = aaa; -- not allowed ERROR: permission denied for view rw_view1 SET SESSION AUTHORIZATION regress_view_user3; SELECT * FROM rw_view2; -- not allowed ERROR: permission denied for view rw_view1 UPDATE rw_view2 SET aaa=aaa; -- not allowed ERROR: permission denied for view rw_view1 MERGE INTO rw_view2 t USING (VALUES (1)) AS v(a) ON t.aaa = v.a WHEN MATCHED THEN UPDATE SET aaa = aaa; -- not allowed ERROR: permission denied for view rw_view1 SET SESSION AUTHORIZATION regress_view_user1; GRANT SELECT ON rw_view1 TO regress_view_user2; GRANT UPDATE (bb, cc) ON rw_view1 TO regress_view_user2; SET SESSION AUTHORIZATION regress_view_user2; SELECT * FROM rw_view2; -- not allowed ERROR: permission denied for table base_tbl UPDATE rw_view2 SET bbb=bbb; -- not allowed ERROR: permission denied for table base_tbl MERGE INTO rw_view2 t USING (VALUES (1)) AS v(a) ON t.aaa = v.a WHEN MATCHED THEN UPDATE SET bbb = bbb; -- not allowed ERROR: permission denied for table base_tbl SET SESSION AUTHORIZATION regress_view_user3; SELECT * FROM rw_view2; -- not allowed ERROR: permission denied for table base_tbl UPDATE rw_view2 SET bbb=bbb; -- not allowed ERROR: permission denied for table base_tbl MERGE INTO rw_view2 t USING (VALUES (1)) AS v(a) ON t.aaa = v.a WHEN MATCHED THEN UPDATE SET bbb = bbb; -- not allowed ERROR: permission denied for table base_tbl RESET SESSION AUTHORIZATION; GRANT SELECT ON base_tbl TO regress_view_user2; GRANT UPDATE (a, c) ON base_tbl TO regress_view_user2; SET SESSION AUTHORIZATION regress_view_user2; SELECT * FROM rw_view2; -- ok ccc | aaa | bbb -----+-----+------- 1 | 1 | Row 1 (1 row) UPDATE rw_view2 SET aaa=aaa; -- not allowed ERROR: permission denied for view rw_view1 UPDATE rw_view2 SET bbb=bbb; -- not allowed ERROR: permission denied for table base_tbl UPDATE rw_view2 SET ccc=ccc; -- ok MERGE INTO rw_view2 t USING (VALUES (1)) AS v(a) ON t.aaa = v.a WHEN MATCHED THEN UPDATE SET aaa = aaa; -- not allowed ERROR: permission denied for view rw_view1 MERGE INTO rw_view2 t USING (VALUES (1)) AS v(a) ON t.aaa = v.a WHEN MATCHED THEN UPDATE SET bbb = bbb; -- not allowed ERROR: permission denied for table base_tbl MERGE INTO rw_view2 t USING (VALUES (1)) AS v(a) ON t.aaa = v.a WHEN MATCHED THEN UPDATE SET ccc = ccc; -- ok SET SESSION AUTHORIZATION regress_view_user3; SELECT * FROM rw_view2; -- not allowed ERROR: permission denied for table base_tbl UPDATE rw_view2 SET aaa=aaa; -- not allowed ERROR: permission denied for view rw_view1 UPDATE rw_view2 SET bbb=bbb; -- not allowed ERROR: permission denied for table base_tbl UPDATE rw_view2 SET ccc=ccc; -- not allowed ERROR: permission denied for table base_tbl MERGE INTO rw_view2 t USING (VALUES (1)) AS v(a) ON t.aaa = v.a WHEN MATCHED THEN UPDATE SET aaa = aaa; -- not allowed ERROR: permission denied for view rw_view1 MERGE INTO rw_view2 t USING (VALUES (1)) AS v(a) ON t.aaa = v.a WHEN MATCHED THEN UPDATE SET bbb = bbb; -- not allowed ERROR: permission denied for table base_tbl MERGE INTO rw_view2 t USING (VALUES (1)) AS v(a) ON t.aaa = v.a WHEN MATCHED THEN UPDATE SET ccc = ccc; -- not allowed ERROR: permission denied for table base_tbl RESET SESSION AUTHORIZATION; GRANT SELECT ON base_tbl TO regress_view_user3; GRANT UPDATE (a, c) ON base_tbl TO regress_view_user3; SET SESSION AUTHORIZATION regress_view_user3; SELECT * FROM rw_view2; -- ok ccc | aaa | bbb -----+-----+------- 1 | 1 | Row 1 (1 row) UPDATE rw_view2 SET aaa=aaa; -- not allowed ERROR: permission denied for view rw_view1 UPDATE rw_view2 SET bbb=bbb; -- not allowed ERROR: permission denied for table base_tbl UPDATE rw_view2 SET ccc=ccc; -- ok MERGE INTO rw_view2 t USING (VALUES (1)) AS v(a) ON t.aaa = v.a WHEN MATCHED THEN UPDATE SET aaa = aaa; -- not allowed ERROR: permission denied for view rw_view1 MERGE INTO rw_view2 t USING (VALUES (1)) AS v(a) ON t.aaa = v.a WHEN MATCHED THEN UPDATE SET bbb = bbb; -- not allowed ERROR: permission denied for table base_tbl MERGE INTO rw_view2 t USING (VALUES (1)) AS v(a) ON t.aaa = v.a WHEN MATCHED THEN UPDATE SET ccc = ccc; -- ok RESET SESSION AUTHORIZATION; REVOKE SELECT, UPDATE ON base_tbl FROM regress_view_user1; SET SESSION AUTHORIZATION regress_view_user1; SELECT * FROM rw_view1; -- not allowed ERROR: permission denied for table base_tbl UPDATE rw_view1 SET aa=aa; -- not allowed ERROR: permission denied for table base_tbl MERGE INTO rw_view1 t USING (VALUES (1)) AS v(a) ON t.aa = v.a WHEN MATCHED THEN UPDATE SET aa = aa; -- not allowed ERROR: permission denied for table base_tbl SET SESSION AUTHORIZATION regress_view_user2; SELECT * FROM rw_view2; -- ok ccc | aaa | bbb -----+-----+------- 1 | 1 | Row 1 (1 row) UPDATE rw_view2 SET aaa=aaa; -- not allowed ERROR: permission denied for view rw_view1 UPDATE rw_view2 SET bbb=bbb; -- not allowed ERROR: permission denied for table base_tbl UPDATE rw_view2 SET ccc=ccc; -- ok MERGE INTO rw_view2 t USING (VALUES (1)) AS v(a) ON t.aaa = v.a WHEN MATCHED THEN UPDATE SET aaa = aaa; -- not allowed ERROR: permission denied for view rw_view1 MERGE INTO rw_view2 t USING (VALUES (1)) AS v(a) ON t.aaa = v.a WHEN MATCHED THEN UPDATE SET bbb = bbb; -- not allowed ERROR: permission denied for table base_tbl MERGE INTO rw_view2 t USING (VALUES (1)) AS v(a) ON t.aaa = v.a WHEN MATCHED THEN UPDATE SET ccc = ccc; -- ok SET SESSION AUTHORIZATION regress_view_user3; SELECT * FROM rw_view2; -- ok ccc | aaa | bbb -----+-----+------- 1 | 1 | Row 1 (1 row) UPDATE rw_view2 SET aaa=aaa; -- not allowed ERROR: permission denied for view rw_view1 UPDATE rw_view2 SET bbb=bbb; -- not allowed ERROR: permission denied for table base_tbl UPDATE rw_view2 SET ccc=ccc; -- ok MERGE INTO rw_view2 t USING (VALUES (1)) AS v(a) ON t.aaa = v.a WHEN MATCHED THEN UPDATE SET aaa = aaa; -- not allowed ERROR: permission denied for view rw_view1 MERGE INTO rw_view2 t USING (VALUES (1)) AS v(a) ON t.aaa = v.a WHEN MATCHED THEN UPDATE SET bbb = bbb; -- not allowed ERROR: permission denied for table base_tbl MERGE INTO rw_view2 t USING (VALUES (1)) AS v(a) ON t.aaa = v.a WHEN MATCHED THEN UPDATE SET ccc = ccc; -- ok RESET SESSION AUTHORIZATION; REVOKE SELECT, UPDATE ON base_tbl FROM regress_view_user2; SET SESSION AUTHORIZATION regress_view_user2; SELECT * FROM rw_view2; -- not allowed ERROR: permission denied for table base_tbl UPDATE rw_view2 SET aaa=aaa; -- not allowed ERROR: permission denied for view rw_view1 UPDATE rw_view2 SET bbb=bbb; -- not allowed ERROR: permission denied for table base_tbl UPDATE rw_view2 SET ccc=ccc; -- not allowed ERROR: permission denied for table base_tbl MERGE INTO rw_view2 t USING (VALUES (1)) AS v(a) ON t.aaa = v.a WHEN MATCHED THEN UPDATE SET aaa = aaa; -- not allowed ERROR: permission denied for view rw_view1 MERGE INTO rw_view2 t USING (VALUES (1)) AS v(a) ON t.aaa = v.a WHEN MATCHED THEN UPDATE SET bbb = bbb; -- not allowed ERROR: permission denied for table base_tbl MERGE INTO rw_view2 t USING (VALUES (1)) AS v(a) ON t.aaa = v.a WHEN MATCHED THEN UPDATE SET ccc = ccc; -- not allowed ERROR: permission denied for table base_tbl SET SESSION AUTHORIZATION regress_view_user3; SELECT * FROM rw_view2; -- ok ccc | aaa | bbb -----+-----+------- 1 | 1 | Row 1 (1 row) UPDATE rw_view2 SET aaa=aaa; -- not allowed ERROR: permission denied for view rw_view1 UPDATE rw_view2 SET bbb=bbb; -- not allowed ERROR: permission denied for table base_tbl UPDATE rw_view2 SET ccc=ccc; -- ok MERGE INTO rw_view2 t USING (VALUES (1)) AS v(a) ON t.aaa = v.a WHEN MATCHED THEN UPDATE SET aaa = aaa; -- not allowed ERROR: permission denied for view rw_view1 MERGE INTO rw_view2 t USING (VALUES (1)) AS v(a) ON t.aaa = v.a WHEN MATCHED THEN UPDATE SET bbb = bbb; -- not allowed ERROR: permission denied for table base_tbl MERGE INTO rw_view2 t USING (VALUES (1)) AS v(a) ON t.aaa = v.a WHEN MATCHED THEN UPDATE SET ccc = ccc; -- ok RESET SESSION AUTHORIZATION; DROP TABLE base_tbl CASCADE; NOTICE: drop cascades to 2 other objects DETAIL: drop cascades to view rw_view1 drop cascades to view rw_view2 DROP USER regress_view_user1; DROP USER regress_view_user2; DROP USER regress_view_user3; -- column defaults CREATE TABLE base_tbl (a int PRIMARY KEY, b text DEFAULT 'Unspecified', c serial); INSERT INTO base_tbl VALUES (1, 'Row 1'); INSERT INTO base_tbl VALUES (2, 'Row 2'); INSERT INTO base_tbl VALUES (3); CREATE VIEW rw_view1 AS SELECT a AS aa, b AS bb FROM base_tbl; ALTER VIEW rw_view1 ALTER COLUMN bb SET DEFAULT 'View default'; INSERT INTO rw_view1 VALUES (4, 'Row 4'); INSERT INTO rw_view1 (aa) VALUES (5); MERGE INTO rw_view1 t USING (VALUES (6)) AS v(a) ON t.aa = v.a WHEN NOT MATCHED THEN INSERT (aa) VALUES (v.a); SELECT * FROM base_tbl; a | b | c ---+--------------+--- 1 | Row 1 | 1 2 | Row 2 | 2 3 | Unspecified | 3 4 | Row 4 | 4 5 | View default | 5 6 | View default | 6 (6 rows) DROP TABLE base_tbl CASCADE; NOTICE: drop cascades to view rw_view1 -- Table having triggers CREATE TABLE base_tbl (a int PRIMARY KEY, b text DEFAULT 'Unspecified'); INSERT INTO base_tbl VALUES (1, 'Row 1'); INSERT INTO base_tbl VALUES (2, 'Row 2'); CREATE FUNCTION rw_view1_trig_fn() RETURNS trigger AS $$ BEGIN IF TG_OP = 'INSERT' THEN UPDATE base_tbl SET b=NEW.b WHERE a=1; RETURN NULL; END IF; RETURN NULL; END; $$ LANGUAGE plpgsql; CREATE TRIGGER rw_view1_ins_trig AFTER INSERT ON base_tbl FOR EACH ROW EXECUTE PROCEDURE rw_view1_trig_fn(); CREATE VIEW rw_view1 AS SELECT a AS aa, b AS bb FROM base_tbl; INSERT INTO rw_view1 VALUES (3, 'Row 3'); select * from base_tbl; a | b ---+------- 2 | Row 2 3 | Row 3 1 | Row 3 (3 rows) DROP VIEW rw_view1; DROP TRIGGER rw_view1_ins_trig on base_tbl; DROP FUNCTION rw_view1_trig_fn(); DROP TABLE base_tbl; -- view with ORDER BY CREATE TABLE base_tbl (a int, b int); INSERT INTO base_tbl VALUES (1,2), (4,5), (3,-3); CREATE VIEW rw_view1 AS SELECT * FROM base_tbl ORDER BY a+b; SELECT * FROM rw_view1; a | b ---+---- 3 | -3 1 | 2 4 | 5 (3 rows) INSERT INTO rw_view1 VALUES (7,-8); SELECT * FROM rw_view1; a | b ---+---- 7 | -8 3 | -3 1 | 2 4 | 5 (4 rows) EXPLAIN (verbose, costs off) UPDATE rw_view1 SET b = b + 1 RETURNING *; QUERY PLAN ------------------------------------------------- Update on public.base_tbl Output: base_tbl.a, base_tbl.b -> Seq Scan on public.base_tbl Output: (base_tbl.b + 1), base_tbl.ctid (4 rows) UPDATE rw_view1 SET b = b + 1 RETURNING *; a | b ---+---- 1 | 3 4 | 6 3 | -2 7 | -7 (4 rows) SELECT * FROM rw_view1; a | b ---+---- 7 | -7 3 | -2 1 | 3 4 | 6 (4 rows) DROP TABLE base_tbl CASCADE; NOTICE: drop cascades to view rw_view1 -- multiple array-column updates CREATE TABLE base_tbl (a int, arr int[]); INSERT INTO base_tbl VALUES (1,ARRAY[2]), (3,ARRAY[4]); CREATE VIEW rw_view1 AS SELECT * FROM base_tbl; UPDATE rw_view1 SET arr[1] = 42, arr[2] = 77 WHERE a = 3; SELECT * FROM rw_view1; a | arr ---+--------- 1 | {2} 3 | {42,77} (2 rows) DROP TABLE base_tbl CASCADE; NOTICE: drop cascades to view rw_view1 -- views with updatable and non-updatable columns CREATE TABLE base_tbl(a float); INSERT INTO base_tbl SELECT i/10.0 FROM generate_series(1,10) g(i); CREATE VIEW rw_view1 AS SELECT ctid, sin(a) s, a, cos(a) c FROM base_tbl WHERE a != 0 ORDER BY abs(a); INSERT INTO rw_view1 VALUES (null, null, 1.1, null); -- should fail ERROR: cannot insert into column "ctid" of view "rw_view1" DETAIL: View columns that refer to system columns are not updatable. INSERT INTO rw_view1 (s, c, a) VALUES (null, null, 1.1); -- should fail ERROR: cannot insert into column "s" of view "rw_view1" DETAIL: View columns that are not columns of their base relation are not updatable. INSERT INTO rw_view1 (s, c, a) VALUES (default, default, 1.1); -- should fail ERROR: cannot insert into column "s" of view "rw_view1" DETAIL: View columns that are not columns of their base relation are not updatable. INSERT INTO rw_view1 (a) VALUES (1.1) RETURNING a, s, c; -- OK a | s | c -----+-------------------+------------------- 1.1 | 0.891207360061435 | 0.453596121425577 (1 row) UPDATE rw_view1 SET s = s WHERE a = 1.1; -- should fail ERROR: cannot update column "s" of view "rw_view1" DETAIL: View columns that are not columns of their base relation are not updatable. UPDATE rw_view1 SET a = 1.05 WHERE a = 1.1 RETURNING s; -- OK s ------------------- 0.867423225594017 (1 row) DELETE FROM rw_view1 WHERE a = 1.05; -- OK CREATE VIEW rw_view2 AS SELECT s, c, s/c t, a base_a, ctid FROM rw_view1; INSERT INTO rw_view2 VALUES (null, null, null, 1.1, null); -- should fail ERROR: cannot insert into column "t" of view "rw_view2" DETAIL: View columns that are not columns of their base relation are not updatable. INSERT INTO rw_view2(s, c, base_a) VALUES (null, null, 1.1); -- should fail ERROR: cannot insert into column "s" of view "rw_view1" DETAIL: View columns that are not columns of their base relation are not updatable. INSERT INTO rw_view2(base_a) VALUES (1.1) RETURNING t; -- OK t ------------------ 1.96475965724865 (1 row) UPDATE rw_view2 SET s = s WHERE base_a = 1.1; -- should fail ERROR: cannot update column "s" of view "rw_view1" DETAIL: View columns that are not columns of their base relation are not updatable. UPDATE rw_view2 SET t = t WHERE base_a = 1.1; -- should fail ERROR: cannot update column "t" of view "rw_view2" DETAIL: View columns that are not columns of their base relation are not updatable. UPDATE rw_view2 SET base_a = 1.05 WHERE base_a = 1.1; -- OK DELETE FROM rw_view2 WHERE base_a = 1.05 RETURNING base_a, s, c, t; -- OK base_a | s | c | t --------+-------------------+-------------------+------------------ 1.05 | 0.867423225594017 | 0.497571047891727 | 1.74331530998317 (1 row) CREATE VIEW rw_view3 AS SELECT s, c, s/c t, ctid FROM rw_view1; INSERT INTO rw_view3 VALUES (null, null, null, null); -- should fail ERROR: cannot insert into column "t" of view "rw_view3" DETAIL: View columns that are not columns of their base relation are not updatable. INSERT INTO rw_view3(s) VALUES (null); -- should fail ERROR: cannot insert into column "s" of view "rw_view1" DETAIL: View columns that are not columns of their base relation are not updatable. UPDATE rw_view3 SET s = s; -- should fail ERROR: cannot update column "s" of view "rw_view1" DETAIL: View columns that are not columns of their base relation are not updatable. DELETE FROM rw_view3 WHERE s = sin(0.1); -- should be OK SELECT * FROM base_tbl ORDER BY a; a ----- 0.2 0.3 0.4 0.5 0.6 0.7 0.8 0.9 1 (9 rows) SELECT table_name, is_insertable_into FROM information_schema.tables WHERE table_name LIKE E'r_\\_view%' ORDER BY table_name; table_name | is_insertable_into ------------+-------------------- rw_view1 | YES rw_view2 | YES rw_view3 | NO (3 rows) SELECT table_name, is_updatable, is_insertable_into FROM information_schema.views WHERE table_name LIKE E'r_\\_view%' ORDER BY table_name; table_name | is_updatable | is_insertable_into ------------+--------------+-------------------- rw_view1 | YES | YES rw_view2 | YES | YES rw_view3 | NO | NO (3 rows) SELECT table_name, column_name, is_updatable FROM information_schema.columns WHERE table_name LIKE E'r_\\_view%' ORDER BY table_name, ordinal_position; table_name | column_name | is_updatable ------------+-------------+-------------- rw_view1 | ctid | NO rw_view1 | s | NO rw_view1 | a | YES rw_view1 | c | NO rw_view2 | s | NO rw_view2 | c | NO rw_view2 | t | NO rw_view2 | base_a | YES rw_view2 | ctid | NO rw_view3 | s | NO rw_view3 | c | NO rw_view3 | t | NO rw_view3 | ctid | NO (13 rows) SELECT events & 4 != 0 AS upd, events & 8 != 0 AS ins, events & 16 != 0 AS del FROM pg_catalog.pg_relation_is_updatable('rw_view3'::regclass, false) t(events); upd | ins | del -----+-----+----- f | f | t (1 row) DROP TABLE base_tbl CASCADE; NOTICE: drop cascades to 3 other objects DETAIL: drop cascades to view rw_view1 drop cascades to view rw_view2 drop cascades to view rw_view3 -- view on table with GENERATED columns CREATE TABLE base_tbl (id int, idplus1 int GENERATED ALWAYS AS (id + 1) STORED); CREATE VIEW rw_view1 AS SELECT * FROM base_tbl; INSERT INTO base_tbl (id) VALUES (1); INSERT INTO rw_view1 (id) VALUES (2); INSERT INTO base_tbl (id, idplus1) VALUES (3, DEFAULT); INSERT INTO rw_view1 (id, idplus1) VALUES (4, DEFAULT); INSERT INTO base_tbl (id, idplus1) VALUES (5, 6); -- error ERROR: cannot insert a non-DEFAULT value into column "idplus1" DETAIL: Column "idplus1" is a generated column. INSERT INTO rw_view1 (id, idplus1) VALUES (6, 7); -- error ERROR: cannot insert a non-DEFAULT value into column "idplus1" DETAIL: Column "idplus1" is a generated column. SELECT * FROM base_tbl; id | idplus1 ----+--------- 1 | 2 2 | 3 3 | 4 4 | 5 (4 rows) UPDATE base_tbl SET id = 2000 WHERE id = 2; UPDATE rw_view1 SET id = 3000 WHERE id = 3; SELECT * FROM base_tbl; id | idplus1 ------+--------- 1 | 2 4 | 5 2000 | 2001 3000 | 3001 (4 rows) DROP TABLE base_tbl CASCADE; NOTICE: drop cascades to view rw_view1 -- inheritance tests CREATE TABLE base_tbl_parent (a int); CREATE TABLE base_tbl_child (CHECK (a > 0)) INHERITS (base_tbl_parent); INSERT INTO base_tbl_parent SELECT * FROM generate_series(-8, -1); INSERT INTO base_tbl_child SELECT * FROM generate_series(1, 8); CREATE VIEW rw_view1 AS SELECT * FROM base_tbl_parent; CREATE VIEW rw_view2 AS SELECT * FROM ONLY base_tbl_parent; SELECT * FROM rw_view1 ORDER BY a; a ---- -8 -7 -6 -5 -4 -3 -2 -1 1 2 3 4 5 6 7 8 (16 rows) SELECT * FROM ONLY rw_view1 ORDER BY a; a ---- -8 -7 -6 -5 -4 -3 -2 -1 1 2 3 4 5 6 7 8 (16 rows) SELECT * FROM rw_view2 ORDER BY a; a ---- -8 -7 -6 -5 -4 -3 -2 -1 (8 rows) INSERT INTO rw_view1 VALUES (-100), (100); INSERT INTO rw_view2 VALUES (-200), (200); UPDATE rw_view1 SET a = a*10 WHERE a IN (-1, 1); -- Should produce -10 and 10 UPDATE ONLY rw_view1 SET a = a*10 WHERE a IN (-2, 2); -- Should produce -20 and 20 UPDATE rw_view2 SET a = a*10 WHERE a IN (-3, 3); -- Should produce -30 only UPDATE ONLY rw_view2 SET a = a*10 WHERE a IN (-4, 4); -- Should produce -40 only DELETE FROM rw_view1 WHERE a IN (-5, 5); -- Should delete -5 and 5 DELETE FROM ONLY rw_view1 WHERE a IN (-6, 6); -- Should delete -6 and 6 DELETE FROM rw_view2 WHERE a IN (-7, 7); -- Should delete -7 only DELETE FROM ONLY rw_view2 WHERE a IN (-8, 8); -- Should delete -8 only SELECT * FROM ONLY base_tbl_parent ORDER BY a; a ------ -200 -100 -40 -30 -20 -10 100 200 (8 rows) SELECT * FROM base_tbl_child ORDER BY a; a ---- 3 4 7 8 10 20 (6 rows) MERGE INTO rw_view1 t USING (VALUES (-200), (10)) AS v(a) ON t.a = v.a WHEN MATCHED THEN UPDATE SET a = t.a+1; -- Should produce -199 and 11 MERGE INTO ONLY rw_view1 t USING (VALUES (-100), (20)) AS v(a) ON t.a = v.a WHEN MATCHED THEN UPDATE SET a = t.a+1; -- Should produce -99 and 21 MERGE INTO rw_view2 t USING (VALUES (-40), (3)) AS v(a) ON t.a = v.a WHEN MATCHED THEN UPDATE SET a = t.a+1; -- Should produce -39 only MERGE INTO ONLY rw_view2 t USING (VALUES (-30), (4)) AS v(a) ON t.a = v.a WHEN MATCHED THEN UPDATE SET a = t.a+1; -- Should produce -29 only SELECT * FROM ONLY base_tbl_parent ORDER BY a; a ------ -199 -99 -39 -29 -20 -10 100 200 (8 rows) SELECT * FROM base_tbl_child ORDER BY a; a ---- 3 4 7 8 11 21 (6 rows) CREATE TABLE other_tbl_parent (id int); CREATE TABLE other_tbl_child () INHERITS (other_tbl_parent); INSERT INTO other_tbl_parent VALUES (7),(200); INSERT INTO other_tbl_child VALUES (8),(100); EXPLAIN (costs off) UPDATE rw_view1 SET a = a + 1000 FROM other_tbl_parent WHERE a = id; QUERY PLAN ------------------------------------------------------------------------- Update on base_tbl_parent Update on base_tbl_parent base_tbl_parent_1 Update on base_tbl_child base_tbl_parent_2 -> Merge Join Merge Cond: (base_tbl_parent.a = other_tbl_parent.id) -> Sort Sort Key: base_tbl_parent.a -> Append -> Seq Scan on base_tbl_parent base_tbl_parent_1 -> Seq Scan on base_tbl_child base_tbl_parent_2 -> Sort Sort Key: other_tbl_parent.id -> Append -> Seq Scan on other_tbl_parent other_tbl_parent_1 -> Seq Scan on other_tbl_child other_tbl_parent_2 (15 rows) UPDATE rw_view1 SET a = a + 1000 FROM other_tbl_parent WHERE a = id; SELECT * FROM ONLY base_tbl_parent ORDER BY a; a ------ -199 -99 -39 -29 -20 -10 1100 1200 (8 rows) SELECT * FROM base_tbl_child ORDER BY a; a ------ 3 4 11 21 1007 1008 (6 rows) DROP TABLE base_tbl_parent, base_tbl_child CASCADE; NOTICE: drop cascades to 2 other objects DETAIL: drop cascades to view rw_view1 drop cascades to view rw_view2 DROP TABLE other_tbl_parent CASCADE; NOTICE: drop cascades to table other_tbl_child -- simple WITH CHECK OPTION CREATE TABLE base_tbl (a int, b int DEFAULT 10); INSERT INTO base_tbl VALUES (1,2), (2,3), (1,-1); CREATE VIEW rw_view1 AS SELECT * FROM base_tbl WHERE a < b WITH LOCAL CHECK OPTION; \d+ rw_view1 View "public.rw_view1" Column | Type | Collation | Nullable | Default | Storage | Description --------+---------+-----------+----------+---------+---------+------------- a | integer | | | | plain | b | integer | | | | plain | View definition: SELECT a, b FROM base_tbl WHERE a < b; Options: check_option=local SELECT * FROM information_schema.views WHERE table_name = 'rw_view1'; table_catalog | table_schema | table_name | view_definition | check_option | is_updatable | ---------------+--------------+------------+------------------+--------------+-- regression | public | rw_view1 | SELECT a, +| LOCAL | YES | YES | NO | NO | NO | | | b +| | | | | | | | | FROM base_tbl+| | | | | | | | | WHERE (a < b); | | | | | | (1 row) INSERT INTO rw_view1 VALUES(3,4); -- ok INSERT INTO rw_view1 VALUES(4,3); -- should fail ERROR: new row violates check option for view "rw_view1" DETAIL: Failing row contains (4, 3). INSERT INTO rw_view1 VALUES(5,null); -- should fail ERROR: new row violates check option for view "rw_view1" DETAIL: Failing row contains (5, null). UPDATE rw_view1 SET b = 5 WHERE a = 3; -- ok UPDATE rw_view1 SET b = -5 WHERE a = 3; -- should fail ERROR: new row violates check option for view "rw_view1" DETAIL: Failing row contains (3, -5). INSERT INTO rw_view1(a) VALUES (9); -- ok INSERT INTO rw_view1(a) VALUES (10); -- should fail ERROR: new row violates check option for view "rw_view1" DETAIL: Failing row contains (10, 10). SELECT * FROM base_tbl ORDER BY a, b; a | b ---+---- 1 | -1 1 | 2 2 | 3 3 | 5 9 | 10 (5 rows) MERGE INTO rw_view1 t USING (VALUES (10)) AS v(a) ON t.a = v.a WHEN NOT MATCHED THEN INSERT VALUES (v.a, v.a + 1); -- ok MERGE INTO rw_view1 t USING (VALUES (11)) AS v(a) ON t.a = v.a WHEN NOT MATCHED THEN INSERT VALUES (v.a, v.a - 1); -- should fail ERROR: new row violates check option for view "rw_view1" DETAIL: Failing row contains (11, 10). MERGE INTO rw_view1 t USING (VALUES (1)) AS v(a) ON t.a = v.a WHEN MATCHED THEN UPDATE SET a = t.a - 1; -- ok MERGE INTO rw_view1 t USING (VALUES (2)) AS v(a) ON t.a = v.a WHEN MATCHED THEN UPDATE SET a = t.a + 1; -- should fail ERROR: new row violates check option for view "rw_view1" DETAIL: Failing row contains (3, 3). SELECT * FROM base_tbl ORDER BY a, b; a | b ----+---- 0 | 2 1 | -1 2 | 3 3 | 5 9 | 10 10 | 11 (6 rows) DROP TABLE base_tbl CASCADE; NOTICE: drop cascades to view rw_view1 -- WITH LOCAL/CASCADED CHECK OPTION CREATE TABLE base_tbl (a int); CREATE VIEW rw_view1 AS SELECT * FROM base_tbl WHERE a > 0; CREATE VIEW rw_view2 AS SELECT * FROM rw_view1 WHERE a < 10 WITH CHECK OPTION; -- implicitly cascaded \d+ rw_view2 View "public.rw_view2" Column | Type | Collation | Nullable | Default | Storage | Description --------+---------+-----------+----------+---------+---------+------------- a | integer | | | | plain | View definition: SELECT a FROM rw_view1 WHERE a < 10; Options: check_option=cascaded SELECT * FROM information_schema.views WHERE table_name = 'rw_view2'; table_catalog | table_schema | table_name | view_definition | check_option | is_updatable | ---------------+--------------+------------+-------------------+--------------+- regression | public | rw_view2 | SELECT a +| CASCADED | YES | YES | NO | NO | NO | | | FROM rw_view1 +| | | | | | | | | WHERE (a < 10); | | | | | | (1 row) INSERT INTO rw_view2 VALUES (-5); -- should fail ERROR: new row violates check option for view "rw_view1" DETAIL: Failing row contains (-5). INSERT INTO rw_view2 VALUES (5); -- ok INSERT INTO rw_view2 VALUES (15); -- should fail ERROR: new row violates check option for view "rw_view2" DETAIL: Failing row contains (15). SELECT * FROM base_tbl; a --- 5 (1 row) UPDATE rw_view2 SET a = a - 10; -- should fail ERROR: new row violates check option for view "rw_view1" DETAIL: Failing row contains (-5). UPDATE rw_view2 SET a = a + 10; -- should fail ERROR: new row violates check option for view "rw_view2" DETAIL: Failing row contains (15). CREATE OR REPLACE VIEW rw_view2 AS SELECT * FROM rw_view1 WHERE a < 10 WITH LOCAL CHECK OPTION; \d+ rw_view2 View "public.rw_view2" Column | Type | Collation | Nullable | Default | Storage | Description --------+---------+-----------+----------+---------+---------+------------- a | integer | | | | plain | View definition: SELECT a FROM rw_view1 WHERE a < 10; Options: check_option=local SELECT * FROM information_schema.views WHERE table_name = 'rw_view2'; table_catalog | table_schema | table_name | view_definition | check_option | is_updatable | ---------------+--------------+------------+-------------------+--------------+- regression | public | rw_view2 | SELECT a +| LOCAL | YES | YES | NO | NO | NO | | | FROM rw_view1 +| | | | | | | | | WHERE (a < 10); | | | | | | (1 row) INSERT INTO rw_view2 VALUES (-10); -- ok, but not in view INSERT INTO rw_view2 VALUES (20); -- should fail ERROR: new row violates check option for view "rw_view2" DETAIL: Failing row contains (20). SELECT * FROM base_tbl; a ----- 5 -10 (2 rows) ALTER VIEW rw_view1 SET (check_option=here); -- invalid ERROR: invalid value for enum option "check_option": here DETAIL: Valid values are "local" and "cascaded". ALTER VIEW rw_view1 SET (check_option=local); INSERT INTO rw_view2 VALUES (-20); -- should fail ERROR: new row violates check option for view "rw_view1" DETAIL: Failing row contains (-20). INSERT INTO rw_view2 VALUES (30); -- should fail ERROR: new row violates check option for view "rw_view2" DETAIL: Failing row contains (30). ALTER VIEW rw_view2 RESET (check_option); \d+ rw_view2 View "public.rw_view2" Column | Type | Collation | Nullable | Default | Storage | Description --------+---------+-----------+----------+---------+---------+------------- a | integer | | | | plain | View definition: SELECT a FROM rw_view1 WHERE a < 10; SELECT * FROM information_schema.views WHERE table_name = 'rw_view2'; table_catalog | table_schema | table_name | view_definition | check_option | is_updatable | ---------------+--------------+------------+-------------------+--------------+- regression | public | rw_view2 | SELECT a +| NONE | YES | YES | NO | NO | NO | | | FROM rw_view1 +| | | | | | | | | WHERE (a < 10); | | | | | | (1 row) INSERT INTO rw_view2 VALUES (30); -- ok, but not in view SELECT * FROM base_tbl; a ----- 5 -10 30 (3 rows) DROP TABLE base_tbl CASCADE; NOTICE: drop cascades to 2 other objects DETAIL: drop cascades to view rw_view1 drop cascades to view rw_view2 -- WITH CHECK OPTION with no local view qual CREATE TABLE base_tbl (a int); CREATE VIEW rw_view1 AS SELECT * FROM base_tbl WITH CHECK OPTION; CREATE VIEW rw_view2 AS SELECT * FROM rw_view1 WHERE a > 0; CREATE VIEW rw_view3 AS SELECT * FROM rw_view2 WITH CHECK OPTION; SELECT * FROM information_schema.views WHERE table_name LIKE E'rw\\_view_' ORDER BY table_ table_catalog | table_schema | table_name | view_definition | check_option | is_updatable | ---------------+--------------+------------+-------------------+--------------+- regression | public | rw_view1 | SELECT a +| CASCADED | YES | YES | NO | NO | NO | | | FROM base_tbl; | | | | | | regression | public | rw_view2 | SELECT a +| NONE | YES | YES | NO | NO | NO | | | FROM rw_view1 +| | | | | | | | | WHERE (a > 0); | | | | | | regression | public | rw_view3 | SELECT a +| CASCADED | YES | YES | NO | NO | NO | | | FROM rw_view2; | | | | | | (3 rows) INSERT INTO rw_view1 VALUES (-1); -- ok INSERT INTO rw_view1 VALUES (1); -- ok INSERT INTO rw_view2 VALUES (-2); -- ok, but not in view INSERT INTO rw_view2 VALUES (2); -- ok INSERT INTO rw_view3 VALUES (-3); -- should fail ERROR: new row violates check option for view "rw_view2" DETAIL: Failing row contains (-3). INSERT INTO rw_view3 VALUES (3); -- ok DROP TABLE base_tbl CASCADE; NOTICE: drop cascades to 3 other objects DETAIL: drop cascades to view rw_view1 drop cascades to view rw_view2 drop cascades to view rw_view3 -- WITH CHECK OPTION with scalar array ops CREATE TABLE base_tbl (a int, b int[]); CREATE VIEW rw_view1 AS SELECT * FROM base_tbl WHERE a = ANY (b) WITH CHECK OPTION; INSERT INTO rw_view1 VALUES (1, ARRAY[1,2,3]); -- ok INSERT INTO rw_view1 VALUES (10, ARRAY[4,5]); -- should fail ERROR: new row violates check option for view "rw_view1" DETAIL: Failing row contains (10, {4,5}). UPDATE rw_view1 SET b[2] = -b[2] WHERE a = 1; -- ok UPDATE rw_view1 SET b[1] = -b[1] WHERE a = 1; -- should fail ERROR: new row violates check option for view "rw_view1" DETAIL: Failing row contains (1, {-1,-2,3}). PREPARE ins(int, int[]) AS INSERT INTO rw_view1 VALUES($1, $2); EXECUTE ins(2, ARRAY[1,2,3]); -- ok EXECUTE ins(10, ARRAY[4,5]); -- should fail ERROR: new row violates check option for view "rw_view1" DETAIL: Failing row contains (10, {4,5}). DEALLOCATE PREPARE ins; DROP TABLE base_tbl CASCADE; NOTICE: drop cascades to view rw_view1 -- WITH CHECK OPTION with subquery CREATE TABLE base_tbl (a int); CREATE TABLE ref_tbl (a int PRIMARY KEY); INSERT INTO ref_tbl SELECT * FROM generate_series(1,10); CREATE VIEW rw_view1 AS SELECT * FROM base_tbl b WHERE EXISTS(SELECT 1 FROM ref_tbl r WHERE r.a = b.a) WITH CHECK OPTION; INSERT INTO rw_view1 VALUES (5); -- ok INSERT INTO rw_view1 VALUES (15); -- should fail ERROR: new row violates check option for view "rw_view1" DETAIL: Failing row contains (15). UPDATE rw_view1 SET a = a + 5; -- ok UPDATE rw_view1 SET a = a + 5; -- should fail ERROR: new row violates check option for view "rw_view1" DETAIL: Failing row contains (15). EXPLAIN (costs off) INSERT INTO rw_view1 VALUES (5); QUERY PLAN --------------------------------------------------------- Insert on base_tbl b -> Result SubPlan 1 -> Index Only Scan using ref_tbl_pkey on ref_tbl r Index Cond: (a = b.a) (5 rows) EXPLAIN (costs off) UPDATE rw_view1 SET a = a + 5; QUERY PLAN ----------------------------------------------------------- Update on base_tbl b -> Hash Join Hash Cond: (b.a = r.a) -> Seq Scan on base_tbl b -> Hash -> Seq Scan on ref_tbl r SubPlan 1 -> Index Only Scan using ref_tbl_pkey on ref_tbl r_1 Index Cond: (a = b.a) (9 rows) DROP TABLE base_tbl, ref_tbl CASCADE; NOTICE: drop cascades to view rw_view1 -- WITH CHECK OPTION with BEFORE trigger on base table CREATE TABLE base_tbl (a int, b int); CREATE FUNCTION base_tbl_trig_fn() RETURNS trigger AS $$ BEGIN NEW.b := 10; RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER base_tbl_trig BEFORE INSERT OR UPDATE ON base_tbl FOR EACH ROW EXECUTE PROCEDURE base_tbl_trig_fn(); CREATE VIEW rw_view1 AS SELECT * FROM base_tbl WHERE a < b WITH CHECK OPTION; INSERT INTO rw_view1 VALUES (5,0); -- ok INSERT INTO rw_view1 VALUES (15, 20); -- should fail ERROR: new row violates check option for view "rw_view1" DETAIL: Failing row contains (15, 10). UPDATE rw_view1 SET a = 20, b = 30; -- should fail ERROR: new row violates check option for view "rw_view1" DETAIL: Failing row contains (20, 10). DROP TABLE base_tbl CASCADE; NOTICE: drop cascades to view rw_view1 DROP FUNCTION base_tbl_trig_fn(); -- WITH LOCAL CHECK OPTION with INSTEAD OF trigger on base view CREATE TABLE base_tbl (a int, b int); CREATE VIEW rw_view1 AS SELECT a FROM base_tbl WHERE a < b; CREATE FUNCTION rw_view1_trig_fn() RETURNS trigger AS $$ BEGIN IF TG_OP = 'INSERT' THEN INSERT INTO base_tbl VALUES (NEW.a, 10); RETURN NEW; ELSIF TG_OP = 'UPDATE' THEN UPDATE base_tbl SET a=NEW.a WHERE a=OLD.a; RETURN NEW; ELSIF TG_OP = 'DELETE' THEN DELETE FROM base_tbl WHERE a=OLD.a; RETURN OLD; END IF; END; $$ LANGUAGE plpgsql; CREATE TRIGGER rw_view1_trig INSTEAD OF INSERT OR UPDATE OR DELETE ON rw_view1 FOR EACH ROW EXECUTE PROCEDURE rw_view1_trig_fn(); CREATE VIEW rw_view2 AS SELECT * FROM rw_view1 WHERE a > 0 WITH LOCAL CHECK OPTION; INSERT INTO rw_view2 VALUES (-5); -- should fail ERROR: new row violates check option for view "rw_view2" DETAIL: Failing row contains (-5). MERGE INTO rw_view2 t USING (VALUES (-5)) AS v(a) ON t.a = v.a WHEN NOT MATCHED THEN INSERT VALUES (v.a); -- should fail ERROR: new row violates check option for view "rw_view2" DETAIL: Failing row contains (-5). INSERT INTO rw_view2 VALUES (5); -- ok MERGE INTO rw_view2 t USING (VALUES (6)) AS v(a) ON t.a = v.a WHEN NOT MATCHED THEN INSERT VALUES (v.a); -- ok INSERT INTO rw_view2 VALUES (50); -- ok, but not in view MERGE INTO rw_view2 t USING (VALUES (60)) AS v(a) ON t.a = v.a WHEN NOT MATCHED THEN INSERT VALUES (v.a); -- ok, but not in view UPDATE rw_view2 SET a = a - 10; -- should fail ERROR: new row violates check option for view "rw_view2" DETAIL: Failing row contains (-5). MERGE INTO rw_view2 t USING (VALUES (6)) AS v(a) ON t.a = v.a WHEN MATCHED THEN UPDATE SET a = t.a - 10; -- should fail ERROR: new row violates check option for view "rw_view2" DETAIL: Failing row contains (-4). SELECT * FROM base_tbl; a | b ----+---- 5 | 10 6 | 10 50 | 10 60 | 10 (4 rows) -- Check option won't cascade down to base view with INSTEAD OF triggers ALTER VIEW rw_view2 SET (check_option=cascaded); INSERT INTO rw_view2 VALUES (100); -- ok, but not in view (doesn't fail rw_view1's check) UPDATE rw_view2 SET a = 200 WHERE a = 5; -- ok, but not in view (doesn't fail rw_view1's check) SELECT * FROM base_tbl; a | b -----+---- 6 | 10 50 | 10 60 | 10 100 | 10 200 | 10 (5 rows) -- Neither local nor cascaded check options work with INSTEAD rules DROP TRIGGER rw_view1_trig ON rw_view1; CREATE RULE rw_view1_ins_rule AS ON INSERT TO rw_view1 DO INSTEAD INSERT INTO base_tbl VALUES (NEW.a, 10); CREATE RULE rw_view1_upd_rule AS ON UPDATE TO rw_view1 DO INSTEAD UPDATE base_tbl SET a=NEW.a WHERE a=OLD.a; INSERT INTO rw_view2 VALUES (-10); -- ok, but not in view (doesn't fail rw_view2's check) INSERT INTO rw_view2 VALUES (5); -- ok INSERT INTO rw_view2 VALUES (20); -- ok, but not in view (doesn't fail rw_view1's check) UPDATE rw_view2 SET a = 30 WHERE a = 5; -- ok, but not in view (doesn't fail rw_view1's check) INSERT INTO rw_view2 VALUES (5); -- ok UPDATE rw_view2 SET a = -5 WHERE a = 5; -- ok, but not in view (doesn't fail rw_view2's check) SELECT * FROM base_tbl; a | b -----+---- 6 | 10 50 | 10 60 | 10 100 | 10 200 | 10 -10 | 10 20 | 10 30 | 10 -5 | 10 (9 rows) DROP TABLE base_tbl CASCADE; NOTICE: drop cascades to 2 other objects DETAIL: drop cascades to view rw_view1 drop cascades to view rw_view2 DROP FUNCTION rw_view1_trig_fn(); CREATE TABLE base_tbl (a int); CREATE VIEW rw_view1 AS SELECT a,10 AS b FROM base_tbl; CREATE RULE rw_view1_ins_rule AS ON INSERT TO rw_view1 DO INSTEAD INSERT INTO base_tbl VALUES (NEW.a); CREATE VIEW rw_view2 AS SELECT * FROM rw_view1 WHERE a > b WITH LOCAL CHECK OPTION; INSERT INTO rw_view2 VALUES (2,3); -- ok, but not in view (doesn't fail rw_view2's check) DROP TABLE base_tbl CASCADE; NOTICE: drop cascades to 2 other objects DETAIL: drop cascades to view rw_view1 drop cascades to view rw_view2 -- security barrier view CREATE TABLE base_tbl (person text, visibility text); INSERT INTO base_tbl VALUES ('Tom', 'public'), ('Dick', 'private'), ('Harry', 'public'); CREATE VIEW rw_view1 AS SELECT person FROM base_tbl WHERE visibility = 'public'; CREATE FUNCTION snoop(anyelement) RETURNS boolean AS $$ BEGIN RAISE NOTICE 'snooped value: %', $1; RETURN true; END; $$ LANGUAGE plpgsql COST 0.000001; CREATE OR REPLACE FUNCTION leakproof(anyelement) RETURNS boolean AS $$ BEGIN RETURN true; END; $$ LANGUAGE plpgsql STRICT IMMUTABLE LEAKPROOF; SELECT * FROM rw_view1 WHERE snoop(person); NOTICE: snooped value: Tom NOTICE: snooped value: Dick NOTICE: snooped value: Harry person -------- Tom Harry (2 rows) UPDATE rw_view1 SET person=person WHERE snoop(person); NOTICE: snooped value: Tom NOTICE: snooped value: Dick NOTICE: snooped value: Harry DELETE FROM rw_view1 WHERE NOT snoop(person); NOTICE: snooped value: Dick NOTICE: snooped value: Tom NOTICE: snooped value: Harry ALTER VIEW rw_view1 SET (security_barrier = true); SELECT table_name, is_insertable_into FROM information_schema.tables WHERE table_name = 'rw_view1'; table_name | is_insertable_into ------------+-------------------- rw_view1 | YES (1 row) SELECT table_name, is_updatable, is_insertable_into FROM information_schema.views WHERE table_name = 'rw_view1'; table_name | is_updatable | is_insertable_into ------------+--------------+-------------------- rw_view1 | YES | YES (1 row) SELECT table_name, column_name, is_updatable FROM information_schema.columns WHERE table_name = 'rw_view1' ORDER BY ordinal_position; table_name | column_name | is_updatable ------------+-------------+-------------- rw_view1 | person | YES (1 row) SELECT * FROM rw_view1 WHERE snoop(person); NOTICE: snooped value: Tom NOTICE: snooped value: Harry person -------- Tom Harry (2 rows) UPDATE rw_view1 SET person=person WHERE snoop(person); NOTICE: snooped value: Tom NOTICE: snooped value: Harry DELETE FROM rw_view1 WHERE NOT snoop(person); NOTICE: snooped value: Tom NOTICE: snooped value: Harry MERGE INTO rw_view1 t USING (VALUES ('Tom'), ('Dick'), ('Harry')) AS v(person) ON t.person = v.person WHEN MATCHED AND snoop(t.person) THEN UPDATE SET person = v.person; NOTICE: snooped value: Tom NOTICE: snooped value: Harry EXPLAIN (costs off) SELECT * FROM rw_view1 WHERE snoop(person); QUERY PLAN ----------------------------------------------- Subquery Scan on rw_view1 Filter: snoop(rw_view1.person) -> Seq Scan on base_tbl Filter: (visibility = 'public'::text) (4 rows) EXPLAIN (costs off) UPDATE rw_view1 SET person=person WHERE snoop(person); QUERY PLAN ------------------------------------------------------------------- Update on base_tbl -> Seq Scan on base_tbl Filter: ((visibility = 'public'::text) AND snoop(person)) (3 rows) EXPLAIN (costs off) DELETE FROM rw_view1 WHERE NOT snoop(person); QUERY PLAN ------------------------------------------------------------------------- Delete on base_tbl -> Seq Scan on base_tbl Filter: ((visibility = 'public'::text) AND (NOT snoop(person))) (3 rows) EXPLAIN (costs off) MERGE INTO rw_view1 t USING (VALUES ('Tom'), ('Dick'), ('Harry')) AS v(person) ON t.person = v.person WHEN MATCHED AND snoop(t.person) THEN UPDATE SET person = v.person; QUERY PLAN ------------------------------------------------------------- Merge on base_tbl -> Nested Loop Join Filter: (base_tbl.person = "*VALUES*".column1) -> Seq Scan on base_tbl Filter: (visibility = 'public'::text) -> Materialize -> Values Scan on "*VALUES*" (7 rows) -- security barrier view on top of security barrier view CREATE VIEW rw_view2 WITH (security_barrier = true) AS SELECT * FROM rw_view1 WHERE snoop(person); SELECT table_name, is_insertable_into FROM information_schema.tables WHERE table_name = 'rw_view2'; table_name | is_insertable_into ------------+-------------------- rw_view2 | YES (1 row) SELECT table_name, is_updatable, is_insertable_into FROM information_schema.views WHERE table_name = 'rw_view2'; table_name | is_updatable | is_insertable_into ------------+--------------+-------------------- rw_view2 | YES | YES (1 row) SELECT table_name, column_name, is_updatable FROM information_schema.columns WHERE table_name = 'rw_view2' ORDER BY ordinal_position; table_name | column_name | is_updatable ------------+-------------+-------------- rw_view2 | person | YES (1 row) SELECT * FROM rw_view2 WHERE snoop(person); NOTICE: snooped value: Tom NOTICE: snooped value: Tom NOTICE: snooped value: Harry NOTICE: snooped value: Harry person -------- Tom Harry (2 rows) UPDATE rw_view2 SET person=person WHERE snoop(person); NOTICE: snooped value: Tom NOTICE: snooped value: Tom NOTICE: snooped value: Harry NOTICE: snooped value: Harry DELETE FROM rw_view2 WHERE NOT snoop(person); NOTICE: snooped value: Tom NOTICE: snooped value: Tom NOTICE: snooped value: Harry NOTICE: snooped value: Harry MERGE INTO rw_view2 t USING (VALUES ('Tom'), ('Dick'), ('Harry')) AS v(person) ON t.person = v.person WHEN MATCHED AND snoop(t.person) THEN UPDATE SET person = v.person; NOTICE: snooped value: Tom NOTICE: snooped value: Tom NOTICE: snooped value: Harry NOTICE: snooped value: Harry EXPLAIN (costs off) SELECT * FROM rw_view2 WHERE snoop(person); QUERY PLAN ----------------------------------------------------- Subquery Scan on rw_view2 Filter: snoop(rw_view2.person) -> Subquery Scan on rw_view1 Filter: snoop(rw_view1.person) -> Seq Scan on base_tbl Filter: (visibility = 'public'::text) (6 rows) EXPLAIN (costs off) UPDATE rw_view2 SET person=person WHERE snoop(person); QUERY PLAN -------------------------------------------------------------------------------- Update on base_tbl -> Seq Scan on base_tbl Filter: ((visibility = 'public'::text) AND snoop(person) AND snoop(person)) (3 rows) EXPLAIN (costs off) DELETE FROM rw_view2 WHERE NOT snoop(person); QUERY PLAN -------------------------------------------------------------------------------- Delete on base_tbl -> Seq Scan on base_tbl Filter: ((visibility = 'public'::text) AND snoop(person) AND (NOT snoop(person))) (3 rows) EXPLAIN (costs off) MERGE INTO rw_view2 t USING (VALUES ('Tom'), ('Dick'), ('Harry')) AS v(person) ON t.person = v.person WHEN MATCHED AND snoop(t.person) THEN UPDATE SET person = v.person; QUERY PLAN ------------------------------------------------------------------------- Merge on base_tbl -> Nested Loop Join Filter: (base_tbl.person = "*VALUES*".column1) -> Seq Scan on base_tbl Filter: ((visibility = 'public'::text) AND snoop(person)) -> Values Scan on "*VALUES*" (6 rows) DROP TABLE base_tbl CASCADE; NOTICE: drop cascades to 2 other objects DETAIL: drop cascades to view rw_view1 drop cascades to view rw_view2 -- security barrier view on top of table with rules CREATE TABLE base_tbl(id int PRIMARY KEY, data text, deleted boolean); INSERT INTO base_tbl VALUES (1, 'Row 1', false), (2, 'Row 2', true); CREATE RULE base_tbl_ins_rule AS ON INSERT TO base_tbl WHERE EXISTS (SELECT 1 FROM base_tbl t WHERE t.id = new.id) DO INSTEAD UPDATE base_tbl SET data = new.data, deleted = false WHERE id = new.id; CREATE RULE base_tbl_del_rule AS ON DELETE TO base_tbl DO INSTEAD UPDATE base_tbl SET deleted = true WHERE id = old.id; CREATE VIEW rw_view1 WITH (security_barrier=true) AS SELECT id, data FROM base_tbl WHERE NOT deleted; SELECT * FROM rw_view1; id | data ----+------- 1 | Row 1 (1 row) EXPLAIN (costs off) DELETE FROM rw_view1 WHERE id = 1 AND snoop(data); QUERY PLAN ------------------------------------------------------------------- Update on base_tbl base_tbl_1 -> Nested Loop -> Index Scan using base_tbl_pkey on base_tbl base_tbl_1 Index Cond: (id = 1) -> Index Scan using base_tbl_pkey on base_tbl Index Cond: (id = 1) Filter: ((NOT deleted) AND snoop(data)) (7 rows) DELETE FROM rw_view1 WHERE id = 1 AND snoop(data); NOTICE: snooped value: Row 1 EXPLAIN (costs off) INSERT INTO rw_view1 VALUES (2, 'New row 2'); QUERY PLAN ----------------------------------------------------------- Insert on base_tbl InitPlan 1 -> Index Only Scan using base_tbl_pkey on base_tbl t Index Cond: (id = 2) -> Result One-Time Filter: ((InitPlan 1).col1 IS NOT TRUE) Update on base_tbl InitPlan 1 -> Index Only Scan using base_tbl_pkey on base_tbl t Index Cond: (id = 2) -> Result One-Time Filter: (InitPlan 1).col1 -> Index Scan using base_tbl_pkey on base_tbl Index Cond: (id = 2) (15 rows) INSERT INTO rw_view1 VALUES (2, 'New row 2'); SELECT * FROM base_tbl; id | data | deleted ----+-----------+--------- 1 | Row 1 | t 2 | New row 2 | f (2 rows) DROP TABLE base_tbl CASCADE; NOTICE: drop cascades to view rw_view1 -- security barrier view based on inheritance set CREATE TABLE t1 (a int, b float, c text); CREATE INDEX t1_a_idx ON t1(a); INSERT INTO t1 SELECT i,i,'t1' FROM generate_series(1,10) g(i); ANALYZE t1; CREATE TABLE t11 (d text) INHERITS (t1); CREATE INDEX t11_a_idx ON t11(a); INSERT INTO t11 SELECT i,i,'t11','t11d' FROM generate_series(1,10) g(i); ANALYZE t11; CREATE TABLE t12 (e int[]) INHERITS (t1); CREATE INDEX t12_a_idx ON t12(a); INSERT INTO t12 SELECT i,i,'t12','{1,2}'::int[] FROM generate_series(1,10) g(i); ANALYZE t12; CREATE TABLE t111 () INHERITS (t11, t12); NOTICE: merging multiple inherited definitions of column "a" NOTICE: merging multiple inherited definitions of column "b" NOTICE: merging multiple inherited definitions of column "c" CREATE INDEX t111_a_idx ON t111(a); INSERT INTO t111 SELECT i,i,'t111','t111d','{1,1,1}'::int[] FROM generate_series(1,10) g(i); ANALYZE t111; CREATE VIEW v1 WITH (security_barrier=true) AS SELECT *, (SELECT d FROM t11 WHERE t11.a = t1.a LIMIT 1) AS d FROM t1 WHERE a > 5 AND EXISTS(SELECT 1 FROM t12 WHERE t12.a = t1.a); SELECT * FROM v1 WHERE a=3; -- should not see anything a | b | c | d ---+---+---+--- (0 rows) SELECT * FROM v1 WHERE a=8; a | b | c | d ---+---+------+------ 8 | 8 | t1 | t11d 8 | 8 | t11 | t11d 8 | 8 | t12 | t11d 8 | 8 | t111 | t11d (4 rows) EXPLAIN (VERBOSE, COSTS OFF) UPDATE v1 SET a=100 WHERE snoop(a) AND leakproof(a) AND a < 7 AND a != 6; QUERY PLAN -------------------------------------------------------------------------------- Update on public.t1 Update on public.t1 t1_1 Update on public.t11 t1_2 Update on public.t12 t1_3 Update on public.t111 t1_4 -> Result Output: 100, t1.tableoid, t1.ctid -> Append -> Index Scan using t1_a_idx on public.t1 t1_1 Output: t1_1.tableoid, t1_1.ctid Index Cond: ((t1_1.a > 5) AND (t1_1.a < 7)) Filter: ((t1_1.a <> 6) AND EXISTS(SubPlan 1) AND snoop(t1_1.a) AND leakproof(t1_1.a)) SubPlan 1 -> Append -> Seq Scan on public.t12 t12_1 Filter: (t12_1.a = t1_1.a) -> Seq Scan on public.t111 t12_2 Filter: (t12_2.a = t1_1.a) -> Index Scan using t11_a_idx on public.t11 t1_2 Output: t1_2.tableoid, t1_2.ctid Index Cond: ((t1_2.a > 5) AND (t1_2.a < 7)) Filter: ((t1_2.a <> 6) AND EXISTS(SubPlan 1) AND snoop(t1_2.a) AND leakproof(t1_2.a)) -> Index Scan using t12_a_idx on public.t12 t1_3 Output: t1_3.tableoid, t1_3.ctid Index Cond: ((t1_3.a > 5) AND (t1_3.a < 7)) Filter: ((t1_3.a <> 6) AND EXISTS(SubPlan 1) AND snoop(t1_3.a) AND leakproof(t1_3.a)) -> Index Scan using t111_a_idx on public.t111 t1_4 Output: t1_4.tableoid, t1_4.ctid Index Cond: ((t1_4.a > 5) AND (t1_4.a < 7)) Filter: ((t1_4.a <> 6) AND EXISTS(SubPlan 1) AND snoop(t1_4.a) AND leakproof(t1_4.a)) (30 rows) UPDATE v1 SET a=100 WHERE snoop(a) AND leakproof(a) AND a < 7 AND a != 6; SELECT * FROM v1 WHERE a=100; -- Nothing should have been changed to 100 a | b | c | d ---+---+---+--- (0 rows) SELECT * FROM t1 WHERE a=100; -- Nothing should have been changed to 100 a | b | c ---+---+--- (0 rows) EXPLAIN (VERBOSE, COSTS OFF) UPDATE v1 SET a=a+1 WHERE snoop(a) AND leakproof(a) AND a = 8; QUERY PLAN -------------------------------------------------------------------------------- Update on public.t1 Update on public.t1 t1_1 Update on public.t11 t1_2 Update on public.t12 t1_3 Update on public.t111 t1_4 -> Result Output: (t1.a + 1), t1.tableoid, t1.ctid -> Append -> Index Scan using t1_a_idx on public.t1 t1_1 Output: t1_1.a, t1_1.tableoid, t1_1.ctid Index Cond: ((t1_1.a > 5) AND (t1_1.a = 8)) Filter: (EXISTS(SubPlan 1) AND snoop(t1_1.a) AND leakproof(t1_1.a)) SubPlan 1 -> Append -> Seq Scan on public.t12 t12_1 Filter: (t12_1.a = t1_1.a) -> Seq Scan on public.t111 t12_2 Filter: (t12_2.a = t1_1.a) -> Index Scan using t11_a_idx on public.t11 t1_2 Output: t1_2.a, t1_2.tableoid, t1_2.ctid Index Cond: ((t1_2.a > 5) AND (t1_2.a = 8)) Filter: (EXISTS(SubPlan 1) AND snoop(t1_2.a) AND leakproof(t1_2.a)) -> Index Scan using t12_a_idx on public.t12 t1_3 Output: t1_3.a, t1_3.tableoid, t1_3.ctid Index Cond: ((t1_3.a > 5) AND (t1_3.a = 8)) Filter: (EXISTS(SubPlan 1) AND snoop(t1_3.a) AND leakproof(t1_3.a)) -> Index Scan using t111_a_idx on public.t111 t1_4 Output: t1_4.a, t1_4.tableoid, t1_4.ctid Index Cond: ((t1_4.a > 5) AND (t1_4.a = 8)) Filter: (EXISTS(SubPlan 1) AND snoop(t1_4.a) AND leakproof(t1_4.a)) (30 rows) UPDATE v1 SET a=a+1 WHERE snoop(a) AND leakproof(a) AND a = 8; NOTICE: snooped value: 8 NOTICE: snooped value: 8 NOTICE: snooped value: 8 NOTICE: snooped value: 8 SELECT * FROM v1 WHERE b=8; a | b | c | d ---+---+------+------ 9 | 8 | t111 | t11d 9 | 8 | t12 | t11d 9 | 8 | t11 | t11d 9 | 8 | t1 | t11d (4 rows) DELETE FROM v1 WHERE snoop(a) AND leakproof(a); -- should not delete everything, just where a>5 NOTICE: snooped value: 6 NOTICE: snooped value: 7 NOTICE: snooped value: 9 NOTICE: snooped value: 10 NOTICE: snooped value: 9 NOTICE: snooped value: 6 NOTICE: snooped value: 7 NOTICE: snooped value: 9 NOTICE: snooped value: 10 NOTICE: snooped value: 9 NOTICE: snooped value: 6 NOTICE: snooped value: 7 NOTICE: snooped value: 9 NOTICE: snooped value: 10 NOTICE: snooped value: 9 NOTICE: snooped value: 6 NOTICE: snooped value: 7 NOTICE: snooped value: 9 NOTICE: snooped value: 10 NOTICE: snooped value: 9 TABLE t1; -- verify all a<=5 are intact a | b | c ---+---+------ 1 | 1 | t1 2 | 2 | t1 3 | 3 | t1 4 | 4 | t1 5 | 5 | t1 1 | 1 | t11 2 | 2 | t11 3 | 3 | t11 4 | 4 | t11 5 | 5 | t11 1 | 1 | t12 2 | 2 | t12 3 | 3 | t12 4 | 4 | t12 5 | 5 | t12 1 | 1 | t111 2 | 2 | t111 3 | 3 | t111 4 | 4 | t111 5 | 5 | t111 (20 rows) DROP TABLE t1, t11, t12, t111 CASCADE; NOTICE: drop cascades to view v1 DROP FUNCTION snoop(anyelement); DROP FUNCTION leakproof(anyelement); CREATE TABLE tx1 (a integer); CREATE TABLE tx2 (b integer); CREATE TABLE tx3 (c integer); CREATE VIEW vx1 AS SELECT a FROM tx1 WHERE EXISTS(SELECT 1 FROM tx2 JOIN tx3 ON b=c); INSERT INTO vx1 values (1); SELECT * FROM tx1; a --- 1 (1 row) SELECT * FROM vx1; a --- (0 rows) DROP VIEW vx1; DROP TABLE tx1; DROP TABLE tx2; DROP TABLE tx3; CREATE TABLE tx1 (a integer); CREATE TABLE tx2 (b integer); CREATE TABLE tx3 (c integer); CREATE VIEW vx1 AS SELECT a FROM tx1 WHERE EXISTS(SELECT 1 FROM tx2 JOIN tx3 ON b=c); INSERT INTO vx1 VALUES (1); INSERT INTO vx1 VALUES (1); SELECT * FROM tx1; a --- 1 1 (2 rows) SELECT * FROM vx1; a --- (0 rows) DROP VIEW vx1; DROP TABLE tx1; DROP TABLE tx2; DROP TABLE tx3; CREATE TABLE tx1 (a integer, b integer); CREATE TABLE tx2 (b integer, c integer); CREATE TABLE tx3 (c integer, d integer); ALTER TABLE tx1 DROP COLUMN b; ALTER TABLE tx2 DROP COLUMN c; ALTER TABLE tx3 DROP COLUMN d; CREATE VIEW vx1 AS SELECT a FROM tx1 WHERE EXISTS(SELECT 1 FROM tx2 JOIN tx3 ON b=c); INSERT INTO vx1 VALUES (1); INSERT INTO vx1 VALUES (1); SELECT * FROM tx1; a --- 1 1 (2 rows) SELECT * FROM vx1; a --- (0 rows) DROP VIEW vx1; DROP TABLE tx1; DROP TABLE tx2; DROP TABLE tx3; -- -- Test handling of vars from correlated subqueries in quals from outer -- security barrier views, per bug #13988 -- CREATE TABLE t1 (a int, b text, c int); INSERT INTO t1 VALUES (1, 'one', 10); CREATE TABLE t2 (cc int); INSERT INTO t2 VALUES (10), (20); CREATE VIEW v1 WITH (security_barrier = true) AS SELECT * FROM t1 WHERE (a > 0) WITH CHECK OPTION; CREATE VIEW v2 WITH (security_barrier = true) AS SELECT * FROM v1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.cc = v1.c) WITH CHECK OPTION; INSERT INTO v2 VALUES (2, 'two', 20); -- ok INSERT INTO v2 VALUES (-2, 'minus two', 20); -- not allowed ERROR: new row violates check option for view "v1" DETAIL: Failing row contains (-2, minus two, 20). INSERT INTO v2 VALUES (3, 'three', 30); -- not allowed ERROR: new row violates check option for view "v2" DETAIL: Failing row contains (3, three, 30). UPDATE v2 SET b = 'ONE' WHERE a = 1; -- ok UPDATE v2 SET a = -1 WHERE a = 1; -- not allowed ERROR: new row violates check option for view "v1" DETAIL: Failing row contains (-1, ONE, 10). UPDATE v2 SET c = 30 WHERE a = 1; -- not allowed ERROR: new row violates check option for view "v2" DETAIL: Failing row contains (1, ONE, 30). DELETE FROM v2 WHERE a = 2; -- ok SELECT * FROM v2; a | b | c ---+-----+---- 1 | ONE | 10 (1 row) DROP VIEW v2; DROP VIEW v1; DROP TABLE t2; DROP TABLE t1; -- -- Test sub-select in nested security barrier views, per bug #17972 -- CREATE TABLE t1 (a int); CREATE VIEW v1 WITH (security_barrier = true) AS SELECT * FROM t1; CREATE RULE v1_upd_rule AS ON UPDATE TO v1 DO INSTEAD UPDATE t1 SET a = NEW.a WHERE a = OLD.a; CREATE VIEW v2 WITH (security_barrier = true) AS SELECT * FROM v1 WHERE EXISTS (SELECT 1); EXPLAIN (COSTS OFF) UPDATE v2 SET a = 1; QUERY PLAN -------------------------------------------------------------- Update on t1 InitPlan 1 -> Result -> Merge Join Merge Cond: (t1.a = v1.a) -> Sort Sort Key: t1.a -> Seq Scan on t1 -> Sort Sort Key: v1.a -> Subquery Scan on v1 -> Result One-Time Filter: (InitPlan 1).col1 -> Seq Scan on t1 t1_1 (14 rows) DROP VIEW v2; DROP VIEW v1; DROP TABLE t1; -- -- Test CREATE OR REPLACE VIEW turning a non-updatable view into an -- auto-updatable view and adding check options in a single step -- CREATE TABLE t1 (a int, b text); CREATE VIEW v1 AS SELECT null::int AS a; CREATE OR REPLACE VIEW v1 AS SELECT * FROM t1 WHERE a > 0 WITH CHECK OPTION; INSERT INTO v1 VALUES (1, 'ok'); -- ok INSERT INTO v1 VALUES (-1, 'invalid'); -- should fail ERROR: new row violates check option for view "v1" DETAIL: Failing row contains (-1, invalid). DROP VIEW v1; DROP TABLE t1; -- check that an auto-updatable view on a partitioned table works correctly create table uv_pt (a int, b int, v varchar) partition by range (a, b); create table uv_pt1 (b int not null, v varchar, a int not null) partition by range (b); create table uv_pt11 (like uv_pt1); alter table uv_pt11 drop a; alter table uv_pt11 add a int; alter table uv_pt11 drop a; alter table uv_pt11 add a int not null; alter table uv_pt1 attach partition uv_pt11 for values from (2) to (5); alter table uv_pt attach partition uv_pt1 for values from (1, 2) to (1, 10); create view uv_ptv as select * from uv_pt; select events & 4 != 0 AS upd, events & 8 != 0 AS ins, events & 16 != 0 AS del from pg_catalog.pg_relation_is_updatable('uv_pt'::regclass, false) t(events); upd | ins | del -----+-----+----- t | t | t (1 row) select pg_catalog.pg_column_is_updatable('uv_pt'::regclass, 1::smallint, false); pg_column_is_updatable ------------------------ t (1 row) select pg_catalog.pg_column_is_updatable('uv_pt'::regclass, 2::smallint, false); pg_column_is_updatable ------------------------ t (1 row) select table_name, is_updatable, is_insertable_into from information_schema.views where table_name = 'uv_ptv'; table_name | is_updatable | is_insertable_into ------------+--------------+-------------------- uv_ptv | YES | YES (1 row) select table_name, column_name, is_updatable from information_schema.columns where table_name = 'uv_ptv' order by column_name; table_name | column_name | is_updatable ------------+-------------+-------------- uv_ptv | a | YES uv_ptv | b | YES uv_ptv | v | YES (3 rows) insert into uv_ptv values (1, 2); select tableoid::regclass, * from uv_pt; tableoid | a | b | v ----------+---+---+--- uv_pt11 | 1 | 2 | (1 row) create view uv_ptv_wco as select * from uv_pt where a = 0 with check option; insert into uv_ptv_wco values (1, 2); ERROR: new row violates check option for view "uv_ptv_wco" DETAIL: Failing row contains (1, 2, null). merge into uv_ptv t using (values (1,2), (1,4)) as v(a,b) on t.a = v.a -- fail: matches 2 src rows when matched then update set b = t.b + 1 when not matched then insert values (v.a, v.b + 1); ERROR: MERGE command cannot affect row a second time HINT: Ensure that not more than one source row matches any one target row. merge into uv_ptv t using (values (1,2), (1,4)) as v(a,b) on t.a = v.a and t.b = v.b when matched then update set b = t.b + 1 when not matched then insert values (v.a, v.b + 1); -- fail: no partition for b=5 ERROR: no partition of relation "uv_pt1" found for row DETAIL: Partition key of the failing row contains (b) = (5). merge into uv_ptv t using (values (1,2), (1,3)) as v(a,b) on t.a = v.a and t.b = v.b when matched then update set b = t.b + 1 when not matched then insert values (v.a, v.b + 1); -- ok select tableoid::regclass, * from uv_pt order by a, b; tableoid | a | b | v ----------+---+---+--- uv_pt11 | 1 | 3 | uv_pt11 | 1 | 4 | (2 rows) drop view uv_ptv, uv_ptv_wco; drop table uv_pt, uv_pt1, uv_pt11; -- check that wholerow vars appearing in WITH CHECK OPTION constraint expressions -- work fine with partitioned tables create table wcowrtest (a int) partition by list (a); create table wcowrtest1 partition of wcowrtest for values in (1); create view wcowrtest_v as select * from wcowrtest where wcowrtest = '(2)'::wcowrtest with ch insert into wcowrtest_v values (1); ERROR: new row violates check option for view "wcowrtest_v" DETAIL: Failing row contains (1). alter table wcowrtest add b text; create table wcowrtest2 (b text, c int, a int); alter table wcowrtest2 drop c; alter table wcowrtest attach partition wcowrtest2 for values in (2); create table sometable (a int, b text); insert into sometable values (1, 'a'), (2, 'b'); create view wcowrtest_v2 as select * from wcowrtest r where r in (select s from sometable s where r.a = s.a) with check option; -- WITH CHECK qual will be processed with wcowrtest2's -- rowtype after tuple-routing insert into wcowrtest_v2 values (2, 'no such row in sometable'); ERROR: new row violates check option for view "wcowrtest_v2" DETAIL: Failing row contains (2, no such row in sometable). drop view wcowrtest_v, wcowrtest_v2; drop table wcowrtest, sometable; -- Check INSERT .. ON CONFLICT DO UPDATE works correctly when the view's -- columns are named and ordered differently than the underlying table's. create table uv_iocu_tab (a text unique, b float); insert into uv_iocu_tab values ('xyxyxy', 0); create view uv_iocu_view as select b, b+1 as c, a, '2.0'::text as two from uv_iocu_tab; insert into uv_iocu_view (a, b) values ('xyxyxy', 1) on conflict (a) do update set b = uv_iocu_view.b; select * from uv_iocu_tab; a | b --------+--- xyxyxy | 0 (1 row) insert into uv_iocu_view (a, b) values ('xyxyxy', 1) on conflict (a) do update set b = excluded.b; select * from uv_iocu_tab; a | b --------+--- xyxyxy | 1 (1 row) -- OK to access view columns that are not present in underlying base -- relation in the ON CONFLICT portion of the query insert into uv_iocu_view (a, b) values ('xyxyxy', 3) on conflict (a) do update set b = cast(excluded.two as float); select * from uv_iocu_tab; a | b --------+--- xyxyxy | 2 (1 row) explain (costs off) insert into uv_iocu_view (a, b) values ('xyxyxy', 3) on conflict (a) do update set b = excluded.b where excluded.c > 0; QUERY PLAN -------------------------------------------------------------------------------- Insert on uv_iocu_tab Conflict Resolution: UPDATE Conflict Arbiter Indexes: uv_iocu_tab_a_key Conflict Filter: ((excluded.b + '1'::double precision) > '0'::double precision) -> Result (5 rows) insert into uv_iocu_view (a, b) values ('xyxyxy', 3) on conflict (a) do update set b = excluded.b where excluded.c > 0; select * from uv_iocu_tab; a | b --------+--- xyxyxy | 3 (1 row) drop view uv_iocu_view; drop table uv_iocu_tab; -- Test whole-row references to the view create table uv_iocu_tab (a int unique, b text); create view uv_iocu_view as select b as bb, a as aa, uv_iocu_tab::text as cc from uv_iocu_tab; insert into uv_iocu_view (aa,bb) values (1,'x'); explain (costs off) insert into uv_iocu_view (aa,bb) values (1,'y') on conflict (aa) do update set bb = 'Rejected: '||excluded.* where excluded.aa > 0 and excluded.bb != '' and excluded.cc is not null; QUERY PLAN -------------------------------------------------------------------------------- Insert on uv_iocu_tab Conflict Resolution: UPDATE Conflict Arbiter Indexes: uv_iocu_tab_a_key Conflict Filter: ((excluded.a > 0) AND (excluded.b <> ''::text) AND ((excluded.*)::text IS NOT N -> Result (5 rows) insert into uv_iocu_view (aa,bb) values (1,'y') on conflict (aa) do update set bb = 'Rejected: '||excluded.* where excluded.aa > 0 and excluded.bb != '' and excluded.cc is not null; select * from uv_iocu_view; bb | aa | cc -------------------------+----+--------------------------------- Rejected: (y,1,"(1,y)") | 1 | (1,"Rejected: (y,1,""(1,y)"")") (1 row) -- Test omitting a column of the base relation delete from uv_iocu_view; insert into uv_iocu_view (aa,bb) values (1,'x'); insert into uv_iocu_view (aa) values (1) on conflict (aa) do update set bb = 'Rejected: '||excluded.*; select * from uv_iocu_view; bb | aa | cc -----------------------+----+------------------------------- Rejected: (,1,"(1,)") | 1 | (1,"Rejected: (,1,""(1,)"")") (1 row) alter table uv_iocu_tab alter column b set default 'table default'; insert into uv_iocu_view (aa) values (1) on conflict (aa) do update set bb = 'Rejected: '||excluded.*; select * from uv_iocu_view; bb | aa | cc -------------------------------------------------------+----+------------------- Rejected: ("table default",1,"(1,""table default"")") | 1 | (1,"Rejected: (""table defaul (1 row) alter view uv_iocu_view alter column bb set default 'view default'; insert into uv_iocu_view (aa) values (1) on conflict (aa) do update set bb = 'Rejected: '||excluded.*; select * from uv_iocu_view; bb | aa | cc -----------------------------------------------------+----+--------------------- Rejected: ("view default",1,"(1,""view default"")") | 1 | (1,"Rejected: (""view default"" (1 row) -- Should fail to update non-updatable columns insert into uv_iocu_view (aa) values (1) on conflict (aa) do update set cc = 'XXX'; ERROR: cannot insert into column "cc" of view "uv_iocu_view" DETAIL: View columns that are not columns of their base relation are not updatable. drop view uv_iocu_view; drop table uv_iocu_tab; -- ON CONFLICT DO UPDATE permissions checks create user regress_view_user1; create user regress_view_user2; set session authorization regress_view_user1; create table base_tbl(a int unique, b text, c float); insert into base_tbl values (1,'xxx',1.0); create view rw_view1 as select b as bb, c as cc, a as aa from base_tbl; grant select (aa,bb) on rw_view1 to regress_view_user2; grant insert on rw_view1 to regress_view_user2; grant update (bb) on rw_view1 to regress_view_user2; set session authorization regress_view_user2; insert into rw_view1 values ('yyy',2.0,1) on conflict (aa) do update set bb = excluded.cc; -- Not allowed ERROR: permission denied for view rw_view1 insert into rw_view1 values ('yyy',2.0,1) on conflict (aa) do update set bb = rw_view1.cc; -- Not allowed ERROR: permission denied for view rw_view1 insert into rw_view1 values ('yyy',2.0,1) on conflict (aa) do update set bb = excluded.bb; -- OK insert into rw_view1 values ('zzz',2.0,1) on conflict (aa) do update set bb = rw_view1.bb||'xxx'; -- OK insert into rw_view1 values ('zzz',2.0,1) on conflict (aa) do update set cc = 3.0; -- Not allowed ERROR: permission denied for view rw_view1 reset session authorization; select * from base_tbl; a | b | c ---+--------+--- 1 | yyyxxx | 1 (1 row) set session authorization regress_view_user1; grant select (a,b) on base_tbl to regress_view_user2; grant insert (a,b) on base_tbl to regress_view_user2; grant update (a,b) on base_tbl to regress_view_user2; set session authorization regress_view_user2; create view rw_view2 as select b as bb, c as cc, a as aa from base_tbl; insert into rw_view2 (aa,bb) values (1,'xxx') on conflict (aa) do update set bb = excluded.bb; -- Not allowed ERROR: permission denied for table base_tbl create view rw_view3 as select b as bb, a as aa from base_tbl; insert into rw_view3 (aa,bb) values (1,'xxx') on conflict (aa) do update set bb = excluded.bb; -- OK reset session authorization; select * from base_tbl; a | b | c ---+-----+--- 1 | xxx | 1 (1 row) set session authorization regress_view_user2; create view rw_view4 as select aa, bb, cc FROM rw_view1; insert into rw_view4 (aa,bb) values (1,'yyy') on conflict (aa) do update set bb = excluded.bb; -- Not allowed ERROR: permission denied for view rw_view1 create view rw_view5 as select aa, bb FROM rw_view1; insert into rw_view5 (aa,bb) values (1,'yyy') on conflict (aa) do update set bb = excluded.bb; -- OK reset session authorization; select * from base_tbl; a | b | c ---+-----+--- 1 | yyy | 1 (1 row) drop view rw_view5; drop view rw_view4; drop view rw_view3; drop view rw_view2; drop view rw_view1; drop table base_tbl; drop user regress_view_user1; drop user regress_view_user2; -- Test single- and multi-row inserts with table and view defaults. -- Table defaults should be used, unless overridden by view defaults. create table base_tab_def (a int, b text default 'Table default', c text default 'Table default', d text, e text); create view base_tab_def_view as select * from base_tab_def; alter view base_tab_def_view alter b set default 'View default'; alter view base_tab_def_view alter d set default 'View default'; insert into base_tab_def values (1); insert into base_tab_def values (2), (3); insert into base_tab_def values (4, default, default, default, default); insert into base_tab_def values (5, default, default, default, default), (6, default, default, default, default); insert into base_tab_def_view values (11); insert into base_tab_def_view values (12), (13); insert into base_tab_def_view values (14, default, default, default, default); insert into base_tab_def_view values (15, default, default, default, default), (16, default, default, default, default); insert into base_tab_def_view values (17), (default); select * from base_tab_def order by a; a | b | c | d | e ----+---------------+---------------+--------------+--- 1 | Table default | Table default | | 2 | Table default | Table default | | 3 | Table default | Table default | | 4 | Table default | Table default | | 5 | Table default | Table default | | 6 | Table default | Table default | | 11 | View default | Table default | View default | 12 | View default | Table default | View default | 13 | View default | Table default | View default | 14 | View default | Table default | View default | 15 | View default | Table default | View default | 16 | View default | Table default | View default | 17 | View default | Table default | View default | | View default | Table default | View default | (14 rows) -- Adding an INSTEAD OF trigger should cause NULLs to be inserted instead of -- table defaults, where there are no view defaults. create function base_tab_def_view_instrig_func() returns trigger as $$ begin insert into base_tab_def values (new.a, new.b, new.c, new.d, new.e); return new; end; $$ language plpgsql; create trigger base_tab_def_view_instrig instead of insert on base_tab_def_view for each row execute function base_tab_def_view_instrig_func(); truncate base_tab_def; insert into base_tab_def values (1); insert into base_tab_def values (2), (3); insert into base_tab_def values (4, default, default, default, default); insert into base_tab_def values (5, default, default, default, default), (6, default, default, default, default); insert into base_tab_def_view values (11); insert into base_tab_def_view values (12), (13); insert into base_tab_def_view values (14, default, default, default, default); insert into base_tab_def_view values (15, default, default, default, default), (16, default, default, default, default); insert into base_tab_def_view values (17), (default); select * from base_tab_def order by a; a | b | c | d | e ----+---------------+---------------+--------------+--- 1 | Table default | Table default | | 2 | Table default | Table default | | 3 | Table default | Table default | | 4 | Table default | Table default | | 5 | Table default | Table default | | 6 | Table default | Table default | | 11 | View default | | View default | 12 | View default | | View default | 13 | View default | | View default | 14 | View default | | View default | 15 | View default | | View default | 16 | View default | | View default | 17 | View default | | View default | | View default | | View default | (14 rows) -- Using an unconditional DO INSTEAD rule should also cause NULLs to be -- inserted where there are no view defaults. drop trigger base_tab_def_view_instrig on base_tab_def_view; drop function base_tab_def_view_instrig_func; create rule base_tab_def_view_ins_rule as on insert to base_tab_def_view do instead insert into base_tab_def values (new.a, new.b, new.c, new.d, new.e); truncate base_tab_def; insert into base_tab_def values (1); insert into base_tab_def values (2), (3); insert into base_tab_def values (4, default, default, default, default); insert into base_tab_def values (5, default, default, default, default), (6, default, default, default, default); insert into base_tab_def_view values (11); insert into base_tab_def_view values (12), (13); insert into base_tab_def_view values (14, default, default, default, default); insert into base_tab_def_view values (15, default, default, default, default), (16, default, default, default, default); insert into base_tab_def_view values (17), (default); select * from base_tab_def order by a; a | b | c | d | e ----+---------------+---------------+--------------+--- 1 | Table default | Table default | | 2 | Table default | Table default | | 3 | Table default | Table default | | 4 | Table default | Table default | | 5 | Table default | Table default | | 6 | Table default | Table default | | 11 | View default | | View default | 12 | View default | | View default | 13 | View default | | View default | 14 | View default | | View default | 15 | View default | | View default | 16 | View default | | View default | 17 | View default | | View default | | View default | | View default | (14 rows) -- A DO ALSO rule should cause each row to be inserted twice. The first -- insert should behave the same as an auto-updatable view (using table -- defaults, unless overridden by view defaults). The second insert should -- behave the same as a rule-updatable view (inserting NULLs where there are -- no view defaults). drop rule base_tab_def_view_ins_rule on base_tab_def_view; create rule base_tab_def_view_ins_rule as on insert to base_tab_def_view do also insert into base_tab_def values (new.a, new.b, new.c, new.d, new.e); truncate base_tab_def; insert into base_tab_def values (1); insert into base_tab_def values (2), (3); insert into base_tab_def values (4, default, default, default, default); insert into base_tab_def values (5, default, default, default, default), (6, default, default, default, default); insert into base_tab_def_view values (11); insert into base_tab_def_view values (12), (13); insert into base_tab_def_view values (14, default, default, default, default); insert into base_tab_def_view values (15, default, default, default, default), (16, default, default, default, default); insert into base_tab_def_view values (17), (default); select * from base_tab_def order by a, c NULLS LAST; a | b | c | d | e ----+---------------+---------------+--------------+--- 1 | Table default | Table default | | 2 | Table default | Table default | | 3 | Table default | Table default | | 4 | Table default | Table default | | 5 | Table default | Table default | | 6 | Table default | Table default | | 11 | View default | Table default | View default | 11 | View default | | View default | 12 | View default | Table default | View default | 12 | View default | | View default | 13 | View default | Table default | View default | 13 | View default | | View default | 14 | View default | Table default | View default | 14 | View default | | View default | 15 | View default | Table default | View default | 15 | View default | | View default | 16 | View default | Table default | View default | 16 | View default | | View default | 17 | View default | Table default | View default | 17 | View default | | View default | | View default | Table default | View default | | View default | | View default | (22 rows) -- Test a DO ALSO INSERT ... SELECT rule drop rule base_tab_def_view_ins_rule on base_tab_def_view; create rule base_tab_def_view_ins_rule as on insert to base_tab_def_view do also insert into base_tab_def (a, b, e) select new.a, new.b, 'xxx'; truncate base_tab_def; insert into base_tab_def_view values (1, default, default, default, default); insert into base_tab_def_view values (2, default, default, default, default), (3, default, default, default, default); select * from base_tab_def order by a, e nulls first; a | b | c | d | e ---+--------------+---------------+--------------+----- 1 | View default | Table default | View default | 1 | View default | Table default | | xxx 2 | View default | Table default | View default | 2 | View default | Table default | | xxx 3 | View default | Table default | View default | 3 | View default | Table default | | xxx (6 rows) drop view base_tab_def_view; drop table base_tab_def; -- Test defaults with array assignments create table base_tab (a serial, b int[], c text, d text default 'Table default'); create view base_tab_view as select c, a, b from base_tab; alter view base_tab_view alter column c set default 'View default'; insert into base_tab_view (b[1], b[2], c, b[5], b[4], a, b[3]) values (1, 2, default, 5, 4, default, 3), (10, 11, 'C value', 14, 13, 100, 12); select * from base_tab order by a; a | b | c | d -----+------------------+--------------+--------------- 1 | {1,2,3,4,5} | View default | Table default 100 | {10,11,12,13,14} | C value | Table default (2 rows) drop view base_tab_view; drop table base_tab; [0.75QuellennavigatorsProjekt 2026-09-27] |
2026-10-10