4 -- avoid bit-exact output here because operations may not be bit-exact.
5 SET extra_float_digits = 0;
6 -- check that non-updatable views and columns are rejected with useful error
8 CREATE TABLE base_tbl (a int PRIMARY KEY, b text DEFAULT 'Unspecified');
9 INSERT INTO base_tbl SELECT i, 'Row ' || i FROM generate_series(-2, 2) g(i);
10 CREATE VIEW ro_view1 AS SELECT DISTINCT a, b FROM base_tbl; -- DISTINCT not supported
11 CREATE VIEW ro_view2 AS SELECT a, b FROM base_tbl GROUP BY a, b; -- GROUP BY not supported
12 CREATE VIEW ro_view3 AS SELECT 1 FROM base_tbl HAVING max(a) > 0; -- HAVING not supported
13 CREATE VIEW ro_view4 AS SELECT count(*) FROM base_tbl; -- Aggregate functions not supported
14 CREATE VIEW ro_view5 AS SELECT a, rank() OVER() FROM base_tbl; -- Window functions not supported
15 CREATE VIEW ro_view6 AS SELECT a, b FROM base_tbl UNION SELECT -a, b FROM base_tbl; -- Set ops not supported
16 CREATE VIEW ro_view7 AS WITH t AS (SELECT a, b FROM base_tbl) SELECT * FROM t; -- WITH not supported
17 CREATE VIEW ro_view8 AS SELECT a, b FROM base_tbl ORDER BY a OFFSET 1; -- OFFSET not supported
18 CREATE VIEW ro_view9 AS SELECT a, b FROM base_tbl ORDER BY a LIMIT 1; -- LIMIT not supported
19 CREATE VIEW ro_view10 AS SELECT 1 AS a; -- No base relations
20 CREATE VIEW ro_view11 AS SELECT b1.a, b2.b FROM base_tbl b1, base_tbl b2; -- Multiple base relations
21 CREATE VIEW ro_view12 AS SELECT * FROM generate_series(1, 10) AS g(a); -- SRF in rangetable
22 CREATE VIEW ro_view13 AS SELECT a, b FROM (SELECT * FROM base_tbl) AS t; -- Subselect in rangetable
23 CREATE VIEW rw_view14 AS SELECT ctid, a, b FROM base_tbl; -- System columns may be part of an updatable view
24 CREATE VIEW rw_view15 AS SELECT a, upper(b) FROM base_tbl; -- Expression/function may be part of an updatable view
25 CREATE VIEW rw_view16 AS SELECT a, b, a AS aa FROM base_tbl; -- Repeated column may be part of an updatable view
26 CREATE VIEW ro_view17 AS SELECT * FROM ro_view1; -- Base relation not updatable
27 CREATE VIEW ro_view18 AS SELECT * FROM (VALUES(1)) AS tmp(a); -- VALUES in rangetable
28 CREATE SEQUENCE uv_seq;
29 CREATE VIEW ro_view19 AS SELECT * FROM uv_seq; -- View based on a sequence
30 CREATE VIEW ro_view20 AS SELECT a, b, generate_series(1, a) g FROM base_tbl; -- SRF in targetlist not supported
31 SELECT table_name, is_insertable_into
32 FROM information_schema.tables
33 WHERE table_name LIKE E'r_\\_view%'
35 table_name | is_insertable_into
36 ------------+--------------------
59 SELECT table_name, is_updatable, is_insertable_into
60 FROM information_schema.views
61 WHERE table_name LIKE E'r_\\_view%'
63 table_name | is_updatable | is_insertable_into
64 ------------+--------------+--------------------
87 SELECT table_name, column_name, is_updatable
88 FROM information_schema.columns
89 WHERE table_name LIKE E'r_\\_view%'
90 ORDER BY table_name, ordinal_position;
91 table_name | column_name | is_updatable
92 ------------+-------------+--------------
104 ro_view19 | last_value | NO
105 ro_view19 | log_cnt | NO
106 ro_view19 | is_called | NO
112 ro_view3 | ?column? | NO
113 ro_view4 | count | NO
124 rw_view14 | ctid | NO
128 rw_view15 | upper | NO
135 DELETE FROM ro_view1;
136 ERROR: cannot delete from view "ro_view1"
137 DETAIL: Views containing DISTINCT are not automatically updatable.
138 HINT: To enable deleting from the view, provide an INSTEAD OF DELETE trigger or an unconditional ON DELETE DO INSTEAD rule.
139 DELETE FROM ro_view2;
140 ERROR: cannot delete from view "ro_view2"
141 DETAIL: Views containing GROUP BY are not automatically updatable.
142 HINT: To enable deleting from the view, provide an INSTEAD OF DELETE trigger or an unconditional ON DELETE DO INSTEAD rule.
143 DELETE FROM ro_view3;
144 ERROR: cannot delete from view "ro_view3"
145 DETAIL: Views containing HAVING are not automatically updatable.
146 HINT: To enable deleting from the view, provide an INSTEAD OF DELETE trigger or an unconditional ON DELETE DO INSTEAD rule.
147 DELETE FROM ro_view4;
148 ERROR: cannot delete from view "ro_view4"
149 DETAIL: Views that return aggregate functions are not automatically updatable.
150 HINT: To enable deleting from the view, provide an INSTEAD OF DELETE trigger or an unconditional ON DELETE DO INSTEAD rule.
151 DELETE FROM ro_view5;
152 ERROR: cannot delete from view "ro_view5"
153 DETAIL: Views that return window functions are not automatically updatable.
154 HINT: To enable deleting from the view, provide an INSTEAD OF DELETE trigger or an unconditional ON DELETE DO INSTEAD rule.
155 DELETE FROM ro_view6;
156 ERROR: cannot delete from view "ro_view6"
157 DETAIL: Views containing UNION, INTERSECT, or EXCEPT are not automatically updatable.
158 HINT: To enable deleting from the view, provide an INSTEAD OF DELETE trigger or an unconditional ON DELETE DO INSTEAD rule.
159 UPDATE ro_view7 SET a=a+1;
160 ERROR: cannot update view "ro_view7"
161 DETAIL: Views containing WITH are not automatically updatable.
162 HINT: To enable updating the view, provide an INSTEAD OF UPDATE trigger or an unconditional ON UPDATE DO INSTEAD rule.
163 UPDATE ro_view8 SET a=a+1;
164 ERROR: cannot update view "ro_view8"
165 DETAIL: Views containing LIMIT or OFFSET are not automatically updatable.
166 HINT: To enable updating the view, provide an INSTEAD OF UPDATE trigger or an unconditional ON UPDATE DO INSTEAD rule.
167 UPDATE ro_view9 SET a=a+1;
168 ERROR: cannot update view "ro_view9"
169 DETAIL: Views containing LIMIT or OFFSET are not automatically updatable.
170 HINT: To enable updating the view, provide an INSTEAD OF UPDATE trigger or an unconditional ON UPDATE DO INSTEAD rule.
171 UPDATE ro_view10 SET a=a+1;
172 ERROR: cannot update view "ro_view10"
173 DETAIL: Views that do not select from a single table or view are not automatically updatable.
174 HINT: To enable updating the view, provide an INSTEAD OF UPDATE trigger or an unconditional ON UPDATE DO INSTEAD rule.
175 UPDATE ro_view11 SET a=a+1;
176 ERROR: cannot update view "ro_view11"
177 DETAIL: Views that do not select from a single table or view are not automatically updatable.
178 HINT: To enable updating the view, provide an INSTEAD OF UPDATE trigger or an unconditional ON UPDATE DO INSTEAD rule.
179 UPDATE ro_view12 SET a=a+1;
180 ERROR: cannot update view "ro_view12"
181 DETAIL: Views that do not select from a single table or view are not automatically updatable.
182 HINT: To enable updating the view, provide an INSTEAD OF UPDATE trigger or an unconditional ON UPDATE DO INSTEAD rule.
183 INSERT INTO ro_view13 VALUES (3, 'Row 3');
184 ERROR: cannot insert into view "ro_view13"
185 DETAIL: Views that do not select from a single table or view are not automatically updatable.
186 HINT: To enable inserting into the view, provide an INSTEAD OF INSERT trigger or an unconditional ON INSERT DO INSTEAD rule.
187 -- Partially updatable view
188 INSERT INTO rw_view14 VALUES (null, 3, 'Row 3'); -- should fail
189 ERROR: cannot insert into column "ctid" of view "rw_view14"
190 DETAIL: View columns that refer to system columns are not updatable.
191 INSERT INTO rw_view14 (a, b) VALUES (3, 'Row 3'); -- should be OK
192 UPDATE rw_view14 SET ctid=null WHERE a=3; -- should fail
193 ERROR: cannot update column "ctid" of view "rw_view14"
194 DETAIL: View columns that refer to system columns are not updatable.
195 UPDATE rw_view14 SET b='ROW 3' WHERE a=3; -- should be OK
196 SELECT * FROM base_tbl;
207 DELETE FROM rw_view14 WHERE a=3; -- should be OK
208 -- Partially updatable view
209 INSERT INTO rw_view15 VALUES (3, 'ROW 3'); -- should fail
210 ERROR: cannot insert into column "upper" of view "rw_view15"
211 DETAIL: View columns that are not columns of their base relation are not updatable.
212 INSERT INTO rw_view15 (a) VALUES (3); -- should be OK
213 INSERT INTO rw_view15 (a) VALUES (3) ON CONFLICT DO NOTHING; -- succeeds
214 SELECT * FROM rw_view15;
225 INSERT INTO rw_view15 (a) VALUES (3) ON CONFLICT (a) DO NOTHING; -- succeeds
226 SELECT * FROM rw_view15;
237 INSERT INTO rw_view15 (a) VALUES (3) ON CONFLICT (a) DO UPDATE set a = excluded.a; -- succeeds
238 SELECT * FROM rw_view15;
249 INSERT INTO rw_view15 (a) VALUES (3) ON CONFLICT (a) DO UPDATE set upper = 'blarg'; -- fails
250 ERROR: cannot insert into column "upper" of view "rw_view15"
251 DETAIL: View columns that are not columns of their base relation are not updatable.
252 SELECT * FROM rw_view15;
263 SELECT * FROM rw_view15;
274 ALTER VIEW rw_view15 ALTER COLUMN upper SET DEFAULT 'NOT SET';
275 INSERT INTO rw_view15 (a) VALUES (4); -- should fail
276 ERROR: cannot insert into column "upper" of view "rw_view15"
277 DETAIL: View columns that are not columns of their base relation are not updatable.
278 UPDATE rw_view15 SET upper='ROW 3' WHERE a=3; -- should fail
279 ERROR: cannot update column "upper" of view "rw_view15"
280 DETAIL: View columns that are not columns of their base relation are not updatable.
281 UPDATE rw_view15 SET upper=DEFAULT WHERE a=3; -- should fail
282 ERROR: cannot update column "upper" of view "rw_view15"
283 DETAIL: View columns that are not columns of their base relation are not updatable.
284 UPDATE rw_view15 SET a=4 WHERE a=3; -- should be OK
285 SELECT * FROM base_tbl;
296 DELETE FROM rw_view15 WHERE a=4; -- should be OK
297 -- Partially updatable view
298 INSERT INTO rw_view16 VALUES (3, 'Row 3', 3); -- should fail
299 ERROR: multiple assignments to same column "a"
300 INSERT INTO rw_view16 (a, b) VALUES (3, 'Row 3'); -- should be OK
301 UPDATE rw_view16 SET a=3, aa=-3 WHERE a=3; -- should fail
302 ERROR: multiple assignments to same column "a"
303 UPDATE rw_view16 SET aa=-3 WHERE a=3; -- should be OK
304 SELECT * FROM base_tbl;
315 DELETE FROM rw_view16 WHERE a=-3; -- should be OK
317 INSERT INTO ro_view17 VALUES (3, 'ROW 3');
318 ERROR: cannot insert into view "ro_view1"
319 DETAIL: Views containing DISTINCT are not automatically updatable.
320 HINT: To enable inserting into the view, provide an INSTEAD OF INSERT trigger or an unconditional ON INSERT DO INSTEAD rule.
321 DELETE FROM ro_view18;
322 ERROR: cannot delete from view "ro_view18"
323 DETAIL: Views that do not select from a single table or view are not automatically updatable.
324 HINT: To enable deleting from the view, provide an INSTEAD OF DELETE trigger or an unconditional ON DELETE DO INSTEAD rule.
325 UPDATE ro_view19 SET last_value=1000;
326 ERROR: cannot update view "ro_view19"
327 DETAIL: Views that do not select from a single table or view are not automatically updatable.
328 HINT: To enable updating the view, provide an INSTEAD OF UPDATE trigger or an unconditional ON UPDATE DO INSTEAD rule.
329 UPDATE ro_view20 SET b=upper(b);
330 ERROR: cannot update view "ro_view20"
331 DETAIL: Views that return set-returning functions are not automatically updatable.
332 HINT: To enable updating the view, provide an INSTEAD OF UPDATE trigger or an unconditional ON UPDATE DO INSTEAD rule.
333 DROP TABLE base_tbl CASCADE;
334 NOTICE: drop cascades to 16 other objects
335 DETAIL: drop cascades to view ro_view1
336 drop cascades to view ro_view17
337 drop cascades to view ro_view2
338 drop cascades to view ro_view3
339 drop cascades to view ro_view4
340 drop cascades to view ro_view5
341 drop cascades to view ro_view6
342 drop cascades to view ro_view7
343 drop cascades to view ro_view8
344 drop cascades to view ro_view9
345 drop cascades to view ro_view11
346 drop cascades to view ro_view13
347 drop cascades to view rw_view14
348 drop cascades to view rw_view15
349 drop cascades to view rw_view16
350 drop cascades to view ro_view20
351 DROP VIEW ro_view10, ro_view12, ro_view18;
352 DROP SEQUENCE uv_seq CASCADE;
353 NOTICE: drop cascades to view ro_view19
354 -- simple updatable view
355 CREATE TABLE base_tbl (a int PRIMARY KEY, b text DEFAULT 'Unspecified');
356 INSERT INTO base_tbl SELECT i, 'Row ' || i FROM generate_series(-2, 2) g(i);
357 CREATE VIEW rw_view1 AS SELECT * FROM base_tbl WHERE a>0;
358 SELECT table_name, is_insertable_into
359 FROM information_schema.tables
360 WHERE table_name = 'rw_view1';
361 table_name | is_insertable_into
362 ------------+--------------------
366 SELECT table_name, is_updatable, is_insertable_into
367 FROM information_schema.views
368 WHERE table_name = 'rw_view1';
369 table_name | is_updatable | is_insertable_into
370 ------------+--------------+--------------------
374 SELECT table_name, column_name, is_updatable
375 FROM information_schema.columns
376 WHERE table_name = 'rw_view1'
377 ORDER BY ordinal_position;
378 table_name | column_name | is_updatable
379 ------------+-------------+--------------
384 INSERT INTO rw_view1 VALUES (3, 'Row 3');
385 INSERT INTO rw_view1 (a) VALUES (4);
386 UPDATE rw_view1 SET a=5 WHERE a=4;
387 DELETE FROM rw_view1 WHERE b='Row 2';
388 SELECT * FROM base_tbl;
399 EXPLAIN (costs off) UPDATE rw_view1 SET a=6 WHERE a=5;
401 --------------------------------------------------
403 -> Index Scan using base_tbl_pkey on base_tbl
404 Index Cond: ((a > 0) AND (a = 5))
407 EXPLAIN (costs off) DELETE FROM rw_view1 WHERE a=5;
409 --------------------------------------------------
411 -> Index Scan using base_tbl_pkey on base_tbl
412 Index Cond: ((a > 0) AND (a = 5))
415 DROP TABLE base_tbl CASCADE;
416 NOTICE: drop cascades to view rw_view1
417 -- view on top of view
418 CREATE TABLE base_tbl (a int PRIMARY KEY, b text DEFAULT 'Unspecified');
419 INSERT INTO base_tbl SELECT i, 'Row ' || i FROM generate_series(-2, 2) g(i);
420 CREATE VIEW rw_view1 AS SELECT b AS bb, a AS aa FROM base_tbl WHERE a>0;
421 CREATE VIEW rw_view2 AS SELECT aa AS aaa, bb AS bbb FROM rw_view1 WHERE aa<10;
422 SELECT table_name, is_insertable_into
423 FROM information_schema.tables
424 WHERE table_name = 'rw_view2';
425 table_name | is_insertable_into
426 ------------+--------------------
430 SELECT table_name, is_updatable, is_insertable_into
431 FROM information_schema.views
432 WHERE table_name = 'rw_view2';
433 table_name | is_updatable | is_insertable_into
434 ------------+--------------+--------------------
438 SELECT table_name, column_name, is_updatable
439 FROM information_schema.columns
440 WHERE table_name = 'rw_view2'
441 ORDER BY ordinal_position;
442 table_name | column_name | is_updatable
443 ------------+-------------+--------------
448 INSERT INTO rw_view2 VALUES (3, 'Row 3');
449 INSERT INTO rw_view2 (aaa) VALUES (4);
450 SELECT * FROM rw_view2;
459 UPDATE rw_view2 SET bbb='Row 4' WHERE aaa=4;
460 DELETE FROM rw_view2 WHERE aaa=2;
461 SELECT * FROM rw_view2;
469 EXPLAIN (costs off) UPDATE rw_view2 SET aaa=5 WHERE aaa=4;
471 --------------------------------------------------------
473 -> Index Scan using base_tbl_pkey on base_tbl
474 Index Cond: ((a < 10) AND (a > 0) AND (a = 4))
477 EXPLAIN (costs off) DELETE FROM rw_view2 WHERE aaa=4;
479 --------------------------------------------------------
481 -> Index Scan using base_tbl_pkey on base_tbl
482 Index Cond: ((a < 10) AND (a > 0) AND (a = 4))
485 DROP TABLE base_tbl CASCADE;
486 NOTICE: drop cascades to 2 other objects
487 DETAIL: drop cascades to view rw_view1
488 drop cascades to view rw_view2
489 -- view on top of view with rules
490 CREATE TABLE base_tbl (a int PRIMARY KEY, b text DEFAULT 'Unspecified');
491 INSERT INTO base_tbl SELECT i, 'Row ' || i FROM generate_series(-2, 2) g(i);
492 CREATE VIEW rw_view1 AS SELECT * FROM base_tbl WHERE a>0 OFFSET 0; -- not updatable without rules/triggers
493 CREATE VIEW rw_view2 AS SELECT * FROM rw_view1 WHERE a<10;
494 SELECT table_name, is_insertable_into
495 FROM information_schema.tables
496 WHERE table_name LIKE 'rw_view%'
498 table_name | is_insertable_into
499 ------------+--------------------
504 SELECT table_name, is_updatable, is_insertable_into
505 FROM information_schema.views
506 WHERE table_name LIKE 'rw_view%'
508 table_name | is_updatable | is_insertable_into
509 ------------+--------------+--------------------
514 SELECT table_name, column_name, is_updatable
515 FROM information_schema.columns
516 WHERE table_name LIKE 'rw_view%'
517 ORDER BY table_name, ordinal_position;
518 table_name | column_name | is_updatable
519 ------------+-------------+--------------
526 CREATE RULE rw_view1_ins_rule AS ON INSERT TO rw_view1
527 DO INSTEAD INSERT INTO base_tbl VALUES (NEW.a, NEW.b) RETURNING *;
528 SELECT table_name, is_insertable_into
529 FROM information_schema.tables
530 WHERE table_name LIKE 'rw_view%'
532 table_name | is_insertable_into
533 ------------+--------------------
538 SELECT table_name, is_updatable, is_insertable_into
539 FROM information_schema.views
540 WHERE table_name LIKE 'rw_view%'
542 table_name | is_updatable | is_insertable_into
543 ------------+--------------+--------------------
548 SELECT table_name, column_name, is_updatable
549 FROM information_schema.columns
550 WHERE table_name LIKE 'rw_view%'
551 ORDER BY table_name, ordinal_position;
552 table_name | column_name | is_updatable
553 ------------+-------------+--------------
560 CREATE RULE rw_view1_upd_rule AS ON UPDATE TO rw_view1
561 DO INSTEAD UPDATE base_tbl SET b=NEW.b WHERE a=OLD.a RETURNING NEW.*;
562 SELECT table_name, is_insertable_into
563 FROM information_schema.tables
564 WHERE table_name LIKE 'rw_view%'
566 table_name | is_insertable_into
567 ------------+--------------------
572 SELECT table_name, is_updatable, is_insertable_into
573 FROM information_schema.views
574 WHERE table_name LIKE 'rw_view%'
576 table_name | is_updatable | is_insertable_into
577 ------------+--------------+--------------------
582 SELECT table_name, column_name, is_updatable
583 FROM information_schema.columns
584 WHERE table_name LIKE 'rw_view%'
585 ORDER BY table_name, ordinal_position;
586 table_name | column_name | is_updatable
587 ------------+-------------+--------------
594 CREATE RULE rw_view1_del_rule AS ON DELETE TO rw_view1
595 DO INSTEAD DELETE FROM base_tbl WHERE a=OLD.a RETURNING OLD.*;
596 SELECT table_name, is_insertable_into
597 FROM information_schema.tables
598 WHERE table_name LIKE 'rw_view%'
600 table_name | is_insertable_into
601 ------------+--------------------
606 SELECT table_name, is_updatable, is_insertable_into
607 FROM information_schema.views
608 WHERE table_name LIKE 'rw_view%'
610 table_name | is_updatable | is_insertable_into
611 ------------+--------------+--------------------
616 SELECT table_name, column_name, is_updatable
617 FROM information_schema.columns
618 WHERE table_name LIKE 'rw_view%'
619 ORDER BY table_name, ordinal_position;
620 table_name | column_name | is_updatable
621 ------------+-------------+--------------
628 INSERT INTO rw_view2 VALUES (3, 'Row 3') RETURNING *;
634 UPDATE rw_view2 SET b='Row three' WHERE a=3 RETURNING *;
640 SELECT * FROM rw_view2;
648 DELETE FROM rw_view2 WHERE a=3 RETURNING *;
654 SELECT * FROM rw_view2;
661 EXPLAIN (costs off) UPDATE rw_view2 SET a=3 WHERE a=2;
663 ----------------------------------------------------------------
666 -> Index Scan using base_tbl_pkey on base_tbl
668 -> Subquery Scan on rw_view1
669 Filter: ((rw_view1.a < 10) AND (rw_view1.a = 2))
670 -> Bitmap Heap Scan on base_tbl base_tbl_1
671 Recheck Cond: (a > 0)
672 -> Bitmap Index Scan on base_tbl_pkey
676 EXPLAIN (costs off) DELETE FROM rw_view2 WHERE a=2;
678 ----------------------------------------------------------------
681 -> Index Scan using base_tbl_pkey on base_tbl
683 -> Subquery Scan on rw_view1
684 Filter: ((rw_view1.a < 10) AND (rw_view1.a = 2))
685 -> Bitmap Heap Scan on base_tbl base_tbl_1
686 Recheck Cond: (a > 0)
687 -> Bitmap Index Scan on base_tbl_pkey
691 DROP TABLE base_tbl CASCADE;
692 NOTICE: drop cascades to 2 other objects
693 DETAIL: drop cascades to view rw_view1
694 drop cascades to view rw_view2
695 -- view on top of view with triggers
696 CREATE TABLE base_tbl (a int PRIMARY KEY, b text DEFAULT 'Unspecified');
697 INSERT INTO base_tbl SELECT i, 'Row ' || i FROM generate_series(-2, 2) g(i);
698 CREATE VIEW rw_view1 AS SELECT * FROM base_tbl WHERE a>0 OFFSET 0; -- not updatable without rules/triggers
699 CREATE VIEW rw_view2 AS SELECT * FROM rw_view1 WHERE a<10;
700 SELECT table_name, is_insertable_into
701 FROM information_schema.tables
702 WHERE table_name LIKE 'rw_view%'
704 table_name | is_insertable_into
705 ------------+--------------------
710 SELECT table_name, is_updatable, is_insertable_into,
711 is_trigger_updatable, is_trigger_deletable,
712 is_trigger_insertable_into
713 FROM information_schema.views
714 WHERE table_name LIKE 'rw_view%'
716 table_name | is_updatable | is_insertable_into | is_trigger_updatable | is_trigger_deletable | is_trigger_insertable_into
717 ------------+--------------+--------------------+----------------------+----------------------+----------------------------
718 rw_view1 | NO | NO | NO | NO | NO
719 rw_view2 | NO | NO | NO | NO | NO
722 SELECT table_name, column_name, is_updatable
723 FROM information_schema.columns
724 WHERE table_name LIKE 'rw_view%'
725 ORDER BY table_name, ordinal_position;
726 table_name | column_name | is_updatable
727 ------------+-------------+--------------
734 CREATE FUNCTION rw_view1_trig_fn()
738 IF TG_OP = 'INSERT' THEN
739 INSERT INTO base_tbl VALUES (NEW.a, NEW.b);
741 ELSIF TG_OP = 'UPDATE' THEN
742 UPDATE base_tbl SET b=NEW.b WHERE a=OLD.a;
744 ELSIF TG_OP = 'DELETE' THEN
745 DELETE FROM base_tbl WHERE a=OLD.a;
751 CREATE TRIGGER rw_view1_ins_trig INSTEAD OF INSERT ON rw_view1
752 FOR EACH ROW EXECUTE PROCEDURE rw_view1_trig_fn();
753 SELECT table_name, is_insertable_into
754 FROM information_schema.tables
755 WHERE table_name LIKE 'rw_view%'
757 table_name | is_insertable_into
758 ------------+--------------------
763 SELECT table_name, is_updatable, is_insertable_into,
764 is_trigger_updatable, is_trigger_deletable,
765 is_trigger_insertable_into
766 FROM information_schema.views
767 WHERE table_name LIKE 'rw_view%'
769 table_name | is_updatable | is_insertable_into | is_trigger_updatable | is_trigger_deletable | is_trigger_insertable_into
770 ------------+--------------+--------------------+----------------------+----------------------+----------------------------
771 rw_view1 | NO | NO | NO | NO | YES
772 rw_view2 | NO | NO | NO | NO | NO
775 SELECT table_name, column_name, is_updatable
776 FROM information_schema.columns
777 WHERE table_name LIKE 'rw_view%'
778 ORDER BY table_name, ordinal_position;
779 table_name | column_name | is_updatable
780 ------------+-------------+--------------
787 CREATE TRIGGER rw_view1_upd_trig INSTEAD OF UPDATE ON rw_view1
788 FOR EACH ROW EXECUTE PROCEDURE rw_view1_trig_fn();
789 SELECT table_name, is_insertable_into
790 FROM information_schema.tables
791 WHERE table_name LIKE 'rw_view%'
793 table_name | is_insertable_into
794 ------------+--------------------
799 SELECT table_name, is_updatable, is_insertable_into,
800 is_trigger_updatable, is_trigger_deletable,
801 is_trigger_insertable_into
802 FROM information_schema.views
803 WHERE table_name LIKE 'rw_view%'
805 table_name | is_updatable | is_insertable_into | is_trigger_updatable | is_trigger_deletable | is_trigger_insertable_into
806 ------------+--------------+--------------------+----------------------+----------------------+----------------------------
807 rw_view1 | NO | NO | YES | NO | YES
808 rw_view2 | NO | NO | NO | NO | NO
811 SELECT table_name, column_name, is_updatable
812 FROM information_schema.columns
813 WHERE table_name LIKE 'rw_view%'
814 ORDER BY table_name, ordinal_position;
815 table_name | column_name | is_updatable
816 ------------+-------------+--------------
823 CREATE TRIGGER rw_view1_del_trig INSTEAD OF DELETE ON rw_view1
824 FOR EACH ROW EXECUTE PROCEDURE rw_view1_trig_fn();
825 SELECT table_name, is_insertable_into
826 FROM information_schema.tables
827 WHERE table_name LIKE 'rw_view%'
829 table_name | is_insertable_into
830 ------------+--------------------
835 SELECT table_name, is_updatable, is_insertable_into,
836 is_trigger_updatable, is_trigger_deletable,
837 is_trigger_insertable_into
838 FROM information_schema.views
839 WHERE table_name LIKE 'rw_view%'
841 table_name | is_updatable | is_insertable_into | is_trigger_updatable | is_trigger_deletable | is_trigger_insertable_into
842 ------------+--------------+--------------------+----------------------+----------------------+----------------------------
843 rw_view1 | NO | NO | YES | YES | YES
844 rw_view2 | NO | NO | NO | NO | NO
847 SELECT table_name, column_name, is_updatable
848 FROM information_schema.columns
849 WHERE table_name LIKE 'rw_view%'
850 ORDER BY table_name, ordinal_position;
851 table_name | column_name | is_updatable
852 ------------+-------------+--------------
859 INSERT INTO rw_view2 VALUES (3, 'Row 3') RETURNING *;
865 UPDATE rw_view2 SET b='Row three' WHERE a=3 RETURNING *;
871 SELECT * FROM rw_view2;
879 DELETE FROM rw_view2 WHERE a=3 RETURNING *;
885 SELECT * FROM rw_view2;
892 EXPLAIN (costs off) UPDATE rw_view2 SET a=3 WHERE a=2;
894 ----------------------------------------------------------
895 Update on rw_view1 rw_view1_1
896 -> Subquery Scan on rw_view1
897 Filter: ((rw_view1.a < 10) AND (rw_view1.a = 2))
898 -> Bitmap Heap Scan on base_tbl
899 Recheck Cond: (a > 0)
900 -> Bitmap Index Scan on base_tbl_pkey
904 EXPLAIN (costs off) DELETE FROM rw_view2 WHERE a=2;
906 ----------------------------------------------------------
907 Delete on rw_view1 rw_view1_1
908 -> Subquery Scan on rw_view1
909 Filter: ((rw_view1.a < 10) AND (rw_view1.a = 2))
910 -> Bitmap Heap Scan on base_tbl
911 Recheck Cond: (a > 0)
912 -> Bitmap Index Scan on base_tbl_pkey
916 DROP TABLE base_tbl CASCADE;
917 NOTICE: drop cascades to 2 other objects
918 DETAIL: drop cascades to view rw_view1
919 drop cascades to view rw_view2
920 DROP FUNCTION rw_view1_trig_fn();
921 -- update using whole row from view
922 CREATE TABLE base_tbl (a int PRIMARY KEY, b text DEFAULT 'Unspecified');
923 INSERT INTO base_tbl SELECT i, 'Row ' || i FROM generate_series(-2, 2) g(i);
924 CREATE VIEW rw_view1 AS SELECT b AS bb, a AS aa FROM base_tbl;
925 CREATE FUNCTION rw_view1_aa(x rw_view1)
926 RETURNS int AS $$ SELECT x.aa $$ LANGUAGE sql;
927 UPDATE rw_view1 v SET bb='Updated row 2' WHERE rw_view1_aa(v)=2
928 RETURNING rw_view1_aa(v), v.bb;
930 -------------+---------------
934 SELECT * FROM base_tbl;
945 UPDATE rw_view1 v SET bb='Updated row 2' WHERE rw_view1_aa(v)=2
946 RETURNING rw_view1_aa(v), v.bb;
948 --------------------------------------------------
950 -> Index Scan using base_tbl_pkey on base_tbl
954 DROP TABLE base_tbl CASCADE;
955 NOTICE: drop cascades to 2 other objects
956 DETAIL: drop cascades to view rw_view1
957 drop cascades to function rw_view1_aa(rw_view1)
958 -- permissions checks
959 CREATE USER regress_view_user1;
960 CREATE USER regress_view_user2;
961 SET SESSION AUTHORIZATION regress_view_user1;
962 CREATE TABLE base_tbl(a int, b text, c float);
963 INSERT INTO base_tbl VALUES (1, 'Row 1', 1.0);
964 CREATE VIEW rw_view1 AS SELECT b AS bb, c AS cc, a AS aa FROM base_tbl;
965 INSERT INTO rw_view1 VALUES ('Row 2', 2.0, 2);
966 GRANT SELECT ON base_tbl TO regress_view_user2;
967 GRANT SELECT ON rw_view1 TO regress_view_user2;
968 GRANT UPDATE (a,c) ON base_tbl TO regress_view_user2;
969 GRANT UPDATE (bb,cc) ON rw_view1 TO regress_view_user2;
970 RESET SESSION AUTHORIZATION;
971 SET SESSION AUTHORIZATION regress_view_user2;
972 CREATE VIEW rw_view2 AS SELECT b AS bb, c AS cc, a AS aa FROM base_tbl;
973 SELECT * FROM base_tbl; -- ok
980 SELECT * FROM rw_view1; -- ok
987 SELECT * FROM rw_view2; -- ok
994 INSERT INTO base_tbl VALUES (3, 'Row 3', 3.0); -- not allowed
995 ERROR: permission denied for table base_tbl
996 INSERT INTO rw_view1 VALUES ('Row 3', 3.0, 3); -- not allowed
997 ERROR: permission denied for view rw_view1
998 INSERT INTO rw_view2 VALUES ('Row 3', 3.0, 3); -- not allowed
999 ERROR: permission denied for table base_tbl
1000 UPDATE base_tbl SET a=a, c=c; -- ok
1001 UPDATE base_tbl SET b=b; -- not allowed
1002 ERROR: permission denied for table base_tbl
1003 UPDATE rw_view1 SET bb=bb, cc=cc; -- ok
1004 UPDATE rw_view1 SET aa=aa; -- not allowed
1005 ERROR: permission denied for view rw_view1
1006 UPDATE rw_view2 SET aa=aa, cc=cc; -- ok
1007 UPDATE rw_view2 SET bb=bb; -- not allowed
1008 ERROR: permission denied for table base_tbl
1009 DELETE FROM base_tbl; -- not allowed
1010 ERROR: permission denied for table base_tbl
1011 DELETE FROM rw_view1; -- not allowed
1012 ERROR: permission denied for view rw_view1
1013 DELETE FROM rw_view2; -- not allowed
1014 ERROR: permission denied for table base_tbl
1015 RESET SESSION AUTHORIZATION;
1016 SET SESSION AUTHORIZATION regress_view_user1;
1017 GRANT INSERT, DELETE ON base_tbl TO regress_view_user2;
1018 RESET SESSION AUTHORIZATION;
1019 SET SESSION AUTHORIZATION regress_view_user2;
1020 INSERT INTO base_tbl VALUES (3, 'Row 3', 3.0); -- ok
1021 INSERT INTO rw_view1 VALUES ('Row 4', 4.0, 4); -- not allowed
1022 ERROR: permission denied for view rw_view1
1023 INSERT INTO rw_view2 VALUES ('Row 4', 4.0, 4); -- ok
1024 DELETE FROM base_tbl WHERE a=1; -- ok
1025 DELETE FROM rw_view1 WHERE aa=2; -- not allowed
1026 ERROR: permission denied for view rw_view1
1027 DELETE FROM rw_view2 WHERE aa=2; -- ok
1028 SELECT * FROM base_tbl;
1035 RESET SESSION AUTHORIZATION;
1036 SET SESSION AUTHORIZATION regress_view_user1;
1037 REVOKE INSERT, DELETE ON base_tbl FROM regress_view_user2;
1038 GRANT INSERT, DELETE ON rw_view1 TO regress_view_user2;
1039 RESET SESSION AUTHORIZATION;
1040 SET SESSION AUTHORIZATION regress_view_user2;
1041 INSERT INTO base_tbl VALUES (5, 'Row 5', 5.0); -- not allowed
1042 ERROR: permission denied for table base_tbl
1043 INSERT INTO rw_view1 VALUES ('Row 5', 5.0, 5); -- ok
1044 INSERT INTO rw_view2 VALUES ('Row 6', 6.0, 6); -- not allowed
1045 ERROR: permission denied for table base_tbl
1046 DELETE FROM base_tbl WHERE a=3; -- not allowed
1047 ERROR: permission denied for table base_tbl
1048 DELETE FROM rw_view1 WHERE aa=3; -- ok
1049 DELETE FROM rw_view2 WHERE aa=4; -- not allowed
1050 ERROR: permission denied for table base_tbl
1051 SELECT * FROM base_tbl;
1058 RESET SESSION AUTHORIZATION;
1059 DROP TABLE base_tbl CASCADE;
1060 NOTICE: drop cascades to 2 other objects
1061 DETAIL: drop cascades to view rw_view1
1062 drop cascades to view rw_view2
1063 -- nested-view permissions
1064 CREATE TABLE base_tbl(a int, b text, c float);
1065 INSERT INTO base_tbl VALUES (1, 'Row 1', 1.0);
1066 SET SESSION AUTHORIZATION regress_view_user1;
1067 CREATE VIEW rw_view1 AS SELECT * FROM base_tbl;
1068 SELECT * FROM rw_view1; -- not allowed
1069 ERROR: permission denied for table base_tbl
1070 SELECT * FROM rw_view1 FOR UPDATE; -- not allowed
1071 ERROR: permission denied for table base_tbl
1072 UPDATE rw_view1 SET b = 'foo' WHERE a = 1; -- not allowed
1073 ERROR: permission denied for table base_tbl
1074 SET SESSION AUTHORIZATION regress_view_user2;
1075 CREATE VIEW rw_view2 AS SELECT * FROM rw_view1;
1076 SELECT * FROM rw_view2; -- not allowed
1077 ERROR: permission denied for view rw_view1
1078 SELECT * FROM rw_view2 FOR UPDATE; -- not allowed
1079 ERROR: permission denied for view rw_view1
1080 UPDATE rw_view2 SET b = 'bar' WHERE a = 1; -- not allowed
1081 ERROR: permission denied for view rw_view1
1082 RESET SESSION AUTHORIZATION;
1083 GRANT SELECT ON base_tbl TO regress_view_user1;
1084 SET SESSION AUTHORIZATION regress_view_user1;
1085 SELECT * FROM rw_view1;
1091 SELECT * FROM rw_view1 FOR UPDATE; -- not allowed
1092 ERROR: permission denied for table base_tbl
1093 UPDATE rw_view1 SET b = 'foo' WHERE a = 1; -- not allowed
1094 ERROR: permission denied for table base_tbl
1095 SET SESSION AUTHORIZATION regress_view_user2;
1096 SELECT * FROM rw_view2; -- not allowed
1097 ERROR: permission denied for view rw_view1
1098 SELECT * FROM rw_view2 FOR UPDATE; -- not allowed
1099 ERROR: permission denied for view rw_view1
1100 UPDATE rw_view2 SET b = 'bar' WHERE a = 1; -- not allowed
1101 ERROR: permission denied for view rw_view1
1102 SET SESSION AUTHORIZATION regress_view_user1;
1103 GRANT SELECT ON rw_view1 TO regress_view_user2;
1104 SET SESSION AUTHORIZATION regress_view_user2;
1105 SELECT * FROM rw_view2;
1111 SELECT * FROM rw_view2 FOR UPDATE; -- not allowed
1112 ERROR: permission denied for view rw_view1
1113 UPDATE rw_view2 SET b = 'bar' WHERE a = 1; -- not allowed
1114 ERROR: permission denied for view rw_view1
1115 RESET SESSION AUTHORIZATION;
1116 GRANT UPDATE ON base_tbl TO regress_view_user1;
1117 SET SESSION AUTHORIZATION regress_view_user1;
1118 SELECT * FROM rw_view1;
1124 SELECT * FROM rw_view1 FOR UPDATE;
1130 UPDATE rw_view1 SET b = 'foo' WHERE a = 1;
1131 SET SESSION AUTHORIZATION regress_view_user2;
1132 SELECT * FROM rw_view2;
1138 SELECT * FROM rw_view2 FOR UPDATE; -- not allowed
1139 ERROR: permission denied for view rw_view1
1140 UPDATE rw_view2 SET b = 'bar' WHERE a = 1; -- not allowed
1141 ERROR: permission denied for view rw_view1
1142 SET SESSION AUTHORIZATION regress_view_user1;
1143 GRANT UPDATE ON rw_view1 TO regress_view_user2;
1144 SET SESSION AUTHORIZATION regress_view_user2;
1145 SELECT * FROM rw_view2;
1151 SELECT * FROM rw_view2 FOR UPDATE;
1157 UPDATE rw_view2 SET b = 'bar' WHERE a = 1;
1158 RESET SESSION AUTHORIZATION;
1159 REVOKE UPDATE ON base_tbl FROM regress_view_user1;
1160 SET SESSION AUTHORIZATION regress_view_user1;
1161 SELECT * FROM rw_view1;
1167 SELECT * FROM rw_view1 FOR UPDATE; -- not allowed
1168 ERROR: permission denied for table base_tbl
1169 UPDATE rw_view1 SET b = 'foo' WHERE a = 1; -- not allowed
1170 ERROR: permission denied for table base_tbl
1171 SET SESSION AUTHORIZATION regress_view_user2;
1172 SELECT * FROM rw_view2;
1178 SELECT * FROM rw_view2 FOR UPDATE; -- not allowed
1179 ERROR: permission denied for table base_tbl
1180 UPDATE rw_view2 SET b = 'bar' WHERE a = 1; -- not allowed
1181 ERROR: permission denied for table base_tbl
1182 RESET SESSION AUTHORIZATION;
1183 DROP TABLE base_tbl CASCADE;
1184 NOTICE: drop cascades to 2 other objects
1185 DETAIL: drop cascades to view rw_view1
1186 drop cascades to view rw_view2
1187 DROP USER regress_view_user1;
1188 DROP USER regress_view_user2;
1190 CREATE TABLE base_tbl (a int PRIMARY KEY, b text DEFAULT 'Unspecified', c serial);
1191 INSERT INTO base_tbl VALUES (1, 'Row 1');
1192 INSERT INTO base_tbl VALUES (2, 'Row 2');
1193 INSERT INTO base_tbl VALUES (3);
1194 CREATE VIEW rw_view1 AS SELECT a AS aa, b AS bb FROM base_tbl;
1195 ALTER VIEW rw_view1 ALTER COLUMN bb SET DEFAULT 'View default';
1196 INSERT INTO rw_view1 VALUES (4, 'Row 4');
1197 INSERT INTO rw_view1 (aa) VALUES (5);
1198 SELECT * FROM base_tbl;
1200 ---+--------------+---
1205 5 | View default | 5
1208 DROP TABLE base_tbl CASCADE;
1209 NOTICE: drop cascades to view rw_view1
1210 -- Table having triggers
1211 CREATE TABLE base_tbl (a int PRIMARY KEY, b text DEFAULT 'Unspecified');
1212 INSERT INTO base_tbl VALUES (1, 'Row 1');
1213 INSERT INTO base_tbl VALUES (2, 'Row 2');
1214 CREATE FUNCTION rw_view1_trig_fn()
1218 IF TG_OP = 'INSERT' THEN
1219 UPDATE base_tbl SET b=NEW.b WHERE a=1;
1226 CREATE TRIGGER rw_view1_ins_trig AFTER INSERT ON base_tbl
1227 FOR EACH ROW EXECUTE PROCEDURE rw_view1_trig_fn();
1228 CREATE VIEW rw_view1 AS SELECT a AS aa, b AS bb FROM base_tbl;
1229 INSERT INTO rw_view1 VALUES (3, 'Row 3');
1230 select * from base_tbl;
1239 DROP TRIGGER rw_view1_ins_trig on base_tbl;
1240 DROP FUNCTION rw_view1_trig_fn();
1241 DROP TABLE base_tbl;
1242 -- view with ORDER BY
1243 CREATE TABLE base_tbl (a int, b int);
1244 INSERT INTO base_tbl VALUES (1,2), (4,5), (3,-3);
1245 CREATE VIEW rw_view1 AS SELECT * FROM base_tbl ORDER BY a+b;
1246 SELECT * FROM rw_view1;
1254 INSERT INTO rw_view1 VALUES (7,-8);
1255 SELECT * FROM rw_view1;
1264 EXPLAIN (verbose, costs off) UPDATE rw_view1 SET b = b + 1 RETURNING *;
1266 -------------------------------------------------------------
1267 Update on public.base_tbl
1268 Output: base_tbl.a, base_tbl.b
1269 -> Seq Scan on public.base_tbl
1270 Output: base_tbl.a, (base_tbl.b + 1), base_tbl.ctid
1273 UPDATE rw_view1 SET b = b + 1 RETURNING *;
1282 SELECT * FROM rw_view1;
1291 DROP TABLE base_tbl CASCADE;
1292 NOTICE: drop cascades to view rw_view1
1293 -- multiple array-column updates
1294 CREATE TABLE base_tbl (a int, arr int[]);
1295 INSERT INTO base_tbl VALUES (1,ARRAY[2]), (3,ARRAY[4]);
1296 CREATE VIEW rw_view1 AS SELECT * FROM base_tbl;
1297 UPDATE rw_view1 SET arr[1] = 42, arr[2] = 77 WHERE a = 3;
1298 SELECT * FROM rw_view1;
1305 DROP TABLE base_tbl CASCADE;
1306 NOTICE: drop cascades to view rw_view1
1307 -- views with updatable and non-updatable columns
1308 CREATE TABLE base_tbl(a float);
1309 INSERT INTO base_tbl SELECT i/10.0 FROM generate_series(1,10) g(i);
1310 CREATE VIEW rw_view1 AS
1311 SELECT ctid, sin(a) s, a, cos(a) c
1315 INSERT INTO rw_view1 VALUES (null, null, 1.1, null); -- should fail
1316 ERROR: cannot insert into column "ctid" of view "rw_view1"
1317 DETAIL: View columns that refer to system columns are not updatable.
1318 INSERT INTO rw_view1 (s, c, a) VALUES (null, null, 1.1); -- should fail
1319 ERROR: cannot insert into column "s" of view "rw_view1"
1320 DETAIL: View columns that are not columns of their base relation are not updatable.
1321 INSERT INTO rw_view1 (a) VALUES (1.1) RETURNING a, s, c; -- OK
1323 -----+-------------------+-------------------
1324 1.1 | 0.891207360061435 | 0.453596121425577
1327 UPDATE rw_view1 SET s = s WHERE a = 1.1; -- should fail
1328 ERROR: cannot update column "s" of view "rw_view1"
1329 DETAIL: View columns that are not columns of their base relation are not updatable.
1330 UPDATE rw_view1 SET a = 1.05 WHERE a = 1.1 RETURNING s; -- OK
1336 DELETE FROM rw_view1 WHERE a = 1.05; -- OK
1337 CREATE VIEW rw_view2 AS
1338 SELECT s, c, s/c t, a base_a, ctid
1340 INSERT INTO rw_view2 VALUES (null, null, null, 1.1, null); -- should fail
1341 ERROR: cannot insert into column "t" of view "rw_view2"
1342 DETAIL: View columns that are not columns of their base relation are not updatable.
1343 INSERT INTO rw_view2(s, c, base_a) VALUES (null, null, 1.1); -- should fail
1344 ERROR: cannot insert into column "s" of view "rw_view1"
1345 DETAIL: View columns that are not columns of their base relation are not updatable.
1346 INSERT INTO rw_view2(base_a) VALUES (1.1) RETURNING t; -- OK
1352 UPDATE rw_view2 SET s = s WHERE base_a = 1.1; -- should fail
1353 ERROR: cannot update column "s" of view "rw_view1"
1354 DETAIL: View columns that are not columns of their base relation are not updatable.
1355 UPDATE rw_view2 SET t = t WHERE base_a = 1.1; -- should fail
1356 ERROR: cannot update column "t" of view "rw_view2"
1357 DETAIL: View columns that are not columns of their base relation are not updatable.
1358 UPDATE rw_view2 SET base_a = 1.05 WHERE base_a = 1.1; -- OK
1359 DELETE FROM rw_view2 WHERE base_a = 1.05 RETURNING base_a, s, c, t; -- OK
1361 --------+-------------------+-------------------+------------------
1362 1.05 | 0.867423225594017 | 0.497571047891727 | 1.74331530998317
1365 CREATE VIEW rw_view3 AS
1366 SELECT s, c, s/c t, ctid
1368 INSERT INTO rw_view3 VALUES (null, null, null, null); -- should fail
1369 ERROR: cannot insert into column "t" of view "rw_view3"
1370 DETAIL: View columns that are not columns of their base relation are not updatable.
1371 INSERT INTO rw_view3(s) VALUES (null); -- should fail
1372 ERROR: cannot insert into column "s" of view "rw_view1"
1373 DETAIL: View columns that are not columns of their base relation are not updatable.
1374 UPDATE rw_view3 SET s = s; -- should fail
1375 ERROR: cannot update column "s" of view "rw_view1"
1376 DETAIL: View columns that are not columns of their base relation are not updatable.
1377 DELETE FROM rw_view3 WHERE s = sin(0.1); -- should be OK
1378 SELECT * FROM base_tbl ORDER BY a;
1392 SELECT table_name, is_insertable_into
1393 FROM information_schema.tables
1394 WHERE table_name LIKE E'r_\\_view%'
1395 ORDER BY table_name;
1396 table_name | is_insertable_into
1397 ------------+--------------------
1403 SELECT table_name, is_updatable, is_insertable_into
1404 FROM information_schema.views
1405 WHERE table_name LIKE E'r_\\_view%'
1406 ORDER BY table_name;
1407 table_name | is_updatable | is_insertable_into
1408 ------------+--------------+--------------------
1409 rw_view1 | YES | YES
1410 rw_view2 | YES | YES
1414 SELECT table_name, column_name, is_updatable
1415 FROM information_schema.columns
1416 WHERE table_name LIKE E'r_\\_view%'
1417 ORDER BY table_name, ordinal_position;
1418 table_name | column_name | is_updatable
1419 ------------+-------------+--------------
1420 rw_view1 | ctid | NO
1427 rw_view2 | base_a | YES
1428 rw_view2 | ctid | NO
1432 rw_view3 | ctid | NO
1435 SELECT events & 4 != 0 AS upd,
1436 events & 8 != 0 AS ins,
1437 events & 16 != 0 AS del
1438 FROM pg_catalog.pg_relation_is_updatable('rw_view3'::regclass, false) t(events);
1444 DROP TABLE base_tbl CASCADE;
1445 NOTICE: drop cascades to 3 other objects
1446 DETAIL: drop cascades to view rw_view1
1447 drop cascades to view rw_view2
1448 drop cascades to view rw_view3
1449 -- inheritance tests
1450 CREATE TABLE base_tbl_parent (a int);
1451 CREATE TABLE base_tbl_child (CHECK (a > 0)) INHERITS (base_tbl_parent);
1452 INSERT INTO base_tbl_parent SELECT * FROM generate_series(-8, -1);
1453 INSERT INTO base_tbl_child SELECT * FROM generate_series(1, 8);
1454 CREATE VIEW rw_view1 AS SELECT * FROM base_tbl_parent;
1455 CREATE VIEW rw_view2 AS SELECT * FROM ONLY base_tbl_parent;
1456 SELECT * FROM rw_view1 ORDER BY a;
1477 SELECT * FROM ONLY rw_view1 ORDER BY a;
1498 SELECT * FROM rw_view2 ORDER BY a;
1511 INSERT INTO rw_view1 VALUES (-100), (100);
1512 INSERT INTO rw_view2 VALUES (-200), (200);
1513 UPDATE rw_view1 SET a = a*10 WHERE a IN (-1, 1); -- Should produce -10 and 10
1514 UPDATE ONLY rw_view1 SET a = a*10 WHERE a IN (-2, 2); -- Should produce -20 and 20
1515 UPDATE rw_view2 SET a = a*10 WHERE a IN (-3, 3); -- Should produce -30 only
1516 UPDATE ONLY rw_view2 SET a = a*10 WHERE a IN (-4, 4); -- Should produce -40 only
1517 DELETE FROM rw_view1 WHERE a IN (-5, 5); -- Should delete -5 and 5
1518 DELETE FROM ONLY rw_view1 WHERE a IN (-6, 6); -- Should delete -6 and 6
1519 DELETE FROM rw_view2 WHERE a IN (-7, 7); -- Should delete -7 only
1520 DELETE FROM ONLY rw_view2 WHERE a IN (-8, 8); -- Should delete -8 only
1521 SELECT * FROM ONLY base_tbl_parent ORDER BY a;
1534 SELECT * FROM base_tbl_child ORDER BY a;
1545 CREATE TABLE other_tbl_parent (id int);
1546 CREATE TABLE other_tbl_child () INHERITS (other_tbl_parent);
1547 INSERT INTO other_tbl_parent VALUES (7),(200);
1548 INSERT INTO other_tbl_child VALUES (8),(100);
1550 UPDATE rw_view1 SET a = a + 1000 FROM other_tbl_parent WHERE a = id;
1552 --------------------------------------------------------------
1553 Update on base_tbl_parent
1554 Update on base_tbl_parent
1555 Update on base_tbl_child
1557 Hash Cond: (other_tbl_parent.id = base_tbl_parent.a)
1559 -> Seq Scan on other_tbl_parent
1560 -> Seq Scan on other_tbl_child
1562 -> Seq Scan on base_tbl_parent
1564 Merge Cond: (base_tbl_child.a = other_tbl_parent.id)
1566 Sort Key: base_tbl_child.a
1567 -> Seq Scan on base_tbl_child
1569 Sort Key: other_tbl_parent.id
1571 -> Seq Scan on other_tbl_parent
1572 -> Seq Scan on other_tbl_child
1575 UPDATE rw_view1 SET a = a + 1000 FROM other_tbl_parent WHERE a = id;
1576 SELECT * FROM ONLY base_tbl_parent ORDER BY a;
1589 SELECT * FROM base_tbl_child ORDER BY a;
1600 DROP TABLE base_tbl_parent, base_tbl_child CASCADE;
1601 NOTICE: drop cascades to 2 other objects
1602 DETAIL: drop cascades to view rw_view1
1603 drop cascades to view rw_view2
1604 DROP TABLE other_tbl_parent CASCADE;
1605 NOTICE: drop cascades to table other_tbl_child
1606 -- simple WITH CHECK OPTION
1607 CREATE TABLE base_tbl (a int, b int DEFAULT 10);
1608 INSERT INTO base_tbl VALUES (1,2), (2,3), (1,-1);
1609 CREATE VIEW rw_view1 AS SELECT * FROM base_tbl WHERE a < b
1610 WITH LOCAL CHECK OPTION;
1612 View "public.rw_view1"
1613 Column | Type | Collation | Nullable | Default | Storage | Description
1614 --------+---------+-----------+----------+---------+---------+-------------
1615 a | integer | | | | plain |
1616 b | integer | | | | plain |
1621 WHERE base_tbl.a < base_tbl.b;
1622 Options: check_option=local
1624 SELECT * FROM information_schema.views WHERE table_name = 'rw_view1';
1625 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
1626 ---------------+--------------+------------+------------------------------------+--------------+--------------+--------------------+----------------------+----------------------+----------------------------
1627 regression | public | rw_view1 | SELECT base_tbl.a, +| LOCAL | YES | YES | NO | NO | NO
1628 | | | base_tbl.b +| | | | | |
1629 | | | FROM base_tbl +| | | | | |
1630 | | | WHERE (base_tbl.a < base_tbl.b); | | | | | |
1633 INSERT INTO rw_view1 VALUES(3,4); -- ok
1634 INSERT INTO rw_view1 VALUES(4,3); -- should fail
1635 ERROR: new row violates check option for view "rw_view1"
1636 DETAIL: Failing row contains (4, 3).
1637 INSERT INTO rw_view1 VALUES(5,null); -- should fail
1638 ERROR: new row violates check option for view "rw_view1"
1639 DETAIL: Failing row contains (5, null).
1640 UPDATE rw_view1 SET b = 5 WHERE a = 3; -- ok
1641 UPDATE rw_view1 SET b = -5 WHERE a = 3; -- should fail
1642 ERROR: new row violates check option for view "rw_view1"
1643 DETAIL: Failing row contains (3, -5).
1644 INSERT INTO rw_view1(a) VALUES (9); -- ok
1645 INSERT INTO rw_view1(a) VALUES (10); -- should fail
1646 ERROR: new row violates check option for view "rw_view1"
1647 DETAIL: Failing row contains (10, 10).
1648 SELECT * FROM base_tbl;
1658 DROP TABLE base_tbl CASCADE;
1659 NOTICE: drop cascades to view rw_view1
1660 -- WITH LOCAL/CASCADED CHECK OPTION
1661 CREATE TABLE base_tbl (a int);
1662 CREATE VIEW rw_view1 AS SELECT * FROM base_tbl WHERE a > 0;
1663 CREATE VIEW rw_view2 AS SELECT * FROM rw_view1 WHERE a < 10
1664 WITH CHECK OPTION; -- implicitly cascaded
1666 View "public.rw_view2"
1667 Column | Type | Collation | Nullable | Default | Storage | Description
1668 --------+---------+-----------+----------+---------+---------+-------------
1669 a | integer | | | | plain |
1673 WHERE rw_view1.a < 10;
1674 Options: check_option=cascaded
1676 SELECT * FROM information_schema.views WHERE table_name = 'rw_view2';
1677 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
1678 ---------------+--------------+------------+----------------------------+--------------+--------------+--------------------+----------------------+----------------------+----------------------------
1679 regression | public | rw_view2 | SELECT rw_view1.a +| CASCADED | YES | YES | NO | NO | NO
1680 | | | FROM rw_view1 +| | | | | |
1681 | | | WHERE (rw_view1.a < 10); | | | | | |
1684 INSERT INTO rw_view2 VALUES (-5); -- should fail
1685 ERROR: new row violates check option for view "rw_view1"
1686 DETAIL: Failing row contains (-5).
1687 INSERT INTO rw_view2 VALUES (5); -- ok
1688 INSERT INTO rw_view2 VALUES (15); -- should fail
1689 ERROR: new row violates check option for view "rw_view2"
1690 DETAIL: Failing row contains (15).
1691 SELECT * FROM base_tbl;
1697 UPDATE rw_view2 SET a = a - 10; -- should fail
1698 ERROR: new row violates check option for view "rw_view1"
1699 DETAIL: Failing row contains (-5).
1700 UPDATE rw_view2 SET a = a + 10; -- should fail
1701 ERROR: new row violates check option for view "rw_view2"
1702 DETAIL: Failing row contains (15).
1703 CREATE OR REPLACE VIEW rw_view2 AS SELECT * FROM rw_view1 WHERE a < 10
1704 WITH LOCAL CHECK OPTION;
1706 View "public.rw_view2"
1707 Column | Type | Collation | Nullable | Default | Storage | Description
1708 --------+---------+-----------+----------+---------+---------+-------------
1709 a | integer | | | | plain |
1713 WHERE rw_view1.a < 10;
1714 Options: check_option=local
1716 SELECT * FROM information_schema.views WHERE table_name = 'rw_view2';
1717 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
1718 ---------------+--------------+------------+----------------------------+--------------+--------------+--------------------+----------------------+----------------------+----------------------------
1719 regression | public | rw_view2 | SELECT rw_view1.a +| LOCAL | YES | YES | NO | NO | NO
1720 | | | FROM rw_view1 +| | | | | |
1721 | | | WHERE (rw_view1.a < 10); | | | | | |
1724 INSERT INTO rw_view2 VALUES (-10); -- ok, but not in view
1725 INSERT INTO rw_view2 VALUES (20); -- should fail
1726 ERROR: new row violates check option for view "rw_view2"
1727 DETAIL: Failing row contains (20).
1728 SELECT * FROM base_tbl;
1735 ALTER VIEW rw_view1 SET (check_option=here); -- invalid
1736 ERROR: invalid value for "check_option" option
1737 DETAIL: Valid values are "local" and "cascaded".
1738 ALTER VIEW rw_view1 SET (check_option=local);
1739 INSERT INTO rw_view2 VALUES (-20); -- should fail
1740 ERROR: new row violates check option for view "rw_view1"
1741 DETAIL: Failing row contains (-20).
1742 INSERT INTO rw_view2 VALUES (30); -- should fail
1743 ERROR: new row violates check option for view "rw_view2"
1744 DETAIL: Failing row contains (30).
1745 ALTER VIEW rw_view2 RESET (check_option);
1747 View "public.rw_view2"
1748 Column | Type | Collation | Nullable | Default | Storage | Description
1749 --------+---------+-----------+----------+---------+---------+-------------
1750 a | integer | | | | plain |
1754 WHERE rw_view1.a < 10;
1756 SELECT * FROM information_schema.views WHERE table_name = 'rw_view2';
1757 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
1758 ---------------+--------------+------------+----------------------------+--------------+--------------+--------------------+----------------------+----------------------+----------------------------
1759 regression | public | rw_view2 | SELECT rw_view1.a +| NONE | YES | YES | NO | NO | NO
1760 | | | FROM rw_view1 +| | | | | |
1761 | | | WHERE (rw_view1.a < 10); | | | | | |
1764 INSERT INTO rw_view2 VALUES (30); -- ok, but not in view
1765 SELECT * FROM base_tbl;
1773 DROP TABLE base_tbl CASCADE;
1774 NOTICE: drop cascades to 2 other objects
1775 DETAIL: drop cascades to view rw_view1
1776 drop cascades to view rw_view2
1777 -- WITH CHECK OPTION with no local view qual
1778 CREATE TABLE base_tbl (a int);
1779 CREATE VIEW rw_view1 AS SELECT * FROM base_tbl WITH CHECK OPTION;
1780 CREATE VIEW rw_view2 AS SELECT * FROM rw_view1 WHERE a > 0;
1781 CREATE VIEW rw_view3 AS SELECT * FROM rw_view2 WITH CHECK OPTION;
1782 SELECT * FROM information_schema.views WHERE table_name LIKE E'rw\\_view_' ORDER BY table_name;
1783 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
1784 ---------------+--------------+------------+---------------------------+--------------+--------------+--------------------+----------------------+----------------------+----------------------------
1785 regression | public | rw_view1 | SELECT base_tbl.a +| CASCADED | YES | YES | NO | NO | NO
1786 | | | FROM base_tbl; | | | | | |
1787 regression | public | rw_view2 | SELECT rw_view1.a +| NONE | YES | YES | NO | NO | NO
1788 | | | FROM rw_view1 +| | | | | |
1789 | | | WHERE (rw_view1.a > 0); | | | | | |
1790 regression | public | rw_view3 | SELECT rw_view2.a +| CASCADED | YES | YES | NO | NO | NO
1791 | | | FROM rw_view2; | | | | | |
1794 INSERT INTO rw_view1 VALUES (-1); -- ok
1795 INSERT INTO rw_view1 VALUES (1); -- ok
1796 INSERT INTO rw_view2 VALUES (-2); -- ok, but not in view
1797 INSERT INTO rw_view2 VALUES (2); -- ok
1798 INSERT INTO rw_view3 VALUES (-3); -- should fail
1799 ERROR: new row violates check option for view "rw_view2"
1800 DETAIL: Failing row contains (-3).
1801 INSERT INTO rw_view3 VALUES (3); -- ok
1802 DROP TABLE base_tbl CASCADE;
1803 NOTICE: drop cascades to 3 other objects
1804 DETAIL: drop cascades to view rw_view1
1805 drop cascades to view rw_view2
1806 drop cascades to view rw_view3
1807 -- WITH CHECK OPTION with scalar array ops
1808 CREATE TABLE base_tbl (a int, b int[]);
1809 CREATE VIEW rw_view1 AS SELECT * FROM base_tbl WHERE a = ANY (b)
1811 INSERT INTO rw_view1 VALUES (1, ARRAY[1,2,3]); -- ok
1812 INSERT INTO rw_view1 VALUES (10, ARRAY[4,5]); -- should fail
1813 ERROR: new row violates check option for view "rw_view1"
1814 DETAIL: Failing row contains (10, {4,5}).
1815 UPDATE rw_view1 SET b[2] = -b[2] WHERE a = 1; -- ok
1816 UPDATE rw_view1 SET b[1] = -b[1] WHERE a = 1; -- should fail
1817 ERROR: new row violates check option for view "rw_view1"
1818 DETAIL: Failing row contains (1, {-1,-2,3}).
1819 PREPARE ins(int, int[]) AS INSERT INTO rw_view1 VALUES($1, $2);
1820 EXECUTE ins(2, ARRAY[1,2,3]); -- ok
1821 EXECUTE ins(10, ARRAY[4,5]); -- should fail
1822 ERROR: new row violates check option for view "rw_view1"
1823 DETAIL: Failing row contains (10, {4,5}).
1824 DEALLOCATE PREPARE ins;
1825 DROP TABLE base_tbl CASCADE;
1826 NOTICE: drop cascades to view rw_view1
1827 -- WITH CHECK OPTION with subquery
1828 CREATE TABLE base_tbl (a int);
1829 CREATE TABLE ref_tbl (a int PRIMARY KEY);
1830 INSERT INTO ref_tbl SELECT * FROM generate_series(1,10);
1831 CREATE VIEW rw_view1 AS
1832 SELECT * FROM base_tbl b
1833 WHERE EXISTS(SELECT 1 FROM ref_tbl r WHERE r.a = b.a)
1835 INSERT INTO rw_view1 VALUES (5); -- ok
1836 INSERT INTO rw_view1 VALUES (15); -- should fail
1837 ERROR: new row violates check option for view "rw_view1"
1838 DETAIL: Failing row contains (15).
1839 UPDATE rw_view1 SET a = a + 5; -- ok
1840 UPDATE rw_view1 SET a = a + 5; -- should fail
1841 ERROR: new row violates check option for view "rw_view1"
1842 DETAIL: Failing row contains (15).
1843 EXPLAIN (costs off) INSERT INTO rw_view1 VALUES (5);
1845 ---------------------------------------------------------
1846 Insert on base_tbl b
1849 -> Index Only Scan using ref_tbl_pkey on ref_tbl r
1850 Index Cond: (a = b.a)
1852 -> Seq Scan on ref_tbl r_1
1855 EXPLAIN (costs off) UPDATE rw_view1 SET a = a + 5;
1857 -----------------------------------------------------------
1858 Update on base_tbl b
1860 Hash Cond: (b.a = r.a)
1861 -> Seq Scan on base_tbl b
1863 -> Seq Scan on ref_tbl r
1865 -> Index Only Scan using ref_tbl_pkey on ref_tbl r_1
1866 Index Cond: (a = b.a)
1868 -> Seq Scan on ref_tbl r_2
1871 DROP TABLE base_tbl, ref_tbl CASCADE;
1872 NOTICE: drop cascades to view rw_view1
1873 -- WITH CHECK OPTION with BEFORE trigger on base table
1874 CREATE TABLE base_tbl (a int, b int);
1875 CREATE FUNCTION base_tbl_trig_fn()
1884 CREATE TRIGGER base_tbl_trig BEFORE INSERT OR UPDATE ON base_tbl
1885 FOR EACH ROW EXECUTE PROCEDURE base_tbl_trig_fn();
1886 CREATE VIEW rw_view1 AS SELECT * FROM base_tbl WHERE a < b WITH CHECK OPTION;
1887 INSERT INTO rw_view1 VALUES (5,0); -- ok
1888 INSERT INTO rw_view1 VALUES (15, 20); -- should fail
1889 ERROR: new row violates check option for view "rw_view1"
1890 DETAIL: Failing row contains (15, 10).
1891 UPDATE rw_view1 SET a = 20, b = 30; -- should fail
1892 ERROR: new row violates check option for view "rw_view1"
1893 DETAIL: Failing row contains (20, 10).
1894 DROP TABLE base_tbl CASCADE;
1895 NOTICE: drop cascades to view rw_view1
1896 DROP FUNCTION base_tbl_trig_fn();
1897 -- WITH LOCAL CHECK OPTION with INSTEAD OF trigger on base view
1898 CREATE TABLE base_tbl (a int, b int);
1899 CREATE VIEW rw_view1 AS SELECT a FROM base_tbl WHERE a < b;
1900 CREATE FUNCTION rw_view1_trig_fn()
1904 IF TG_OP = 'INSERT' THEN
1905 INSERT INTO base_tbl VALUES (NEW.a, 10);
1907 ELSIF TG_OP = 'UPDATE' THEN
1908 UPDATE base_tbl SET a=NEW.a WHERE a=OLD.a;
1910 ELSIF TG_OP = 'DELETE' THEN
1911 DELETE FROM base_tbl WHERE a=OLD.a;
1917 CREATE TRIGGER rw_view1_trig
1918 INSTEAD OF INSERT OR UPDATE OR DELETE ON rw_view1
1919 FOR EACH ROW EXECUTE PROCEDURE rw_view1_trig_fn();
1920 CREATE VIEW rw_view2 AS
1921 SELECT * FROM rw_view1 WHERE a > 0 WITH LOCAL CHECK OPTION;
1922 INSERT INTO rw_view2 VALUES (-5); -- should fail
1923 ERROR: new row violates check option for view "rw_view2"
1924 DETAIL: Failing row contains (-5).
1925 INSERT INTO rw_view2 VALUES (5); -- ok
1926 INSERT INTO rw_view2 VALUES (50); -- ok, but not in view
1927 UPDATE rw_view2 SET a = a - 10; -- should fail
1928 ERROR: new row violates check option for view "rw_view2"
1929 DETAIL: Failing row contains (-5).
1930 SELECT * FROM base_tbl;
1937 -- Check option won't cascade down to base view with INSTEAD OF triggers
1938 ALTER VIEW rw_view2 SET (check_option=cascaded);
1939 INSERT INTO rw_view2 VALUES (100); -- ok, but not in view (doesn't fail rw_view1's check)
1940 UPDATE rw_view2 SET a = 200 WHERE a = 5; -- ok, but not in view (doesn't fail rw_view1's check)
1941 SELECT * FROM base_tbl;
1949 -- Neither local nor cascaded check options work with INSTEAD rules
1950 DROP TRIGGER rw_view1_trig ON rw_view1;
1951 CREATE RULE rw_view1_ins_rule AS ON INSERT TO rw_view1
1952 DO INSTEAD INSERT INTO base_tbl VALUES (NEW.a, 10);
1953 CREATE RULE rw_view1_upd_rule AS ON UPDATE TO rw_view1
1954 DO INSTEAD UPDATE base_tbl SET a=NEW.a WHERE a=OLD.a;
1955 INSERT INTO rw_view2 VALUES (-10); -- ok, but not in view (doesn't fail rw_view2's check)
1956 INSERT INTO rw_view2 VALUES (5); -- ok
1957 INSERT INTO rw_view2 VALUES (20); -- ok, but not in view (doesn't fail rw_view1's check)
1958 UPDATE rw_view2 SET a = 30 WHERE a = 5; -- ok, but not in view (doesn't fail rw_view1's check)
1959 INSERT INTO rw_view2 VALUES (5); -- ok
1960 UPDATE rw_view2 SET a = -5 WHERE a = 5; -- ok, but not in view (doesn't fail rw_view2's check)
1961 SELECT * FROM base_tbl;
1973 DROP TABLE base_tbl CASCADE;
1974 NOTICE: drop cascades to 2 other objects
1975 DETAIL: drop cascades to view rw_view1
1976 drop cascades to view rw_view2
1977 DROP FUNCTION rw_view1_trig_fn();
1978 CREATE TABLE base_tbl (a int);
1979 CREATE VIEW rw_view1 AS SELECT a,10 AS b FROM base_tbl;
1980 CREATE RULE rw_view1_ins_rule AS ON INSERT TO rw_view1
1981 DO INSTEAD INSERT INTO base_tbl VALUES (NEW.a);
1982 CREATE VIEW rw_view2 AS
1983 SELECT * FROM rw_view1 WHERE a > b WITH LOCAL CHECK OPTION;
1984 INSERT INTO rw_view2 VALUES (2,3); -- ok, but not in view (doesn't fail rw_view2's check)
1985 DROP TABLE base_tbl CASCADE;
1986 NOTICE: drop cascades to 2 other objects
1987 DETAIL: drop cascades to view rw_view1
1988 drop cascades to view rw_view2
1989 -- security barrier view
1990 CREATE TABLE base_tbl (person text, visibility text);
1991 INSERT INTO base_tbl VALUES ('Tom', 'public'),
1992 ('Dick', 'private'),
1993 ('Harry', 'public');
1994 CREATE VIEW rw_view1 AS
1995 SELECT person FROM base_tbl WHERE visibility = 'public';
1996 CREATE FUNCTION snoop(anyelement)
2000 RAISE NOTICE 'snooped value: %', $1;
2004 LANGUAGE plpgsql COST 0.000001;
2005 CREATE OR REPLACE FUNCTION leakproof(anyelement)
2012 LANGUAGE plpgsql STRICT IMMUTABLE LEAKPROOF;
2013 SELECT * FROM rw_view1 WHERE snoop(person);
2014 NOTICE: snooped value: Tom
2015 NOTICE: snooped value: Dick
2016 NOTICE: snooped value: Harry
2023 UPDATE rw_view1 SET person=person WHERE snoop(person);
2024 NOTICE: snooped value: Tom
2025 NOTICE: snooped value: Dick
2026 NOTICE: snooped value: Harry
2027 DELETE FROM rw_view1 WHERE NOT snoop(person);
2028 NOTICE: snooped value: Dick
2029 NOTICE: snooped value: Tom
2030 NOTICE: snooped value: Harry
2031 ALTER VIEW rw_view1 SET (security_barrier = true);
2032 SELECT table_name, is_insertable_into
2033 FROM information_schema.tables
2034 WHERE table_name = 'rw_view1';
2035 table_name | is_insertable_into
2036 ------------+--------------------
2040 SELECT table_name, is_updatable, is_insertable_into
2041 FROM information_schema.views
2042 WHERE table_name = 'rw_view1';
2043 table_name | is_updatable | is_insertable_into
2044 ------------+--------------+--------------------
2045 rw_view1 | YES | YES
2048 SELECT table_name, column_name, is_updatable
2049 FROM information_schema.columns
2050 WHERE table_name = 'rw_view1'
2051 ORDER BY ordinal_position;
2052 table_name | column_name | is_updatable
2053 ------------+-------------+--------------
2054 rw_view1 | person | YES
2057 SELECT * FROM rw_view1 WHERE snoop(person);
2058 NOTICE: snooped value: Tom
2059 NOTICE: snooped value: Harry
2066 UPDATE rw_view1 SET person=person WHERE snoop(person);
2067 NOTICE: snooped value: Tom
2068 NOTICE: snooped value: Harry
2069 DELETE FROM rw_view1 WHERE NOT snoop(person);
2070 NOTICE: snooped value: Tom
2071 NOTICE: snooped value: Harry
2072 EXPLAIN (costs off) SELECT * FROM rw_view1 WHERE snoop(person);
2074 -----------------------------------------------
2075 Subquery Scan on rw_view1
2076 Filter: snoop(rw_view1.person)
2077 -> Seq Scan on base_tbl
2078 Filter: (visibility = 'public'::text)
2081 EXPLAIN (costs off) UPDATE rw_view1 SET person=person WHERE snoop(person);
2083 -------------------------------------------------------------------
2085 -> Seq Scan on base_tbl
2086 Filter: ((visibility = 'public'::text) AND snoop(person))
2089 EXPLAIN (costs off) DELETE FROM rw_view1 WHERE NOT snoop(person);
2091 -------------------------------------------------------------------------
2093 -> Seq Scan on base_tbl
2094 Filter: ((visibility = 'public'::text) AND (NOT snoop(person)))
2097 -- security barrier view on top of security barrier view
2098 CREATE VIEW rw_view2 WITH (security_barrier = true) AS
2099 SELECT * FROM rw_view1 WHERE snoop(person);
2100 SELECT table_name, is_insertable_into
2101 FROM information_schema.tables
2102 WHERE table_name = 'rw_view2';
2103 table_name | is_insertable_into
2104 ------------+--------------------
2108 SELECT table_name, is_updatable, is_insertable_into
2109 FROM information_schema.views
2110 WHERE table_name = 'rw_view2';
2111 table_name | is_updatable | is_insertable_into
2112 ------------+--------------+--------------------
2113 rw_view2 | YES | YES
2116 SELECT table_name, column_name, is_updatable
2117 FROM information_schema.columns
2118 WHERE table_name = 'rw_view2'
2119 ORDER BY ordinal_position;
2120 table_name | column_name | is_updatable
2121 ------------+-------------+--------------
2122 rw_view2 | person | YES
2125 SELECT * FROM rw_view2 WHERE snoop(person);
2126 NOTICE: snooped value: Tom
2127 NOTICE: snooped value: Tom
2128 NOTICE: snooped value: Harry
2129 NOTICE: snooped value: Harry
2136 UPDATE rw_view2 SET person=person WHERE snoop(person);
2137 NOTICE: snooped value: Tom
2138 NOTICE: snooped value: Tom
2139 NOTICE: snooped value: Harry
2140 NOTICE: snooped value: Harry
2141 DELETE FROM rw_view2 WHERE NOT snoop(person);
2142 NOTICE: snooped value: Tom
2143 NOTICE: snooped value: Tom
2144 NOTICE: snooped value: Harry
2145 NOTICE: snooped value: Harry
2146 EXPLAIN (costs off) SELECT * FROM rw_view2 WHERE snoop(person);
2148 -----------------------------------------------------
2149 Subquery Scan on rw_view2
2150 Filter: snoop(rw_view2.person)
2151 -> Subquery Scan on rw_view1
2152 Filter: snoop(rw_view1.person)
2153 -> Seq Scan on base_tbl
2154 Filter: (visibility = 'public'::text)
2157 EXPLAIN (costs off) UPDATE rw_view2 SET person=person WHERE snoop(person);
2159 -------------------------------------------------------------------------------------
2161 -> Seq Scan on base_tbl
2162 Filter: ((visibility = 'public'::text) AND snoop(person) AND snoop(person))
2165 EXPLAIN (costs off) DELETE FROM rw_view2 WHERE NOT snoop(person);
2167 -------------------------------------------------------------------------------------------
2169 -> Seq Scan on base_tbl
2170 Filter: ((visibility = 'public'::text) AND snoop(person) AND (NOT snoop(person)))
2173 DROP TABLE base_tbl CASCADE;
2174 NOTICE: drop cascades to 2 other objects
2175 DETAIL: drop cascades to view rw_view1
2176 drop cascades to view rw_view2
2177 -- security barrier view on top of table with rules
2178 CREATE TABLE base_tbl(id int PRIMARY KEY, data text, deleted boolean);
2179 INSERT INTO base_tbl VALUES (1, 'Row 1', false), (2, 'Row 2', true);
2180 CREATE RULE base_tbl_ins_rule AS ON INSERT TO base_tbl
2181 WHERE EXISTS (SELECT 1 FROM base_tbl t WHERE t.id = new.id)
2183 UPDATE base_tbl SET data = new.data, deleted = false WHERE id = new.id;
2184 CREATE RULE base_tbl_del_rule AS ON DELETE TO base_tbl
2186 UPDATE base_tbl SET deleted = true WHERE id = old.id;
2187 CREATE VIEW rw_view1 WITH (security_barrier=true) AS
2188 SELECT id, data FROM base_tbl WHERE NOT deleted;
2189 SELECT * FROM rw_view1;
2195 EXPLAIN (costs off) DELETE FROM rw_view1 WHERE id = 1 AND snoop(data);
2197 -------------------------------------------------------------------
2198 Update on base_tbl base_tbl_1
2200 -> Index Scan using base_tbl_pkey on base_tbl base_tbl_1
2201 Index Cond: (id = 1)
2202 -> Index Scan using base_tbl_pkey on base_tbl
2203 Index Cond: (id = 1)
2204 Filter: ((NOT deleted) AND snoop(data))
2207 DELETE FROM rw_view1 WHERE id = 1 AND snoop(data);
2208 NOTICE: snooped value: Row 1
2209 EXPLAIN (costs off) INSERT INTO rw_view1 VALUES (2, 'New row 2');
2211 -----------------------------------------------------------
2213 InitPlan 1 (returns $0)
2214 -> Index Only Scan using base_tbl_pkey on base_tbl t
2215 Index Cond: (id = 2)
2217 One-Time Filter: ($0 IS NOT TRUE)
2220 InitPlan 1 (returns $0)
2221 -> Index Only Scan using base_tbl_pkey on base_tbl t
2222 Index Cond: (id = 2)
2225 -> Index Scan using base_tbl_pkey on base_tbl
2226 Index Cond: (id = 2)
2229 INSERT INTO rw_view1 VALUES (2, 'New row 2');
2230 SELECT * FROM base_tbl;
2232 ----+-----------+---------
2237 DROP TABLE base_tbl CASCADE;
2238 NOTICE: drop cascades to view rw_view1
2239 -- security barrier view based on inheritance set
2240 CREATE TABLE t1 (a int, b float, c text);
2241 CREATE INDEX t1_a_idx ON t1(a);
2243 SELECT i,i,'t1' FROM generate_series(1,10) g(i);
2245 CREATE TABLE t11 (d text) INHERITS (t1);
2246 CREATE INDEX t11_a_idx ON t11(a);
2248 SELECT i,i,'t11','t11d' FROM generate_series(1,10) g(i);
2250 CREATE TABLE t12 (e int[]) INHERITS (t1);
2251 CREATE INDEX t12_a_idx ON t12(a);
2253 SELECT i,i,'t12','{1,2}'::int[] FROM generate_series(1,10) g(i);
2255 CREATE TABLE t111 () INHERITS (t11, t12);
2256 NOTICE: merging multiple inherited definitions of column "a"
2257 NOTICE: merging multiple inherited definitions of column "b"
2258 NOTICE: merging multiple inherited definitions of column "c"
2259 CREATE INDEX t111_a_idx ON t111(a);
2261 SELECT i,i,'t111','t111d','{1,1,1}'::int[] FROM generate_series(1,10) g(i);
2263 CREATE VIEW v1 WITH (security_barrier=true) AS
2264 SELECT *, (SELECT d FROM t11 WHERE t11.a = t1.a LIMIT 1) AS d
2266 WHERE a > 5 AND EXISTS(SELECT 1 FROM t12 WHERE t12.a = t1.a);
2267 SELECT * FROM v1 WHERE a=3; -- should not see anything
2272 SELECT * FROM v1 WHERE a=8;
2274 ---+---+------+------
2281 EXPLAIN (VERBOSE, COSTS OFF)
2282 UPDATE v1 SET a=100 WHERE snoop(a) AND leakproof(a) AND a < 7 AND a != 6;
2284 ---------------------------------------------------------------------------------------------------------------------------
2287 Update on public.t11
2288 Update on public.t12
2289 Update on public.t111
2290 -> Index Scan using t1_a_idx on public.t1
2291 Output: 100, t1.b, t1.c, t1.ctid
2292 Index Cond: ((t1.a > 5) AND (t1.a < 7))
2293 Filter: ((t1.a <> 6) AND (alternatives: SubPlan 1 or hashed SubPlan 2) AND snoop(t1.a) AND leakproof(t1.a))
2296 -> Seq Scan on public.t12 t12_1
2297 Filter: (t12_1.a = t1.a)
2298 -> Seq Scan on public.t111 t111_1
2299 Filter: (t111_1.a = t1.a)
2302 -> Seq Scan on public.t12 t12_2
2304 -> Seq Scan on public.t111 t111_2
2306 -> Index Scan using t11_a_idx on public.t11
2307 Output: 100, t11.b, t11.c, t11.d, t11.ctid
2308 Index Cond: ((t11.a > 5) AND (t11.a < 7))
2309 Filter: ((t11.a <> 6) AND (alternatives: SubPlan 1 or hashed SubPlan 2) AND snoop(t11.a) AND leakproof(t11.a))
2310 -> Index Scan using t12_a_idx on public.t12
2311 Output: 100, t12.b, t12.c, t12.e, t12.ctid
2312 Index Cond: ((t12.a > 5) AND (t12.a < 7))
2313 Filter: ((t12.a <> 6) AND (alternatives: SubPlan 1 or hashed SubPlan 2) AND snoop(t12.a) AND leakproof(t12.a))
2314 -> Index Scan using t111_a_idx on public.t111
2315 Output: 100, t111.b, t111.c, t111.d, t111.e, t111.ctid
2316 Index Cond: ((t111.a > 5) AND (t111.a < 7))
2317 Filter: ((t111.a <> 6) AND (alternatives: SubPlan 1 or hashed SubPlan 2) AND snoop(t111.a) AND leakproof(t111.a))
2320 UPDATE v1 SET a=100 WHERE snoop(a) AND leakproof(a) AND a < 7 AND a != 6;
2321 SELECT * FROM v1 WHERE a=100; -- Nothing should have been changed to 100
2326 SELECT * FROM t1 WHERE a=100; -- Nothing should have been changed to 100
2331 EXPLAIN (VERBOSE, COSTS OFF)
2332 UPDATE v1 SET a=a+1 WHERE snoop(a) AND leakproof(a) AND a = 8;
2334 ---------------------------------------------------------------------------------------------------------
2337 Update on public.t11
2338 Update on public.t12
2339 Update on public.t111
2340 -> Index Scan using t1_a_idx on public.t1
2341 Output: (t1.a + 1), t1.b, t1.c, t1.ctid
2342 Index Cond: ((t1.a > 5) AND (t1.a = 8))
2343 Filter: ((alternatives: SubPlan 1 or hashed SubPlan 2) AND snoop(t1.a) AND leakproof(t1.a))
2346 -> Seq Scan on public.t12 t12_1
2347 Filter: (t12_1.a = t1.a)
2348 -> Seq Scan on public.t111 t111_1
2349 Filter: (t111_1.a = t1.a)
2352 -> Seq Scan on public.t12 t12_2
2354 -> Seq Scan on public.t111 t111_2
2356 -> Index Scan using t11_a_idx on public.t11
2357 Output: (t11.a + 1), t11.b, t11.c, t11.d, t11.ctid
2358 Index Cond: ((t11.a > 5) AND (t11.a = 8))
2359 Filter: ((alternatives: SubPlan 1 or hashed SubPlan 2) AND snoop(t11.a) AND leakproof(t11.a))
2360 -> Index Scan using t12_a_idx on public.t12
2361 Output: (t12.a + 1), t12.b, t12.c, t12.e, t12.ctid
2362 Index Cond: ((t12.a > 5) AND (t12.a = 8))
2363 Filter: ((alternatives: SubPlan 1 or hashed SubPlan 2) AND snoop(t12.a) AND leakproof(t12.a))
2364 -> Index Scan using t111_a_idx on public.t111
2365 Output: (t111.a + 1), t111.b, t111.c, t111.d, t111.e, t111.ctid
2366 Index Cond: ((t111.a > 5) AND (t111.a = 8))
2367 Filter: ((alternatives: SubPlan 1 or hashed SubPlan 2) AND snoop(t111.a) AND leakproof(t111.a))
2370 UPDATE v1 SET a=a+1 WHERE snoop(a) AND leakproof(a) AND a = 8;
2371 NOTICE: snooped value: 8
2372 NOTICE: snooped value: 8
2373 NOTICE: snooped value: 8
2374 NOTICE: snooped value: 8
2375 SELECT * FROM v1 WHERE b=8;
2377 ---+---+------+------
2384 DELETE FROM v1 WHERE snoop(a) AND leakproof(a); -- should not delete everything, just where a>5
2385 NOTICE: snooped value: 6
2386 NOTICE: snooped value: 7
2387 NOTICE: snooped value: 9
2388 NOTICE: snooped value: 10
2389 NOTICE: snooped value: 9
2390 NOTICE: snooped value: 6
2391 NOTICE: snooped value: 7
2392 NOTICE: snooped value: 9
2393 NOTICE: snooped value: 10
2394 NOTICE: snooped value: 9
2395 NOTICE: snooped value: 6
2396 NOTICE: snooped value: 7
2397 NOTICE: snooped value: 9
2398 NOTICE: snooped value: 10
2399 NOTICE: snooped value: 9
2400 NOTICE: snooped value: 6
2401 NOTICE: snooped value: 7
2402 NOTICE: snooped value: 9
2403 NOTICE: snooped value: 10
2404 NOTICE: snooped value: 9
2405 TABLE t1; -- verify all a<=5 are intact
2430 DROP TABLE t1, t11, t12, t111 CASCADE;
2431 NOTICE: drop cascades to view v1
2432 DROP FUNCTION snoop(anyelement);
2433 DROP FUNCTION leakproof(anyelement);
2434 CREATE TABLE tx1 (a integer);
2435 CREATE TABLE tx2 (b integer);
2436 CREATE TABLE tx3 (c integer);
2437 CREATE VIEW vx1 AS SELECT a FROM tx1 WHERE EXISTS(SELECT 1 FROM tx2 JOIN tx3 ON b=c);
2438 INSERT INTO vx1 values (1);
2454 CREATE TABLE tx1 (a integer);
2455 CREATE TABLE tx2 (b integer);
2456 CREATE TABLE tx3 (c integer);
2457 CREATE VIEW vx1 AS SELECT a FROM tx1 WHERE EXISTS(SELECT 1 FROM tx2 JOIN tx3 ON b=c);
2458 INSERT INTO vx1 VALUES (1);
2459 INSERT INTO vx1 VALUES (1);
2476 CREATE TABLE tx1 (a integer, b integer);
2477 CREATE TABLE tx2 (b integer, c integer);
2478 CREATE TABLE tx3 (c integer, d integer);
2479 ALTER TABLE tx1 DROP COLUMN b;
2480 ALTER TABLE tx2 DROP COLUMN c;
2481 ALTER TABLE tx3 DROP COLUMN d;
2482 CREATE VIEW vx1 AS SELECT a FROM tx1 WHERE EXISTS(SELECT 1 FROM tx2 JOIN tx3 ON b=c);
2483 INSERT INTO vx1 VALUES (1);
2484 INSERT INTO vx1 VALUES (1);
2502 -- Test handling of vars from correlated subqueries in quals from outer
2503 -- security barrier views, per bug #13988
2505 CREATE TABLE t1 (a int, b text, c int);
2506 INSERT INTO t1 VALUES (1, 'one', 10);
2507 CREATE TABLE t2 (cc int);
2508 INSERT INTO t2 VALUES (10), (20);
2509 CREATE VIEW v1 WITH (security_barrier = true) AS
2510 SELECT * FROM t1 WHERE (a > 0)
2512 CREATE VIEW v2 WITH (security_barrier = true) AS
2513 SELECT * FROM v1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.cc = v1.c)
2515 INSERT INTO v2 VALUES (2, 'two', 20); -- ok
2516 INSERT INTO v2 VALUES (-2, 'minus two', 20); -- not allowed
2517 ERROR: new row violates check option for view "v1"
2518 DETAIL: Failing row contains (-2, minus two, 20).
2519 INSERT INTO v2 VALUES (3, 'three', 30); -- not allowed
2520 ERROR: new row violates check option for view "v2"
2521 DETAIL: Failing row contains (3, three, 30).
2522 UPDATE v2 SET b = 'ONE' WHERE a = 1; -- ok
2523 UPDATE v2 SET a = -1 WHERE a = 1; -- not allowed
2524 ERROR: new row violates check option for view "v1"
2525 DETAIL: Failing row contains (-1, ONE, 10).
2526 UPDATE v2 SET c = 30 WHERE a = 1; -- not allowed
2527 ERROR: new row violates check option for view "v2"
2528 DETAIL: Failing row contains (1, ONE, 30).
2529 DELETE FROM v2 WHERE a = 2; -- ok
2541 -- Test CREATE OR REPLACE VIEW turning a non-updatable view into an
2542 -- auto-updatable view and adding check options in a single step
2544 CREATE TABLE t1 (a int, b text);
2545 CREATE VIEW v1 AS SELECT null::int AS a;
2546 CREATE OR REPLACE VIEW v1 AS SELECT * FROM t1 WHERE a > 0 WITH CHECK OPTION;
2547 INSERT INTO v1 VALUES (1, 'ok'); -- ok
2548 INSERT INTO v1 VALUES (-1, 'invalid'); -- should fail
2549 ERROR: new row violates check option for view "v1"
2550 DETAIL: Failing row contains (-1, invalid).
2553 -- check that an auto-updatable view on a partitioned table works correctly
2554 create table uv_pt (a int, b int, v varchar) partition by range (a, b);
2555 create table uv_pt1 (b int not null, v varchar, a int not null) partition by range (b);
2556 create table uv_pt11 (like uv_pt1);
2557 alter table uv_pt11 drop a;
2558 alter table uv_pt11 add a int;
2559 alter table uv_pt11 drop a;
2560 alter table uv_pt11 add a int not null;
2561 alter table uv_pt1 attach partition uv_pt11 for values from (2) to (5);
2562 alter table uv_pt attach partition uv_pt1 for values from (1, 2) to (1, 10);
2563 create view uv_ptv as select * from uv_pt;
2564 select events & 4 != 0 AS upd,
2565 events & 8 != 0 AS ins,
2566 events & 16 != 0 AS del
2567 from pg_catalog.pg_relation_is_updatable('uv_pt'::regclass, false) t(events);
2573 select pg_catalog.pg_column_is_updatable('uv_pt'::regclass, 1::smallint, false);
2574 pg_column_is_updatable
2575 ------------------------
2579 select pg_catalog.pg_column_is_updatable('uv_pt'::regclass, 2::smallint, false);
2580 pg_column_is_updatable
2581 ------------------------
2585 select table_name, is_updatable, is_insertable_into
2586 from information_schema.views where table_name = 'uv_ptv';
2587 table_name | is_updatable | is_insertable_into
2588 ------------+--------------+--------------------
2592 select table_name, column_name, is_updatable
2593 from information_schema.columns where table_name = 'uv_ptv' order by column_name;
2594 table_name | column_name | is_updatable
2595 ------------+-------------+--------------
2601 insert into uv_ptv values (1, 2);
2602 select tableoid::regclass, * from uv_pt;
2603 tableoid | a | b | v
2604 ----------+---+---+---
2608 create view uv_ptv_wco as select * from uv_pt where a = 0 with check option;
2609 insert into uv_ptv_wco values (1, 2);
2610 ERROR: new row violates check option for view "uv_ptv_wco"
2611 DETAIL: Failing row contains (1, 2, null).
2612 drop view uv_ptv, uv_ptv_wco;
2613 drop table uv_pt, uv_pt1, uv_pt11;
2614 -- check that wholerow vars appearing in WITH CHECK OPTION constraint expressions
2615 -- work fine with partitioned tables
2616 create table wcowrtest (a int) partition by list (a);
2617 create table wcowrtest1 partition of wcowrtest for values in (1);
2618 create view wcowrtest_v as select * from wcowrtest where wcowrtest = '(2)'::wcowrtest with check option;
2619 insert into wcowrtest_v values (1);
2620 ERROR: new row violates check option for view "wcowrtest_v"
2621 DETAIL: Failing row contains (1).
2622 alter table wcowrtest add b text;
2623 create table wcowrtest2 (b text, c int, a int);
2624 alter table wcowrtest2 drop c;
2625 alter table wcowrtest attach partition wcowrtest2 for values in (2);
2626 create table sometable (a int, b text);
2627 insert into sometable values (1, 'a'), (2, 'b');
2628 create view wcowrtest_v2 as
2631 where r in (select s from sometable s where r.a = s.a)
2633 -- WITH CHECK qual will be processed with wcowrtest2's
2634 -- rowtype after tuple-routing
2635 insert into wcowrtest_v2 values (2, 'no such row in sometable');
2636 ERROR: new row violates check option for view "wcowrtest_v2"
2637 DETAIL: Failing row contains (2, no such row in sometable).
2638 drop view wcowrtest_v, wcowrtest_v2;
2639 drop table wcowrtest, sometable;
2640 -- Check INSERT .. ON CONFLICT DO UPDATE works correctly when the view's
2641 -- columns are named and ordered differently than the underlying table's.
2642 create table uv_iocu_tab (a text unique, b float);
2643 insert into uv_iocu_tab values ('xyxyxy', 0);
2644 create view uv_iocu_view as
2645 select b, b+1 as c, a, '2.0'::text as two from uv_iocu_tab;
2646 insert into uv_iocu_view (a, b) values ('xyxyxy', 1)
2647 on conflict (a) do update set b = uv_iocu_view.b;
2648 select * from uv_iocu_tab;
2654 insert into uv_iocu_view (a, b) values ('xyxyxy', 1)
2655 on conflict (a) do update set b = excluded.b;
2656 select * from uv_iocu_tab;
2662 -- OK to access view columns that are not present in underlying base
2663 -- relation in the ON CONFLICT portion of the query
2664 insert into uv_iocu_view (a, b) values ('xyxyxy', 3)
2665 on conflict (a) do update set b = cast(excluded.two as float);
2666 select * from uv_iocu_tab;
2673 insert into uv_iocu_view (a, b) values ('xyxyxy', 3)
2674 on conflict (a) do update set b = excluded.b where excluded.c > 0;
2676 -----------------------------------------------------------------------------------
2677 Insert on uv_iocu_tab
2678 Conflict Resolution: UPDATE
2679 Conflict Arbiter Indexes: uv_iocu_tab_a_key
2680 Conflict Filter: ((excluded.b + '1'::double precision) > '0'::double precision)
2684 insert into uv_iocu_view (a, b) values ('xyxyxy', 3)
2685 on conflict (a) do update set b = excluded.b where excluded.c > 0;
2686 select * from uv_iocu_tab;
2692 drop view uv_iocu_view;
2693 drop table uv_iocu_tab;
2694 -- Test whole-row references to the view
2695 create table uv_iocu_tab (a int unique, b text);
2696 create view uv_iocu_view as
2697 select b as bb, a as aa, uv_iocu_tab::text as cc from uv_iocu_tab;
2698 insert into uv_iocu_view (aa,bb) values (1,'x');
2700 insert into uv_iocu_view (aa,bb) values (1,'y')
2701 on conflict (aa) do update set bb = 'Rejected: '||excluded.*
2702 where excluded.aa > 0
2703 and excluded.bb != ''
2704 and excluded.cc is not null;
2706 ---------------------------------------------------------------------------------------------------------
2707 Insert on uv_iocu_tab
2708 Conflict Resolution: UPDATE
2709 Conflict Arbiter Indexes: uv_iocu_tab_a_key
2710 Conflict Filter: ((excluded.a > 0) AND (excluded.b <> ''::text) AND ((excluded.*)::text IS NOT NULL))
2714 insert into uv_iocu_view (aa,bb) values (1,'y')
2715 on conflict (aa) do update set bb = 'Rejected: '||excluded.*
2716 where excluded.aa > 0
2717 and excluded.bb != ''
2718 and excluded.cc is not null;
2719 select * from uv_iocu_view;
2721 -------------------------+----+---------------------------------
2722 Rejected: (y,1,"(1,y)") | 1 | (1,"Rejected: (y,1,""(1,y)"")")
2725 -- Test omitting a column of the base relation
2726 delete from uv_iocu_view;
2727 insert into uv_iocu_view (aa,bb) values (1,'x');
2728 insert into uv_iocu_view (aa) values (1)
2729 on conflict (aa) do update set bb = 'Rejected: '||excluded.*;
2730 select * from uv_iocu_view;
2732 -----------------------+----+-------------------------------
2733 Rejected: (,1,"(1,)") | 1 | (1,"Rejected: (,1,""(1,)"")")
2736 alter table uv_iocu_tab alter column b set default 'table default';
2737 insert into uv_iocu_view (aa) values (1)
2738 on conflict (aa) do update set bb = 'Rejected: '||excluded.*;
2739 select * from uv_iocu_view;
2741 -------------------------------------------------------+----+---------------------------------------------------------------------
2742 Rejected: ("table default",1,"(1,""table default"")") | 1 | (1,"Rejected: (""table default"",1,""(1,""""table default"""")"")")
2745 alter view uv_iocu_view alter column bb set default 'view default';
2746 insert into uv_iocu_view (aa) values (1)
2747 on conflict (aa) do update set bb = 'Rejected: '||excluded.*;
2748 select * from uv_iocu_view;
2750 -----------------------------------------------------+----+-------------------------------------------------------------------
2751 Rejected: ("view default",1,"(1,""view default"")") | 1 | (1,"Rejected: (""view default"",1,""(1,""""view default"""")"")")
2754 -- Should fail to update non-updatable columns
2755 insert into uv_iocu_view (aa) values (1)
2756 on conflict (aa) do update set cc = 'XXX';
2757 ERROR: cannot insert into column "cc" of view "uv_iocu_view"
2758 DETAIL: View columns that are not columns of their base relation are not updatable.
2759 drop view uv_iocu_view;
2760 drop table uv_iocu_tab;
2761 -- ON CONFLICT DO UPDATE permissions checks
2762 create user regress_view_user1;
2763 create user regress_view_user2;
2764 set session authorization regress_view_user1;
2765 create table base_tbl(a int unique, b text, c float);
2766 insert into base_tbl values (1,'xxx',1.0);
2767 create view rw_view1 as select b as bb, c as cc, a as aa from base_tbl;
2768 grant select (aa,bb) on rw_view1 to regress_view_user2;
2769 grant insert on rw_view1 to regress_view_user2;
2770 grant update (bb) on rw_view1 to regress_view_user2;
2771 set session authorization regress_view_user2;
2772 insert into rw_view1 values ('yyy',2.0,1)
2773 on conflict (aa) do update set bb = excluded.cc; -- Not allowed
2774 ERROR: permission denied for view rw_view1
2775 insert into rw_view1 values ('yyy',2.0,1)
2776 on conflict (aa) do update set bb = rw_view1.cc; -- Not allowed
2777 ERROR: permission denied for view rw_view1
2778 insert into rw_view1 values ('yyy',2.0,1)
2779 on conflict (aa) do update set bb = excluded.bb; -- OK
2780 insert into rw_view1 values ('zzz',2.0,1)
2781 on conflict (aa) do update set bb = rw_view1.bb||'xxx'; -- OK
2782 insert into rw_view1 values ('zzz',2.0,1)
2783 on conflict (aa) do update set cc = 3.0; -- Not allowed
2784 ERROR: permission denied for view rw_view1
2785 reset session authorization;
2786 select * from base_tbl;
2792 set session authorization regress_view_user1;
2793 grant select (a,b) on base_tbl to regress_view_user2;
2794 grant insert (a,b) on base_tbl to regress_view_user2;
2795 grant update (a,b) on base_tbl to regress_view_user2;
2796 set session authorization regress_view_user2;
2797 create view rw_view2 as select b as bb, c as cc, a as aa from base_tbl;
2798 insert into rw_view2 (aa,bb) values (1,'xxx')
2799 on conflict (aa) do update set bb = excluded.bb; -- Not allowed
2800 ERROR: permission denied for table base_tbl
2801 create view rw_view3 as select b as bb, a as aa from base_tbl;
2802 insert into rw_view3 (aa,bb) values (1,'xxx')
2803 on conflict (aa) do update set bb = excluded.bb; -- OK
2804 reset session authorization;
2805 select * from base_tbl;
2811 set session authorization regress_view_user2;
2812 create view rw_view4 as select aa, bb, cc FROM rw_view1;
2813 insert into rw_view4 (aa,bb) values (1,'yyy')
2814 on conflict (aa) do update set bb = excluded.bb; -- Not allowed
2815 ERROR: permission denied for view rw_view1
2816 create view rw_view5 as select aa, bb FROM rw_view1;
2817 insert into rw_view5 (aa,bb) values (1,'yyy')
2818 on conflict (aa) do update set bb = excluded.bb; -- OK
2819 reset session authorization;
2820 select * from base_tbl;
2831 drop table base_tbl;
2832 drop user regress_view_user1;
2833 drop user regress_view_user2;
2834 -- Test single- and multi-row inserts with table and view defaults.
2835 -- Table defaults should be used, unless overridden by view defaults.
2836 create table base_tab_def (a int, b text default 'Table default',
2837 c text default 'Table default', d text, e text);
2838 create view base_tab_def_view as select * from base_tab_def;
2839 alter view base_tab_def_view alter b set default 'View default';
2840 alter view base_tab_def_view alter d set default 'View default';
2841 insert into base_tab_def values (1);
2842 insert into base_tab_def values (2), (3);
2843 insert into base_tab_def values (4, default, default, default, default);
2844 insert into base_tab_def values (5, default, default, default, default),
2845 (6, default, default, default, default);
2846 insert into base_tab_def_view values (11);
2847 insert into base_tab_def_view values (12), (13);
2848 insert into base_tab_def_view values (14, default, default, default, default);
2849 insert into base_tab_def_view values (15, default, default, default, default),
2850 (16, default, default, default, default);
2851 insert into base_tab_def_view values (17), (default);
2852 select * from base_tab_def order by a;
2854 ----+---------------+---------------+--------------+---
2855 1 | Table default | Table default | |
2856 2 | Table default | Table default | |
2857 3 | Table default | Table default | |
2858 4 | Table default | Table default | |
2859 5 | Table default | Table default | |
2860 6 | Table default | Table default | |
2861 11 | View default | Table default | View default |
2862 12 | View default | Table default | View default |
2863 13 | View default | Table default | View default |
2864 14 | View default | Table default | View default |
2865 15 | View default | Table default | View default |
2866 16 | View default | Table default | View default |
2867 17 | View default | Table default | View default |
2868 | View default | Table default | View default |
2871 -- Adding an INSTEAD OF trigger should cause NULLs to be inserted instead of
2872 -- table defaults, where there are no view defaults.
2873 create function base_tab_def_view_instrig_func() returns trigger
2877 insert into base_tab_def values (new.a, new.b, new.c, new.d, new.e);
2882 create trigger base_tab_def_view_instrig instead of insert on base_tab_def_view
2883 for each row execute function base_tab_def_view_instrig_func();
2884 truncate base_tab_def;
2885 insert into base_tab_def values (1);
2886 insert into base_tab_def values (2), (3);
2887 insert into base_tab_def values (4, default, default, default, default);
2888 insert into base_tab_def values (5, default, default, default, default),
2889 (6, default, default, default, default);
2890 insert into base_tab_def_view values (11);
2891 insert into base_tab_def_view values (12), (13);
2892 insert into base_tab_def_view values (14, default, default, default, default);
2893 insert into base_tab_def_view values (15, default, default, default, default),
2894 (16, default, default, default, default);
2895 insert into base_tab_def_view values (17), (default);
2896 select * from base_tab_def order by a;
2898 ----+---------------+---------------+--------------+---
2899 1 | Table default | Table default | |
2900 2 | Table default | Table default | |
2901 3 | Table default | Table default | |
2902 4 | Table default | Table default | |
2903 5 | Table default | Table default | |
2904 6 | Table default | Table default | |
2905 11 | View default | | View default |
2906 12 | View default | | View default |
2907 13 | View default | | View default |
2908 14 | View default | | View default |
2909 15 | View default | | View default |
2910 16 | View default | | View default |
2911 17 | View default | | View default |
2912 | View default | | View default |
2915 -- Using an unconditional DO INSTEAD rule should also cause NULLs to be
2916 -- inserted where there are no view defaults.
2917 drop trigger base_tab_def_view_instrig on base_tab_def_view;
2918 drop function base_tab_def_view_instrig_func;
2919 create rule base_tab_def_view_ins_rule as on insert to base_tab_def_view
2920 do instead insert into base_tab_def values (new.a, new.b, new.c, new.d, new.e);
2921 truncate base_tab_def;
2922 insert into base_tab_def values (1);
2923 insert into base_tab_def values (2), (3);
2924 insert into base_tab_def values (4, default, default, default, default);
2925 insert into base_tab_def values (5, default, default, default, default),
2926 (6, default, default, default, default);
2927 insert into base_tab_def_view values (11);
2928 insert into base_tab_def_view values (12), (13);
2929 insert into base_tab_def_view values (14, default, default, default, default);
2930 insert into base_tab_def_view values (15, default, default, default, default),
2931 (16, default, default, default, default);
2932 insert into base_tab_def_view values (17), (default);
2933 select * from base_tab_def order by a;
2935 ----+---------------+---------------+--------------+---
2936 1 | Table default | Table default | |
2937 2 | Table default | Table default | |
2938 3 | Table default | Table default | |
2939 4 | Table default | Table default | |
2940 5 | Table default | Table default | |
2941 6 | Table default | Table default | |
2942 11 | View default | | View default |
2943 12 | View default | | View default |
2944 13 | View default | | View default |
2945 14 | View default | | View default |
2946 15 | View default | | View default |
2947 16 | View default | | View default |
2948 17 | View default | | View default |
2949 | View default | | View default |
2952 -- A DO ALSO rule should cause each row to be inserted twice. The first
2953 -- insert should behave the same as an auto-updatable view (using table
2954 -- defaults, unless overridden by view defaults). The second insert should
2955 -- behave the same as a rule-updatable view (inserting NULLs where there are
2956 -- no view defaults).
2957 drop rule base_tab_def_view_ins_rule on base_tab_def_view;
2958 create rule base_tab_def_view_ins_rule as on insert to base_tab_def_view
2959 do also insert into base_tab_def values (new.a, new.b, new.c, new.d, new.e);
2960 truncate base_tab_def;
2961 insert into base_tab_def values (1);
2962 insert into base_tab_def values (2), (3);
2963 insert into base_tab_def values (4, default, default, default, default);
2964 insert into base_tab_def values (5, default, default, default, default),
2965 (6, default, default, default, default);
2966 insert into base_tab_def_view values (11);
2967 insert into base_tab_def_view values (12), (13);
2968 insert into base_tab_def_view values (14, default, default, default, default);
2969 insert into base_tab_def_view values (15, default, default, default, default),
2970 (16, default, default, default, default);
2971 insert into base_tab_def_view values (17), (default);
2972 select * from base_tab_def order by a, c NULLS LAST;
2974 ----+---------------+---------------+--------------+---
2975 1 | Table default | Table default | |
2976 2 | Table default | Table default | |
2977 3 | Table default | Table default | |
2978 4 | Table default | Table default | |
2979 5 | Table default | Table default | |
2980 6 | Table default | Table default | |
2981 11 | View default | Table default | View default |
2982 11 | View default | | View default |
2983 12 | View default | Table default | View default |
2984 12 | View default | | View default |
2985 13 | View default | Table default | View default |
2986 13 | View default | | View default |
2987 14 | View default | Table default | View default |
2988 14 | View default | | View default |
2989 15 | View default | Table default | View default |
2990 15 | View default | | View default |
2991 16 | View default | Table default | View default |
2992 16 | View default | | View default |
2993 17 | View default | Table default | View default |
2994 17 | View default | | View default |
2995 | View default | Table default | View default |
2996 | View default | | View default |
2999 drop view base_tab_def_view;
3000 drop table base_tab_def;
3001 -- Test defaults with array assignments
3002 create table base_tab (a serial, b int[], c text, d text default 'Table default');
3003 create view base_tab_view as select c, a, b from base_tab;
3004 alter view base_tab_view alter column c set default 'View default';
3005 insert into base_tab_view (b[1], b[2], c, b[5], b[4], a, b[3])
3006 values (1, 2, default, 5, 4, default, 3), (10, 11, 'C value', 14, 13, 100, 12);
3007 select * from base_tab order by a;
3009 -----+------------------+--------------+---------------
3010 1 | {1,2,3,4,5} | View default | Table default
3011 100 | {10,11,12,13,14} | C value | Table default
3014 drop view base_tab_view;
3015 drop table base_tab;
projects / postgresql / blob