summaryrefslogtreecommitdiffstats
path: root/src/test/regress/expected/updatable_views.out
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2024-05-04 12:15:05 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2024-05-04 12:15:05 +0000
commit46651ce6fe013220ed397add242004d764fc0153 (patch)
tree6e5299f990f88e60174a1d3ae6e48eedd2688b2b /src/test/regress/expected/updatable_views.out
parentInitial commit. (diff)
downloadpostgresql-14-upstream.tar.xz
postgresql-14-upstream.zip
Adding upstream version 14.5.upstream/14.5upstream
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'src/test/regress/expected/updatable_views.out')
-rw-r--r--src/test/regress/expected/updatable_views.out3056
1 files changed, 3056 insertions, 0 deletions
diff --git a/src/test/regress/expected/updatable_views.out b/src/test/regress/expected/updatable_views.out
new file mode 100644
index 0000000..cdff914
--- /dev/null
+++ b/src/test/regress/expected/updatable_views.out
@@ -0,0 +1,3056 @@
+--
+-- 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 supported
+CREATE VIEW ro_view6 AS SELECT a, b FROM base_tbl UNION SELECT -a, b FROM base_tbl; -- Set ops not supported
+CREATE VIEW ro_view7 AS WITH t AS (SELECT a, b FROM base_tbl) SELECT * FROM t; -- WITH not supported
+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 relations
+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 rangetable
+CREATE VIEW rw_view14 AS SELECT ctid, a, b FROM base_tbl; -- System columns may be part of an updatable view
+CREATE VIEW rw_view15 AS SELECT a, upper(b) FROM base_tbl; -- Expression/function may be part of an updatable view
+CREATE VIEW rw_view16 AS SELECT a, b, a AS aa FROM base_tbl; -- Repeated column may be part of an updatable view
+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 targetlist not supported
+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 unconditional ON DELETE DO INSTEAD rule.
+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 unconditional ON DELETE DO INSTEAD rule.
+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 unconditional ON DELETE DO INSTEAD rule.
+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 unconditional ON DELETE DO INSTEAD rule.
+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 unconditional ON DELETE DO INSTEAD rule.
+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 unconditional ON DELETE DO INSTEAD rule.
+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 UPDATE DO INSTEAD rule.
+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 UPDATE DO INSTEAD rule.
+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 UPDATE DO INSTEAD rule.
+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 UPDATE DO INSTEAD rule.
+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 UPDATE DO INSTEAD rule.
+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 UPDATE DO INSTEAD rule.
+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 unconditional ON INSERT DO INSTEAD rule.
+-- 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
+-- 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 unconditional ON INSERT DO INSTEAD rule.
+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 unconditional ON DELETE DO INSTEAD rule.
+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 UPDATE DO INSTEAD rule.
+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 UPDATE DO INSTEAD rule.
+-- 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 unconditional ON INSERT DO INSTEAD rule.
+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 UPDATE DO INSTEAD rule.
+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 unconditional ON DELETE DO INSTEAD rule.
+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 * 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
+(2 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)
+
+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)
+
+DROP TABLE base_tbl CASCADE;
+NOTICE: drop cascades to view rw_view1
+-- 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 FROM base_tbl WHERE a>0;
+CREATE VIEW rw_view2 AS SELECT aa AS aaa, bb AS bbb 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
+(2 rows)
+
+INSERT INTO rw_view2 VALUES (3, 'Row 3');
+INSERT INTO rw_view2 (aaa) VALUES (4);
+SELECT * FROM rw_view2;
+ aaa | bbb
+-----+-------------
+ 1 | Row 1
+ 2 | Row 2
+ 3 | Row 3
+ 4 | Unspecified
+(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
+-----+-------
+ 1 | Row 1
+ 3 | Row 3
+ 4 | Row 4
+(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/triggers
+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 *;
+ a | b
+---+-------
+ 3 | Row 3
+(1 row)
+
+UPDATE rw_view2 SET b='Row three' WHERE a=3 RETURNING *;
+ a | b
+---+-----------
+ 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 *;
+ a | b
+---+-----------
+ 3 | Row three
+(1 row)
+
+SELECT * FROM rw_view2;
+ a | b
+---+-------
+ 1 | Row 1
+ 2 | Row 2
+(2 rows)
+
+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 * FROM base_tbl WHERE a>0 OFFSET 0; -- not updatable without rules/triggers
+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,
+ 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_deletable | is_trigger_insertable_into
+------------+--------------+--------------------+----------------------+----------------------+----------------------------
+ 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_view2 | a | NO
+ rw_view2 | b | NO
+(4 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);
+ RETURN NEW;
+ ELSIF TG_OP = 'UPDATE' THEN
+ UPDATE base_tbl SET b=NEW.b 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_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_deletable | is_trigger_insertable_into
+------------+--------------+--------------------+----------------------+----------------------+----------------------------
+ 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_view2 | a | NO
+ rw_view2 | b | NO
+(4 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_deletable | is_trigger_insertable_into
+------------+--------------+--------------------+----------------------+----------------------+----------------------------
+ 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_view2 | a | NO
+ rw_view2 | b | NO
+(4 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_deletable | is_trigger_insertable_into
+------------+--------------+--------------------+----------------------+----------------------+----------------------------
+ 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_view2 | a | NO
+ rw_view2 | b | NO
+(4 rows)
+
+INSERT INTO rw_view2 VALUES (3, 'Row 3') RETURNING *;
+ a | b
+---+-------
+ 3 | Row 3
+(1 row)
+
+UPDATE rw_view2 SET b='Row three' WHERE a=3 RETURNING *;
+ a | b
+---+-----------
+ 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 *;
+ a | b
+---+-----------
+ 3 | Row three
+(1 row)
+
+SELECT * FROM rw_view2;
+ a | b
+---+-------
+ 1 | Row 1
+ 2 | Row 2
+(2 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)
+
+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;
+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
+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
+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
+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
+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
+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
+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
+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
+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
+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;
+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
+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;
+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 | bar | 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
+SET SESSION AUTHORIZATION regress_view_user2;
+SELECT * FROM rw_view2;
+ a | b | c
+---+-----+---
+ 1 | bar | 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
+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;
+-- 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);
+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
+(5 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 (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)
+
+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
+------
+ -200
+ -100
+ -40
+ -30
+ -20
+ -10
+ 1100
+ 1200
+(8 rows)
+
+SELECT * FROM base_tbl_child ORDER BY a;
+ a
+------
+ 3
+ 4
+ 10
+ 20
+ 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 base_tbl.a,
+ base_tbl.b
+ FROM base_tbl
+ WHERE base_tbl.a < base_tbl.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 | is_insertable_into | is_trigger_updatable | is_trigger_deletable | is_trigger_insertable_into
+---------------+--------------+------------+------------------------------------+--------------+--------------+--------------------+----------------------+----------------------+----------------------------
+ regression | public | rw_view1 | SELECT base_tbl.a, +| LOCAL | YES | YES | NO | NO | NO
+ | | | base_tbl.b +| | | | | |
+ | | | FROM base_tbl +| | | | | |
+ | | | WHERE (base_tbl.a < base_tbl.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;
+ a | b
+---+----
+ 1 | 2
+ 2 | 3
+ 1 | -1
+ 3 | 5
+ 9 | 10
+(5 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 rw_view1.a
+ FROM rw_view1
+ WHERE rw_view1.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 | is_insertable_into | is_trigger_updatable | is_trigger_deletable | is_trigger_insertable_into
+---------------+--------------+------------+----------------------------+--------------+--------------+--------------------+----------------------+----------------------+----------------------------
+ regression | public | rw_view2 | SELECT rw_view1.a +| CASCADED | YES | YES | NO | NO | NO
+ | | | FROM rw_view1 +| | | | | |
+ | | | WHERE (rw_view1.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 rw_view1.a
+ FROM rw_view1
+ WHERE rw_view1.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 | is_insertable_into | is_trigger_updatable | is_trigger_deletable | is_trigger_insertable_into
+---------------+--------------+------------+----------------------------+--------------+--------------+--------------------+----------------------+----------------------+----------------------------
+ regression | public | rw_view2 | SELECT rw_view1.a +| LOCAL | YES | YES | NO | NO | NO
+ | | | FROM rw_view1 +| | | | | |
+ | | | WHERE (rw_view1.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 rw_view1.a
+ FROM rw_view1
+ WHERE rw_view1.a < 10;
+
+SELECT * FROM information_schema.views WHERE table_name = 'rw_view2';
+ table_catalog | table_schema | table_name | view_definition | check_option | is_updatable | is_insertable_into | is_trigger_updatable | is_trigger_deletable | is_trigger_insertable_into
+---------------+--------------+------------+----------------------------+--------------+--------------+--------------------+----------------------+----------------------+----------------------------
+ regression | public | rw_view2 | SELECT rw_view1.a +| NONE | YES | YES | NO | NO | NO
+ | | | FROM rw_view1 +| | | | | |
+ | | | WHERE (rw_view1.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_name;
+ table_catalog | table_schema | table_name | view_definition | check_option | is_updatable | is_insertable_into | is_trigger_updatable | is_trigger_deletable | is_trigger_insertable_into
+---------------+--------------+------------+---------------------------+--------------+--------------+--------------------+----------------------+----------------------+----------------------------
+ regression | public | rw_view1 | SELECT base_tbl.a +| CASCADED | YES | YES | NO | NO | NO
+ | | | FROM base_tbl; | | | | | |
+ regression | public | rw_view2 | SELECT rw_view1.a +| NONE | YES | YES | NO | NO | NO
+ | | | FROM rw_view1 +| | | | | |
+ | | | WHERE (rw_view1.a > 0); | | | | | |
+ regression | public | rw_view3 | SELECT rw_view2.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).
+INSERT INTO rw_view2 VALUES (5); -- ok
+INSERT INTO rw_view2 VALUES (50); -- 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).
+SELECT * FROM base_tbl;
+ a | b
+----+----
+ 5 | 10
+ 50 | 10
+(2 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
+-----+----
+ 50 | 10
+ 100 | 10
+ 200 | 10
+(3 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
+-----+----
+ 50 | 10
+ 100 | 10
+ 200 | 10
+ -10 | 10
+ 20 | 10
+ 30 | 10
+ -5 | 10
+(7 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
+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)
+
+-- 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
+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)
+
+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 (returns $0)
+ -> Index Only Scan using base_tbl_pkey on base_tbl t
+ Index Cond: (id = 2)
+ -> Result
+ One-Time Filter: ($0 IS NOT TRUE)
+
+ Update on base_tbl
+ InitPlan 1 (returns $0)
+ -> Index Only Scan using base_tbl_pkey on base_tbl t
+ Index Cond: (id = 2)
+ -> Result
+ One-Time Filter: $0
+ -> 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 (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 (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 (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 (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: ((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: ((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: ((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: ((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 | t1 | t11d
+ 9 | 8 | t11 | t11d
+ 9 | 8 | t12 | t11d
+ 9 | 8 | t111 | 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 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).
+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 check option;
+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 NULL))
+ -> 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 default"",1,""(1,""""table default"""")"")")
+(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,""(1,""""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)
+
+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;
generated by cgit v1.2.3 (git 2.39.1) at 2024-12-02 13:52:04 +0000