summaryrefslogtreecommitdiff
path: root/src/test
diff options
context:
space:
mode:
Diffstat (limited to 'src/test')
-rw-r--r--src/test/regress/expected/alter_table.out5
-rw-r--r--src/test/regress/expected/create_view.out18
-rw-r--r--src/test/regress/expected/drop_if_exists.out1
-rw-r--r--src/test/regress/expected/plancache.out7
-rw-r--r--src/test/regress/expected/portals.out1
-rw-r--r--src/test/regress/expected/privileges.out4
-rw-r--r--src/test/regress/expected/returning.out1
-rw-r--r--src/test/regress/expected/rules.out20
-rw-r--r--src/test/regress/expected/subselect.out1
-rw-r--r--src/test/regress/expected/view_update.out370
-rw-r--r--src/test/regress/parallel_schedule4
-rw-r--r--src/test/regress/serial_schedule3
-rw-r--r--src/test/regress/sql/view_update.sql168
13 files changed, 598 insertions, 5 deletions
diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out
index 4768c8b186c..5ac24e5b3d6 100644
--- a/src/test/regress/expected/alter_table.out
+++ b/src/test/regress/expected/alter_table.out
@@ -132,6 +132,7 @@ ALTER INDEX onek_unique1 RENAME TO tmp_onek_unique1;
ALTER INDEX tmp_onek_unique1 RENAME TO onek_unique1;
-- renaming views
CREATE VIEW tmp_view (unique1) AS SELECT unique1 FROM tenk1;
+NOTICE: CREATE VIEW has created automatic view update rules
ALTER TABLE tmp_view RENAME TO tmp_view_new;
-- hack to ensure we get an indexscan here
ANALYZE tenk1;
@@ -592,6 +593,7 @@ alter table atacc1 alter oid drop not null;
ERROR: cannot alter system column "oid"
-- try creating a view and altering that, should fail
create view myview as select * from atacc1;
+NOTICE: CREATE VIEW has created automatic view update rules
alter table myview alter column test drop not null;
ERROR: "myview" is not a table
alter table myview alter column test set not null;
@@ -659,6 +661,7 @@ ERROR: column "c3" of relation "def_test" does not exist
-- to allow insertions into it, and then alter the view to add
-- a default
create view def_view_test as select * from def_test;
+NOTICE: CREATE VIEW has created automatic view update rules
create rule def_view_test_ins as
on insert to def_view_test
do instead insert into def_test select new.*;
@@ -842,6 +845,7 @@ alter table atacc1 drop xmin;
ERROR: cannot drop system column "xmin"
-- try creating a view and altering that, should fail
create view myview as select * from atacc1;
+NOTICE: CREATE VIEW has created automatic view update rules
select * from myview;
b | c | d
---+---+---
@@ -1436,6 +1440,7 @@ create table alter1.t1(f1 serial primary key, f2 int check (f2 > 0));
NOTICE: CREATE TABLE will create implicit sequence "t1_f1_seq" for serial column "t1.f1"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t1_pkey" for table "t1"
create view alter1.v1 as select * from alter1.t1;
+NOTICE: CREATE VIEW has created automatic view update rules
create function alter1.plus1(int) returns int as 'select $1+1' language sql;
create domain alter1.posint integer check (value > 0);
create type alter1.ctype as (f1 int, f2 text);
diff --git a/src/test/regress/expected/create_view.out b/src/test/regress/expected/create_view.out
index 04383e43d2d..232bacfd6d6 100644
--- a/src/test/regress/expected/create_view.out
+++ b/src/test/regress/expected/create_view.out
@@ -27,8 +27,10 @@ CREATE TABLE viewtest_tbl (a int, b int);
COPY viewtest_tbl FROM stdin;
CREATE OR REPLACE VIEW viewtest AS
SELECT * FROM viewtest_tbl;
+NOTICE: CREATE VIEW has created automatic view update rules
CREATE OR REPLACE VIEW viewtest AS
SELECT * FROM viewtest_tbl WHERE a > 10;
+NOTICE: CREATE VIEW has created automatic view update rules
SELECT * FROM viewtest;
a | b
----+----
@@ -38,6 +40,7 @@ SELECT * FROM viewtest;
CREATE OR REPLACE VIEW viewtest AS
SELECT a, b FROM viewtest_tbl WHERE a > 5 ORDER BY b DESC;
+NOTICE: CREATE VIEW has created automatic view update rules
SELECT * FROM viewtest;
a | b
----+----
@@ -71,13 +74,17 @@ SET search_path TO temp_view_test, public;
CREATE TEMPORARY TABLE temp_table (a int, id int);
-- should be created in temp_view_test schema
CREATE VIEW v1 AS SELECT * FROM base_table;
+NOTICE: CREATE VIEW has created automatic view update rules
-- should be created in temp object schema
CREATE VIEW v1_temp AS SELECT * FROM temp_table;
NOTICE: view "v1_temp" will be a temporary view
+NOTICE: CREATE VIEW has created automatic view update rules
-- should be created in temp object schema
CREATE TEMP VIEW v2_temp AS SELECT * FROM base_table;
+NOTICE: CREATE VIEW has created automatic view update rules
-- should be created in temp_views schema
CREATE VIEW temp_view_test.v2 AS SELECT * FROM base_table;
+NOTICE: CREATE VIEW has created automatic view update rules
-- should fail
CREATE VIEW temp_view_test.v3_temp AS SELECT * FROM temp_table;
NOTICE: view "v3_temp" will be a temporary view
@@ -107,18 +114,25 @@ CREATE VIEW v5_temp AS
NOTICE: view "v5_temp" will be a temporary view
-- subqueries
CREATE VIEW v4 AS SELECT * FROM base_table WHERE id IN (SELECT id FROM base_table2);
+NOTICE: CREATE VIEW has created automatic view update rules
CREATE VIEW v5 AS SELECT t1.id, t2.a FROM base_table t1, (SELECT * FROM base_table2) t2;
CREATE VIEW v6 AS SELECT * FROM base_table WHERE EXISTS (SELECT 1 FROM base_table2);
+NOTICE: CREATE VIEW has created automatic view update rules
CREATE VIEW v7 AS SELECT * FROM base_table WHERE NOT EXISTS (SELECT 1 FROM base_table2);
+NOTICE: CREATE VIEW has created automatic view update rules
CREATE VIEW v8 AS SELECT * FROM base_table WHERE EXISTS (SELECT 1);
+NOTICE: CREATE VIEW has created automatic view update rules
CREATE VIEW v6_temp AS SELECT * FROM base_table WHERE id IN (SELECT id FROM temp_table);
NOTICE: view "v6_temp" will be a temporary view
+NOTICE: CREATE VIEW has created automatic view update rules
CREATE VIEW v7_temp AS SELECT t1.id, t2.a FROM base_table t1, (SELECT * FROM temp_table) t2;
NOTICE: view "v7_temp" will be a temporary view
CREATE VIEW v8_temp AS SELECT * FROM base_table WHERE EXISTS (SELECT 1 FROM temp_table);
NOTICE: view "v8_temp" will be a temporary view
+NOTICE: CREATE VIEW has created automatic view update rules
CREATE VIEW v9_temp AS SELECT * FROM base_table WHERE NOT EXISTS (SELECT 1 FROM temp_table);
NOTICE: view "v9_temp" will be a temporary view
+NOTICE: CREATE VIEW has created automatic view update rules
-- a view should also be temporary if it references a temporary view
CREATE VIEW v10_temp AS SELECT * FROM v7_temp;
NOTICE: view "v10_temp" will be a temporary view
@@ -130,8 +144,10 @@ NOTICE: view "v12_temp" will be a temporary view
CREATE SEQUENCE seq1;
CREATE TEMPORARY SEQUENCE seq1_temp;
CREATE VIEW v9 AS SELECT seq1.is_called FROM seq1;
+NOTICE: CREATE VIEW has created automatic view update rules
CREATE VIEW v13_temp AS SELECT seq1_temp.is_called FROM seq1_temp;
NOTICE: view "v13_temp" will be a temporary view
+NOTICE: CREATE VIEW has created automatic view update rules
SELECT relname FROM pg_class
WHERE relname LIKE 'v_'
AND relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'temp_view_test')
@@ -219,6 +235,7 @@ CREATE TEMP TABLE tmptbl (i int, j int);
CREATE VIEW pubview AS SELECT * FROM tbl1 WHERE tbl1.a
BETWEEN (SELECT d FROM tbl2 WHERE c = 1) AND (SELECT e FROM tbl3 WHERE f = 2)
AND EXISTS (SELECT g FROM tbl4 LEFT JOIN tbl3 ON tbl4.h = tbl3.f);
+NOTICE: CREATE VIEW has created automatic view update rules
SELECT count(*) FROM pg_class where relname = 'pubview'
AND relnamespace IN (SELECT OID FROM pg_namespace WHERE nspname = 'testviewschm2');
count
@@ -232,6 +249,7 @@ BETWEEN (SELECT d FROM tbl2 WHERE c = 1) AND (SELECT e FROM tbl3 WHERE f = 2)
AND EXISTS (SELECT g FROM tbl4 LEFT JOIN tbl3 ON tbl4.h = tbl3.f)
AND NOT EXISTS (SELECT g FROM tbl4 LEFT JOIN tmptbl ON tbl4.h = tmptbl.j);
NOTICE: view "mytempview" will be a temporary view
+NOTICE: CREATE VIEW has created automatic view update rules
SELECT count(*) FROM pg_class where relname LIKE 'mytempview'
And relnamespace IN (SELECT OID FROM pg_namespace WHERE nspname LIKE 'pg_temp%');
count
diff --git a/src/test/regress/expected/drop_if_exists.out b/src/test/regress/expected/drop_if_exists.out
index 092c90403a0..90f1bb86e11 100644
--- a/src/test/regress/expected/drop_if_exists.out
+++ b/src/test/regress/expected/drop_if_exists.out
@@ -13,6 +13,7 @@ ERROR: view "test_view_exists" does not exist
DROP VIEW IF EXISTS test_view_exists;
NOTICE: view "test_view_exists" does not exist, skipping
CREATE VIEW test_view_exists AS select * from test_exists;
+NOTICE: CREATE VIEW has created automatic view update rules
DROP VIEW IF EXISTS test_view_exists;
DROP VIEW test_view_exists;
ERROR: view "test_view_exists" does not exist
diff --git a/src/test/regress/expected/plancache.out b/src/test/regress/expected/plancache.out
index c0681d26e38..853ae94fdba 100644
--- a/src/test/regress/expected/plancache.out
+++ b/src/test/regress/expected/plancache.out
@@ -79,6 +79,7 @@ EXECUTE prepstmt2(123);
-- but should trigger invalidation anyway
CREATE TEMP VIEW pcacheview AS
SELECT * FROM pcachetest;
+NOTICE: CREATE VIEW has created automatic view update rules
PREPARE vprep AS SELECT * FROM pcacheview;
EXECUTE vprep;
q1 | q2
@@ -236,6 +237,9 @@ select cachebug();
NOTICE: table "temptable" does not exist, skipping
CONTEXT: SQL statement "drop table if exists temptable cascade"
PL/pgSQL function "cachebug" line 3 at SQL statement
+NOTICE: CREATE VIEW has created automatic view update rules
+CONTEXT: SQL statement "create temp view vv as select * from temptable"
+PL/pgSQL function "cachebug" line 5 at SQL statement
NOTICE: 1
NOTICE: 2
NOTICE: 3
@@ -248,6 +252,9 @@ select cachebug();
NOTICE: drop cascades to view vv
CONTEXT: SQL statement "drop table if exists temptable cascade"
PL/pgSQL function "cachebug" line 3 at SQL statement
+NOTICE: CREATE VIEW has created automatic view update rules
+CONTEXT: SQL statement "create temp view vv as select * from temptable"
+PL/pgSQL function "cachebug" line 5 at SQL statement
NOTICE: 1
NOTICE: 2
NOTICE: 3
diff --git a/src/test/regress/expected/portals.out b/src/test/regress/expected/portals.out
index 95dcea5a1d9..be5c476548c 100644
--- a/src/test/regress/expected/portals.out
+++ b/src/test/regress/expected/portals.out
@@ -1229,6 +1229,7 @@ ROLLBACK;
-- WHERE CURRENT OF may someday work with views, but today is not that day.
-- For now, just make sure it errors out cleanly.
CREATE TEMP VIEW ucview AS SELECT * FROM uctest;
+NOTICE: CREATE VIEW has created automatic view update rules
CREATE RULE ucrule AS ON DELETE TO ucview DO INSTEAD
DELETE FROM uctest WHERE f1 = OLD.f1;
BEGIN;
diff --git a/src/test/regress/expected/privileges.out b/src/test/regress/expected/privileges.out
index 607c591c0e6..f4a2bd8d8f5 100644
--- a/src/test/regress/expected/privileges.out
+++ b/src/test/regress/expected/privileges.out
@@ -189,9 +189,12 @@ DELETE FROM atest3; -- ok
-- views
SET SESSION AUTHORIZATION regressuser3;
CREATE VIEW atestv1 AS SELECT * FROM atest1; -- ok
+NOTICE: CREATE VIEW has created automatic view update rules
/* The next *should* fail, but it's not implemented that way yet. */
CREATE VIEW atestv2 AS SELECT * FROM atest2;
+NOTICE: CREATE VIEW has created automatic view update rules
CREATE VIEW atestv3 AS SELECT * FROM atest3; -- ok
+NOTICE: CREATE VIEW has created automatic view update rules
SELECT * FROM atestv1; -- ok
a | b
---+-----
@@ -219,6 +222,7 @@ SELECT * FROM atestv3; -- ok
(0 rows)
CREATE VIEW atestv4 AS SELECT * FROM atestv3; -- nested view
+NOTICE: CREATE VIEW has created automatic view update rules
SELECT * FROM atestv4; -- ok
one | two | three
-----+-----+-------
diff --git a/src/test/regress/expected/returning.out b/src/test/regress/expected/returning.out
index b04f6f1a0ef..05afef0f197 100644
--- a/src/test/regress/expected/returning.out
+++ b/src/test/regress/expected/returning.out
@@ -195,6 +195,7 @@ SELECT * FROM foochild;
DROP TABLE foochild;
-- Rules and views
CREATE TEMP VIEW voo AS SELECT f1, f2 FROM foo;
+NOTICE: CREATE VIEW has created automatic view update rules
CREATE RULE voo_i AS ON INSERT TO voo DO INSTEAD
INSERT INTO foo VALUES(new.*, 57);
INSERT INTO voo VALUES(11,'zit');
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 2daa79d732d..e0618abd676 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -10,6 +10,7 @@ create table rtest_t1 (a int4, b int4);
create table rtest_t2 (a int4, b int4);
create table rtest_t3 (a int4, b int4);
create view rtest_v1 as select * from rtest_t1;
+NOTICE: CREATE VIEW has created automatic view update rules
create rule rtest_v1_ins as on insert to rtest_v1 do instead
insert into rtest_t1 values (new.a, new.b);
create rule rtest_v1_upd as on update to rtest_v1 do instead
@@ -755,9 +756,12 @@ create table rtest_view3 (a int4, b text);
create table rtest_view4 (a int4, b text, c int4);
create view rtest_vview1 as select a, b from rtest_view1 X
where 0 < (select count(*) from rtest_view2 Y where Y.a = X.a);
+NOTICE: CREATE VIEW has created automatic view update rules
create view rtest_vview2 as select a, b from rtest_view1 where v;
+NOTICE: CREATE VIEW has created automatic view update rules
create view rtest_vview3 as select a, b from rtest_vview2 X
where 0 < (select count(*) from rtest_view2 Y where Y.a = X.a);
+NOTICE: CREATE VIEW has created automatic view update rules
create view rtest_vview4 as select X.a, X.b, count(Y.a) as refcount
from rtest_view1 X, rtest_view2 Y
where X.a = Y.a
@@ -1333,8 +1337,8 @@ SELECT viewname, definition FROM pg_views WHERE schemaname <> 'information_schem
SELECT tablename, rulename, definition FROM pg_rules
ORDER BY tablename, rulename;
- tablename | rulename | definition
----------------+-----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ tablename | rulename | definition
+---------------+-----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
pg_settings | pg_settings_n | CREATE RULE pg_settings_n AS ON UPDATE TO pg_settings DO INSTEAD NOTHING;
pg_settings | pg_settings_u | CREATE RULE pg_settings_u AS ON UPDATE TO pg_settings WHERE (new.name = old.name) DO SELECT set_config(old.name, new.setting, false) AS set_config;
rtest_emp | rtest_emp_del | CREATE RULE rtest_emp_del AS ON DELETE TO rtest_emp DO INSERT INTO rtest_emplog (ename, who, action, newsal, oldsal) VALUES (old.ename, "current_user"(), 'fired'::bpchar, '$0.00'::money, old.salary);
@@ -1359,12 +1363,21 @@ SELECT tablename, rulename, definition FROM pg_rules
rtest_v1 | rtest_v1_del | CREATE RULE rtest_v1_del AS ON DELETE TO rtest_v1 DO INSTEAD DELETE FROM rtest_t1 WHERE (rtest_t1.a = old.a);
rtest_v1 | rtest_v1_ins | CREATE RULE rtest_v1_ins AS ON INSERT TO rtest_v1 DO INSTEAD INSERT INTO rtest_t1 (a, b) VALUES (new.a, new.b);
rtest_v1 | rtest_v1_upd | CREATE RULE rtest_v1_upd AS ON UPDATE TO rtest_v1 DO INSTEAD UPDATE rtest_t1 SET a = new.a, b = new.b WHERE (rtest_t1.a = old.a);
+ rtest_vview1 | _DELETE | CREATE RULE "_DELETE" AS ON DELETE TO rtest_vview1 DO INSTEAD DELETE FROM rtest_view1 x WHERE ((((old.a IS NULL) AND (x.a IS NULL)) OR (old.a = x.a)) AND (((old.b IS NULL) AND (x.b IS NULL)) OR (old.b = x.b))) RETURNING old.a, old.b;
+ rtest_vview1 | _INSERT | CREATE RULE "_INSERT" AS ON INSERT TO rtest_vview1 DO INSTEAD INSERT INTO rtest_view1 (a, b) VALUES (new.a, new.b) RETURNING new.a, new.b;
+ rtest_vview1 | _UPDATE | CREATE RULE "_UPDATE" AS ON UPDATE TO rtest_vview1 DO INSTEAD UPDATE rtest_view1 x SET a = new.a, b = new.b WHERE ((((old.a IS NULL) AND (x.a IS NULL)) OR (old.a = x.a)) AND (((old.b IS NULL) AND (x.b IS NULL)) OR (old.b = x.b))) RETURNING new.a, new.b;
+ rtest_vview2 | _DELETE | CREATE RULE "_DELETE" AS ON DELETE TO rtest_vview2 DO INSTEAD DELETE FROM rtest_view1 WHERE ((((old.a IS NULL) AND (rtest_view1.a IS NULL)) OR (old.a = rtest_view1.a)) AND (((old.b IS NULL) AND (rtest_view1.b IS NULL)) OR (old.b = rtest_view1.b))) RETURNING old.a, old.b;
+ rtest_vview2 | _INSERT | CREATE RULE "_INSERT" AS ON INSERT TO rtest_vview2 DO INSTEAD INSERT INTO rtest_view1 (a, b) VALUES (new.a, new.b) RETURNING new.a, new.b;
+ rtest_vview2 | _UPDATE | CREATE RULE "_UPDATE" AS ON UPDATE TO rtest_vview2 DO INSTEAD UPDATE rtest_view1 SET a = new.a, b = new.b WHERE ((((old.a IS NULL) AND (rtest_view1.a IS NULL)) OR (old.a = rtest_view1.a)) AND (((old.b IS NULL) AND (rtest_view1.b IS NULL)) OR (old.b = rtest_view1.b))) RETURNING new.a, new.b;
+ rtest_vview3 | _DELETE | CREATE RULE "_DELETE" AS ON DELETE TO rtest_vview3 DO INSTEAD DELETE FROM rtest_vview2 x WHERE ((((old.a IS NULL) AND (x.a IS NULL)) OR (old.a = x.a)) AND (((old.b IS NULL) AND (x.b IS NULL)) OR (old.b = x.b))) RETURNING old.a, old.b;
+ rtest_vview3 | _INSERT | CREATE RULE "_INSERT" AS ON INSERT TO rtest_vview3 DO INSTEAD INSERT INTO rtest_vview2 (a, b) VALUES (new.a, new.b) RETURNING new.a, new.b;
+ rtest_vview3 | _UPDATE | CREATE RULE "_UPDATE" AS ON UPDATE TO rtest_vview3 DO INSTEAD UPDATE rtest_vview2 x SET a = new.a, b = new.b WHERE ((((old.a IS NULL) AND (x.a IS NULL)) OR (old.a = x.a)) AND (((old.b IS NULL) AND (x.b IS NULL)) OR (old.b = x.b))) RETURNING new.a, new.b;
shoelace | shoelace_del | CREATE RULE shoelace_del AS ON DELETE TO shoelace DO INSTEAD DELETE FROM shoelace_data WHERE (shoelace_data.sl_name = old.sl_name);
shoelace | shoelace_ins | CREATE RULE shoelace_ins AS ON INSERT TO shoelace DO INSTEAD INSERT INTO shoelace_data (sl_name, sl_avail, sl_color, sl_len, sl_unit) VALUES (new.sl_name, new.sl_avail, new.sl_color, new.sl_len, new.sl_unit);
shoelace | shoelace_upd | CREATE RULE shoelace_upd AS ON UPDATE TO shoelace DO INSTEAD UPDATE shoelace_data SET sl_name = new.sl_name, sl_avail = new.sl_avail, sl_color = new.sl_color, sl_len = new.sl_len, sl_unit = new.sl_unit WHERE (shoelace_data.sl_name = old.sl_name);
shoelace_data | log_shoelace | CREATE RULE log_shoelace AS ON UPDATE TO shoelace_data WHERE (new.sl_avail <> old.sl_avail) DO INSERT INTO shoelace_log (sl_name, sl_avail, log_who, log_when) VALUES (new.sl_name, new.sl_avail, 'Al Bundy'::name, 'Thu Jan 01 00:00:00 1970'::timestamp without time zone);
shoelace_ok | shoelace_ok_ins | CREATE RULE shoelace_ok_ins AS ON INSERT TO shoelace_ok DO INSTEAD UPDATE shoelace SET sl_avail = (shoelace.sl_avail + new.ok_quant) WHERE (shoelace.sl_name = new.ok_name);
-(29 rows)
+(38 rows)
--
-- CREATE OR REPLACE RULE
@@ -1466,6 +1479,7 @@ insert into test_2 (name) values ('Test 4');
insert into test_3 (name) values ('Test 5');
insert into test_3 (name) values ('Test 6');
create view id_ordered as select * from id order by id;
+NOTICE: CREATE VIEW has created automatic view update rules
create rule update_id_ordered as on update to id_ordered
do instead update id set name = new.name where id = old.id;
select * from id_ordered;
diff --git a/src/test/regress/expected/subselect.out b/src/test/regress/expected/subselect.out
index f6dbc0212cd..b30fdcbb80f 100644
--- a/src/test/regress/expected/subselect.out
+++ b/src/test/regress/expected/subselect.out
@@ -349,6 +349,7 @@ create temp table shipped (
);
create temp view shipped_view as
select * from shipped where ttype = 'wt';
+NOTICE: CREATE VIEW has created automatic view update rules
create rule shipped_view_insert as on insert to shipped_view do instead
insert into shipped values('wt', new.ordnum, new.partnum, new.value);
insert into parts (partnum, cost) values (1, 1234.56);
diff --git a/src/test/regress/expected/view_update.out b/src/test/regress/expected/view_update.out
new file mode 100644
index 00000000000..09c2d4f6f51
--- /dev/null
+++ b/src/test/regress/expected/view_update.out
@@ -0,0 +1,370 @@
+CREATE TABLE vutest1 (a integer, b text);
+INSERT INTO vutest1 VALUES (1, 'one');
+INSERT INTO vutest1 VALUES (2, 'two');
+-- simple view updatability conditions
+CREATE VIEW vutestv1 AS SELECT a, b FROM vutest1;
+NOTICE: CREATE VIEW has created automatic view update rules
+CREATE VIEW vutestv2 AS SELECT * FROM vutest1;
+NOTICE: CREATE VIEW has created automatic view update rules
+CREATE VIEW vutestv3 AS SELECT b, a FROM vutest1;
+NOTICE: CREATE VIEW has created automatic view update rules
+CREATE VIEW vutestv4 AS SELECT a, b FROM vutest1 WHERE a < 5;
+NOTICE: CREATE VIEW has created automatic view update rules
+-- not updatable tests:
+CREATE VIEW vutestv5 AS SELECT sum(a) FROM vutest1; -- aggregate function
+CREATE VIEW vutestv6 AS SELECT b FROM vutest1 GROUP BY b; -- GROUP BY
+CREATE VIEW vutestv7 AS SELECT l.b AS x, r.b AS y FROM vutest1 l, vutest1 r WHERE r.a = l.a; -- JOIN
+CREATE VIEW vutestv8 AS SELECT 42; -- no table
+CREATE VIEW vutestv9 AS SELECT a * 2 AS x, b || b AS y FROM vutest1; -- derived columns
+CREATE VIEW vutestv10 AS SELECT a AS x, a AS y FROM vutest1; -- column referenced more than once
+CREATE VIEW vutestv11 AS SELECT * FROM generate_series(1, 5); -- table function
+CREATE VIEW vutestv12 AS SELECT xmin, xmax, a, b FROM vutest1; -- system columns
+CREATE VIEW vutestv13 AS SELECT DISTINCT a, b FROM vutest1; -- DISTINCT
+CREATE VIEW vutestv14 AS SELECT a, b FROM vutest1 WHERE a > (SELECT avg(a) FROM vutest1); -- *is* updatable, but SQL standard disallows this
+NOTICE: CREATE VIEW has created automatic view update rules
+CREATE VIEW vutestv15 AS SELECT a, b FROM vutest1 UNION ALL SELECT a, b FROM vutest1; -- UNION
+CREATE VIEW vutestv16 AS SELECT x, y FROM (SELECT * FROM vutest1) AS foo (x, y); -- subquery ("derived table"); SQL standard allows this
+CREATE VIEW vutestv17 AS SELECT a, 5, b FROM vutest1; -- constant
+CREATE VIEW vutestv18 AS SELECT a, b FROM vutest1 LIMIT 1; -- LIMIT
+CREATE VIEW vutestv19 AS SELECT a, b FROM vutest1 OFFSET 1; -- OFFSET
+CREATE VIEW vutestv101 AS SELECT a, rank() OVER (PARTITION BY a ORDER BY b DESC) FROM vutest1; -- window function
+CREATE VIEW vutestv102 AS WITH foo AS (SELECT a, b FROM vutest1) SELECT * FROM foo; -- SQL standard allows this
+CREATE VIEW vutestv103 AS WITH RECURSIVE t(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM t) SELECT a FROM vutest1; -- recursive
+INSERT INTO vutestv1 VALUES (3, 'three');
+INSERT INTO vutestv2 VALUES (4, 'four');
+INSERT INTO vutestv3 VALUES (5, 'five'); -- fail
+ERROR: invalid input syntax for integer: "five"
+LINE 1: INSERT INTO vutestv3 VALUES (5, 'five');
+ ^
+INSERT INTO vutestv3 VALUES ('five', 5);
+INSERT INTO vutestv3 (a, b) VALUES (6, 'six');
+INSERT INTO vutestv4 VALUES (7, 'seven'); -- ok, but would be check option issue
+INSERT INTO vutestv5 VALUES (8); -- fail
+ERROR: view is not updatable
+HINT: You need an unconditional ON INSERT DO INSTEAD rule.
+SELECT * FROM vutest1;
+ a | b
+---+-------
+ 1 | one
+ 2 | two
+ 3 | three
+ 4 | four
+ 5 | five
+ 6 | six
+ 7 | seven
+(7 rows)
+
+SELECT * FROM vutestv1;
+ a | b
+---+-------
+ 1 | one
+ 2 | two
+ 3 | three
+ 4 | four
+ 5 | five
+ 6 | six
+ 7 | seven
+(7 rows)
+
+SELECT * FROM vutestv2;
+ a | b
+---+-------
+ 1 | one
+ 2 | two
+ 3 | three
+ 4 | four
+ 5 | five
+ 6 | six
+ 7 | seven
+(7 rows)
+
+SELECT * FROM vutestv3;
+ b | a
+-------+---
+ one | 1
+ two | 2
+ three | 3
+ four | 4
+ five | 5
+ six | 6
+ seven | 7
+(7 rows)
+
+SELECT * FROM vutestv4;
+ a | b
+---+-------
+ 1 | one
+ 2 | two
+ 3 | three
+ 4 | four
+(4 rows)
+
+SELECT * FROM vutestv5;
+ sum
+-----
+ 28
+(1 row)
+
+UPDATE vutestv1 SET b = 'a lot' WHERE a = 7;
+DELETE FROM vutestv2 WHERE a = 1;
+UPDATE vutestv4 SET b = b || '!' WHERE a > 1;
+DELETE FROM vutestv4 WHERE a > 3;
+UPDATE vutestv6 SET b = 37; -- fail
+ERROR: view is not updatable
+HINT: You need an unconditional ON UPDATE DO INSTEAD rule.
+DELETE FROM vutestv5; -- fail
+ERROR: view is not updatable
+HINT: You need an unconditional ON DELETE DO INSTEAD rule.
+SELECT * FROM vutest1 ORDER BY a, b;
+ a | b
+---+--------
+ 2 | two!
+ 3 | three!
+ 5 | five
+ 6 | six
+ 7 | a lot
+(5 rows)
+
+SELECT * FROM vutestv1 ORDER BY a, b;
+ a | b
+---+--------
+ 2 | two!
+ 3 | three!
+ 5 | five
+ 6 | six
+ 7 | a lot
+(5 rows)
+
+SELECT * FROM vutestv2 ORDER BY a, b;
+ a | b
+---+--------
+ 2 | two!
+ 3 | three!
+ 5 | five
+ 6 | six
+ 7 | a lot
+(5 rows)
+
+SELECT * FROM vutestv4 ORDER BY a, b;
+ a | b
+---+--------
+ 2 | two!
+ 3 | three!
+(2 rows)
+
+TRUNCATE TABLE vutest1;
+-- views on views
+CREATE VIEW vutestv20 AS SELECT a AS x, b AS y FROM vutestv1;
+NOTICE: CREATE VIEW has created automatic view update rules
+CREATE VIEW vutestv21 AS SELECT x AS a FROM vutestv20 WHERE x % 2 = 0;
+NOTICE: CREATE VIEW has created automatic view update rules
+CREATE VIEW vutestv22 AS SELECT sum(a) FROM vutestv21; -- not updatable
+CREATE VIEW vutestv23 AS SELECT * FROM vutestv12; -- not updatable
+INSERT INTO vutestv20 (x, y) VALUES (1, 'one');
+INSERT INTO vutestv20 (x, y) VALUES (3, 'three');
+INSERT INTO vutestv21 VALUES (2);
+SELECT * FROM vutest1;
+ a | b
+---+-------
+ 1 | one
+ 3 | three
+ 2 |
+(3 rows)
+
+SELECT * FROM vutestv20;
+ x | y
+---+-------
+ 1 | one
+ 3 | three
+ 2 |
+(3 rows)
+
+SELECT * FROM vutestv21;
+ a
+---
+ 2
+(1 row)
+
+UPDATE vutestv20 SET y = 'eins' WHERE x = 1;
+UPDATE vutestv21 SET a = 222;
+SELECT * FROM vutest1;
+ a | b
+-----+-------
+ 3 | three
+ 1 | eins
+ 222 |
+(3 rows)
+
+SELECT * FROM vutestv20;
+ x | y
+-----+-------
+ 3 | three
+ 1 | eins
+ 222 |
+(3 rows)
+
+SELECT * FROM vutestv21;
+ a
+-----
+ 222
+(1 row)
+
+DELETE FROM vutestv20 WHERE x = 3;
+SELECT * FROM vutest1;
+ a | b
+-----+------
+ 1 | eins
+ 222 |
+(2 rows)
+
+SELECT * FROM vutestv20;
+ x | y
+-----+------
+ 1 | eins
+ 222 |
+(2 rows)
+
+SELECT * FROM vutestv21;
+ a
+-----
+ 222
+(1 row)
+
+-- insert tests
+CREATE TABLE vutest2 (a int PRIMARY KEY, b text NOT NULL, c text NOT NULL DEFAULT 'foo');
+NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "vutest2_pkey" for table "vutest2"
+CREATE VIEW vutestv30 AS SELECT a, b, c FROM vutest2;
+NOTICE: CREATE VIEW has created automatic view update rules
+CREATE VIEW vutestv31 AS SELECT a, b FROM vutest2;
+NOTICE: CREATE VIEW has created automatic view update rules
+CREATE VIEW vutestv32 AS SELECT a, c FROM vutest2;
+NOTICE: CREATE VIEW has created automatic view update rules
+INSERT INTO vutestv30 VALUES (1, 'one', 'eins');
+INSERT INTO vutestv31 VALUES (2, 'two');
+INSERT INTO vutestv32 VALUES (3, 'drei'); -- fail
+ERROR: null value in column "b" violates not-null constraint
+UPDATE vutestv31 SET a = 22 WHERE a = 2;
+UPDATE vutestv32 SET c = 'drei!' WHERE a = 3;
+SELECT rulename, definition FROM pg_rules WHERE tablename LIKE 'vutestv%' ORDER BY tablename, rulename;
+ rulename | definition
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ _DELETE | CREATE RULE "_DELETE" AS ON DELETE TO vutestv1 DO INSTEAD DELETE FROM vutest1 WHERE ((((old.a IS NULL) AND (vutest1.a IS NULL)) OR (old.a = vutest1.a)) AND (((old.b IS NULL) AND (vutest1.b IS NULL)) OR (old.b = vutest1.b))) RETURNING old.a, old.b;
+ _INSERT | CREATE RULE "_INSERT" AS ON INSERT TO vutestv1 DO INSTEAD INSERT INTO vutest1 (a, b) VALUES (new.a, new.b) RETURNING new.a, new.b;
+ _UPDATE | CREATE RULE "_UPDATE" AS ON UPDATE TO vutestv1 DO INSTEAD UPDATE vutest1 SET a = new.a, b = new.b WHERE ((((old.a IS NULL) AND (vutest1.a IS NULL)) OR (old.a = vutest1.a)) AND (((old.b IS NULL) AND (vutest1.b IS NULL)) OR (old.b = vutest1.b))) RETURNING new.a, new.b;
+ _DELETE | CREATE RULE "_DELETE" AS ON DELETE TO vutestv14 DO INSTEAD DELETE FROM vutest1 WHERE ((((old.a IS NULL) AND (vutest1.a IS NULL)) OR (old.a = vutest1.a)) AND (((old.b IS NULL) AND (vutest1.b IS NULL)) OR (old.b = vutest1.b))) RETURNING old.a, old.b;
+ _INSERT | CREATE RULE "_INSERT" AS ON INSERT TO vutestv14 DO INSTEAD INSERT INTO vutest1 (a, b) VALUES (new.a, new.b) RETURNING new.a, new.b;
+ _UPDATE | CREATE RULE "_UPDATE" AS ON UPDATE TO vutestv14 DO INSTEAD UPDATE vutest1 SET a = new.a, b = new.b WHERE ((((old.a IS NULL) AND (vutest1.a IS NULL)) OR (old.a = vutest1.a)) AND (((old.b IS NULL) AND (vutest1.b IS NULL)) OR (old.b = vutest1.b))) RETURNING new.a, new.b;
+ _DELETE | CREATE RULE "_DELETE" AS ON DELETE TO vutestv2 DO INSTEAD DELETE FROM vutest1 WHERE ((((old.a IS NULL) AND (vutest1.a IS NULL)) OR (old.a = vutest1.a)) AND (((old.b IS NULL) AND (vutest1.b IS NULL)) OR (old.b = vutest1.b))) RETURNING old.a, old.b;
+ _INSERT | CREATE RULE "_INSERT" AS ON INSERT TO vutestv2 DO INSTEAD INSERT INTO vutest1 (a, b) VALUES (new.a, new.b) RETURNING new.a, new.b;
+ _UPDATE | CREATE RULE "_UPDATE" AS ON UPDATE TO vutestv2 DO INSTEAD UPDATE vutest1 SET a = new.a, b = new.b WHERE ((((old.a IS NULL) AND (vutest1.a IS NULL)) OR (old.a = vutest1.a)) AND (((old.b IS NULL) AND (vutest1.b IS NULL)) OR (old.b = vutest1.b))) RETURNING new.a, new.b;
+ _DELETE | CREATE RULE "_DELETE" AS ON DELETE TO vutestv20 DO INSTEAD DELETE FROM vutestv1 WHERE ((((old.x IS NULL) AND (vutestv1.a IS NULL)) OR (old.x = vutestv1.a)) AND (((old.y IS NULL) AND (vutestv1.b IS NULL)) OR (old.y = vutestv1.b))) RETURNING old.x, old.y;
+ _INSERT | CREATE RULE "_INSERT" AS ON INSERT TO vutestv20 DO INSTEAD INSERT INTO vutestv1 (a, b) VALUES (new.x, new.y) RETURNING new.x AS a, new.y AS b;
+ _UPDATE | CREATE RULE "_UPDATE" AS ON UPDATE TO vutestv20 DO INSTEAD UPDATE vutestv1 SET a = new.x, b = new.y WHERE ((((old.x IS NULL) AND (vutestv1.a IS NULL)) OR (old.x = vutestv1.a)) AND (((old.y IS NULL) AND (vutestv1.b IS NULL)) OR (old.y = vutestv1.b))) RETURNING new.x AS a, new.y AS b;
+ _DELETE | CREATE RULE "_DELETE" AS ON DELETE TO vutestv21 DO INSTEAD DELETE FROM vutestv20 WHERE ((((old.a IS NULL) AND (vutestv20.x IS NULL)) OR (old.a = vutestv20.x))) RETURNING old.a;
+ _INSERT | CREATE RULE "_INSERT" AS ON INSERT TO vutestv21 DO INSTEAD INSERT INTO vutestv20 (x) VALUES (new.a) RETURNING new.a AS x;
+ _UPDATE | CREATE RULE "_UPDATE" AS ON UPDATE TO vutestv21 DO INSTEAD UPDATE vutestv20 SET x = new.a WHERE ((((old.a IS NULL) AND (vutestv20.x IS NULL)) OR (old.a = vutestv20.x))) RETURNING new.a AS x;
+ _DELETE | CREATE RULE "_DELETE" AS ON DELETE TO vutestv3 DO INSTEAD DELETE FROM vutest1 WHERE ((((old.b IS NULL) AND (vutest1.b IS NULL)) OR (old.b = vutest1.b)) AND (((old.a IS NULL) AND (vutest1.a IS NULL)) OR (old.a = vutest1.a))) RETURNING old.b, old.a;
+ _INSERT | CREATE RULE "_INSERT" AS ON INSERT TO vutestv3 DO INSTEAD INSERT INTO vutest1 (b, a) VALUES (new.b, new.a) RETURNING new.a AS b, new.b AS a;
+ _UPDATE | CREATE RULE "_UPDATE" AS ON UPDATE TO vutestv3 DO INSTEAD UPDATE vutest1 SET b = new.b, a = new.a WHERE ((((old.b IS NULL) AND (vutest1.b IS NULL)) OR (old.b = vutest1.b)) AND (((old.a IS NULL) AND (vutest1.a IS NULL)) OR (old.a = vutest1.a))) RETURNING new.a AS b, new.b AS a;
+ _DELETE | CREATE RULE "_DELETE" AS ON DELETE TO vutestv30 DO INSTEAD DELETE FROM vutest2 WHERE ((((old.a IS NULL) AND (vutest2.a IS NULL)) OR (old.a = vutest2.a)) AND (((old.b IS NULL) AND (vutest2.b IS NULL)) OR (old.b = vutest2.b) OR (((old.c IS NULL) AND (vutest2.c IS NULL)) OR (old.c = vutest2.c)))) RETURNING old.a, old.b, old.c;
+ _INSERT | CREATE RULE "_INSERT" AS ON INSERT TO vutestv30 DO INSTEAD INSERT INTO vutest2 (a, b, c) VALUES (new.a, new.b, new.c) RETURNING new.a, new.b, new.c;
+ _UPDATE | CREATE RULE "_UPDATE" AS ON UPDATE TO vutestv30 DO INSTEAD UPDATE vutest2 SET a = new.a, b = new.b, c = new.c WHERE ((((old.a IS NULL) AND (vutest2.a IS NULL)) OR (old.a = vutest2.a)) AND (((old.b IS NULL) AND (vutest2.b IS NULL)) OR (old.b = vutest2.b) OR (((old.c IS NULL) AND (vutest2.c IS NULL)) OR (old.c = vutest2.c)))) RETURNING new.a, new.b, new.c;
+ _DELETE | CREATE RULE "_DELETE" AS ON DELETE TO vutestv31 DO INSTEAD DELETE FROM vutest2 WHERE ((((old.a IS NULL) AND (vutest2.a IS NULL)) OR (old.a = vutest2.a)) AND (((old.b IS NULL) AND (vutest2.b IS NULL)) OR (old.b = vutest2.b))) RETURNING old.a, old.b;
+ _INSERT | CREATE RULE "_INSERT" AS ON INSERT TO vutestv31 DO INSTEAD INSERT INTO vutest2 (a, b) VALUES (new.a, new.b) RETURNING new.a, new.b;
+ _UPDATE | CREATE RULE "_UPDATE" AS ON UPDATE TO vutestv31 DO INSTEAD UPDATE vutest2 SET a = new.a, b = new.b WHERE ((((old.a IS NULL) AND (vutest2.a IS NULL)) OR (old.a = vutest2.a)) AND (((old.b IS NULL) AND (vutest2.b IS NULL)) OR (old.b = vutest2.b))) RETURNING new.a, new.b;
+ _DELETE | CREATE RULE "_DELETE" AS ON DELETE TO vutestv32 DO INSTEAD DELETE FROM vutest2 WHERE ((((old.a IS NULL) AND (vutest2.a IS NULL)) OR (old.a = vutest2.a)) AND (((old.c IS NULL) AND (vutest2.c IS NULL)) OR (old.c = vutest2.c))) RETURNING old.a, old.c;
+ _INSERT | CREATE RULE "_INSERT" AS ON INSERT TO vutestv32 DO INSTEAD INSERT INTO vutest2 (a, c) VALUES (new.a, new.c) RETURNING new.a, new.c;
+ _UPDATE | CREATE RULE "_UPDATE" AS ON UPDATE TO vutestv32 DO INSTEAD UPDATE vutest2 SET a = new.a, c = new.c WHERE ((((old.a IS NULL) AND (vutest2.a IS NULL)) OR (old.a = vutest2.a)) AND (((old.c IS NULL) AND (vutest2.c IS NULL)) OR (old.c = vutest2.c))) RETURNING new.a, new.c;
+ _DELETE | CREATE RULE "_DELETE" AS ON DELETE TO vutestv4 DO INSTEAD DELETE FROM vutest1 WHERE ((((old.a IS NULL) AND (vutest1.a IS NULL)) OR (old.a = vutest1.a)) AND (((old.b IS NULL) AND (vutest1.b IS NULL)) OR (old.b = vutest1.b))) RETURNING old.a, old.b;
+ _INSERT | CREATE RULE "_INSERT" AS ON INSERT TO vutestv4 DO INSTEAD INSERT INTO vutest1 (a, b) VALUES (new.a, new.b) RETURNING new.a, new.b;
+ _UPDATE | CREATE RULE "_UPDATE" AS ON UPDATE TO vutestv4 DO INSTEAD UPDATE vutest1 SET a = new.a, b = new.b WHERE ((((old.a IS NULL) AND (vutest1.a IS NULL)) OR (old.a = vutest1.a)) AND (((old.b IS NULL) AND (vutest1.b IS NULL)) OR (old.b = vutest1.b))) RETURNING new.a, new.b;
+(30 rows)
+
+-- interaction of manual and automatic rules, view replacement
+CREATE VIEW vutestv40 AS SELECT a, b FROM vutest1;
+NOTICE: CREATE VIEW has created automatic view update rules
+CREATE RULE zmy_update AS ON UPDATE TO vutestv40 DO INSTEAD DELETE FROM vutest1; -- drops automatic _UPDATE rule
+CREATE RULE "_INSERT" AS ON INSERT TO vutestv40 DO INSTEAD DELETE FROM vutest1; -- replaces automatic _INSERT rule
+CREATE RULE zmy_delete AS ON DELETE TO vutestv40 DO ALSO DELETE FROM vutest1; -- leaves automatic _DELETE rule (because of ALSO)
+CREATE VIEW vutestv41 AS SELECT a + 1 AS aa, b FROM vutest1; -- not updatable
+CREATE RULE "_UPDATE" AS ON UPDATE TO vutestv41 DO INSTEAD UPDATE vutest1 SET a = new.aa - 1, b = new.b WHERE a = old.aa - 1 AND b = old.b;
+CREATE OR REPLACE VIEW vutestv41 AS SELECT a AS aa, b FROM vutest1; -- *now* updatable, manual _UPDATE rule stays
+WARNING: automatic UPDATE rule not created because manually created UPDATE rule exists
+HINT: If you prefer to have the automatic rule, drop the manually created rule and run CREATE OR REPLACE VIEW again.
+NOTICE: CREATE VIEW has created automatic view update rules
+CREATE VIEW vutestv42 AS SELECT a + 1 AS aa, b FROM vutest1; -- not updatable
+CREATE RULE zmy_update AS ON UPDATE TO vutestv42 DO INSTEAD UPDATE vutest1 SET a = new.aa - 1, b = new.b WHERE a = old.aa - 1 AND b = old.b;
+CREATE OR REPLACE VIEW vutestv42 AS SELECT a AS aa, b FROM vutest1; -- *now* updatable, zmy_update stays, no _UPDATE created
+WARNING: automatic UPDATE rule not created because manually created UPDATE rule exists
+HINT: If you prefer to have the automatic rule, drop the manually created rule and run CREATE OR REPLACE VIEW again.
+NOTICE: CREATE VIEW has created automatic view update rules
+CREATE VIEW vutestv43 AS SELECT a AS aa, b FROM vutest1; -- updatable
+NOTICE: CREATE VIEW has created automatic view update rules
+CREATE RULE zmy_update AS ON UPDATE TO vutestv43 DO INSTEAD DELETE FROM vutest1; -- drops automatic _UPDATE rule
+CREATE OR REPLACE VIEW vutestv43 AS SELECT a + 1 AS aa, b FROM vutest1; -- no longer updatable, automatic rules are deleted, manual rules kept
+CREATE VIEW vutestv44 AS SELECT a, b FROM vutest1; -- updatable
+NOTICE: CREATE VIEW has created automatic view update rules
+CREATE RULE zmy_update AS ON UPDATE TO vutestv44 DO INSTEAD DELETE FROM vutest1; -- drops automatic _UPDATE rule
+CREATE OR REPLACE VIEW vutestv44 AS SELECT a, b FROM vutest2; -- automatic update rules are updated, manual rules kept
+WARNING: automatic UPDATE rule not created because manually created UPDATE rule exists
+HINT: If you prefer to have the automatic rule, drop the manually created rule and run CREATE OR REPLACE VIEW again.
+NOTICE: CREATE VIEW has created automatic view update rules
+SELECT rulename, definition FROM pg_rules WHERE tablename LIKE 'vutestv4_' ORDER BY tablename, rulename;
+ rulename | definition
+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ _DELETE | CREATE RULE "_DELETE" AS ON DELETE TO vutestv40 DO INSTEAD DELETE FROM vutest1 WHERE ((((old.a IS NULL) AND (vutest1.a IS NULL)) OR (old.a = vutest1.a)) AND (((old.b IS NULL) AND (vutest1.b IS NULL)) OR (old.b = vutest1.b))) RETURNING old.a, old.b;
+ _INSERT | CREATE RULE "_INSERT" AS ON INSERT TO vutestv40 DO INSTEAD DELETE FROM vutest1;
+ zmy_delete | CREATE RULE zmy_delete AS ON DELETE TO vutestv40 DO DELETE FROM vutest1;
+ zmy_update | CREATE RULE zmy_update AS ON UPDATE TO vutestv40 DO INSTEAD DELETE FROM vutest1;
+ _DELETE | CREATE RULE "_DELETE" AS ON DELETE TO vutestv41 DO INSTEAD DELETE FROM vutest1 WHERE ((((old.aa IS NULL) AND (vutest1.a IS NULL)) OR (old.aa = vutest1.a)) AND (((old.b IS NULL) AND (vutest1.b IS NULL)) OR (old.b = vutest1.b))) RETURNING old.aa, old.b;
+ _INSERT | CREATE RULE "_INSERT" AS ON INSERT TO vutestv41 DO INSTEAD INSERT INTO vutest1 (a, b) VALUES (new.aa, new.b) RETURNING new.aa AS a, new.b;
+ _UPDATE | CREATE RULE "_UPDATE" AS ON UPDATE TO vutestv41 DO INSTEAD UPDATE vutest1 SET a = (new.aa - 1), b = new.b WHERE ((vutest1.a = (old.aa - 1)) AND (vutest1.b = old.b));
+ _DELETE | CREATE RULE "_DELETE" AS ON DELETE TO vutestv42 DO INSTEAD DELETE FROM vutest1 WHERE ((((old.aa IS NULL) AND (vutest1.a IS NULL)) OR (old.aa = vutest1.a)) AND (((old.b IS NULL) AND (vutest1.b IS NULL)) OR (old.b = vutest1.b))) RETURNING old.aa, old.b;
+ _INSERT | CREATE RULE "_INSERT" AS ON INSERT TO vutestv42 DO INSTEAD INSERT INTO vutest1 (a, b) VALUES (new.aa, new.b) RETURNING new.aa AS a, new.b;
+ zmy_update | CREATE RULE zmy_update AS ON UPDATE TO vutestv42 DO INSTEAD UPDATE vutest1 SET a = (new.aa - 1), b = new.b WHERE ((vutest1.a = (old.aa - 1)) AND (vutest1.b = old.b));
+ zmy_update | CREATE RULE zmy_update AS ON UPDATE TO vutestv43 DO INSTEAD DELETE FROM vutest1;
+ _DELETE | CREATE RULE "_DELETE" AS ON DELETE TO vutestv44 DO INSTEAD DELETE FROM vutest2 WHERE ((((old.a IS NULL) AND (vutest2.a IS NULL)) OR (old.a = vutest2.a)) AND (((old.b IS NULL) AND (vutest2.b IS NULL)) OR (old.b = vutest2.b))) RETURNING old.a, old.b;
+ _INSERT | CREATE RULE "_INSERT" AS ON INSERT TO vutestv44 DO INSTEAD INSERT INTO vutest2 (a, b) VALUES (new.a, new.b) RETURNING new.a, new.b;
+ zmy_update | CREATE RULE zmy_update AS ON UPDATE TO vutestv44 DO INSTEAD DELETE FROM vutest1;
+(14 rows)
+
+-- ACL
+CREATE USER regressuser1;
+CREATE USER regressuser2;
+GRANT SELECT, INSERT, UPDATE ON vutest1 TO regressuser1;
+SET ROLE regressuser1;
+CREATE VIEW vutestv50 AS SELECT a, b FROM vutest1;
+NOTICE: CREATE VIEW has created automatic view update rules
+GRANT SELECT, UPDATE, DELETE ON vutestv50 TO regressuser2;
+SELECT * FROM vutestv50;
+ a | b
+-----+------
+ 1 | eins
+ 222 |
+(2 rows)
+
+INSERT INTO vutestv50 VALUES (0, 'zero');
+UPDATE vutestv50 SET a = 1;
+UPDATE vutestv50 SET a = 2 WHERE a = 1;
+DELETE FROM vutestv50; -- ERROR
+ERROR: permission denied for relation vutest1
+RESET ROLE;
+SET ROLE regressuser2;
+SELECT * FROM vutestv50;
+ a | b
+---+------
+ 2 | eins
+ 2 |
+ 2 | zero
+(3 rows)
+
+INSERT INTO vutestv50 VALUES (0, 'zero'); -- ERROR
+ERROR: permission denied for relation vutestv50
+UPDATE vutestv50 SET a = 1;
+UPDATE vutestv50 SET a = 2 WHERE a = 1;
+DELETE FROM vutestv50; -- ERROR on vutest1
+ERROR: permission denied for relation vutest1
+RESET ROLE;
+DROP VIEW vutestv50;
+REVOKE ALL PRIVILEGES ON vutest1 FROM regressuser1;
+DROP USER regressuser1, regressuser2;
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index 4c362957c5b..300a182cee8 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -1,5 +1,5 @@
# ----------
-# $PostgreSQL: pgsql/src/test/regress/parallel_schedule,v 1.53 2008/12/30 17:11:26 tgl Exp $
+# $PostgreSQL: pgsql/src/test/regress/parallel_schedule,v 1.54 2009/01/22 17:27:55 petere Exp $
#
# By convention, we put no more than twenty tests in any one parallel group;
# this limits the number of connections needed to run the tests.
@@ -79,6 +79,8 @@ test: misc
# ----------
test: select_views portals_p2 rules foreign_key cluster dependency guc bitmapops combocid tsearch tsdicts foreign_data window
+test: view_update
+
# ----------
# Another group of parallel tests
# NB: temp.sql does a reconnect which transiently uses 2 connections,
diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule
index 38ead933a04..2773cf891f4 100644
--- a/src/test/regress/serial_schedule
+++ b/src/test/regress/serial_schedule
@@ -1,4 +1,4 @@
-# $PostgreSQL: pgsql/src/test/regress/serial_schedule,v 1.50 2008/12/30 17:11:26 tgl Exp $
+# $PostgreSQL: pgsql/src/test/regress/serial_schedule,v 1.51 2009/01/22 17:27:55 petere Exp $
# This should probably be in an order similar to parallel_schedule.
test: boolean
test: char
@@ -100,6 +100,7 @@ test: tsearch
test: tsdicts
test: foreign_data
test: window
+test: view_update
test: plancache
test: limit
test: plpgsql
diff --git a/src/test/regress/sql/view_update.sql b/src/test/regress/sql/view_update.sql
new file mode 100644
index 00000000000..d6ece26b157
--- /dev/null
+++ b/src/test/regress/sql/view_update.sql
@@ -0,0 +1,168 @@
+CREATE TABLE vutest1 (a integer, b text);
+INSERT INTO vutest1 VALUES (1, 'one');
+INSERT INTO vutest1 VALUES (2, 'two');
+
+
+-- simple view updatability conditions
+
+CREATE VIEW vutestv1 AS SELECT a, b FROM vutest1;
+CREATE VIEW vutestv2 AS SELECT * FROM vutest1;
+CREATE VIEW vutestv3 AS SELECT b, a FROM vutest1;
+CREATE VIEW vutestv4 AS SELECT a, b FROM vutest1 WHERE a < 5;
+
+-- not updatable tests:
+CREATE VIEW vutestv5 AS SELECT sum(a) FROM vutest1; -- aggregate function
+CREATE VIEW vutestv6 AS SELECT b FROM vutest1 GROUP BY b; -- GROUP BY
+CREATE VIEW vutestv7 AS SELECT l.b AS x, r.b AS y FROM vutest1 l, vutest1 r WHERE r.a = l.a; -- JOIN
+CREATE VIEW vutestv8 AS SELECT 42; -- no table
+CREATE VIEW vutestv9 AS SELECT a * 2 AS x, b || b AS y FROM vutest1; -- derived columns
+CREATE VIEW vutestv10 AS SELECT a AS x, a AS y FROM vutest1; -- column referenced more than once
+CREATE VIEW vutestv11 AS SELECT * FROM generate_series(1, 5); -- table function
+CREATE VIEW vutestv12 AS SELECT xmin, xmax, a, b FROM vutest1; -- system columns
+CREATE VIEW vutestv13 AS SELECT DISTINCT a, b FROM vutest1; -- DISTINCT
+CREATE VIEW vutestv14 AS SELECT a, b FROM vutest1 WHERE a > (SELECT avg(a) FROM vutest1); -- *is* updatable, but SQL standard disallows this
+CREATE VIEW vutestv15 AS SELECT a, b FROM vutest1 UNION ALL SELECT a, b FROM vutest1; -- UNION
+CREATE VIEW vutestv16 AS SELECT x, y FROM (SELECT * FROM vutest1) AS foo (x, y); -- subquery ("derived table"); SQL standard allows this
+CREATE VIEW vutestv17 AS SELECT a, 5, b FROM vutest1; -- constant
+CREATE VIEW vutestv18 AS SELECT a, b FROM vutest1 LIMIT 1; -- LIMIT
+CREATE VIEW vutestv19 AS SELECT a, b FROM vutest1 OFFSET 1; -- OFFSET
+CREATE VIEW vutestv101 AS SELECT a, rank() OVER (PARTITION BY a ORDER BY b DESC) FROM vutest1; -- window function
+CREATE VIEW vutestv102 AS WITH foo AS (SELECT a, b FROM vutest1) SELECT * FROM foo; -- SQL standard allows this
+CREATE VIEW vutestv103 AS WITH RECURSIVE t(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM t) SELECT a FROM vutest1; -- recursive
+
+INSERT INTO vutestv1 VALUES (3, 'three');
+INSERT INTO vutestv2 VALUES (4, 'four');
+INSERT INTO vutestv3 VALUES (5, 'five'); -- fail
+INSERT INTO vutestv3 VALUES ('five', 5);
+INSERT INTO vutestv3 (a, b) VALUES (6, 'six');
+INSERT INTO vutestv4 VALUES (7, 'seven'); -- ok, but would be check option issue
+INSERT INTO vutestv5 VALUES (8); -- fail
+
+SELECT * FROM vutest1;
+SELECT * FROM vutestv1;
+SELECT * FROM vutestv2;
+SELECT * FROM vutestv3;
+SELECT * FROM vutestv4;
+SELECT * FROM vutestv5;
+
+UPDATE vutestv1 SET b = 'a lot' WHERE a = 7;
+DELETE FROM vutestv2 WHERE a = 1;
+UPDATE vutestv4 SET b = b || '!' WHERE a > 1;
+DELETE FROM vutestv4 WHERE a > 3;
+UPDATE vutestv6 SET b = 37; -- fail
+DELETE FROM vutestv5; -- fail
+
+SELECT * FROM vutest1 ORDER BY a, b;
+SELECT * FROM vutestv1 ORDER BY a, b;
+SELECT * FROM vutestv2 ORDER BY a, b;
+SELECT * FROM vutestv4 ORDER BY a, b;
+
+TRUNCATE TABLE vutest1;
+
+
+-- views on views
+
+CREATE VIEW vutestv20 AS SELECT a AS x, b AS y FROM vutestv1;
+CREATE VIEW vutestv21 AS SELECT x AS a FROM vutestv20 WHERE x % 2 = 0;
+CREATE VIEW vutestv22 AS SELECT sum(a) FROM vutestv21; -- not updatable
+CREATE VIEW vutestv23 AS SELECT * FROM vutestv12; -- not updatable
+
+INSERT INTO vutestv20 (x, y) VALUES (1, 'one');
+INSERT INTO vutestv20 (x, y) VALUES (3, 'three');
+INSERT INTO vutestv21 VALUES (2);
+
+SELECT * FROM vutest1;
+SELECT * FROM vutestv20;
+SELECT * FROM vutestv21;
+
+UPDATE vutestv20 SET y = 'eins' WHERE x = 1;
+UPDATE vutestv21 SET a = 222;
+
+SELECT * FROM vutest1;
+SELECT * FROM vutestv20;
+SELECT * FROM vutestv21;
+
+DELETE FROM vutestv20 WHERE x = 3;
+
+SELECT * FROM vutest1;
+SELECT * FROM vutestv20;
+SELECT * FROM vutestv21;
+
+
+-- insert tests
+
+CREATE TABLE vutest2 (a int PRIMARY KEY, b text NOT NULL, c text NOT NULL DEFAULT 'foo');
+
+CREATE VIEW vutestv30 AS SELECT a, b, c FROM vutest2;
+CREATE VIEW vutestv31 AS SELECT a, b FROM vutest2;
+CREATE VIEW vutestv32 AS SELECT a, c FROM vutest2;
+
+INSERT INTO vutestv30 VALUES (1, 'one', 'eins');
+INSERT INTO vutestv31 VALUES (2, 'two');
+INSERT INTO vutestv32 VALUES (3, 'drei'); -- fail
+
+UPDATE vutestv31 SET a = 22 WHERE a = 2;
+UPDATE vutestv32 SET c = 'drei!' WHERE a = 3;
+
+
+SELECT rulename, definition FROM pg_rules WHERE tablename LIKE 'vutestv%' ORDER BY tablename, rulename;
+
+
+-- interaction of manual and automatic rules, view replacement
+
+CREATE VIEW vutestv40 AS SELECT a, b FROM vutest1;
+CREATE RULE zmy_update AS ON UPDATE TO vutestv40 DO INSTEAD DELETE FROM vutest1; -- drops automatic _UPDATE rule
+CREATE RULE "_INSERT" AS ON INSERT TO vutestv40 DO INSTEAD DELETE FROM vutest1; -- replaces automatic _INSERT rule
+CREATE RULE zmy_delete AS ON DELETE TO vutestv40 DO ALSO DELETE FROM vutest1; -- leaves automatic _DELETE rule (because of ALSO)
+
+CREATE VIEW vutestv41 AS SELECT a + 1 AS aa, b FROM vutest1; -- not updatable
+CREATE RULE "_UPDATE" AS ON UPDATE TO vutestv41 DO INSTEAD UPDATE vutest1 SET a = new.aa - 1, b = new.b WHERE a = old.aa - 1 AND b = old.b;
+CREATE OR REPLACE VIEW vutestv41 AS SELECT a AS aa, b FROM vutest1; -- *now* updatable, manual _UPDATE rule stays
+
+CREATE VIEW vutestv42 AS SELECT a + 1 AS aa, b FROM vutest1; -- not updatable
+CREATE RULE zmy_update AS ON UPDATE TO vutestv42 DO INSTEAD UPDATE vutest1 SET a = new.aa - 1, b = new.b WHERE a = old.aa - 1 AND b = old.b;
+CREATE OR REPLACE VIEW vutestv42 AS SELECT a AS aa, b FROM vutest1; -- *now* updatable, zmy_update stays, no _UPDATE created
+
+CREATE VIEW vutestv43 AS SELECT a AS aa, b FROM vutest1; -- updatable
+CREATE RULE zmy_update AS ON UPDATE TO vutestv43 DO INSTEAD DELETE FROM vutest1; -- drops automatic _UPDATE rule
+CREATE OR REPLACE VIEW vutestv43 AS SELECT a + 1 AS aa, b FROM vutest1; -- no longer updatable, automatic rules are deleted, manual rules kept
+
+CREATE VIEW vutestv44 AS SELECT a, b FROM vutest1; -- updatable
+CREATE RULE zmy_update AS ON UPDATE TO vutestv44 DO INSTEAD DELETE FROM vutest1; -- drops automatic _UPDATE rule
+CREATE OR REPLACE VIEW vutestv44 AS SELECT a, b FROM vutest2; -- automatic update rules are updated, manual rules kept
+
+
+SELECT rulename, definition FROM pg_rules WHERE tablename LIKE 'vutestv4_' ORDER BY tablename, rulename;
+
+
+-- ACL
+
+CREATE USER regressuser1;
+CREATE USER regressuser2;
+
+GRANT SELECT, INSERT, UPDATE ON vutest1 TO regressuser1;
+
+SET ROLE regressuser1;
+CREATE VIEW vutestv50 AS SELECT a, b FROM vutest1;
+
+GRANT SELECT, UPDATE, DELETE ON vutestv50 TO regressuser2;
+
+SELECT * FROM vutestv50;
+INSERT INTO vutestv50 VALUES (0, 'zero');
+UPDATE vutestv50 SET a = 1;
+UPDATE vutestv50 SET a = 2 WHERE a = 1;
+DELETE FROM vutestv50; -- ERROR
+RESET ROLE;
+
+SET ROLE regressuser2;
+SELECT * FROM vutestv50;
+INSERT INTO vutestv50 VALUES (0, 'zero'); -- ERROR
+UPDATE vutestv50 SET a = 1;
+UPDATE vutestv50 SET a = 2 WHERE a = 1;
+DELETE FROM vutestv50; -- ERROR on vutest1
+RESET ROLE;
+
+DROP VIEW vutestv50;
+
+REVOKE ALL PRIVILEGES ON vutest1 FROM regressuser1;
+DROP USER regressuser1, regressuser2;
generated by cgit v1.2.3 (git 2.39.1) at 2025-12-21 20:21:28 +0000