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 MERGE INTO ro_view13 AS t USING (VALUES (1, 'Row 1')) AS v(a,b) ON t.a = v.a
188 WHEN MATCHED THEN DELETE;
189 ERROR: cannot delete from view "ro_view13"
190 DETAIL: Views that do not select from a single table or view are not automatically updatable.
191 HINT: To enable deleting from the view using MERGE, provide an INSTEAD OF DELETE trigger.
192 MERGE INTO ro_view13 AS t USING (VALUES (2, 'Row 2')) AS v(a,b) ON t.a = v.a
193 WHEN MATCHED THEN UPDATE SET b = v.b;
194 ERROR: cannot update view "ro_view13"
195 DETAIL: Views that do not select from a single table or view are not automatically updatable.
196 HINT: To enable updating the view using MERGE, provide an INSTEAD OF UPDATE trigger.
197 MERGE INTO ro_view13 AS t USING (VALUES (3, 'Row 3')) AS v(a,b) ON t.a = v.a
198 WHEN NOT MATCHED THEN INSERT VALUES (v.a, v.b);
199 ERROR: cannot insert into view "ro_view13"
200 DETAIL: Views that do not select from a single table or view are not automatically updatable.
201 HINT: To enable inserting into the view using MERGE, provide an INSTEAD OF INSERT trigger.
202 MERGE INTO ro_view13 AS t USING (VALUES (2, 'Row 2')) AS v(a,b) ON t.a = v.a
203 WHEN MATCHED THEN DO NOTHING
204 WHEN NOT MATCHED THEN DO NOTHING; -- should be OK to do nothing
205 MERGE INTO ro_view13 AS t USING (VALUES (3, 'Row 3')) AS v(a,b) ON t.a = v.a
206 WHEN MATCHED THEN DO NOTHING
207 WHEN NOT MATCHED THEN DO NOTHING; -- should be OK to do nothing
208 -- Partially updatable view
209 INSERT INTO rw_view14 VALUES (null, 3, 'Row 3'); -- should fail
210 ERROR: cannot insert into column "ctid" of view "rw_view14"
211 DETAIL: View columns that refer to system columns are not updatable.
212 INSERT INTO rw_view14 (a, b) VALUES (3, 'Row 3'); -- should be OK
213 UPDATE rw_view14 SET ctid=null WHERE a=3; -- should fail
214 ERROR: cannot update column "ctid" of view "rw_view14"
215 DETAIL: View columns that refer to system columns are not updatable.
216 UPDATE rw_view14 SET b='ROW 3' WHERE a=3; -- should be OK
217 SELECT * FROM base_tbl;
228 DELETE FROM rw_view14 WHERE a=3; -- should be OK
229 MERGE INTO rw_view14 AS t
230 USING (VALUES (2, 'Merged row 2'), (3, 'Merged row 3')) AS v(a,b) ON t.a = v.a
231 WHEN MATCHED THEN UPDATE SET b = v.b -- should be OK, except...
232 WHEN NOT MATCHED THEN INSERT VALUES (null, v.a, v.b); -- should fail
233 ERROR: cannot merge into column "ctid" of view "rw_view14"
234 DETAIL: View columns that refer to system columns are not updatable.
235 MERGE INTO rw_view14 AS t
236 USING (VALUES (2, 'Merged row 2'), (3, 'Merged row 3')) AS v(a,b) ON t.a = v.a
237 WHEN MATCHED THEN UPDATE SET b = v.b -- should be OK
238 WHEN NOT MATCHED THEN INSERT (a,b) VALUES (v.a, v.b); -- should be OK
239 SELECT * FROM base_tbl ORDER BY a;
250 MERGE INTO rw_view14 AS t
251 USING (VALUES (2, 'Row 2'), (3, 'Row 3')) AS v(a,b) ON t.a = v.a
252 WHEN MATCHED AND t.a = 2 THEN UPDATE SET b = v.b -- should be OK
253 WHEN MATCHED AND t.a = 3 THEN DELETE; -- should be OK
254 SELECT * FROM base_tbl ORDER BY a;
264 -- Partially updatable view
265 INSERT INTO rw_view15 VALUES (3, 'ROW 3'); -- should fail
266 ERROR: cannot insert into column "upper" of view "rw_view15"
267 DETAIL: View columns that are not columns of their base relation are not updatable.
268 INSERT INTO rw_view15 (a) VALUES (3); -- should be OK
269 INSERT INTO rw_view15 (a) VALUES (3) ON CONFLICT DO NOTHING; -- succeeds
270 SELECT * FROM rw_view15;
281 INSERT INTO rw_view15 (a) VALUES (3) ON CONFLICT (a) DO NOTHING; -- succeeds
282 SELECT * FROM rw_view15;
293 INSERT INTO rw_view15 (a) VALUES (3) ON CONFLICT (a) DO UPDATE set a = excluded.a; -- succeeds
294 SELECT * FROM rw_view15;
305 INSERT INTO rw_view15 (a) VALUES (3) ON CONFLICT (a) DO UPDATE set upper = 'blarg'; -- fails
306 ERROR: cannot insert into column "upper" of view "rw_view15"
307 DETAIL: View columns that are not columns of their base relation are not updatable.
308 SELECT * FROM rw_view15;
319 SELECT * FROM rw_view15;
330 ALTER VIEW rw_view15 ALTER COLUMN upper SET DEFAULT 'NOT SET';
331 INSERT INTO rw_view15 (a) VALUES (4); -- should fail
332 ERROR: cannot insert into column "upper" of view "rw_view15"
333 DETAIL: View columns that are not columns of their base relation are not updatable.
334 UPDATE rw_view15 SET upper='ROW 3' WHERE a=3; -- should fail
335 ERROR: cannot update column "upper" of view "rw_view15"
336 DETAIL: View columns that are not columns of their base relation are not updatable.
337 UPDATE rw_view15 SET upper=DEFAULT WHERE a=3; -- should fail
338 ERROR: cannot update column "upper" of view "rw_view15"
339 DETAIL: View columns that are not columns of their base relation are not updatable.
340 UPDATE rw_view15 SET a=4 WHERE a=3; -- should be OK
341 SELECT * FROM base_tbl;
352 DELETE FROM rw_view15 WHERE a=4; -- should be OK
353 -- Partially updatable view
354 INSERT INTO rw_view16 VALUES (3, 'Row 3', 3); -- should fail
355 ERROR: multiple assignments to same column "a"
356 INSERT INTO rw_view16 (a, b) VALUES (3, 'Row 3'); -- should be OK
357 UPDATE rw_view16 SET a=3, aa=-3 WHERE a=3; -- should fail
358 ERROR: multiple assignments to same column "a"
359 UPDATE rw_view16 SET aa=-3 WHERE a=3; -- should be OK
360 SELECT * FROM base_tbl;
371 DELETE FROM rw_view16 WHERE a=-3; -- should be OK
373 INSERT INTO ro_view17 VALUES (3, 'ROW 3');
374 ERROR: cannot insert into view "ro_view1"
375 DETAIL: Views containing DISTINCT are not automatically updatable.
376 HINT: To enable inserting into the view, provide an INSTEAD OF INSERT trigger or an unconditional ON INSERT DO INSTEAD rule.
377 DELETE FROM ro_view18;
378 ERROR: cannot delete from view "ro_view18"
379 DETAIL: Views that do not select from a single table or view are not automatically updatable.
380 HINT: To enable deleting from the view, provide an INSTEAD OF DELETE trigger or an unconditional ON DELETE DO INSTEAD rule.
381 MERGE INTO ro_view18 AS t USING (VALUES (1, 'Row 1')) AS v(a,b) ON t.a = v.a
382 WHEN MATCHED THEN DO NOTHING; -- should be OK to do nothing
383 UPDATE ro_view19 SET last_value=1000;
384 ERROR: cannot update view "ro_view19"
385 DETAIL: Views that do not select from a single table or view are not automatically updatable.
386 HINT: To enable updating the view, provide an INSTEAD OF UPDATE trigger or an unconditional ON UPDATE DO INSTEAD rule.
387 UPDATE ro_view20 SET b=upper(b);
388 ERROR: cannot update view "ro_view20"
389 DETAIL: Views that return set-returning functions are not automatically updatable.
390 HINT: To enable updating the view, provide an INSTEAD OF UPDATE trigger or an unconditional ON UPDATE DO INSTEAD rule.
391 -- A view with a conditional INSTEAD rule but no unconditional INSTEAD rules
392 -- or INSTEAD OF triggers should be non-updatable and generate useful error
393 -- messages with appropriate detail
394 CREATE RULE rw_view16_ins_rule AS ON INSERT TO rw_view16
395 WHERE NEW.a > 0 DO INSTEAD INSERT INTO base_tbl VALUES (NEW.a, NEW.b);
396 CREATE RULE rw_view16_upd_rule AS ON UPDATE TO rw_view16
397 WHERE OLD.a > 0 DO INSTEAD UPDATE base_tbl SET b=NEW.b WHERE a=OLD.a;
398 CREATE RULE rw_view16_del_rule AS ON DELETE TO rw_view16
399 WHERE OLD.a > 0 DO INSTEAD DELETE FROM base_tbl WHERE a=OLD.a;
400 INSERT INTO rw_view16 (a, b) VALUES (3, 'Row 3'); -- should fail
401 ERROR: cannot insert into view "rw_view16"
402 DETAIL: Views with conditional DO INSTEAD rules are not automatically updatable.
403 HINT: To enable inserting into the view, provide an INSTEAD OF INSERT trigger or an unconditional ON INSERT DO INSTEAD rule.
404 UPDATE rw_view16 SET b='ROW 2' WHERE a=2; -- should fail
405 ERROR: cannot update view "rw_view16"
406 DETAIL: Views with conditional DO INSTEAD rules are not automatically updatable.
407 HINT: To enable updating the view, provide an INSTEAD OF UPDATE trigger or an unconditional ON UPDATE DO INSTEAD rule.
408 DELETE FROM rw_view16 WHERE a=2; -- should fail
409 ERROR: cannot delete from view "rw_view16"
410 DETAIL: Views with conditional DO INSTEAD rules are not automatically updatable.
411 HINT: To enable deleting from the view, provide an INSTEAD OF DELETE trigger or an unconditional ON DELETE DO INSTEAD rule.
412 MERGE INTO rw_view16 AS t USING (VALUES (3, 'Row 3')) AS v(a,b) ON t.a = v.a
413 WHEN NOT MATCHED THEN INSERT VALUES (v.a, v.b); -- should fail
414 ERROR: cannot execute MERGE on relation "rw_view16"
415 DETAIL: MERGE is not supported for relations with rules.
416 DROP TABLE base_tbl CASCADE;
417 NOTICE: drop cascades to 16 other objects
418 DETAIL: drop cascades to view ro_view1
419 drop cascades to view ro_view17
420 drop cascades to view ro_view2
421 drop cascades to view ro_view3
422 drop cascades to view ro_view4
423 drop cascades to view ro_view5
424 drop cascades to view ro_view6
425 drop cascades to view ro_view7
426 drop cascades to view ro_view8
427 drop cascades to view ro_view9
428 drop cascades to view ro_view11
429 drop cascades to view ro_view13
430 drop cascades to view rw_view14
431 drop cascades to view rw_view15
432 drop cascades to view rw_view16
433 drop cascades to view ro_view20
434 DROP VIEW ro_view10, ro_view12, ro_view18;
435 DROP SEQUENCE uv_seq CASCADE;
436 NOTICE: drop cascades to view ro_view19
437 -- simple updatable view
438 CREATE TABLE base_tbl (a int PRIMARY KEY, b text DEFAULT 'Unspecified');
439 INSERT INTO base_tbl SELECT i, 'Row ' || i FROM generate_series(-2, 2) g(i);
440 CREATE VIEW rw_view1 AS SELECT * FROM base_tbl WHERE a>0;
441 SELECT table_name, is_insertable_into
442 FROM information_schema.tables
443 WHERE table_name = 'rw_view1';
444 table_name | is_insertable_into
445 ------------+--------------------
449 SELECT table_name, is_updatable, is_insertable_into
450 FROM information_schema.views
451 WHERE table_name = 'rw_view1';
452 table_name | is_updatable | is_insertable_into
453 ------------+--------------+--------------------
457 SELECT table_name, column_name, is_updatable
458 FROM information_schema.columns
459 WHERE table_name = 'rw_view1'
460 ORDER BY ordinal_position;
461 table_name | column_name | is_updatable
462 ------------+-------------+--------------
467 INSERT INTO rw_view1 VALUES (3, 'Row 3');
468 INSERT INTO rw_view1 (a) VALUES (4);
469 UPDATE rw_view1 SET a=5 WHERE a=4;
470 DELETE FROM rw_view1 WHERE b='Row 2';
471 SELECT * FROM base_tbl;
482 MERGE INTO rw_view1 t
483 USING (VALUES (0, 'ROW 0'), (1, 'ROW 1'),
484 (2, 'ROW 2'), (3, 'ROW 3')) AS v(a,b) ON t.a = v.a
485 WHEN MATCHED AND t.a <= 1 THEN UPDATE SET b = v.b
486 WHEN MATCHED THEN DELETE
487 WHEN NOT MATCHED AND a > 0 THEN INSERT (a) VALUES (v.a)
488 RETURNING merge_action(), v.*, t.*;
489 merge_action | a | b | a | b
490 --------------+---+-------+---+-------------
491 UPDATE | 1 | ROW 1 | 1 | ROW 1
492 DELETE | 3 | ROW 3 | 3 | Row 3
493 INSERT | 2 | ROW 2 | 2 | Unspecified
496 SELECT * FROM base_tbl ORDER BY a;
507 MERGE INTO rw_view1 t
508 USING (VALUES (0, 'R0'), (1, 'R1'),
509 (2, 'R2'), (3, 'R3')) AS v(a,b) ON t.a = v.a
510 WHEN MATCHED AND t.a <= 1 THEN UPDATE SET b = v.b
511 WHEN MATCHED THEN DELETE
512 WHEN NOT MATCHED BY SOURCE THEN DELETE
513 WHEN NOT MATCHED AND a > 0 THEN INSERT (a) VALUES (v.a)
514 RETURNING merge_action(), v.*, t.*;
515 merge_action | a | b | a | b
516 --------------+---+----+---+-------------
517 UPDATE | 1 | R1 | 1 | R1
518 DELETE | | | 5 | Unspecified
519 DELETE | 2 | R2 | 2 | Unspecified
520 INSERT | 3 | R3 | 3 | Unspecified
523 SELECT * FROM base_tbl ORDER BY a;
533 EXPLAIN (costs off) UPDATE rw_view1 SET a=6 WHERE a=5;
535 --------------------------------------------------
537 -> Index Scan using base_tbl_pkey on base_tbl
538 Index Cond: ((a > 0) AND (a = 5))
541 EXPLAIN (costs off) DELETE FROM rw_view1 WHERE a=5;
543 --------------------------------------------------
545 -> Index Scan using base_tbl_pkey on base_tbl
546 Index Cond: ((a > 0) AND (a = 5))
550 MERGE INTO rw_view1 t USING (VALUES (5, 'X')) AS v(a,b) ON t.a = v.a
551 WHEN MATCHED THEN DELETE;
553 --------------------------------------------------
555 -> Index Scan using base_tbl_pkey on base_tbl
556 Index Cond: ((a > 0) AND (a = 5))
560 MERGE INTO rw_view1 t
561 USING (SELECT * FROM generate_series(1,5)) AS s(a) ON t.a = s.a
562 WHEN MATCHED THEN UPDATE SET b = 'Updated';
564 -------------------------------------------------------------------
567 Hash Cond: (base_tbl.a = generate_series.generate_series)
568 -> Bitmap Heap Scan on base_tbl
569 Recheck Cond: (a > 0)
570 -> Bitmap Index Scan on base_tbl_pkey
573 -> Function Scan on generate_series
577 MERGE INTO rw_view1 t
578 USING (SELECT * FROM generate_series(1,5)) AS s(a) ON t.a = s.a
579 WHEN NOT MATCHED BY SOURCE THEN DELETE;
581 -------------------------------------------------------------------
584 Hash Cond: (base_tbl.a = generate_series.generate_series)
585 -> Bitmap Heap Scan on base_tbl
586 Recheck Cond: (a > 0)
587 -> Bitmap Index Scan on base_tbl_pkey
590 -> Function Scan on generate_series
594 MERGE INTO rw_view1 t
595 USING (SELECT * FROM generate_series(1,5)) AS s(a) ON t.a = s.a
596 WHEN NOT MATCHED THEN INSERT (a) VALUES (s.a);
598 -------------------------------------------------------------------
601 Hash Cond: (base_tbl.a = generate_series.generate_series)
602 -> Bitmap Heap Scan on base_tbl
603 Recheck Cond: (a > 0)
604 -> Bitmap Index Scan on base_tbl_pkey
607 -> Function Scan on generate_series
610 -- it's still updatable if we add a DO ALSO rule
611 CREATE TABLE base_tbl_hist(ts timestamptz default now(), a int, b text);
612 CREATE RULE base_tbl_log AS ON INSERT TO rw_view1 DO ALSO
613 INSERT INTO base_tbl_hist(a,b) VALUES(new.a, new.b);
614 SELECT table_name, is_updatable, is_insertable_into
615 FROM information_schema.views
616 WHERE table_name = 'rw_view1';
617 table_name | is_updatable | is_insertable_into
618 ------------+--------------+--------------------
622 -- Check behavior with DEFAULTs (bug #17633)
623 INSERT INTO rw_view1 VALUES (9, DEFAULT), (10, DEFAULT);
624 SELECT a, b FROM base_tbl_hist;
631 DROP TABLE base_tbl CASCADE;
632 NOTICE: drop cascades to view rw_view1
633 DROP TABLE base_tbl_hist;
634 -- view on top of view
635 CREATE TABLE base_tbl (a int PRIMARY KEY, b text DEFAULT 'Unspecified');
636 INSERT INTO base_tbl SELECT i, 'Row ' || i FROM generate_series(-2, 2) g(i);
637 CREATE VIEW rw_view1 AS SELECT b AS bb, a AS aa FROM base_tbl WHERE a>0;
638 CREATE VIEW rw_view2 AS SELECT aa AS aaa, bb AS bbb FROM rw_view1 WHERE aa<10;
639 SELECT table_name, is_insertable_into
640 FROM information_schema.tables
641 WHERE table_name = 'rw_view2';
642 table_name | is_insertable_into
643 ------------+--------------------
647 SELECT table_name, is_updatable, is_insertable_into
648 FROM information_schema.views
649 WHERE table_name = 'rw_view2';
650 table_name | is_updatable | is_insertable_into
651 ------------+--------------+--------------------
655 SELECT table_name, column_name, is_updatable
656 FROM information_schema.columns
657 WHERE table_name = 'rw_view2'
658 ORDER BY ordinal_position;
659 table_name | column_name | is_updatable
660 ------------+-------------+--------------
665 INSERT INTO rw_view2 VALUES (3, 'Row 3');
666 INSERT INTO rw_view2 (aaa) VALUES (4);
667 SELECT * FROM rw_view2;
676 UPDATE rw_view2 SET bbb='Row 4' WHERE aaa=4;
677 DELETE FROM rw_view2 WHERE aaa=2;
678 SELECT * FROM rw_view2;
686 MERGE INTO rw_view2 t
687 USING (VALUES (3, 'R3'), (4, 'R4'), (5, 'R5')) AS v(a,b) ON aaa = v.a
688 WHEN MATCHED AND aaa = 3 THEN DELETE
689 WHEN MATCHED THEN UPDATE SET bbb = v.b
690 WHEN NOT MATCHED THEN INSERT (aaa) VALUES (v.a)
691 RETURNING merge_action(), v.*, t.*;
692 merge_action | a | b | aaa | bbb
693 --------------+---+----+-----+-------------
694 DELETE | 3 | R3 | 3 | Row 3
695 UPDATE | 4 | R4 | 4 | R4
696 INSERT | 5 | R5 | 5 | Unspecified
699 SELECT * FROM rw_view2 ORDER BY aaa;
707 MERGE INTO rw_view2 t
708 USING (VALUES (4, 'r4'), (5, 'r5'), (6, 'r6')) AS v(a,b) ON aaa = v.a
709 WHEN MATCHED AND aaa = 4 THEN DELETE
710 WHEN MATCHED THEN UPDATE SET bbb = v.b
711 WHEN NOT MATCHED THEN INSERT (aaa) VALUES (v.a)
712 WHEN NOT MATCHED BY SOURCE THEN UPDATE SET bbb = 'Not matched by source'
713 RETURNING merge_action(), v.*, t.*;
714 merge_action | a | b | aaa | bbb
715 --------------+---+----+-----+-----------------------
716 UPDATE | | | 1 | Not matched by source
717 DELETE | 4 | r4 | 4 | R4
718 UPDATE | 5 | r5 | 5 | r5
719 INSERT | 6 | r6 | 6 | Unspecified
722 SELECT * FROM rw_view2 ORDER BY aaa;
724 -----+-----------------------
725 1 | Not matched by source
730 EXPLAIN (costs off) UPDATE rw_view2 SET aaa=5 WHERE aaa=4;
732 --------------------------------------------------------
734 -> Index Scan using base_tbl_pkey on base_tbl
735 Index Cond: ((a < 10) AND (a > 0) AND (a = 4))
738 EXPLAIN (costs off) DELETE FROM rw_view2 WHERE aaa=4;
740 --------------------------------------------------------
742 -> Index Scan using base_tbl_pkey on base_tbl
743 Index Cond: ((a < 10) AND (a > 0) AND (a = 4))
746 DROP TABLE base_tbl CASCADE;
747 NOTICE: drop cascades to 2 other objects
748 DETAIL: drop cascades to view rw_view1
749 drop cascades to view rw_view2
750 -- view on top of view with rules
751 CREATE TABLE base_tbl (a int PRIMARY KEY, b text DEFAULT 'Unspecified');
752 INSERT INTO base_tbl SELECT i, 'Row ' || i FROM generate_series(-2, 2) g(i);
753 CREATE VIEW rw_view1 AS SELECT * FROM base_tbl WHERE a>0 OFFSET 0; -- not updatable without rules/triggers
754 CREATE VIEW rw_view2 AS SELECT * FROM rw_view1 WHERE a<10;
755 SELECT table_name, is_insertable_into
756 FROM information_schema.tables
757 WHERE table_name LIKE 'rw_view%'
759 table_name | is_insertable_into
760 ------------+--------------------
765 SELECT table_name, is_updatable, is_insertable_into
766 FROM information_schema.views
767 WHERE table_name LIKE 'rw_view%'
769 table_name | is_updatable | is_insertable_into
770 ------------+--------------+--------------------
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 RULE rw_view1_ins_rule AS ON INSERT TO rw_view1
788 DO INSTEAD INSERT INTO base_tbl VALUES (NEW.a, NEW.b) RETURNING *;
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 FROM information_schema.views
801 WHERE table_name LIKE 'rw_view%'
803 table_name | is_updatable | is_insertable_into
804 ------------+--------------+--------------------
809 SELECT table_name, column_name, is_updatable
810 FROM information_schema.columns
811 WHERE table_name LIKE 'rw_view%'
812 ORDER BY table_name, ordinal_position;
813 table_name | column_name | is_updatable
814 ------------+-------------+--------------
821 CREATE RULE rw_view1_upd_rule AS ON UPDATE TO rw_view1
822 DO INSTEAD UPDATE base_tbl SET b=NEW.b WHERE a=OLD.a RETURNING NEW.*;
823 SELECT table_name, is_insertable_into
824 FROM information_schema.tables
825 WHERE table_name LIKE 'rw_view%'
827 table_name | is_insertable_into
828 ------------+--------------------
833 SELECT table_name, is_updatable, is_insertable_into
834 FROM information_schema.views
835 WHERE table_name LIKE 'rw_view%'
837 table_name | is_updatable | is_insertable_into
838 ------------+--------------+--------------------
843 SELECT table_name, column_name, is_updatable
844 FROM information_schema.columns
845 WHERE table_name LIKE 'rw_view%'
846 ORDER BY table_name, ordinal_position;
847 table_name | column_name | is_updatable
848 ------------+-------------+--------------
855 CREATE RULE rw_view1_del_rule AS ON DELETE TO rw_view1
856 DO INSTEAD DELETE FROM base_tbl WHERE a=OLD.a RETURNING OLD.*;
857 SELECT table_name, is_insertable_into
858 FROM information_schema.tables
859 WHERE table_name LIKE 'rw_view%'
861 table_name | is_insertable_into
862 ------------+--------------------
867 SELECT table_name, is_updatable, is_insertable_into
868 FROM information_schema.views
869 WHERE table_name LIKE 'rw_view%'
871 table_name | is_updatable | is_insertable_into
872 ------------+--------------+--------------------
877 SELECT table_name, column_name, is_updatable
878 FROM information_schema.columns
879 WHERE table_name LIKE 'rw_view%'
880 ORDER BY table_name, ordinal_position;
881 table_name | column_name | is_updatable
882 ------------+-------------+--------------
889 INSERT INTO rw_view2 VALUES (3, 'Row 3') RETURNING *;
895 UPDATE rw_view2 SET b='Row three' WHERE a=3 RETURNING *;
901 SELECT * FROM rw_view2;
909 DELETE FROM rw_view2 WHERE a=3 RETURNING *;
915 SELECT * FROM rw_view2;
922 MERGE INTO rw_view2 t USING (VALUES (3, 'Row 3')) AS v(a,b) ON t.a = v.a
923 WHEN NOT MATCHED THEN INSERT VALUES (v.a, v.b); -- should fail
924 ERROR: cannot execute MERGE on relation "rw_view1"
925 DETAIL: MERGE is not supported for relations with rules.
926 EXPLAIN (costs off) UPDATE rw_view2 SET a=3 WHERE a=2;
928 ----------------------------------------------------------------
931 -> Index Scan using base_tbl_pkey on base_tbl
933 -> Subquery Scan on rw_view1
934 Filter: ((rw_view1.a < 10) AND (rw_view1.a = 2))
935 -> Bitmap Heap Scan on base_tbl base_tbl_1
936 Recheck Cond: (a > 0)
937 -> Bitmap Index Scan on base_tbl_pkey
941 EXPLAIN (costs off) DELETE FROM rw_view2 WHERE a=2;
943 ----------------------------------------------------------------
946 -> Index Scan using base_tbl_pkey on base_tbl
948 -> Subquery Scan on rw_view1
949 Filter: ((rw_view1.a < 10) AND (rw_view1.a = 2))
950 -> Bitmap Heap Scan on base_tbl base_tbl_1
951 Recheck Cond: (a > 0)
952 -> Bitmap Index Scan on base_tbl_pkey
956 DROP TABLE base_tbl CASCADE;
957 NOTICE: drop cascades to 2 other objects
958 DETAIL: drop cascades to view rw_view1
959 drop cascades to view rw_view2
960 -- view on top of view with triggers
961 CREATE TABLE base_tbl (a int PRIMARY KEY, b text DEFAULT 'Unspecified');
962 INSERT INTO base_tbl SELECT i, 'Row ' || i FROM generate_series(-2, 2) g(i);
963 CREATE VIEW rw_view1 AS SELECT * FROM base_tbl WHERE a>0 OFFSET 0; -- not updatable without rules/triggers
964 CREATE VIEW rw_view2 AS SELECT * FROM rw_view1 WHERE a<10;
965 SELECT table_name, is_insertable_into
966 FROM information_schema.tables
967 WHERE table_name LIKE 'rw_view%'
969 table_name | is_insertable_into
970 ------------+--------------------
975 SELECT table_name, is_updatable, is_insertable_into,
976 is_trigger_updatable, is_trigger_deletable,
977 is_trigger_insertable_into
978 FROM information_schema.views
979 WHERE table_name LIKE 'rw_view%'
981 table_name | is_updatable | is_insertable_into | is_trigger_updatable | is_trigger_deletable | is_trigger_insertable_into
982 ------------+--------------+--------------------+----------------------+----------------------+----------------------------
983 rw_view1 | NO | NO | NO | NO | NO
984 rw_view2 | NO | NO | NO | NO | NO
987 SELECT table_name, column_name, is_updatable
988 FROM information_schema.columns
989 WHERE table_name LIKE 'rw_view%'
990 ORDER BY table_name, ordinal_position;
991 table_name | column_name | is_updatable
992 ------------+-------------+--------------
999 CREATE FUNCTION rw_view1_trig_fn()
1003 IF TG_OP = 'INSERT' THEN
1004 INSERT INTO base_tbl VALUES (NEW.a, NEW.b);
1006 ELSIF TG_OP = 'UPDATE' THEN
1007 UPDATE base_tbl SET b=NEW.b WHERE a=OLD.a;
1009 ELSIF TG_OP = 'DELETE' THEN
1010 DELETE FROM base_tbl WHERE a=OLD.a;
1016 CREATE TRIGGER rw_view1_ins_trig INSTEAD OF INSERT ON rw_view1
1017 FOR EACH ROW EXECUTE PROCEDURE rw_view1_trig_fn();
1018 SELECT table_name, is_insertable_into
1019 FROM information_schema.tables
1020 WHERE table_name LIKE 'rw_view%'
1021 ORDER BY table_name;
1022 table_name | is_insertable_into
1023 ------------+--------------------
1028 SELECT table_name, is_updatable, is_insertable_into,
1029 is_trigger_updatable, is_trigger_deletable,
1030 is_trigger_insertable_into
1031 FROM information_schema.views
1032 WHERE table_name LIKE 'rw_view%'
1033 ORDER BY table_name;
1034 table_name | is_updatable | is_insertable_into | is_trigger_updatable | is_trigger_deletable | is_trigger_insertable_into
1035 ------------+--------------+--------------------+----------------------+----------------------+----------------------------
1036 rw_view1 | NO | NO | NO | NO | YES
1037 rw_view2 | NO | NO | NO | NO | NO
1040 SELECT table_name, column_name, is_updatable
1041 FROM information_schema.columns
1042 WHERE table_name LIKE 'rw_view%'
1043 ORDER BY table_name, ordinal_position;
1044 table_name | column_name | is_updatable
1045 ------------+-------------+--------------
1052 CREATE TRIGGER rw_view1_upd_trig INSTEAD OF UPDATE ON rw_view1
1053 FOR EACH ROW EXECUTE PROCEDURE rw_view1_trig_fn();
1054 SELECT table_name, is_insertable_into
1055 FROM information_schema.tables
1056 WHERE table_name LIKE 'rw_view%'
1057 ORDER BY table_name;
1058 table_name | is_insertable_into
1059 ------------+--------------------
1064 SELECT table_name, is_updatable, is_insertable_into,
1065 is_trigger_updatable, is_trigger_deletable,
1066 is_trigger_insertable_into
1067 FROM information_schema.views
1068 WHERE table_name LIKE 'rw_view%'
1069 ORDER BY table_name;
1070 table_name | is_updatable | is_insertable_into | is_trigger_updatable | is_trigger_deletable | is_trigger_insertable_into
1071 ------------+--------------+--------------------+----------------------+----------------------+----------------------------
1072 rw_view1 | NO | NO | YES | NO | YES
1073 rw_view2 | NO | NO | NO | NO | NO
1076 SELECT table_name, column_name, is_updatable
1077 FROM information_schema.columns
1078 WHERE table_name LIKE 'rw_view%'
1079 ORDER BY table_name, ordinal_position;
1080 table_name | column_name | is_updatable
1081 ------------+-------------+--------------
1088 CREATE TRIGGER rw_view1_del_trig INSTEAD OF DELETE ON rw_view1
1089 FOR EACH ROW EXECUTE PROCEDURE rw_view1_trig_fn();
1090 SELECT table_name, is_insertable_into
1091 FROM information_schema.tables
1092 WHERE table_name LIKE 'rw_view%'
1093 ORDER BY table_name;
1094 table_name | is_insertable_into
1095 ------------+--------------------
1100 SELECT table_name, is_updatable, is_insertable_into,
1101 is_trigger_updatable, is_trigger_deletable,
1102 is_trigger_insertable_into
1103 FROM information_schema.views
1104 WHERE table_name LIKE 'rw_view%'
1105 ORDER BY table_name;
1106 table_name | is_updatable | is_insertable_into | is_trigger_updatable | is_trigger_deletable | is_trigger_insertable_into
1107 ------------+--------------+--------------------+----------------------+----------------------+----------------------------
1108 rw_view1 | NO | NO | YES | YES | YES
1109 rw_view2 | NO | NO | NO | NO | NO
1112 SELECT table_name, column_name, is_updatable
1113 FROM information_schema.columns
1114 WHERE table_name LIKE 'rw_view%'
1115 ORDER BY table_name, ordinal_position;
1116 table_name | column_name | is_updatable
1117 ------------+-------------+--------------
1124 INSERT INTO rw_view2 VALUES (3, 'Row 3') RETURNING *;
1130 UPDATE rw_view2 SET b='Row three' WHERE a=3 RETURNING *;
1136 SELECT * FROM rw_view2;
1144 DELETE FROM rw_view2 WHERE a=3 RETURNING *;
1150 SELECT * FROM rw_view2;
1157 MERGE INTO rw_view2 t
1158 USING (SELECT x, 'R'||x FROM generate_series(0,3) x) AS s(a,b) ON t.a = s.a
1159 WHEN MATCHED AND t.a <= 1 THEN DELETE
1160 WHEN MATCHED THEN UPDATE SET b = s.b
1161 WHEN NOT MATCHED AND s.a > 0 THEN INSERT VALUES (s.a, s.b)
1162 RETURNING merge_action(), s.*, t.*;
1163 merge_action | a | b | a | b
1164 --------------+---+----+---+-------
1165 DELETE | 1 | R1 | 1 | Row 1
1166 UPDATE | 2 | R2 | 2 | R2
1167 INSERT | 3 | R3 | 3 | R3
1170 SELECT * FROM base_tbl ORDER BY a;
1180 MERGE INTO rw_view2 t
1181 USING (SELECT x, 'r'||x FROM generate_series(0,2) x) AS s(a,b) ON t.a = s.a
1182 WHEN MATCHED THEN UPDATE SET b = s.b
1183 WHEN NOT MATCHED AND s.a > 0 THEN INSERT VALUES (s.a, s.b)
1184 WHEN NOT MATCHED BY SOURCE THEN UPDATE SET b = 'Not matched by source'
1185 RETURNING merge_action(), s.*, t.*;
1186 merge_action | a | b | a | b
1187 --------------+---+----+---+-----------------------
1188 UPDATE | 2 | r2 | 2 | r2
1189 UPDATE | | | 3 | Not matched by source
1190 INSERT | 1 | r1 | 1 | r1
1193 SELECT * FROM base_tbl ORDER BY a;
1195 ----+-----------------------
1201 3 | Not matched by source
1204 EXPLAIN (costs off) UPDATE rw_view2 SET a=3 WHERE a=2;
1206 ----------------------------------------------------------
1207 Update on rw_view1 rw_view1_1
1208 -> Subquery Scan on rw_view1
1209 Filter: ((rw_view1.a < 10) AND (rw_view1.a = 2))
1210 -> Bitmap Heap Scan on base_tbl
1211 Recheck Cond: (a > 0)
1212 -> Bitmap Index Scan on base_tbl_pkey
1216 EXPLAIN (costs off) DELETE FROM rw_view2 WHERE a=2;
1218 ----------------------------------------------------------
1219 Delete on rw_view1 rw_view1_1
1220 -> Subquery Scan on rw_view1
1221 Filter: ((rw_view1.a < 10) AND (rw_view1.a = 2))
1222 -> Bitmap Heap Scan on base_tbl
1223 Recheck Cond: (a > 0)
1224 -> Bitmap Index Scan on base_tbl_pkey
1229 MERGE INTO rw_view2 t
1230 USING (SELECT x, 'R'||x FROM generate_series(0,3) x) AS s(a,b) ON t.a = s.a
1231 WHEN MATCHED AND t.a <= 1 THEN DELETE
1232 WHEN MATCHED THEN UPDATE SET b = s.b
1233 WHEN NOT MATCHED AND s.a > 0 THEN INSERT VALUES (s.a, s.b);
1235 ------------------------------------------------------------
1236 Merge on rw_view1 rw_view1_1
1238 Hash Cond: (rw_view1.a = x.x)
1239 -> Subquery Scan on rw_view1
1240 Filter: (rw_view1.a < 10)
1241 -> Bitmap Heap Scan on base_tbl
1242 Recheck Cond: (a > 0)
1243 -> Bitmap Index Scan on base_tbl_pkey
1246 -> Function Scan on generate_series x
1249 -- MERGE with incomplete set of INSTEAD OF triggers
1250 DROP TRIGGER rw_view1_del_trig ON rw_view1;
1251 MERGE INTO rw_view2 t
1252 USING (SELECT x, 'R'||x FROM generate_series(0,3) x) AS s(a,b) ON t.a = s.a
1253 WHEN MATCHED AND t.a <= 1 THEN DELETE
1254 WHEN MATCHED THEN UPDATE SET b = s.b
1255 WHEN NOT MATCHED AND s.a > 0 THEN INSERT VALUES (s.a, s.b); -- should fail
1256 ERROR: cannot delete from view "rw_view1"
1257 DETAIL: Views containing LIMIT or OFFSET are not automatically updatable.
1258 HINT: To enable deleting from the view using MERGE, provide an INSTEAD OF DELETE trigger.
1259 MERGE INTO rw_view2 t
1260 USING (SELECT x, 'R'||x FROM generate_series(0,3) x) AS s(a,b) ON t.a = s.a
1261 WHEN MATCHED THEN UPDATE SET b = s.b
1262 WHEN NOT MATCHED AND s.a > 0 THEN INSERT VALUES (s.a, s.b); -- ok
1263 DROP TRIGGER rw_view1_ins_trig ON rw_view1;
1264 MERGE INTO rw_view2 t
1265 USING (SELECT x, 'R'||x FROM generate_series(0,3) x) AS s(a,b) ON t.a = s.a
1266 WHEN MATCHED THEN UPDATE SET b = s.b
1267 WHEN NOT MATCHED AND s.a > 0 THEN INSERT VALUES (s.a, s.b); -- should fail
1268 ERROR: cannot insert into view "rw_view1"
1269 DETAIL: Views containing LIMIT or OFFSET are not automatically updatable.
1270 HINT: To enable inserting into the view using MERGE, provide an INSTEAD OF INSERT trigger.
1271 MERGE INTO rw_view2 t
1272 USING (SELECT x, 'R'||x FROM generate_series(0,3) x) AS s(a,b) ON t.a = s.a
1273 WHEN MATCHED THEN UPDATE SET b = s.b; -- ok
1274 -- MERGE with INSTEAD OF triggers on auto-updatable view
1275 CREATE TRIGGER rw_view2_upd_trig INSTEAD OF UPDATE ON rw_view2
1276 FOR EACH ROW EXECUTE PROCEDURE rw_view1_trig_fn();
1277 MERGE INTO rw_view2 t
1278 USING (SELECT x, 'R'||x FROM generate_series(0,3) x) AS s(a,b) ON t.a = s.a
1279 WHEN MATCHED THEN UPDATE SET b = s.b
1280 WHEN NOT MATCHED AND s.a > 0 THEN INSERT VALUES (s.a, s.b); -- should fail
1281 ERROR: cannot merge into view "rw_view2"
1282 DETAIL: MERGE is not supported for views with INSTEAD OF triggers for some actions but not all.
1283 HINT: To enable merging into the view, either provide a full set of INSTEAD OF triggers or drop the existing INSTEAD OF triggers.
1284 MERGE INTO rw_view2 t
1285 USING (SELECT x, 'R'||x FROM generate_series(0,3) x) AS s(a,b) ON t.a = s.a
1286 WHEN MATCHED THEN UPDATE SET b = s.b; -- ok
1287 SELECT * FROM base_tbl ORDER BY a;
1298 DROP TABLE base_tbl CASCADE;
1299 NOTICE: drop cascades to 2 other objects
1300 DETAIL: drop cascades to view rw_view1
1301 drop cascades to view rw_view2
1302 DROP FUNCTION rw_view1_trig_fn();
1303 -- update using whole row from view
1304 CREATE TABLE base_tbl (a int PRIMARY KEY, b text DEFAULT 'Unspecified');
1305 INSERT INTO base_tbl SELECT i, 'Row ' || i FROM generate_series(-2, 2) g(i);
1306 CREATE VIEW rw_view1 AS SELECT b AS bb, a AS aa FROM base_tbl;
1307 CREATE FUNCTION rw_view1_aa(x rw_view1)
1308 RETURNS int AS $$ SELECT x.aa $$ LANGUAGE sql;
1309 UPDATE rw_view1 v SET bb='Updated row 2' WHERE rw_view1_aa(v)=2
1310 RETURNING rw_view1_aa(v), v.bb;
1312 -------------+---------------
1316 SELECT * FROM base_tbl;
1318 ----+---------------
1327 UPDATE rw_view1 v SET bb='Updated row 2' WHERE rw_view1_aa(v)=2
1328 RETURNING rw_view1_aa(v), v.bb;
1330 --------------------------------------------------
1332 -> Index Scan using base_tbl_pkey on base_tbl
1336 DROP TABLE base_tbl CASCADE;
1337 NOTICE: drop cascades to 2 other objects
1338 DETAIL: drop cascades to view rw_view1
1339 drop cascades to function rw_view1_aa(rw_view1)
1340 -- permissions checks
1341 CREATE USER regress_view_user1;
1342 CREATE USER regress_view_user2;
1343 CREATE USER regress_view_user3;
1344 SET SESSION AUTHORIZATION regress_view_user1;
1345 CREATE TABLE base_tbl(a int, b text, c float);
1346 INSERT INTO base_tbl VALUES (1, 'Row 1', 1.0);
1347 CREATE VIEW rw_view1 AS SELECT b AS bb, c AS cc, a AS aa FROM base_tbl;
1348 INSERT INTO rw_view1 VALUES ('Row 2', 2.0, 2);
1349 GRANT SELECT ON base_tbl TO regress_view_user2;
1350 GRANT SELECT ON rw_view1 TO regress_view_user2;
1351 GRANT UPDATE (a,c) ON base_tbl TO regress_view_user2;
1352 GRANT UPDATE (bb,cc) ON rw_view1 TO regress_view_user2;
1353 RESET SESSION AUTHORIZATION;
1354 SET SESSION AUTHORIZATION regress_view_user2;
1355 CREATE VIEW rw_view2 AS SELECT b AS bb, c AS cc, a AS aa FROM base_tbl;
1356 SELECT * FROM base_tbl; -- ok
1363 SELECT * FROM rw_view1; -- ok
1370 SELECT * FROM rw_view2; -- ok
1377 INSERT INTO base_tbl VALUES (3, 'Row 3', 3.0); -- not allowed
1378 ERROR: permission denied for table base_tbl
1379 INSERT INTO rw_view1 VALUES ('Row 3', 3.0, 3); -- not allowed
1380 ERROR: permission denied for view rw_view1
1381 INSERT INTO rw_view2 VALUES ('Row 3', 3.0, 3); -- not allowed
1382 ERROR: permission denied for table base_tbl
1383 MERGE INTO rw_view1 t
1384 USING (VALUES ('Row 3', 3.0, 3)) AS v(b,c,a) ON t.aa = v.a
1385 WHEN NOT MATCHED THEN INSERT VALUES (v.b, v.c, v.a); -- not allowed
1386 ERROR: permission denied for view rw_view1
1387 MERGE INTO rw_view2 t
1388 USING (VALUES ('Row 3', 3.0, 3)) AS v(b,c,a) ON t.aa = v.a
1389 WHEN NOT MATCHED THEN INSERT VALUES (v.b, v.c, v.a); -- not allowed
1390 ERROR: permission denied for table base_tbl
1391 UPDATE base_tbl SET a=a, c=c; -- ok
1392 UPDATE base_tbl SET b=b; -- not allowed
1393 ERROR: permission denied for table base_tbl
1394 UPDATE rw_view1 SET bb=bb, cc=cc; -- ok
1395 UPDATE rw_view1 SET aa=aa; -- not allowed
1396 ERROR: permission denied for view rw_view1
1397 UPDATE rw_view2 SET aa=aa, cc=cc; -- ok
1398 UPDATE rw_view2 SET bb=bb; -- not allowed
1399 ERROR: permission denied for table base_tbl
1400 MERGE INTO rw_view1 t USING (VALUES (1)) AS v(a) ON t.aa = v.a
1401 WHEN MATCHED THEN UPDATE SET bb = bb, cc = cc; -- ok
1402 MERGE INTO rw_view1 t USING (VALUES (1)) AS v(a) ON t.aa = v.a
1403 WHEN MATCHED THEN UPDATE SET aa = aa; -- not allowed
1404 ERROR: permission denied for view rw_view1
1405 MERGE INTO rw_view2 t USING (VALUES (1)) AS v(a) ON t.aa = v.a
1406 WHEN MATCHED THEN UPDATE SET aa = aa, cc = cc; -- ok
1407 MERGE INTO rw_view2 t USING (VALUES (1)) AS v(a) ON t.aa = v.a
1408 WHEN MATCHED THEN UPDATE SET bb = bb; -- not allowed
1409 ERROR: permission denied for table base_tbl
1410 DELETE FROM base_tbl; -- not allowed
1411 ERROR: permission denied for table base_tbl
1412 DELETE FROM rw_view1; -- not allowed
1413 ERROR: permission denied for view rw_view1
1414 DELETE FROM rw_view2; -- not allowed
1415 ERROR: permission denied for table base_tbl
1416 RESET SESSION AUTHORIZATION;
1417 SET SESSION AUTHORIZATION regress_view_user1;
1418 GRANT INSERT, DELETE ON base_tbl TO regress_view_user2;
1419 RESET SESSION AUTHORIZATION;
1420 SET SESSION AUTHORIZATION regress_view_user2;
1421 INSERT INTO base_tbl VALUES (3, 'Row 3', 3.0); -- ok
1422 INSERT INTO rw_view1 VALUES ('Row 4', 4.0, 4); -- not allowed
1423 ERROR: permission denied for view rw_view1
1424 INSERT INTO rw_view2 VALUES ('Row 4', 4.0, 4); -- ok
1425 DELETE FROM base_tbl WHERE a=1; -- ok
1426 DELETE FROM rw_view1 WHERE aa=2; -- not allowed
1427 ERROR: permission denied for view rw_view1
1428 DELETE FROM rw_view2 WHERE aa=2; -- ok
1429 MERGE INTO rw_view1 t USING (VALUES (1)) AS v(a) ON t.aa = v.a
1430 WHEN MATCHED AND bb = 'xxx' THEN DELETE; -- not allowed
1431 ERROR: permission denied for view rw_view1
1432 MERGE INTO rw_view2 t USING (VALUES (1)) AS v(a) ON t.aa = v.a
1433 WHEN MATCHED AND bb = 'xxx' THEN DELETE; -- ok
1434 SELECT * FROM base_tbl;
1441 RESET SESSION AUTHORIZATION;
1442 SET SESSION AUTHORIZATION regress_view_user1;
1443 REVOKE INSERT, DELETE ON base_tbl FROM regress_view_user2;
1444 GRANT INSERT, DELETE ON rw_view1 TO regress_view_user2;
1445 RESET SESSION AUTHORIZATION;
1446 SET SESSION AUTHORIZATION regress_view_user2;
1447 INSERT INTO base_tbl VALUES (5, 'Row 5', 5.0); -- not allowed
1448 ERROR: permission denied for table base_tbl
1449 INSERT INTO rw_view1 VALUES ('Row 5', 5.0, 5); -- ok
1450 INSERT INTO rw_view2 VALUES ('Row 6', 6.0, 6); -- not allowed
1451 ERROR: permission denied for table base_tbl
1452 DELETE FROM base_tbl WHERE a=3; -- not allowed
1453 ERROR: permission denied for table base_tbl
1454 DELETE FROM rw_view1 WHERE aa=3; -- ok
1455 DELETE FROM rw_view2 WHERE aa=4; -- not allowed
1456 ERROR: permission denied for table base_tbl
1457 MERGE INTO rw_view1 t USING (VALUES (1)) AS v(a) ON t.aa = v.a
1458 WHEN MATCHED AND bb = 'xxx' THEN DELETE; -- ok
1459 MERGE INTO rw_view2 t USING (VALUES (1)) AS v(a) ON t.aa = v.a
1460 WHEN MATCHED AND bb = 'xxx' THEN DELETE; -- not allowed
1461 ERROR: permission denied for table base_tbl
1462 SELECT * FROM base_tbl;
1469 RESET SESSION AUTHORIZATION;
1470 DROP TABLE base_tbl CASCADE;
1471 NOTICE: drop cascades to 2 other objects
1472 DETAIL: drop cascades to view rw_view1
1473 drop cascades to view rw_view2
1474 -- nested-view permissions
1475 CREATE TABLE base_tbl(a int, b text, c float);
1476 INSERT INTO base_tbl VALUES (1, 'Row 1', 1.0);
1477 SET SESSION AUTHORIZATION regress_view_user1;
1478 CREATE VIEW rw_view1 AS SELECT * FROM base_tbl;
1479 SELECT * FROM rw_view1; -- not allowed
1480 ERROR: permission denied for table base_tbl
1481 SELECT * FROM rw_view1 FOR UPDATE; -- not allowed
1482 ERROR: permission denied for table base_tbl
1483 UPDATE rw_view1 SET b = 'foo' WHERE a = 1; -- not allowed
1484 ERROR: permission denied for table base_tbl
1485 MERGE INTO rw_view1 t USING (VALUES (1)) AS v(a) ON t.a = v.a
1486 WHEN MATCHED THEN UPDATE SET b = 'foo'; -- not allowed
1487 ERROR: permission denied for table base_tbl
1488 SET SESSION AUTHORIZATION regress_view_user2;
1489 CREATE VIEW rw_view2 AS SELECT * FROM rw_view1;
1490 SELECT * FROM rw_view2; -- not allowed
1491 ERROR: permission denied for view rw_view1
1492 SELECT * FROM rw_view2 FOR UPDATE; -- not allowed
1493 ERROR: permission denied for view rw_view1
1494 UPDATE rw_view2 SET b = 'bar' WHERE a = 1; -- not allowed
1495 ERROR: permission denied for view rw_view1
1496 MERGE INTO rw_view2 t USING (VALUES (1)) AS v(a) ON t.a = v.a
1497 WHEN MATCHED THEN UPDATE SET b = 'foo'; -- not allowed
1498 ERROR: permission denied for view rw_view1
1499 RESET SESSION AUTHORIZATION;
1500 GRANT SELECT ON base_tbl TO regress_view_user1;
1501 SET SESSION AUTHORIZATION regress_view_user1;
1502 SELECT * FROM rw_view1;
1508 SELECT * FROM rw_view1 FOR UPDATE; -- not allowed
1509 ERROR: permission denied for table base_tbl
1510 UPDATE rw_view1 SET b = 'foo' WHERE a = 1; -- not allowed
1511 ERROR: permission denied for table base_tbl
1512 MERGE INTO rw_view1 t USING (VALUES (1)) AS v(a) ON t.a = v.a
1513 WHEN MATCHED THEN UPDATE SET b = 'foo'; -- not allowed
1514 ERROR: permission denied for table base_tbl
1515 SET SESSION AUTHORIZATION regress_view_user2;
1516 SELECT * FROM rw_view2; -- not allowed
1517 ERROR: permission denied for view rw_view1
1518 SELECT * FROM rw_view2 FOR UPDATE; -- not allowed
1519 ERROR: permission denied for view rw_view1
1520 UPDATE rw_view2 SET b = 'bar' WHERE a = 1; -- not allowed
1521 ERROR: permission denied for view rw_view1
1522 MERGE INTO rw_view2 t USING (VALUES (1)) AS v(a) ON t.a = v.a
1523 WHEN MATCHED THEN UPDATE SET b = 'foo'; -- not allowed
1524 ERROR: permission denied for view rw_view1
1525 SET SESSION AUTHORIZATION regress_view_user1;
1526 GRANT SELECT ON rw_view1 TO regress_view_user2;
1527 SET SESSION AUTHORIZATION regress_view_user2;
1528 SELECT * FROM rw_view2;
1534 SELECT * FROM rw_view2 FOR UPDATE; -- not allowed
1535 ERROR: permission denied for view rw_view1
1536 UPDATE rw_view2 SET b = 'bar' WHERE a = 1; -- not allowed
1537 ERROR: permission denied for view rw_view1
1538 MERGE INTO rw_view2 t USING (VALUES (1)) AS v(a) ON t.a = v.a
1539 WHEN MATCHED THEN UPDATE SET b = 'foo'; -- not allowed
1540 ERROR: permission denied for view rw_view1
1541 RESET SESSION AUTHORIZATION;
1542 GRANT UPDATE ON base_tbl TO regress_view_user1;
1543 SET SESSION AUTHORIZATION regress_view_user1;
1544 SELECT * FROM rw_view1;
1550 SELECT * FROM rw_view1 FOR UPDATE;
1556 UPDATE rw_view1 SET b = 'foo' WHERE a = 1;
1557 MERGE INTO rw_view1 t USING (VALUES (1)) AS v(a) ON t.a = v.a
1558 WHEN MATCHED THEN UPDATE SET b = 'foo';
1559 SET SESSION AUTHORIZATION regress_view_user2;
1560 SELECT * FROM rw_view2;
1566 SELECT * FROM rw_view2 FOR UPDATE; -- not allowed
1567 ERROR: permission denied for view rw_view1
1568 UPDATE rw_view2 SET b = 'bar' WHERE a = 1; -- not allowed
1569 ERROR: permission denied for view rw_view1
1570 MERGE INTO rw_view2 t USING (VALUES (1)) AS v(a) ON t.a = v.a
1571 WHEN MATCHED THEN UPDATE SET b = 'bar'; -- not allowed
1572 ERROR: permission denied for view rw_view1
1573 SET SESSION AUTHORIZATION regress_view_user1;
1574 GRANT UPDATE ON rw_view1 TO regress_view_user2;
1575 SET SESSION AUTHORIZATION regress_view_user2;
1576 SELECT * FROM rw_view2;
1582 SELECT * FROM rw_view2 FOR UPDATE;
1588 UPDATE rw_view2 SET b = 'bar' WHERE a = 1;
1589 MERGE INTO rw_view2 t USING (VALUES (1)) AS v(a) ON t.a = v.a
1590 WHEN MATCHED THEN UPDATE SET b = 'fud';
1591 RESET SESSION AUTHORIZATION;
1592 REVOKE UPDATE ON base_tbl FROM regress_view_user1;
1593 SET SESSION AUTHORIZATION regress_view_user1;
1594 SELECT * FROM rw_view1;
1600 SELECT * FROM rw_view1 FOR UPDATE; -- not allowed
1601 ERROR: permission denied for table base_tbl
1602 UPDATE rw_view1 SET b = 'foo' WHERE a = 1; -- not allowed
1603 ERROR: permission denied for table base_tbl
1604 MERGE INTO rw_view1 t USING (VALUES (1)) AS v(a) ON t.a = v.a
1605 WHEN MATCHED THEN UPDATE SET b = 'foo'; -- not allowed
1606 ERROR: permission denied for table base_tbl
1607 SET SESSION AUTHORIZATION regress_view_user2;
1608 SELECT * FROM rw_view2;
1614 SELECT * FROM rw_view2 FOR UPDATE; -- not allowed
1615 ERROR: permission denied for table base_tbl
1616 UPDATE rw_view2 SET b = 'bar' WHERE a = 1; -- not allowed
1617 ERROR: permission denied for table base_tbl
1618 MERGE INTO rw_view2 t USING (VALUES (1)) AS v(a) ON t.a = v.a
1619 WHEN MATCHED THEN UPDATE SET b = 'foo'; -- not allowed
1620 ERROR: permission denied for table base_tbl
1621 RESET SESSION AUTHORIZATION;
1622 DROP TABLE base_tbl CASCADE;
1623 NOTICE: drop cascades to 2 other objects
1624 DETAIL: drop cascades to view rw_view1
1625 drop cascades to view rw_view2
1626 -- security invoker view permissions
1627 SET SESSION AUTHORIZATION regress_view_user1;
1628 CREATE TABLE base_tbl(a int, b text, c float);
1629 INSERT INTO base_tbl VALUES (1, 'Row 1', 1.0);
1630 CREATE VIEW rw_view1 AS SELECT b AS bb, c AS cc, a AS aa FROM base_tbl;
1631 ALTER VIEW rw_view1 SET (security_invoker = true);
1632 INSERT INTO rw_view1 VALUES ('Row 2', 2.0, 2);
1633 GRANT SELECT ON rw_view1 TO regress_view_user2;
1634 GRANT UPDATE (bb,cc) ON rw_view1 TO regress_view_user2;
1635 SET SESSION AUTHORIZATION regress_view_user2;
1636 SELECT * FROM base_tbl; -- not allowed
1637 ERROR: permission denied for table base_tbl
1638 SELECT * FROM rw_view1; -- not allowed
1639 ERROR: permission denied for table base_tbl
1640 INSERT INTO base_tbl VALUES (3, 'Row 3', 3.0); -- not allowed
1641 ERROR: permission denied for table base_tbl
1642 INSERT INTO rw_view1 VALUES ('Row 3', 3.0, 3); -- not allowed
1643 ERROR: permission denied for view rw_view1
1644 UPDATE base_tbl SET a=a; -- not allowed
1645 ERROR: permission denied for table base_tbl
1646 UPDATE rw_view1 SET bb=bb, cc=cc; -- not allowed
1647 ERROR: permission denied for table base_tbl
1648 MERGE INTO rw_view1 t USING (VALUES (1)) AS v(a) ON t.aa = v.a
1649 WHEN MATCHED THEN UPDATE SET bb = bb; -- not allowed
1650 ERROR: permission denied for table base_tbl
1651 DELETE FROM base_tbl; -- not allowed
1652 ERROR: permission denied for table base_tbl
1653 DELETE FROM rw_view1; -- not allowed
1654 ERROR: permission denied for view rw_view1
1655 MERGE INTO rw_view1 t USING (VALUES (1)) AS v(a) ON t.aa = v.a
1656 WHEN MATCHED THEN DELETE; -- not allowed
1657 ERROR: permission denied for view rw_view1
1658 SET SESSION AUTHORIZATION regress_view_user1;
1659 GRANT SELECT ON base_tbl TO regress_view_user2;
1660 GRANT UPDATE (a,c) ON base_tbl TO regress_view_user2;
1661 SET SESSION AUTHORIZATION regress_view_user2;
1662 SELECT * FROM base_tbl; -- ok
1669 SELECT * FROM rw_view1; -- ok
1676 UPDATE base_tbl SET a=a, c=c; -- ok
1677 UPDATE base_tbl SET b=b; -- not allowed
1678 ERROR: permission denied for table base_tbl
1679 UPDATE rw_view1 SET cc=cc; -- ok
1680 MERGE INTO rw_view1 t USING (VALUES (1)) AS v(a) ON t.aa = v.a
1681 WHEN MATCHED THEN UPDATE SET cc = cc; -- ok
1682 UPDATE rw_view1 SET aa=aa; -- not allowed
1683 ERROR: permission denied for view rw_view1
1684 UPDATE rw_view1 SET bb=bb; -- not allowed
1685 ERROR: permission denied for table base_tbl
1686 MERGE INTO rw_view1 t USING (VALUES (1)) AS v(a) ON t.aa = v.a
1687 WHEN MATCHED THEN UPDATE SET aa = aa; -- not allowed
1688 ERROR: permission denied for view rw_view1
1689 MERGE INTO rw_view1 t USING (VALUES (1)) AS v(a) ON t.aa = v.a
1690 WHEN MATCHED THEN UPDATE SET bb = bb; -- not allowed
1691 ERROR: permission denied for table base_tbl
1692 SET SESSION AUTHORIZATION regress_view_user1;
1693 GRANT INSERT, DELETE ON base_tbl TO regress_view_user2;
1694 SET SESSION AUTHORIZATION regress_view_user2;
1695 INSERT INTO base_tbl VALUES (3, 'Row 3', 3.0); -- ok
1696 INSERT INTO rw_view1 VALUES ('Row 4', 4.0, 4); -- not allowed
1697 ERROR: permission denied for view rw_view1
1698 DELETE FROM base_tbl WHERE a=1; -- ok
1699 DELETE FROM rw_view1 WHERE aa=2; -- not allowed
1700 ERROR: permission denied for view rw_view1
1701 MERGE INTO rw_view1 t USING (VALUES (1)) AS v(a) ON t.aa = v.a
1702 WHEN MATCHED THEN DELETE; -- not allowed
1703 ERROR: permission denied for view rw_view1
1704 SET SESSION AUTHORIZATION regress_view_user1;
1705 REVOKE INSERT, DELETE ON base_tbl FROM regress_view_user2;
1706 GRANT INSERT, DELETE ON rw_view1 TO regress_view_user2;
1707 SET SESSION AUTHORIZATION regress_view_user2;
1708 INSERT INTO rw_view1 VALUES ('Row 4', 4.0, 4); -- not allowed
1709 ERROR: permission denied for table base_tbl
1710 DELETE FROM rw_view1 WHERE aa=2; -- not allowed
1711 ERROR: permission denied for table base_tbl
1712 MERGE INTO rw_view1 t USING (VALUES (1)) AS v(a) ON t.aa = v.a
1713 WHEN MATCHED THEN DELETE; -- not allowed
1714 ERROR: permission denied for table base_tbl
1715 SET SESSION AUTHORIZATION regress_view_user1;
1716 GRANT INSERT, DELETE ON base_tbl TO regress_view_user2;
1717 SET SESSION AUTHORIZATION regress_view_user2;
1718 INSERT INTO rw_view1 VALUES ('Row 4', 4.0, 4); -- ok
1719 DELETE FROM rw_view1 WHERE aa=2; -- ok
1720 MERGE INTO rw_view1 t USING (VALUES (3)) AS v(a) ON t.aa = v.a
1721 WHEN MATCHED THEN DELETE; -- ok
1722 SELECT * FROM base_tbl; -- ok
1728 RESET SESSION AUTHORIZATION;
1729 DROP TABLE base_tbl CASCADE;
1730 NOTICE: drop cascades to view rw_view1
1731 -- ordinary view on top of security invoker view permissions
1732 CREATE TABLE base_tbl(a int, b text, c float);
1733 INSERT INTO base_tbl VALUES (1, 'Row 1', 1.0);
1734 SET SESSION AUTHORIZATION regress_view_user1;
1735 CREATE VIEW rw_view1 AS SELECT b AS bb, c AS cc, a AS aa FROM base_tbl;
1736 ALTER VIEW rw_view1 SET (security_invoker = true);
1737 SELECT * FROM rw_view1; -- not allowed
1738 ERROR: permission denied for table base_tbl
1739 UPDATE rw_view1 SET aa=aa; -- not allowed
1740 ERROR: permission denied for table base_tbl
1741 MERGE INTO rw_view1 t USING (VALUES (2, 'Row 2', 2.0)) AS v(a,b,c) ON t.aa = v.a
1742 WHEN NOT MATCHED THEN INSERT VALUES (v.b, v.c, v.a); -- not allowed
1743 ERROR: permission denied for table base_tbl
1744 SET SESSION AUTHORIZATION regress_view_user2;
1745 CREATE VIEW rw_view2 AS SELECT cc AS ccc, aa AS aaa, bb AS bbb FROM rw_view1;
1746 GRANT SELECT, UPDATE ON rw_view2 TO regress_view_user3;
1747 SELECT * FROM rw_view2; -- not allowed
1748 ERROR: permission denied for view rw_view1
1749 UPDATE rw_view2 SET aaa=aaa; -- not allowed
1750 ERROR: permission denied for view rw_view1
1751 MERGE INTO rw_view2 t USING (VALUES (2, 'Row 2', 2.0)) AS v(a,b,c) ON t.aaa = v.a
1752 WHEN NOT MATCHED THEN INSERT VALUES (v.c, v.a, v.b); -- not allowed
1753 ERROR: permission denied for view rw_view1
1754 RESET SESSION AUTHORIZATION;
1755 GRANT SELECT ON base_tbl TO regress_view_user1;
1756 GRANT UPDATE (a, b) ON base_tbl TO regress_view_user1;
1757 SET SESSION AUTHORIZATION regress_view_user1;
1758 SELECT * FROM rw_view1; -- ok
1764 UPDATE rw_view1 SET aa=aa, bb=bb; -- ok
1765 UPDATE rw_view1 SET cc=cc; -- not allowed
1766 ERROR: permission denied for table base_tbl
1767 MERGE INTO rw_view1 t USING (VALUES (1)) AS v(a) ON t.aa = v.a
1768 WHEN MATCHED THEN UPDATE SET aa = aa, bb = bb; -- ok
1769 MERGE INTO rw_view1 t USING (VALUES (1)) AS v(a) ON t.aa = v.a
1770 WHEN MATCHED THEN UPDATE SET cc = cc; -- not allowed
1771 ERROR: permission denied for table base_tbl
1772 SET SESSION AUTHORIZATION regress_view_user2;
1773 SELECT * FROM rw_view2; -- not allowed
1774 ERROR: permission denied for view rw_view1
1775 UPDATE rw_view2 SET aaa=aaa; -- not allowed
1776 ERROR: permission denied for view rw_view1
1777 MERGE INTO rw_view2 t USING (VALUES (1)) AS v(a) ON t.aaa = v.a
1778 WHEN MATCHED THEN UPDATE SET aaa = aaa; -- not allowed
1779 ERROR: permission denied for view rw_view1
1780 SET SESSION AUTHORIZATION regress_view_user3;
1781 SELECT * FROM rw_view2; -- not allowed
1782 ERROR: permission denied for view rw_view1
1783 UPDATE rw_view2 SET aaa=aaa; -- not allowed
1784 ERROR: permission denied for view rw_view1
1785 MERGE INTO rw_view2 t USING (VALUES (1)) AS v(a) ON t.aaa = v.a
1786 WHEN MATCHED THEN UPDATE SET aaa = aaa; -- not allowed
1787 ERROR: permission denied for view rw_view1
1788 SET SESSION AUTHORIZATION regress_view_user1;
1789 GRANT SELECT ON rw_view1 TO regress_view_user2;
1790 GRANT UPDATE (bb, cc) ON rw_view1 TO regress_view_user2;
1791 SET SESSION AUTHORIZATION regress_view_user2;
1792 SELECT * FROM rw_view2; -- not allowed
1793 ERROR: permission denied for table base_tbl
1794 UPDATE rw_view2 SET bbb=bbb; -- not allowed
1795 ERROR: permission denied for table base_tbl
1796 MERGE INTO rw_view2 t USING (VALUES (1)) AS v(a) ON t.aaa = v.a
1797 WHEN MATCHED THEN UPDATE SET bbb = bbb; -- not allowed
1798 ERROR: permission denied for table base_tbl
1799 SET SESSION AUTHORIZATION regress_view_user3;
1800 SELECT * FROM rw_view2; -- not allowed
1801 ERROR: permission denied for table base_tbl
1802 UPDATE rw_view2 SET bbb=bbb; -- not allowed
1803 ERROR: permission denied for table base_tbl
1804 MERGE INTO rw_view2 t USING (VALUES (1)) AS v(a) ON t.aaa = v.a
1805 WHEN MATCHED THEN UPDATE SET bbb = bbb; -- not allowed
1806 ERROR: permission denied for table base_tbl
1807 RESET SESSION AUTHORIZATION;
1808 GRANT SELECT ON base_tbl TO regress_view_user2;
1809 GRANT UPDATE (a, c) ON base_tbl TO regress_view_user2;
1810 SET SESSION AUTHORIZATION regress_view_user2;
1811 SELECT * FROM rw_view2; -- ok
1817 UPDATE rw_view2 SET aaa=aaa; -- not allowed
1818 ERROR: permission denied for view rw_view1
1819 UPDATE rw_view2 SET bbb=bbb; -- not allowed
1820 ERROR: permission denied for table base_tbl
1821 UPDATE rw_view2 SET ccc=ccc; -- ok
1822 MERGE INTO rw_view2 t USING (VALUES (1)) AS v(a) ON t.aaa = v.a
1823 WHEN MATCHED THEN UPDATE SET aaa = aaa; -- not allowed
1824 ERROR: permission denied for view rw_view1
1825 MERGE INTO rw_view2 t USING (VALUES (1)) AS v(a) ON t.aaa = v.a
1826 WHEN MATCHED THEN UPDATE SET bbb = bbb; -- not allowed
1827 ERROR: permission denied for table base_tbl
1828 MERGE INTO rw_view2 t USING (VALUES (1)) AS v(a) ON t.aaa = v.a
1829 WHEN MATCHED THEN UPDATE SET ccc = ccc; -- ok
1830 SET SESSION AUTHORIZATION regress_view_user3;
1831 SELECT * FROM rw_view2; -- not allowed
1832 ERROR: permission denied for table base_tbl
1833 UPDATE rw_view2 SET aaa=aaa; -- not allowed
1834 ERROR: permission denied for view rw_view1
1835 UPDATE rw_view2 SET bbb=bbb; -- not allowed
1836 ERROR: permission denied for table base_tbl
1837 UPDATE rw_view2 SET ccc=ccc; -- not allowed
1838 ERROR: permission denied for table base_tbl
1839 MERGE INTO rw_view2 t USING (VALUES (1)) AS v(a) ON t.aaa = v.a
1840 WHEN MATCHED THEN UPDATE SET aaa = aaa; -- not allowed
1841 ERROR: permission denied for view rw_view1
1842 MERGE INTO rw_view2 t USING (VALUES (1)) AS v(a) ON t.aaa = v.a
1843 WHEN MATCHED THEN UPDATE SET bbb = bbb; -- not allowed
1844 ERROR: permission denied for table base_tbl
1845 MERGE INTO rw_view2 t USING (VALUES (1)) AS v(a) ON t.aaa = v.a
1846 WHEN MATCHED THEN UPDATE SET ccc = ccc; -- not allowed
1847 ERROR: permission denied for table base_tbl
1848 RESET SESSION AUTHORIZATION;
1849 GRANT SELECT ON base_tbl TO regress_view_user3;
1850 GRANT UPDATE (a, c) ON base_tbl TO regress_view_user3;
1851 SET SESSION AUTHORIZATION regress_view_user3;
1852 SELECT * FROM rw_view2; -- ok
1858 UPDATE rw_view2 SET aaa=aaa; -- not allowed
1859 ERROR: permission denied for view rw_view1
1860 UPDATE rw_view2 SET bbb=bbb; -- not allowed
1861 ERROR: permission denied for table base_tbl
1862 UPDATE rw_view2 SET ccc=ccc; -- ok
1863 MERGE INTO rw_view2 t USING (VALUES (1)) AS v(a) ON t.aaa = v.a
1864 WHEN MATCHED THEN UPDATE SET aaa = aaa; -- not allowed
1865 ERROR: permission denied for view rw_view1
1866 MERGE INTO rw_view2 t USING (VALUES (1)) AS v(a) ON t.aaa = v.a
1867 WHEN MATCHED THEN UPDATE SET bbb = bbb; -- not allowed
1868 ERROR: permission denied for table base_tbl
1869 MERGE INTO rw_view2 t USING (VALUES (1)) AS v(a) ON t.aaa = v.a
1870 WHEN MATCHED THEN UPDATE SET ccc = ccc; -- ok
1871 RESET SESSION AUTHORIZATION;
1872 REVOKE SELECT, UPDATE ON base_tbl FROM regress_view_user1;
1873 SET SESSION AUTHORIZATION regress_view_user1;
1874 SELECT * FROM rw_view1; -- not allowed
1875 ERROR: permission denied for table base_tbl
1876 UPDATE rw_view1 SET aa=aa; -- not allowed
1877 ERROR: permission denied for table base_tbl
1878 MERGE INTO rw_view1 t USING (VALUES (1)) AS v(a) ON t.aa = v.a
1879 WHEN MATCHED THEN UPDATE SET aa = aa; -- not allowed
1880 ERROR: permission denied for table base_tbl
1881 SET SESSION AUTHORIZATION regress_view_user2;
1882 SELECT * FROM rw_view2; -- ok
1888 UPDATE rw_view2 SET aaa=aaa; -- not allowed
1889 ERROR: permission denied for view rw_view1
1890 UPDATE rw_view2 SET bbb=bbb; -- not allowed
1891 ERROR: permission denied for table base_tbl
1892 UPDATE rw_view2 SET ccc=ccc; -- ok
1893 MERGE INTO rw_view2 t USING (VALUES (1)) AS v(a) ON t.aaa = v.a
1894 WHEN MATCHED THEN UPDATE SET aaa = aaa; -- not allowed
1895 ERROR: permission denied for view rw_view1
1896 MERGE INTO rw_view2 t USING (VALUES (1)) AS v(a) ON t.aaa = v.a
1897 WHEN MATCHED THEN UPDATE SET bbb = bbb; -- not allowed
1898 ERROR: permission denied for table base_tbl
1899 MERGE INTO rw_view2 t USING (VALUES (1)) AS v(a) ON t.aaa = v.a
1900 WHEN MATCHED THEN UPDATE SET ccc = ccc; -- ok
1901 SET SESSION AUTHORIZATION regress_view_user3;
1902 SELECT * FROM rw_view2; -- ok
1908 UPDATE rw_view2 SET aaa=aaa; -- not allowed
1909 ERROR: permission denied for view rw_view1
1910 UPDATE rw_view2 SET bbb=bbb; -- not allowed
1911 ERROR: permission denied for table base_tbl
1912 UPDATE rw_view2 SET ccc=ccc; -- ok
1913 MERGE INTO rw_view2 t USING (VALUES (1)) AS v(a) ON t.aaa = v.a
1914 WHEN MATCHED THEN UPDATE SET aaa = aaa; -- not allowed
1915 ERROR: permission denied for view rw_view1
1916 MERGE INTO rw_view2 t USING (VALUES (1)) AS v(a) ON t.aaa = v.a
1917 WHEN MATCHED THEN UPDATE SET bbb = bbb; -- not allowed
1918 ERROR: permission denied for table base_tbl
1919 MERGE INTO rw_view2 t USING (VALUES (1)) AS v(a) ON t.aaa = v.a
1920 WHEN MATCHED THEN UPDATE SET ccc = ccc; -- ok
1921 RESET SESSION AUTHORIZATION;
1922 REVOKE SELECT, UPDATE ON base_tbl FROM regress_view_user2;
1923 SET SESSION AUTHORIZATION regress_view_user2;
1924 SELECT * FROM rw_view2; -- not allowed
1925 ERROR: permission denied for table base_tbl
1926 UPDATE rw_view2 SET aaa=aaa; -- not allowed
1927 ERROR: permission denied for view rw_view1
1928 UPDATE rw_view2 SET bbb=bbb; -- not allowed
1929 ERROR: permission denied for table base_tbl
1930 UPDATE rw_view2 SET ccc=ccc; -- not allowed
1931 ERROR: permission denied for table base_tbl
1932 MERGE INTO rw_view2 t USING (VALUES (1)) AS v(a) ON t.aaa = v.a
1933 WHEN MATCHED THEN UPDATE SET aaa = aaa; -- not allowed
1934 ERROR: permission denied for view rw_view1
1935 MERGE INTO rw_view2 t USING (VALUES (1)) AS v(a) ON t.aaa = v.a
1936 WHEN MATCHED THEN UPDATE SET bbb = bbb; -- not allowed
1937 ERROR: permission denied for table base_tbl
1938 MERGE INTO rw_view2 t USING (VALUES (1)) AS v(a) ON t.aaa = v.a
1939 WHEN MATCHED THEN UPDATE SET ccc = ccc; -- not allowed
1940 ERROR: permission denied for table base_tbl
1941 SET SESSION AUTHORIZATION regress_view_user3;
1942 SELECT * FROM rw_view2; -- ok
1948 UPDATE rw_view2 SET aaa=aaa; -- not allowed
1949 ERROR: permission denied for view rw_view1
1950 UPDATE rw_view2 SET bbb=bbb; -- not allowed
1951 ERROR: permission denied for table base_tbl
1952 UPDATE rw_view2 SET ccc=ccc; -- ok
1953 MERGE INTO rw_view2 t USING (VALUES (1)) AS v(a) ON t.aaa = v.a
1954 WHEN MATCHED THEN UPDATE SET aaa = aaa; -- not allowed
1955 ERROR: permission denied for view rw_view1
1956 MERGE INTO rw_view2 t USING (VALUES (1)) AS v(a) ON t.aaa = v.a
1957 WHEN MATCHED THEN UPDATE SET bbb = bbb; -- not allowed
1958 ERROR: permission denied for table base_tbl
1959 MERGE INTO rw_view2 t USING (VALUES (1)) AS v(a) ON t.aaa = v.a
1960 WHEN MATCHED THEN UPDATE SET ccc = ccc; -- ok
1961 RESET SESSION AUTHORIZATION;
1962 DROP TABLE base_tbl CASCADE;
1963 NOTICE: drop cascades to 2 other objects
1964 DETAIL: drop cascades to view rw_view1
1965 drop cascades to view rw_view2
1966 DROP USER regress_view_user1;
1967 DROP USER regress_view_user2;
1968 DROP USER regress_view_user3;
1970 CREATE TABLE base_tbl (a int PRIMARY KEY, b text DEFAULT 'Unspecified', c serial);
1971 INSERT INTO base_tbl VALUES (1, 'Row 1');
1972 INSERT INTO base_tbl VALUES (2, 'Row 2');
1973 INSERT INTO base_tbl VALUES (3);
1974 CREATE VIEW rw_view1 AS SELECT a AS aa, b AS bb FROM base_tbl;
1975 ALTER VIEW rw_view1 ALTER COLUMN bb SET DEFAULT 'View default';
1976 INSERT INTO rw_view1 VALUES (4, 'Row 4');
1977 INSERT INTO rw_view1 (aa) VALUES (5);
1978 MERGE INTO rw_view1 t USING (VALUES (6)) AS v(a) ON t.aa = v.a
1979 WHEN NOT MATCHED THEN INSERT (aa) VALUES (v.a);
1980 SELECT * FROM base_tbl;
1982 ---+--------------+---
1987 5 | View default | 5
1988 6 | View default | 6
1991 DROP TABLE base_tbl CASCADE;
1992 NOTICE: drop cascades to view rw_view1
1993 -- Table having triggers
1994 CREATE TABLE base_tbl (a int PRIMARY KEY, b text DEFAULT 'Unspecified');
1995 INSERT INTO base_tbl VALUES (1, 'Row 1');
1996 INSERT INTO base_tbl VALUES (2, 'Row 2');
1997 CREATE FUNCTION rw_view1_trig_fn()
2001 IF TG_OP = 'INSERT' THEN
2002 UPDATE base_tbl SET b=NEW.b WHERE a=1;
2009 CREATE TRIGGER rw_view1_ins_trig AFTER INSERT ON base_tbl
2010 FOR EACH ROW EXECUTE PROCEDURE rw_view1_trig_fn();
2011 CREATE VIEW rw_view1 AS SELECT a AS aa, b AS bb FROM base_tbl;
2012 INSERT INTO rw_view1 VALUES (3, 'Row 3');
2013 select * from base_tbl;
2022 DROP TRIGGER rw_view1_ins_trig on base_tbl;
2023 DROP FUNCTION rw_view1_trig_fn();
2024 DROP TABLE base_tbl;
2025 -- view with ORDER BY
2026 CREATE TABLE base_tbl (a int, b int);
2027 INSERT INTO base_tbl VALUES (1,2), (4,5), (3,-3);
2028 CREATE VIEW rw_view1 AS SELECT * FROM base_tbl ORDER BY a+b;
2029 SELECT * FROM rw_view1;
2037 INSERT INTO rw_view1 VALUES (7,-8);
2038 SELECT * FROM rw_view1;
2047 EXPLAIN (verbose, costs off) UPDATE rw_view1 SET b = b + 1 RETURNING *;
2049 -------------------------------------------------
2050 Update on public.base_tbl
2051 Output: base_tbl.a, base_tbl.b
2052 -> Seq Scan on public.base_tbl
2053 Output: (base_tbl.b + 1), base_tbl.ctid
2056 UPDATE rw_view1 SET b = b + 1 RETURNING *;
2065 SELECT * FROM rw_view1;
2074 DROP TABLE base_tbl CASCADE;
2075 NOTICE: drop cascades to view rw_view1
2076 -- multiple array-column updates
2077 CREATE TABLE base_tbl (a int, arr int[]);
2078 INSERT INTO base_tbl VALUES (1,ARRAY[2]), (3,ARRAY[4]);
2079 CREATE VIEW rw_view1 AS SELECT * FROM base_tbl;
2080 UPDATE rw_view1 SET arr[1] = 42, arr[2] = 77 WHERE a = 3;
2081 SELECT * FROM rw_view1;
2088 DROP TABLE base_tbl CASCADE;
2089 NOTICE: drop cascades to view rw_view1
2090 -- views with updatable and non-updatable columns
2091 CREATE TABLE base_tbl(a float);
2092 INSERT INTO base_tbl SELECT i/10.0 FROM generate_series(1,10) g(i);
2093 CREATE VIEW rw_view1 AS
2094 SELECT ctid, sin(a) s, a, cos(a) c
2098 INSERT INTO rw_view1 VALUES (null, null, 1.1, null); -- should fail
2099 ERROR: cannot insert into column "ctid" of view "rw_view1"
2100 DETAIL: View columns that refer to system columns are not updatable.
2101 INSERT INTO rw_view1 (s, c, a) VALUES (null, null, 1.1); -- should fail
2102 ERROR: cannot insert into column "s" of view "rw_view1"
2103 DETAIL: View columns that are not columns of their base relation are not updatable.
2104 INSERT INTO rw_view1 (s, c, a) VALUES (default, default, 1.1); -- should fail
2105 ERROR: cannot insert into column "s" of view "rw_view1"
2106 DETAIL: View columns that are not columns of their base relation are not updatable.
2107 INSERT INTO rw_view1 (a) VALUES (1.1) RETURNING a, s, c; -- OK
2109 -----+-------------------+-------------------
2110 1.1 | 0.891207360061435 | 0.453596121425577
2113 UPDATE rw_view1 SET s = s WHERE a = 1.1; -- should fail
2114 ERROR: cannot update column "s" of view "rw_view1"
2115 DETAIL: View columns that are not columns of their base relation are not updatable.
2116 UPDATE rw_view1 SET a = 1.05 WHERE a = 1.1 RETURNING s; -- OK
2122 DELETE FROM rw_view1 WHERE a = 1.05; -- OK
2123 CREATE VIEW rw_view2 AS
2124 SELECT s, c, s/c t, a base_a, ctid
2126 INSERT INTO rw_view2 VALUES (null, null, null, 1.1, null); -- should fail
2127 ERROR: cannot insert into column "t" of view "rw_view2"
2128 DETAIL: View columns that are not columns of their base relation are not updatable.
2129 INSERT INTO rw_view2(s, c, base_a) VALUES (null, null, 1.1); -- should fail
2130 ERROR: cannot insert into column "s" of view "rw_view1"
2131 DETAIL: View columns that are not columns of their base relation are not updatable.
2132 INSERT INTO rw_view2(base_a) VALUES (1.1) RETURNING t; -- OK
2138 UPDATE rw_view2 SET s = s WHERE base_a = 1.1; -- should fail
2139 ERROR: cannot update column "s" of view "rw_view1"
2140 DETAIL: View columns that are not columns of their base relation are not updatable.
2141 UPDATE rw_view2 SET t = t WHERE base_a = 1.1; -- should fail
2142 ERROR: cannot update column "t" of view "rw_view2"
2143 DETAIL: View columns that are not columns of their base relation are not updatable.
2144 UPDATE rw_view2 SET base_a = 1.05 WHERE base_a = 1.1; -- OK
2145 DELETE FROM rw_view2 WHERE base_a = 1.05 RETURNING base_a, s, c, t; -- OK
2147 --------+-------------------+-------------------+------------------
2148 1.05 | 0.867423225594017 | 0.497571047891727 | 1.74331530998317
2151 CREATE VIEW rw_view3 AS
2152 SELECT s, c, s/c t, ctid
2154 INSERT INTO rw_view3 VALUES (null, null, null, null); -- should fail
2155 ERROR: cannot insert into column "t" of view "rw_view3"
2156 DETAIL: View columns that are not columns of their base relation are not updatable.
2157 INSERT INTO rw_view3(s) VALUES (null); -- should fail
2158 ERROR: cannot insert into column "s" of view "rw_view1"
2159 DETAIL: View columns that are not columns of their base relation are not updatable.
2160 UPDATE rw_view3 SET s = s; -- should fail
2161 ERROR: cannot update column "s" of view "rw_view1"
2162 DETAIL: View columns that are not columns of their base relation are not updatable.
2163 DELETE FROM rw_view3 WHERE s = sin(0.1); -- should be OK
2164 SELECT * FROM base_tbl ORDER BY a;
2178 SELECT table_name, is_insertable_into
2179 FROM information_schema.tables
2180 WHERE table_name LIKE E'r_\\_view%'
2181 ORDER BY table_name;
2182 table_name | is_insertable_into
2183 ------------+--------------------
2189 SELECT table_name, is_updatable, is_insertable_into
2190 FROM information_schema.views
2191 WHERE table_name LIKE E'r_\\_view%'
2192 ORDER BY table_name;
2193 table_name | is_updatable | is_insertable_into
2194 ------------+--------------+--------------------
2195 rw_view1 | YES | YES
2196 rw_view2 | YES | YES
2200 SELECT table_name, column_name, is_updatable
2201 FROM information_schema.columns
2202 WHERE table_name LIKE E'r_\\_view%'
2203 ORDER BY table_name, ordinal_position;
2204 table_name | column_name | is_updatable
2205 ------------+-------------+--------------
2206 rw_view1 | ctid | NO
2213 rw_view2 | base_a | YES
2214 rw_view2 | ctid | NO
2218 rw_view3 | ctid | NO
2221 SELECT events & 4 != 0 AS upd,
2222 events & 8 != 0 AS ins,
2223 events & 16 != 0 AS del
2224 FROM pg_catalog.pg_relation_is_updatable('rw_view3'::regclass, false) t(events);
2230 DROP TABLE base_tbl CASCADE;
2231 NOTICE: drop cascades to 3 other objects
2232 DETAIL: drop cascades to view rw_view1
2233 drop cascades to view rw_view2
2234 drop cascades to view rw_view3
2235 -- view on table with GENERATED columns
2236 CREATE TABLE base_tbl (id int, idplus1 int GENERATED ALWAYS AS (id + 1) STORED);
2237 CREATE VIEW rw_view1 AS SELECT * FROM base_tbl;
2238 INSERT INTO base_tbl (id) VALUES (1);
2239 INSERT INTO rw_view1 (id) VALUES (2);
2240 INSERT INTO base_tbl (id, idplus1) VALUES (3, DEFAULT);
2241 INSERT INTO rw_view1 (id, idplus1) VALUES (4, DEFAULT);
2242 INSERT INTO base_tbl (id, idplus1) VALUES (5, 6); -- error
2243 ERROR: cannot insert a non-DEFAULT value into column "idplus1"
2244 DETAIL: Column "idplus1" is a generated column.
2245 INSERT INTO rw_view1 (id, idplus1) VALUES (6, 7); -- error
2246 ERROR: cannot insert a non-DEFAULT value into column "idplus1"
2247 DETAIL: Column "idplus1" is a generated column.
2248 SELECT * FROM base_tbl;
2257 UPDATE base_tbl SET id = 2000 WHERE id = 2;
2258 UPDATE rw_view1 SET id = 3000 WHERE id = 3;
2259 SELECT * FROM base_tbl;
2268 DROP TABLE base_tbl CASCADE;
2269 NOTICE: drop cascades to view rw_view1
2270 -- inheritance tests
2271 CREATE TABLE base_tbl_parent (a int);
2272 CREATE TABLE base_tbl_child (CHECK (a > 0)) INHERITS (base_tbl_parent);
2273 INSERT INTO base_tbl_parent SELECT * FROM generate_series(-8, -1);
2274 INSERT INTO base_tbl_child SELECT * FROM generate_series(1, 8);
2275 CREATE VIEW rw_view1 AS SELECT * FROM base_tbl_parent;
2276 CREATE VIEW rw_view2 AS SELECT * FROM ONLY base_tbl_parent;
2277 SELECT * FROM rw_view1 ORDER BY a;
2298 SELECT * FROM ONLY rw_view1 ORDER BY a;
2319 SELECT * FROM rw_view2 ORDER BY a;
2332 INSERT INTO rw_view1 VALUES (-100), (100);
2333 INSERT INTO rw_view2 VALUES (-200), (200);
2334 UPDATE rw_view1 SET a = a*10 WHERE a IN (-1, 1); -- Should produce -10 and 10
2335 UPDATE ONLY rw_view1 SET a = a*10 WHERE a IN (-2, 2); -- Should produce -20 and 20
2336 UPDATE rw_view2 SET a = a*10 WHERE a IN (-3, 3); -- Should produce -30 only
2337 UPDATE ONLY rw_view2 SET a = a*10 WHERE a IN (-4, 4); -- Should produce -40 only
2338 DELETE FROM rw_view1 WHERE a IN (-5, 5); -- Should delete -5 and 5
2339 DELETE FROM ONLY rw_view1 WHERE a IN (-6, 6); -- Should delete -6 and 6
2340 DELETE FROM rw_view2 WHERE a IN (-7, 7); -- Should delete -7 only
2341 DELETE FROM ONLY rw_view2 WHERE a IN (-8, 8); -- Should delete -8 only
2342 SELECT * FROM ONLY base_tbl_parent ORDER BY a;
2355 SELECT * FROM base_tbl_child ORDER BY a;
2366 MERGE INTO rw_view1 t USING (VALUES (-200), (10)) AS v(a) ON t.a = v.a
2367 WHEN MATCHED THEN UPDATE SET a = t.a+1; -- Should produce -199 and 11
2368 MERGE INTO ONLY rw_view1 t USING (VALUES (-100), (20)) AS v(a) ON t.a = v.a
2369 WHEN MATCHED THEN UPDATE SET a = t.a+1; -- Should produce -99 and 21
2370 MERGE INTO rw_view2 t USING (VALUES (-40), (3)) AS v(a) ON t.a = v.a
2371 WHEN MATCHED THEN UPDATE SET a = t.a+1; -- Should produce -39 only
2372 MERGE INTO ONLY rw_view2 t USING (VALUES (-30), (4)) AS v(a) ON t.a = v.a
2373 WHEN MATCHED THEN UPDATE SET a = t.a+1; -- Should produce -29 only
2374 SELECT * FROM ONLY base_tbl_parent ORDER BY a;
2387 SELECT * FROM base_tbl_child ORDER BY a;
2398 CREATE TABLE other_tbl_parent (id int);
2399 CREATE TABLE other_tbl_child () INHERITS (other_tbl_parent);
2400 INSERT INTO other_tbl_parent VALUES (7),(200);
2401 INSERT INTO other_tbl_child VALUES (8),(100);
2403 UPDATE rw_view1 SET a = a + 1000 FROM other_tbl_parent WHERE a = id;
2405 -------------------------------------------------------------------------
2406 Update on base_tbl_parent
2407 Update on base_tbl_parent base_tbl_parent_1
2408 Update on base_tbl_child base_tbl_parent_2
2410 Merge Cond: (base_tbl_parent.a = other_tbl_parent.id)
2412 Sort Key: base_tbl_parent.a
2414 -> Seq Scan on base_tbl_parent base_tbl_parent_1
2415 -> Seq Scan on base_tbl_child base_tbl_parent_2
2417 Sort Key: other_tbl_parent.id
2419 -> Seq Scan on other_tbl_parent other_tbl_parent_1
2420 -> Seq Scan on other_tbl_child other_tbl_parent_2
2423 UPDATE rw_view1 SET a = a + 1000 FROM other_tbl_parent WHERE a = id;
2424 SELECT * FROM ONLY base_tbl_parent ORDER BY a;
2437 SELECT * FROM base_tbl_child ORDER BY a;
2448 DROP TABLE base_tbl_parent, base_tbl_child CASCADE;
2449 NOTICE: drop cascades to 2 other objects
2450 DETAIL: drop cascades to view rw_view1
2451 drop cascades to view rw_view2
2452 DROP TABLE other_tbl_parent CASCADE;
2453 NOTICE: drop cascades to table other_tbl_child
2454 -- simple WITH CHECK OPTION
2455 CREATE TABLE base_tbl (a int, b int DEFAULT 10);
2456 INSERT INTO base_tbl VALUES (1,2), (2,3), (1,-1);
2457 CREATE VIEW rw_view1 AS SELECT * FROM base_tbl WHERE a < b
2458 WITH LOCAL CHECK OPTION;
2460 View "public.rw_view1"
2461 Column | Type | Collation | Nullable | Default | Storage | Description
2462 --------+---------+-----------+----------+---------+---------+-------------
2463 a | integer | | | | plain |
2464 b | integer | | | | plain |
2470 Options: check_option=local
2472 SELECT * FROM information_schema.views WHERE table_name = 'rw_view1';
2473 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
2474 ---------------+--------------+------------+------------------+--------------+--------------+--------------------+----------------------+----------------------+----------------------------
2475 regression | public | rw_view1 | SELECT a, +| LOCAL | YES | YES | NO | NO | NO
2476 | | | b +| | | | | |
2477 | | | FROM base_tbl+| | | | | |
2478 | | | WHERE (a < b); | | | | | |
2481 INSERT INTO rw_view1 VALUES(3,4); -- ok
2482 INSERT INTO rw_view1 VALUES(4,3); -- should fail
2483 ERROR: new row violates check option for view "rw_view1"
2484 DETAIL: Failing row contains (4, 3).
2485 INSERT INTO rw_view1 VALUES(5,null); -- should fail
2486 ERROR: new row violates check option for view "rw_view1"
2487 DETAIL: Failing row contains (5, null).
2488 UPDATE rw_view1 SET b = 5 WHERE a = 3; -- ok
2489 UPDATE rw_view1 SET b = -5 WHERE a = 3; -- should fail
2490 ERROR: new row violates check option for view "rw_view1"
2491 DETAIL: Failing row contains (3, -5).
2492 INSERT INTO rw_view1(a) VALUES (9); -- ok
2493 INSERT INTO rw_view1(a) VALUES (10); -- should fail
2494 ERROR: new row violates check option for view "rw_view1"
2495 DETAIL: Failing row contains (10, 10).
2496 SELECT * FROM base_tbl ORDER BY a, b;
2506 MERGE INTO rw_view1 t USING (VALUES (10)) AS v(a) ON t.a = v.a
2507 WHEN NOT MATCHED THEN INSERT VALUES (v.a, v.a + 1); -- ok
2508 MERGE INTO rw_view1 t USING (VALUES (11)) AS v(a) ON t.a = v.a
2509 WHEN NOT MATCHED THEN INSERT VALUES (v.a, v.a - 1); -- should fail
2510 ERROR: new row violates check option for view "rw_view1"
2511 DETAIL: Failing row contains (11, 10).
2512 MERGE INTO rw_view1 t USING (VALUES (1)) AS v(a) ON t.a = v.a
2513 WHEN MATCHED THEN UPDATE SET a = t.a - 1; -- ok
2514 MERGE INTO rw_view1 t USING (VALUES (2)) AS v(a) ON t.a = v.a
2515 WHEN MATCHED THEN UPDATE SET a = t.a + 1; -- should fail
2516 ERROR: new row violates check option for view "rw_view1"
2517 DETAIL: Failing row contains (3, 3).
2518 SELECT * FROM base_tbl ORDER BY a, b;
2529 DROP TABLE base_tbl CASCADE;
2530 NOTICE: drop cascades to view rw_view1
2531 -- WITH LOCAL/CASCADED CHECK OPTION
2532 CREATE TABLE base_tbl (a int);
2533 CREATE VIEW rw_view1 AS SELECT * FROM base_tbl WHERE a > 0;
2534 CREATE VIEW rw_view2 AS SELECT * FROM rw_view1 WHERE a < 10
2535 WITH CHECK OPTION; -- implicitly cascaded
2537 View "public.rw_view2"
2538 Column | Type | Collation | Nullable | Default | Storage | Description
2539 --------+---------+-----------+----------+---------+---------+-------------
2540 a | integer | | | | plain |
2545 Options: check_option=cascaded
2547 SELECT * FROM information_schema.views WHERE table_name = 'rw_view2';
2548 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
2549 ---------------+--------------+------------+-------------------+--------------+--------------+--------------------+----------------------+----------------------+----------------------------
2550 regression | public | rw_view2 | SELECT a +| CASCADED | YES | YES | NO | NO | NO
2551 | | | FROM rw_view1 +| | | | | |
2552 | | | WHERE (a < 10); | | | | | |
2555 INSERT INTO rw_view2 VALUES (-5); -- should fail
2556 ERROR: new row violates check option for view "rw_view1"
2557 DETAIL: Failing row contains (-5).
2558 INSERT INTO rw_view2 VALUES (5); -- ok
2559 INSERT INTO rw_view2 VALUES (15); -- should fail
2560 ERROR: new row violates check option for view "rw_view2"
2561 DETAIL: Failing row contains (15).
2562 SELECT * FROM base_tbl;
2568 UPDATE rw_view2 SET a = a - 10; -- should fail
2569 ERROR: new row violates check option for view "rw_view1"
2570 DETAIL: Failing row contains (-5).
2571 UPDATE rw_view2 SET a = a + 10; -- should fail
2572 ERROR: new row violates check option for view "rw_view2"
2573 DETAIL: Failing row contains (15).
2574 CREATE OR REPLACE VIEW rw_view2 AS SELECT * FROM rw_view1 WHERE a < 10
2575 WITH LOCAL CHECK OPTION;
2577 View "public.rw_view2"
2578 Column | Type | Collation | Nullable | Default | Storage | Description
2579 --------+---------+-----------+----------+---------+---------+-------------
2580 a | integer | | | | plain |
2585 Options: check_option=local
2587 SELECT * FROM information_schema.views WHERE table_name = 'rw_view2';
2588 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
2589 ---------------+--------------+------------+-------------------+--------------+--------------+--------------------+----------------------+----------------------+----------------------------
2590 regression | public | rw_view2 | SELECT a +| LOCAL | YES | YES | NO | NO | NO
2591 | | | FROM rw_view1 +| | | | | |
2592 | | | WHERE (a < 10); | | | | | |
2595 INSERT INTO rw_view2 VALUES (-10); -- ok, but not in view
2596 INSERT INTO rw_view2 VALUES (20); -- should fail
2597 ERROR: new row violates check option for view "rw_view2"
2598 DETAIL: Failing row contains (20).
2599 SELECT * FROM base_tbl;
2606 ALTER VIEW rw_view1 SET (check_option=here); -- invalid
2607 ERROR: invalid value for enum option "check_option": here
2608 DETAIL: Valid values are "local" and "cascaded".
2609 ALTER VIEW rw_view1 SET (check_option=local);
2610 INSERT INTO rw_view2 VALUES (-20); -- should fail
2611 ERROR: new row violates check option for view "rw_view1"
2612 DETAIL: Failing row contains (-20).
2613 INSERT INTO rw_view2 VALUES (30); -- should fail
2614 ERROR: new row violates check option for view "rw_view2"
2615 DETAIL: Failing row contains (30).
2616 ALTER VIEW rw_view2 RESET (check_option);
2618 View "public.rw_view2"
2619 Column | Type | Collation | Nullable | Default | Storage | Description
2620 --------+---------+-----------+----------+---------+---------+-------------
2621 a | integer | | | | plain |
2627 SELECT * FROM information_schema.views WHERE table_name = 'rw_view2';
2628 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
2629 ---------------+--------------+------------+-------------------+--------------+--------------+--------------------+----------------------+----------------------+----------------------------
2630 regression | public | rw_view2 | SELECT a +| NONE | YES | YES | NO | NO | NO
2631 | | | FROM rw_view1 +| | | | | |
2632 | | | WHERE (a < 10); | | | | | |
2635 INSERT INTO rw_view2 VALUES (30); -- ok, but not in view
2636 SELECT * FROM base_tbl;
2644 DROP TABLE base_tbl CASCADE;
2645 NOTICE: drop cascades to 2 other objects
2646 DETAIL: drop cascades to view rw_view1
2647 drop cascades to view rw_view2
2648 -- WITH CHECK OPTION with no local view qual
2649 CREATE TABLE base_tbl (a int);
2650 CREATE VIEW rw_view1 AS SELECT * FROM base_tbl WITH CHECK OPTION;
2651 CREATE VIEW rw_view2 AS SELECT * FROM rw_view1 WHERE a > 0;
2652 CREATE VIEW rw_view3 AS SELECT * FROM rw_view2 WITH CHECK OPTION;
2653 SELECT * FROM information_schema.views WHERE table_name LIKE E'rw\\_view_' ORDER BY table_name;
2654 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
2655 ---------------+--------------+------------+-------------------+--------------+--------------+--------------------+----------------------+----------------------+----------------------------
2656 regression | public | rw_view1 | SELECT a +| CASCADED | YES | YES | NO | NO | NO
2657 | | | FROM base_tbl; | | | | | |
2658 regression | public | rw_view2 | SELECT a +| NONE | YES | YES | NO | NO | NO
2659 | | | FROM rw_view1 +| | | | | |
2660 | | | WHERE (a > 0); | | | | | |
2661 regression | public | rw_view3 | SELECT a +| CASCADED | YES | YES | NO | NO | NO
2662 | | | FROM rw_view2; | | | | | |
2665 INSERT INTO rw_view1 VALUES (-1); -- ok
2666 INSERT INTO rw_view1 VALUES (1); -- ok
2667 INSERT INTO rw_view2 VALUES (-2); -- ok, but not in view
2668 INSERT INTO rw_view2 VALUES (2); -- ok
2669 INSERT INTO rw_view3 VALUES (-3); -- should fail
2670 ERROR: new row violates check option for view "rw_view2"
2671 DETAIL: Failing row contains (-3).
2672 INSERT INTO rw_view3 VALUES (3); -- ok
2673 DROP TABLE base_tbl CASCADE;
2674 NOTICE: drop cascades to 3 other objects
2675 DETAIL: drop cascades to view rw_view1
2676 drop cascades to view rw_view2
2677 drop cascades to view rw_view3
2678 -- WITH CHECK OPTION with scalar array ops
2679 CREATE TABLE base_tbl (a int, b int[]);
2680 CREATE VIEW rw_view1 AS SELECT * FROM base_tbl WHERE a = ANY (b)
2682 INSERT INTO rw_view1 VALUES (1, ARRAY[1,2,3]); -- ok
2683 INSERT INTO rw_view1 VALUES (10, ARRAY[4,5]); -- should fail
2684 ERROR: new row violates check option for view "rw_view1"
2685 DETAIL: Failing row contains (10, {4,5}).
2686 UPDATE rw_view1 SET b[2] = -b[2] WHERE a = 1; -- ok
2687 UPDATE rw_view1 SET b[1] = -b[1] WHERE a = 1; -- should fail
2688 ERROR: new row violates check option for view "rw_view1"
2689 DETAIL: Failing row contains (1, {-1,-2,3}).
2690 PREPARE ins(int, int[]) AS INSERT INTO rw_view1 VALUES($1, $2);
2691 EXECUTE ins(2, ARRAY[1,2,3]); -- ok
2692 EXECUTE ins(10, ARRAY[4,5]); -- should fail
2693 ERROR: new row violates check option for view "rw_view1"
2694 DETAIL: Failing row contains (10, {4,5}).
2695 DEALLOCATE PREPARE ins;
2696 DROP TABLE base_tbl CASCADE;
2697 NOTICE: drop cascades to view rw_view1
2698 -- WITH CHECK OPTION with subquery
2699 CREATE TABLE base_tbl (a int);
2700 CREATE TABLE ref_tbl (a int PRIMARY KEY);
2701 INSERT INTO ref_tbl SELECT * FROM generate_series(1,10);
2702 CREATE VIEW rw_view1 AS
2703 SELECT * FROM base_tbl b
2704 WHERE EXISTS(SELECT 1 FROM ref_tbl r WHERE r.a = b.a)
2706 INSERT INTO rw_view1 VALUES (5); -- ok
2707 INSERT INTO rw_view1 VALUES (15); -- should fail
2708 ERROR: new row violates check option for view "rw_view1"
2709 DETAIL: Failing row contains (15).
2710 UPDATE rw_view1 SET a = a + 5; -- ok
2711 UPDATE rw_view1 SET a = a + 5; -- should fail
2712 ERROR: new row violates check option for view "rw_view1"
2713 DETAIL: Failing row contains (15).
2714 EXPLAIN (costs off) INSERT INTO rw_view1 VALUES (5);
2716 ---------------------------------------------------------
2717 Insert on base_tbl b
2720 -> Index Only Scan using ref_tbl_pkey on ref_tbl r
2721 Index Cond: (a = b.a)
2724 EXPLAIN (costs off) UPDATE rw_view1 SET a = a + 5;
2726 -----------------------------------------------------------
2727 Update on base_tbl b
2729 Hash Cond: (b.a = r.a)
2730 -> Seq Scan on base_tbl b
2732 -> Seq Scan on ref_tbl r
2734 -> Index Only Scan using ref_tbl_pkey on ref_tbl r_1
2735 Index Cond: (a = b.a)
2738 DROP TABLE base_tbl, ref_tbl CASCADE;
2739 NOTICE: drop cascades to view rw_view1
2740 -- WITH CHECK OPTION with BEFORE trigger on base table
2741 CREATE TABLE base_tbl (a int, b int);
2742 CREATE FUNCTION base_tbl_trig_fn()
2751 CREATE TRIGGER base_tbl_trig BEFORE INSERT OR UPDATE ON base_tbl
2752 FOR EACH ROW EXECUTE PROCEDURE base_tbl_trig_fn();
2753 CREATE VIEW rw_view1 AS SELECT * FROM base_tbl WHERE a < b WITH CHECK OPTION;
2754 INSERT INTO rw_view1 VALUES (5,0); -- ok
2755 INSERT INTO rw_view1 VALUES (15, 20); -- should fail
2756 ERROR: new row violates check option for view "rw_view1"
2757 DETAIL: Failing row contains (15, 10).
2758 UPDATE rw_view1 SET a = 20, b = 30; -- should fail
2759 ERROR: new row violates check option for view "rw_view1"
2760 DETAIL: Failing row contains (20, 10).
2761 DROP TABLE base_tbl CASCADE;
2762 NOTICE: drop cascades to view rw_view1
2763 DROP FUNCTION base_tbl_trig_fn();
2764 -- WITH LOCAL CHECK OPTION with INSTEAD OF trigger on base view
2765 CREATE TABLE base_tbl (a int, b int);
2766 CREATE VIEW rw_view1 AS SELECT a FROM base_tbl WHERE a < b;
2767 CREATE FUNCTION rw_view1_trig_fn()
2771 IF TG_OP = 'INSERT' THEN
2772 INSERT INTO base_tbl VALUES (NEW.a, 10);
2774 ELSIF TG_OP = 'UPDATE' THEN
2775 UPDATE base_tbl SET a=NEW.a WHERE a=OLD.a;
2777 ELSIF TG_OP = 'DELETE' THEN
2778 DELETE FROM base_tbl WHERE a=OLD.a;
2784 CREATE TRIGGER rw_view1_trig
2785 INSTEAD OF INSERT OR UPDATE OR DELETE ON rw_view1
2786 FOR EACH ROW EXECUTE PROCEDURE rw_view1_trig_fn();
2787 CREATE VIEW rw_view2 AS
2788 SELECT * FROM rw_view1 WHERE a > 0 WITH LOCAL CHECK OPTION;
2789 INSERT INTO rw_view2 VALUES (-5); -- should fail
2790 ERROR: new row violates check option for view "rw_view2"
2791 DETAIL: Failing row contains (-5).
2792 MERGE INTO rw_view2 t USING (VALUES (-5)) AS v(a) ON t.a = v.a
2793 WHEN NOT MATCHED THEN INSERT VALUES (v.a); -- should fail
2794 ERROR: new row violates check option for view "rw_view2"
2795 DETAIL: Failing row contains (-5).
2796 INSERT INTO rw_view2 VALUES (5); -- ok
2797 MERGE INTO rw_view2 t USING (VALUES (6)) AS v(a) ON t.a = v.a
2798 WHEN NOT MATCHED THEN INSERT VALUES (v.a); -- ok
2799 INSERT INTO rw_view2 VALUES (50); -- ok, but not in view
2800 MERGE INTO rw_view2 t USING (VALUES (60)) AS v(a) ON t.a = v.a
2801 WHEN NOT MATCHED THEN INSERT VALUES (v.a); -- ok, but not in view
2802 UPDATE rw_view2 SET a = a - 10; -- should fail
2803 ERROR: new row violates check option for view "rw_view2"
2804 DETAIL: Failing row contains (-5).
2805 MERGE INTO rw_view2 t USING (VALUES (6)) AS v(a) ON t.a = v.a
2806 WHEN MATCHED THEN UPDATE SET a = t.a - 10; -- should fail
2807 ERROR: new row violates check option for view "rw_view2"
2808 DETAIL: Failing row contains (-4).
2809 SELECT * FROM base_tbl;
2818 -- Check option won't cascade down to base view with INSTEAD OF triggers
2819 ALTER VIEW rw_view2 SET (check_option=cascaded);
2820 INSERT INTO rw_view2 VALUES (100); -- ok, but not in view (doesn't fail rw_view1's check)
2821 UPDATE rw_view2 SET a = 200 WHERE a = 5; -- ok, but not in view (doesn't fail rw_view1's check)
2822 SELECT * FROM base_tbl;
2832 -- Neither local nor cascaded check options work with INSTEAD rules
2833 DROP TRIGGER rw_view1_trig ON rw_view1;
2834 CREATE RULE rw_view1_ins_rule AS ON INSERT TO rw_view1
2835 DO INSTEAD INSERT INTO base_tbl VALUES (NEW.a, 10);
2836 CREATE RULE rw_view1_upd_rule AS ON UPDATE TO rw_view1
2837 DO INSTEAD UPDATE base_tbl SET a=NEW.a WHERE a=OLD.a;
2838 INSERT INTO rw_view2 VALUES (-10); -- ok, but not in view (doesn't fail rw_view2's check)
2839 INSERT INTO rw_view2 VALUES (5); -- ok
2840 INSERT INTO rw_view2 VALUES (20); -- ok, but not in view (doesn't fail rw_view1's check)
2841 UPDATE rw_view2 SET a = 30 WHERE a = 5; -- ok, but not in view (doesn't fail rw_view1's check)
2842 INSERT INTO rw_view2 VALUES (5); -- ok
2843 UPDATE rw_view2 SET a = -5 WHERE a = 5; -- ok, but not in view (doesn't fail rw_view2's check)
2844 SELECT * FROM base_tbl;
2858 DROP TABLE base_tbl CASCADE;
2859 NOTICE: drop cascades to 2 other objects
2860 DETAIL: drop cascades to view rw_view1
2861 drop cascades to view rw_view2
2862 DROP FUNCTION rw_view1_trig_fn();
2863 CREATE TABLE base_tbl (a int);
2864 CREATE VIEW rw_view1 AS SELECT a,10 AS b FROM base_tbl;
2865 CREATE RULE rw_view1_ins_rule AS ON INSERT TO rw_view1
2866 DO INSTEAD INSERT INTO base_tbl VALUES (NEW.a);
2867 CREATE VIEW rw_view2 AS
2868 SELECT * FROM rw_view1 WHERE a > b WITH LOCAL CHECK OPTION;
2869 INSERT INTO rw_view2 VALUES (2,3); -- ok, but not in view (doesn't fail rw_view2's check)
2870 DROP TABLE base_tbl CASCADE;
2871 NOTICE: drop cascades to 2 other objects
2872 DETAIL: drop cascades to view rw_view1
2873 drop cascades to view rw_view2
2874 -- security barrier view
2875 CREATE TABLE base_tbl (person text, visibility text);
2876 INSERT INTO base_tbl VALUES ('Tom', 'public'),
2877 ('Dick', 'private'),
2878 ('Harry', 'public');
2879 CREATE VIEW rw_view1 AS
2880 SELECT person FROM base_tbl WHERE visibility = 'public';
2881 CREATE FUNCTION snoop(anyelement)
2885 RAISE NOTICE 'snooped value: %', $1;
2889 LANGUAGE plpgsql COST 0.000001;
2890 CREATE OR REPLACE FUNCTION leakproof(anyelement)
2897 LANGUAGE plpgsql STRICT IMMUTABLE LEAKPROOF;
2898 SELECT * FROM rw_view1 WHERE snoop(person);
2899 NOTICE: snooped value: Tom
2900 NOTICE: snooped value: Dick
2901 NOTICE: snooped value: Harry
2908 UPDATE rw_view1 SET person=person WHERE snoop(person);
2909 NOTICE: snooped value: Tom
2910 NOTICE: snooped value: Dick
2911 NOTICE: snooped value: Harry
2912 DELETE FROM rw_view1 WHERE NOT snoop(person);
2913 NOTICE: snooped value: Dick
2914 NOTICE: snooped value: Tom
2915 NOTICE: snooped value: Harry
2916 ALTER VIEW rw_view1 SET (security_barrier = true);
2917 SELECT table_name, is_insertable_into
2918 FROM information_schema.tables
2919 WHERE table_name = 'rw_view1';
2920 table_name | is_insertable_into
2921 ------------+--------------------
2925 SELECT table_name, is_updatable, is_insertable_into
2926 FROM information_schema.views
2927 WHERE table_name = 'rw_view1';
2928 table_name | is_updatable | is_insertable_into
2929 ------------+--------------+--------------------
2930 rw_view1 | YES | YES
2933 SELECT table_name, column_name, is_updatable
2934 FROM information_schema.columns
2935 WHERE table_name = 'rw_view1'
2936 ORDER BY ordinal_position;
2937 table_name | column_name | is_updatable
2938 ------------+-------------+--------------
2939 rw_view1 | person | YES
2942 SELECT * FROM rw_view1 WHERE snoop(person);
2943 NOTICE: snooped value: Tom
2944 NOTICE: snooped value: Harry
2951 UPDATE rw_view1 SET person=person WHERE snoop(person);
2952 NOTICE: snooped value: Tom
2953 NOTICE: snooped value: Harry
2954 DELETE FROM rw_view1 WHERE NOT snoop(person);
2955 NOTICE: snooped value: Tom
2956 NOTICE: snooped value: Harry
2957 MERGE INTO rw_view1 t
2958 USING (VALUES ('Tom'), ('Dick'), ('Harry')) AS v(person) ON t.person = v.person
2959 WHEN MATCHED AND snoop(t.person) THEN UPDATE SET person = v.person;
2960 NOTICE: snooped value: Tom
2961 NOTICE: snooped value: Harry
2962 EXPLAIN (costs off) SELECT * FROM rw_view1 WHERE snoop(person);
2964 -----------------------------------------------
2965 Subquery Scan on rw_view1
2966 Filter: snoop(rw_view1.person)
2967 -> Seq Scan on base_tbl
2968 Filter: (visibility = 'public'::text)
2971 EXPLAIN (costs off) UPDATE rw_view1 SET person=person WHERE snoop(person);
2973 -------------------------------------------------------------------
2975 -> Seq Scan on base_tbl
2976 Filter: ((visibility = 'public'::text) AND snoop(person))
2979 EXPLAIN (costs off) DELETE FROM rw_view1 WHERE NOT snoop(person);
2981 -------------------------------------------------------------------------
2983 -> Seq Scan on base_tbl
2984 Filter: ((visibility = 'public'::text) AND (NOT snoop(person)))
2988 MERGE INTO rw_view1 t
2989 USING (VALUES ('Tom'), ('Dick'), ('Harry')) AS v(person) ON t.person = v.person
2990 WHEN MATCHED AND snoop(t.person) THEN UPDATE SET person = v.person;
2992 -------------------------------------------------------------
2995 Join Filter: (base_tbl.person = "*VALUES*".column1)
2996 -> Seq Scan on base_tbl
2997 Filter: (visibility = 'public'::text)
2999 -> Values Scan on "*VALUES*"
3002 -- security barrier view on top of security barrier view
3003 CREATE VIEW rw_view2 WITH (security_barrier = true) AS
3004 SELECT * FROM rw_view1 WHERE snoop(person);
3005 SELECT table_name, is_insertable_into
3006 FROM information_schema.tables
3007 WHERE table_name = 'rw_view2';
3008 table_name | is_insertable_into
3009 ------------+--------------------
3013 SELECT table_name, is_updatable, is_insertable_into
3014 FROM information_schema.views
3015 WHERE table_name = 'rw_view2';
3016 table_name | is_updatable | is_insertable_into
3017 ------------+--------------+--------------------
3018 rw_view2 | YES | YES
3021 SELECT table_name, column_name, is_updatable
3022 FROM information_schema.columns
3023 WHERE table_name = 'rw_view2'
3024 ORDER BY ordinal_position;
3025 table_name | column_name | is_updatable
3026 ------------+-------------+--------------
3027 rw_view2 | person | YES
3030 SELECT * FROM rw_view2 WHERE snoop(person);
3031 NOTICE: snooped value: Tom
3032 NOTICE: snooped value: Tom
3033 NOTICE: snooped value: Harry
3034 NOTICE: snooped value: Harry
3041 UPDATE rw_view2 SET person=person WHERE snoop(person);
3042 NOTICE: snooped value: Tom
3043 NOTICE: snooped value: Tom
3044 NOTICE: snooped value: Harry
3045 NOTICE: snooped value: Harry
3046 DELETE FROM rw_view2 WHERE NOT snoop(person);
3047 NOTICE: snooped value: Tom
3048 NOTICE: snooped value: Tom
3049 NOTICE: snooped value: Harry
3050 NOTICE: snooped value: Harry
3051 MERGE INTO rw_view2 t
3052 USING (VALUES ('Tom'), ('Dick'), ('Harry')) AS v(person) ON t.person = v.person
3053 WHEN MATCHED AND snoop(t.person) THEN UPDATE SET person = v.person;
3054 NOTICE: snooped value: Tom
3055 NOTICE: snooped value: Tom
3056 NOTICE: snooped value: Harry
3057 NOTICE: snooped value: Harry
3058 EXPLAIN (costs off) SELECT * FROM rw_view2 WHERE snoop(person);
3060 -----------------------------------------------------
3061 Subquery Scan on rw_view2
3062 Filter: snoop(rw_view2.person)
3063 -> Subquery Scan on rw_view1
3064 Filter: snoop(rw_view1.person)
3065 -> Seq Scan on base_tbl
3066 Filter: (visibility = 'public'::text)
3069 EXPLAIN (costs off) UPDATE rw_view2 SET person=person WHERE snoop(person);
3071 -------------------------------------------------------------------------------------
3073 -> Seq Scan on base_tbl
3074 Filter: ((visibility = 'public'::text) AND snoop(person) AND snoop(person))
3077 EXPLAIN (costs off) DELETE FROM rw_view2 WHERE NOT snoop(person);
3079 -------------------------------------------------------------------------------------------
3081 -> Seq Scan on base_tbl
3082 Filter: ((visibility = 'public'::text) AND snoop(person) AND (NOT snoop(person)))
3086 MERGE INTO rw_view2 t
3087 USING (VALUES ('Tom'), ('Dick'), ('Harry')) AS v(person) ON t.person = v.person
3088 WHEN MATCHED AND snoop(t.person) THEN UPDATE SET person = v.person;
3090 -------------------------------------------------------------------------
3093 Join Filter: (base_tbl.person = "*VALUES*".column1)
3094 -> Seq Scan on base_tbl
3095 Filter: ((visibility = 'public'::text) AND snoop(person))
3096 -> Values Scan on "*VALUES*"
3099 DROP TABLE base_tbl CASCADE;
3100 NOTICE: drop cascades to 2 other objects
3101 DETAIL: drop cascades to view rw_view1
3102 drop cascades to view rw_view2
3103 -- security barrier view on top of table with rules
3104 CREATE TABLE base_tbl(id int PRIMARY KEY, data text, deleted boolean);
3105 INSERT INTO base_tbl VALUES (1, 'Row 1', false), (2, 'Row 2', true);
3106 CREATE RULE base_tbl_ins_rule AS ON INSERT TO base_tbl
3107 WHERE EXISTS (SELECT 1 FROM base_tbl t WHERE t.id = new.id)
3109 UPDATE base_tbl SET data = new.data, deleted = false WHERE id = new.id;
3110 CREATE RULE base_tbl_del_rule AS ON DELETE TO base_tbl
3112 UPDATE base_tbl SET deleted = true WHERE id = old.id;
3113 CREATE VIEW rw_view1 WITH (security_barrier=true) AS
3114 SELECT id, data FROM base_tbl WHERE NOT deleted;
3115 SELECT * FROM rw_view1;
3121 EXPLAIN (costs off) DELETE FROM rw_view1 WHERE id = 1 AND snoop(data);
3123 -------------------------------------------------------------------
3124 Update on base_tbl base_tbl_1
3126 -> Index Scan using base_tbl_pkey on base_tbl base_tbl_1
3127 Index Cond: (id = 1)
3128 -> Index Scan using base_tbl_pkey on base_tbl
3129 Index Cond: (id = 1)
3130 Filter: ((NOT deleted) AND snoop(data))
3133 DELETE FROM rw_view1 WHERE id = 1 AND snoop(data);
3134 NOTICE: snooped value: Row 1
3135 EXPLAIN (costs off) INSERT INTO rw_view1 VALUES (2, 'New row 2');
3137 -----------------------------------------------------------
3140 -> Index Only Scan using base_tbl_pkey on base_tbl t
3141 Index Cond: (id = 2)
3143 One-Time Filter: ((InitPlan 1).col1 IS NOT TRUE)
3147 -> Index Only Scan using base_tbl_pkey on base_tbl t
3148 Index Cond: (id = 2)
3150 One-Time Filter: (InitPlan 1).col1
3151 -> Index Scan using base_tbl_pkey on base_tbl
3152 Index Cond: (id = 2)
3155 INSERT INTO rw_view1 VALUES (2, 'New row 2');
3156 SELECT * FROM base_tbl;
3158 ----+-----------+---------
3163 DROP TABLE base_tbl CASCADE;
3164 NOTICE: drop cascades to view rw_view1
3165 -- security barrier view based on inheritance set
3166 CREATE TABLE t1 (a int, b float, c text);
3167 CREATE INDEX t1_a_idx ON t1(a);
3169 SELECT i,i,'t1' FROM generate_series(1,10) g(i);
3171 CREATE TABLE t11 (d text) INHERITS (t1);
3172 CREATE INDEX t11_a_idx ON t11(a);
3174 SELECT i,i,'t11','t11d' FROM generate_series(1,10) g(i);
3176 CREATE TABLE t12 (e int[]) INHERITS (t1);
3177 CREATE INDEX t12_a_idx ON t12(a);
3179 SELECT i,i,'t12','{1,2}'::int[] FROM generate_series(1,10) g(i);
3181 CREATE TABLE t111 () INHERITS (t11, t12);
3182 NOTICE: merging multiple inherited definitions of column "a"
3183 NOTICE: merging multiple inherited definitions of column "b"
3184 NOTICE: merging multiple inherited definitions of column "c"
3185 CREATE INDEX t111_a_idx ON t111(a);
3187 SELECT i,i,'t111','t111d','{1,1,1}'::int[] FROM generate_series(1,10) g(i);
3189 CREATE VIEW v1 WITH (security_barrier=true) AS
3190 SELECT *, (SELECT d FROM t11 WHERE t11.a = t1.a LIMIT 1) AS d
3192 WHERE a > 5 AND EXISTS(SELECT 1 FROM t12 WHERE t12.a = t1.a);
3193 SELECT * FROM v1 WHERE a=3; -- should not see anything
3198 SELECT * FROM v1 WHERE a=8;
3200 ---+---+------+------
3207 EXPLAIN (VERBOSE, COSTS OFF)
3208 UPDATE v1 SET a=100 WHERE snoop(a) AND leakproof(a) AND a < 7 AND a != 6;
3210 -----------------------------------------------------------------------------------------------------------
3212 Update on public.t1 t1_1
3213 Update on public.t11 t1_2
3214 Update on public.t12 t1_3
3215 Update on public.t111 t1_4
3217 Output: 100, t1.tableoid, t1.ctid
3219 -> Index Scan using t1_a_idx on public.t1 t1_1
3220 Output: t1_1.tableoid, t1_1.ctid
3221 Index Cond: ((t1_1.a > 5) AND (t1_1.a < 7))
3222 Filter: ((t1_1.a <> 6) AND EXISTS(SubPlan 1) AND snoop(t1_1.a) AND leakproof(t1_1.a))
3225 -> Seq Scan on public.t12 t12_1
3226 Filter: (t12_1.a = t1_1.a)
3227 -> Seq Scan on public.t111 t12_2
3228 Filter: (t12_2.a = t1_1.a)
3229 -> Index Scan using t11_a_idx on public.t11 t1_2
3230 Output: t1_2.tableoid, t1_2.ctid
3231 Index Cond: ((t1_2.a > 5) AND (t1_2.a < 7))
3232 Filter: ((t1_2.a <> 6) AND EXISTS(SubPlan 1) AND snoop(t1_2.a) AND leakproof(t1_2.a))
3233 -> Index Scan using t12_a_idx on public.t12 t1_3
3234 Output: t1_3.tableoid, t1_3.ctid
3235 Index Cond: ((t1_3.a > 5) AND (t1_3.a < 7))
3236 Filter: ((t1_3.a <> 6) AND EXISTS(SubPlan 1) AND snoop(t1_3.a) AND leakproof(t1_3.a))
3237 -> Index Scan using t111_a_idx on public.t111 t1_4
3238 Output: t1_4.tableoid, t1_4.ctid
3239 Index Cond: ((t1_4.a > 5) AND (t1_4.a < 7))
3240 Filter: ((t1_4.a <> 6) AND EXISTS(SubPlan 1) AND snoop(t1_4.a) AND leakproof(t1_4.a))
3243 UPDATE v1 SET a=100 WHERE snoop(a) AND leakproof(a) AND a < 7 AND a != 6;
3244 SELECT * FROM v1 WHERE a=100; -- Nothing should have been changed to 100
3249 SELECT * FROM t1 WHERE a=100; -- Nothing should have been changed to 100
3254 EXPLAIN (VERBOSE, COSTS OFF)
3255 UPDATE v1 SET a=a+1 WHERE snoop(a) AND leakproof(a) AND a = 8;
3257 -----------------------------------------------------------------------------------------
3259 Update on public.t1 t1_1
3260 Update on public.t11 t1_2
3261 Update on public.t12 t1_3
3262 Update on public.t111 t1_4
3264 Output: (t1.a + 1), t1.tableoid, t1.ctid
3266 -> Index Scan using t1_a_idx on public.t1 t1_1
3267 Output: t1_1.a, t1_1.tableoid, t1_1.ctid
3268 Index Cond: ((t1_1.a > 5) AND (t1_1.a = 8))
3269 Filter: (EXISTS(SubPlan 1) AND snoop(t1_1.a) AND leakproof(t1_1.a))
3272 -> Seq Scan on public.t12 t12_1
3273 Filter: (t12_1.a = t1_1.a)
3274 -> Seq Scan on public.t111 t12_2
3275 Filter: (t12_2.a = t1_1.a)
3276 -> Index Scan using t11_a_idx on public.t11 t1_2
3277 Output: t1_2.a, t1_2.tableoid, t1_2.ctid
3278 Index Cond: ((t1_2.a > 5) AND (t1_2.a = 8))
3279 Filter: (EXISTS(SubPlan 1) AND snoop(t1_2.a) AND leakproof(t1_2.a))
3280 -> Index Scan using t12_a_idx on public.t12 t1_3
3281 Output: t1_3.a, t1_3.tableoid, t1_3.ctid
3282 Index Cond: ((t1_3.a > 5) AND (t1_3.a = 8))
3283 Filter: (EXISTS(SubPlan 1) AND snoop(t1_3.a) AND leakproof(t1_3.a))
3284 -> Index Scan using t111_a_idx on public.t111 t1_4
3285 Output: t1_4.a, t1_4.tableoid, t1_4.ctid
3286 Index Cond: ((t1_4.a > 5) AND (t1_4.a = 8))
3287 Filter: (EXISTS(SubPlan 1) AND snoop(t1_4.a) AND leakproof(t1_4.a))
3290 UPDATE v1 SET a=a+1 WHERE snoop(a) AND leakproof(a) AND a = 8;
3291 NOTICE: snooped value: 8
3292 NOTICE: snooped value: 8
3293 NOTICE: snooped value: 8
3294 NOTICE: snooped value: 8
3295 SELECT * FROM v1 WHERE b=8;
3297 ---+---+------+------
3304 DELETE FROM v1 WHERE snoop(a) AND leakproof(a); -- should not delete everything, just where a>5
3305 NOTICE: snooped value: 6
3306 NOTICE: snooped value: 7
3307 NOTICE: snooped value: 9
3308 NOTICE: snooped value: 10
3309 NOTICE: snooped value: 9
3310 NOTICE: snooped value: 6
3311 NOTICE: snooped value: 7
3312 NOTICE: snooped value: 9
3313 NOTICE: snooped value: 10
3314 NOTICE: snooped value: 9
3315 NOTICE: snooped value: 6
3316 NOTICE: snooped value: 7
3317 NOTICE: snooped value: 9
3318 NOTICE: snooped value: 10
3319 NOTICE: snooped value: 9
3320 NOTICE: snooped value: 6
3321 NOTICE: snooped value: 7
3322 NOTICE: snooped value: 9
3323 NOTICE: snooped value: 10
3324 NOTICE: snooped value: 9
3325 TABLE t1; -- verify all a<=5 are intact
3350 DROP TABLE t1, t11, t12, t111 CASCADE;
3351 NOTICE: drop cascades to view v1
3352 DROP FUNCTION snoop(anyelement);
3353 DROP FUNCTION leakproof(anyelement);
3354 CREATE TABLE tx1 (a integer);
3355 CREATE TABLE tx2 (b integer);
3356 CREATE TABLE tx3 (c integer);
3357 CREATE VIEW vx1 AS SELECT a FROM tx1 WHERE EXISTS(SELECT 1 FROM tx2 JOIN tx3 ON b=c);
3358 INSERT INTO vx1 values (1);
3374 CREATE TABLE tx1 (a integer);
3375 CREATE TABLE tx2 (b integer);
3376 CREATE TABLE tx3 (c integer);
3377 CREATE VIEW vx1 AS SELECT a FROM tx1 WHERE EXISTS(SELECT 1 FROM tx2 JOIN tx3 ON b=c);
3378 INSERT INTO vx1 VALUES (1);
3379 INSERT INTO vx1 VALUES (1);
3396 CREATE TABLE tx1 (a integer, b integer);
3397 CREATE TABLE tx2 (b integer, c integer);
3398 CREATE TABLE tx3 (c integer, d integer);
3399 ALTER TABLE tx1 DROP COLUMN b;
3400 ALTER TABLE tx2 DROP COLUMN c;
3401 ALTER TABLE tx3 DROP COLUMN d;
3402 CREATE VIEW vx1 AS SELECT a FROM tx1 WHERE EXISTS(SELECT 1 FROM tx2 JOIN tx3 ON b=c);
3403 INSERT INTO vx1 VALUES (1);
3404 INSERT INTO vx1 VALUES (1);
3422 -- Test handling of vars from correlated subqueries in quals from outer
3423 -- security barrier views, per bug #13988
3425 CREATE TABLE t1 (a int, b text, c int);
3426 INSERT INTO t1 VALUES (1, 'one', 10);
3427 CREATE TABLE t2 (cc int);
3428 INSERT INTO t2 VALUES (10), (20);
3429 CREATE VIEW v1 WITH (security_barrier = true) AS
3430 SELECT * FROM t1 WHERE (a > 0)
3432 CREATE VIEW v2 WITH (security_barrier = true) AS
3433 SELECT * FROM v1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.cc = v1.c)
3435 INSERT INTO v2 VALUES (2, 'two', 20); -- ok
3436 INSERT INTO v2 VALUES (-2, 'minus two', 20); -- not allowed
3437 ERROR: new row violates check option for view "v1"
3438 DETAIL: Failing row contains (-2, minus two, 20).
3439 INSERT INTO v2 VALUES (3, 'three', 30); -- not allowed
3440 ERROR: new row violates check option for view "v2"
3441 DETAIL: Failing row contains (3, three, 30).
3442 UPDATE v2 SET b = 'ONE' WHERE a = 1; -- ok
3443 UPDATE v2 SET a = -1 WHERE a = 1; -- not allowed
3444 ERROR: new row violates check option for view "v1"
3445 DETAIL: Failing row contains (-1, ONE, 10).
3446 UPDATE v2 SET c = 30 WHERE a = 1; -- not allowed
3447 ERROR: new row violates check option for view "v2"
3448 DETAIL: Failing row contains (1, ONE, 30).
3449 DELETE FROM v2 WHERE a = 2; -- ok
3461 -- Test sub-select in nested security barrier views, per bug #17972
3463 CREATE TABLE t1 (a int);
3464 CREATE VIEW v1 WITH (security_barrier = true) AS
3466 CREATE RULE v1_upd_rule AS ON UPDATE TO v1 DO INSTEAD
3467 UPDATE t1 SET a = NEW.a WHERE a = OLD.a;
3468 CREATE VIEW v2 WITH (security_barrier = true) AS
3469 SELECT * FROM v1 WHERE EXISTS (SELECT 1);
3470 EXPLAIN (COSTS OFF) UPDATE v2 SET a = 1;
3472 --------------------------------------------------------------
3477 Merge Cond: (t1.a = v1.a)
3483 -> Subquery Scan on v1
3485 One-Time Filter: (InitPlan 1).col1
3486 -> Seq Scan on t1 t1_1
3493 -- Test CREATE OR REPLACE VIEW turning a non-updatable view into an
3494 -- auto-updatable view and adding check options in a single step
3496 CREATE TABLE t1 (a int, b text);
3497 CREATE VIEW v1 AS SELECT null::int AS a;
3498 CREATE OR REPLACE VIEW v1 AS SELECT * FROM t1 WHERE a > 0 WITH CHECK OPTION;
3499 INSERT INTO v1 VALUES (1, 'ok'); -- ok
3500 INSERT INTO v1 VALUES (-1, 'invalid'); -- should fail
3501 ERROR: new row violates check option for view "v1"
3502 DETAIL: Failing row contains (-1, invalid).
3505 -- check that an auto-updatable view on a partitioned table works correctly
3506 create table uv_pt (a int, b int, v varchar) partition by range (a, b);
3507 create table uv_pt1 (b int not null, v varchar, a int not null) partition by range (b);
3508 create table uv_pt11 (like uv_pt1);
3509 alter table uv_pt11 drop a;
3510 alter table uv_pt11 add a int;
3511 alter table uv_pt11 drop a;
3512 alter table uv_pt11 add a int not null;
3513 alter table uv_pt1 attach partition uv_pt11 for values from (2) to (5);
3514 alter table uv_pt attach partition uv_pt1 for values from (1, 2) to (1, 10);
3515 create view uv_ptv as select * from uv_pt;
3516 select events & 4 != 0 AS upd,
3517 events & 8 != 0 AS ins,
3518 events & 16 != 0 AS del
3519 from pg_catalog.pg_relation_is_updatable('uv_pt'::regclass, false) t(events);
3525 select pg_catalog.pg_column_is_updatable('uv_pt'::regclass, 1::smallint, false);
3526 pg_column_is_updatable
3527 ------------------------
3531 select pg_catalog.pg_column_is_updatable('uv_pt'::regclass, 2::smallint, false);
3532 pg_column_is_updatable
3533 ------------------------
3537 select table_name, is_updatable, is_insertable_into
3538 from information_schema.views where table_name = 'uv_ptv';
3539 table_name | is_updatable | is_insertable_into
3540 ------------+--------------+--------------------
3544 select table_name, column_name, is_updatable
3545 from information_schema.columns where table_name = 'uv_ptv' order by column_name;
3546 table_name | column_name | is_updatable
3547 ------------+-------------+--------------
3553 insert into uv_ptv values (1, 2);
3554 select tableoid::regclass, * from uv_pt;
3555 tableoid | a | b | v
3556 ----------+---+---+---
3560 create view uv_ptv_wco as select * from uv_pt where a = 0 with check option;
3561 insert into uv_ptv_wco values (1, 2);
3562 ERROR: new row violates check option for view "uv_ptv_wco"
3563 DETAIL: Failing row contains (1, 2, null).
3565 using (values (1,2), (1,4)) as v(a,b) on t.a = v.a -- fail: matches 2 src rows
3566 when matched then update set b = t.b + 1
3567 when not matched then insert values (v.a, v.b + 1);
3568 ERROR: MERGE command cannot affect row a second time
3569 HINT: Ensure that not more than one source row matches any one target row.
3571 using (values (1,2), (1,4)) as v(a,b) on t.a = v.a and t.b = v.b
3572 when matched then update set b = t.b + 1
3573 when not matched then insert values (v.a, v.b + 1); -- fail: no partition for b=5
3574 ERROR: no partition of relation "uv_pt1" found for row
3575 DETAIL: Partition key of the failing row contains (b) = (5).
3577 using (values (1,2), (1,3)) as v(a,b) on t.a = v.a and t.b = v.b
3578 when matched then update set b = t.b + 1
3579 when not matched then insert values (v.a, v.b + 1); -- ok
3580 select tableoid::regclass, * from uv_pt order by a, b;
3581 tableoid | a | b | v
3582 ----------+---+---+---
3587 drop view uv_ptv, uv_ptv_wco;
3588 drop table uv_pt, uv_pt1, uv_pt11;
3589 -- check that wholerow vars appearing in WITH CHECK OPTION constraint expressions
3590 -- work fine with partitioned tables
3591 create table wcowrtest (a int) partition by list (a);
3592 create table wcowrtest1 partition of wcowrtest for values in (1);
3593 create view wcowrtest_v as select * from wcowrtest where wcowrtest = '(2)'::wcowrtest with check option;
3594 insert into wcowrtest_v values (1);
3595 ERROR: new row violates check option for view "wcowrtest_v"
3596 DETAIL: Failing row contains (1).
3597 alter table wcowrtest add b text;
3598 create table wcowrtest2 (b text, c int, a int);
3599 alter table wcowrtest2 drop c;
3600 alter table wcowrtest attach partition wcowrtest2 for values in (2);
3601 create table sometable (a int, b text);
3602 insert into sometable values (1, 'a'), (2, 'b');
3603 create view wcowrtest_v2 as
3606 where r in (select s from sometable s where r.a = s.a)
3608 -- WITH CHECK qual will be processed with wcowrtest2's
3609 -- rowtype after tuple-routing
3610 insert into wcowrtest_v2 values (2, 'no such row in sometable');
3611 ERROR: new row violates check option for view "wcowrtest_v2"
3612 DETAIL: Failing row contains (2, no such row in sometable).
3613 drop view wcowrtest_v, wcowrtest_v2;
3614 drop table wcowrtest, sometable;
3615 -- Check INSERT .. ON CONFLICT DO UPDATE works correctly when the view's
3616 -- columns are named and ordered differently than the underlying table's.
3617 create table uv_iocu_tab (a text unique, b float);
3618 insert into uv_iocu_tab values ('xyxyxy', 0);
3619 create view uv_iocu_view as
3620 select b, b+1 as c, a, '2.0'::text as two from uv_iocu_tab;
3621 insert into uv_iocu_view (a, b) values ('xyxyxy', 1)
3622 on conflict (a) do update set b = uv_iocu_view.b;
3623 select * from uv_iocu_tab;
3629 insert into uv_iocu_view (a, b) values ('xyxyxy', 1)
3630 on conflict (a) do update set b = excluded.b;
3631 select * from uv_iocu_tab;
3637 -- OK to access view columns that are not present in underlying base
3638 -- relation in the ON CONFLICT portion of the query
3639 insert into uv_iocu_view (a, b) values ('xyxyxy', 3)
3640 on conflict (a) do update set b = cast(excluded.two as float);
3641 select * from uv_iocu_tab;
3648 insert into uv_iocu_view (a, b) values ('xyxyxy', 3)
3649 on conflict (a) do update set b = excluded.b where excluded.c > 0;
3651 -----------------------------------------------------------------------------------
3652 Insert on uv_iocu_tab
3653 Conflict Resolution: UPDATE
3654 Conflict Arbiter Indexes: uv_iocu_tab_a_key
3655 Conflict Filter: ((excluded.b + '1'::double precision) > '0'::double precision)
3659 insert into uv_iocu_view (a, b) values ('xyxyxy', 3)
3660 on conflict (a) do update set b = excluded.b where excluded.c > 0;
3661 select * from uv_iocu_tab;
3667 drop view uv_iocu_view;
3668 drop table uv_iocu_tab;
3669 -- Test whole-row references to the view
3670 create table uv_iocu_tab (a int unique, b text);
3671 create view uv_iocu_view as
3672 select b as bb, a as aa, uv_iocu_tab::text as cc from uv_iocu_tab;
3673 insert into uv_iocu_view (aa,bb) values (1,'x');
3675 insert into uv_iocu_view (aa,bb) values (1,'y')
3676 on conflict (aa) do update set bb = 'Rejected: '||excluded.*
3677 where excluded.aa > 0
3678 and excluded.bb != ''
3679 and excluded.cc is not null;
3681 ---------------------------------------------------------------------------------------------------------
3682 Insert on uv_iocu_tab
3683 Conflict Resolution: UPDATE
3684 Conflict Arbiter Indexes: uv_iocu_tab_a_key
3685 Conflict Filter: ((excluded.a > 0) AND (excluded.b <> ''::text) AND ((excluded.*)::text IS NOT NULL))
3689 insert into uv_iocu_view (aa,bb) values (1,'y')
3690 on conflict (aa) do update set bb = 'Rejected: '||excluded.*
3691 where excluded.aa > 0
3692 and excluded.bb != ''
3693 and excluded.cc is not null;
3694 select * from uv_iocu_view;
3696 -------------------------+----+---------------------------------
3697 Rejected: (y,1,"(1,y)") | 1 | (1,"Rejected: (y,1,""(1,y)"")")
3700 -- Test omitting a column of the base relation
3701 delete from uv_iocu_view;
3702 insert into uv_iocu_view (aa,bb) values (1,'x');
3703 insert into uv_iocu_view (aa) values (1)
3704 on conflict (aa) do update set bb = 'Rejected: '||excluded.*;
3705 select * from uv_iocu_view;
3707 -----------------------+----+-------------------------------
3708 Rejected: (,1,"(1,)") | 1 | (1,"Rejected: (,1,""(1,)"")")
3711 alter table uv_iocu_tab alter column b set default 'table default';
3712 insert into uv_iocu_view (aa) values (1)
3713 on conflict (aa) do update set bb = 'Rejected: '||excluded.*;
3714 select * from uv_iocu_view;
3716 -------------------------------------------------------+----+---------------------------------------------------------------------
3717 Rejected: ("table default",1,"(1,""table default"")") | 1 | (1,"Rejected: (""table default"",1,""(1,""""table default"""")"")")
3720 alter view uv_iocu_view alter column bb set default 'view default';
3721 insert into uv_iocu_view (aa) values (1)
3722 on conflict (aa) do update set bb = 'Rejected: '||excluded.*;
3723 select * from uv_iocu_view;
3725 -----------------------------------------------------+----+-------------------------------------------------------------------
3726 Rejected: ("view default",1,"(1,""view default"")") | 1 | (1,"Rejected: (""view default"",1,""(1,""""view default"""")"")")
3729 -- Should fail to update non-updatable columns
3730 insert into uv_iocu_view (aa) values (1)
3731 on conflict (aa) do update set cc = 'XXX';
3732 ERROR: cannot insert into column "cc" of view "uv_iocu_view"
3733 DETAIL: View columns that are not columns of their base relation are not updatable.
3734 drop view uv_iocu_view;
3735 drop table uv_iocu_tab;
3736 -- ON CONFLICT DO UPDATE permissions checks
3737 create user regress_view_user1;
3738 create user regress_view_user2;
3739 set session authorization regress_view_user1;
3740 create table base_tbl(a int unique, b text, c float);
3741 insert into base_tbl values (1,'xxx',1.0);
3742 create view rw_view1 as select b as bb, c as cc, a as aa from base_tbl;
3743 grant select (aa,bb) on rw_view1 to regress_view_user2;
3744 grant insert on rw_view1 to regress_view_user2;
3745 grant update (bb) on rw_view1 to regress_view_user2;
3746 set session authorization regress_view_user2;
3747 insert into rw_view1 values ('yyy',2.0,1)
3748 on conflict (aa) do update set bb = excluded.cc; -- Not allowed
3749 ERROR: permission denied for view rw_view1
3750 insert into rw_view1 values ('yyy',2.0,1)
3751 on conflict (aa) do update set bb = rw_view1.cc; -- Not allowed
3752 ERROR: permission denied for view rw_view1
3753 insert into rw_view1 values ('yyy',2.0,1)
3754 on conflict (aa) do update set bb = excluded.bb; -- OK
3755 insert into rw_view1 values ('zzz',2.0,1)
3756 on conflict (aa) do update set bb = rw_view1.bb||'xxx'; -- OK
3757 insert into rw_view1 values ('zzz',2.0,1)
3758 on conflict (aa) do update set cc = 3.0; -- Not allowed
3759 ERROR: permission denied for view rw_view1
3760 reset session authorization;
3761 select * from base_tbl;
3767 set session authorization regress_view_user1;
3768 grant select (a,b) on base_tbl to regress_view_user2;
3769 grant insert (a,b) on base_tbl to regress_view_user2;
3770 grant update (a,b) on base_tbl to regress_view_user2;
3771 set session authorization regress_view_user2;
3772 create view rw_view2 as select b as bb, c as cc, a as aa from base_tbl;
3773 insert into rw_view2 (aa,bb) values (1,'xxx')
3774 on conflict (aa) do update set bb = excluded.bb; -- Not allowed
3775 ERROR: permission denied for table base_tbl
3776 create view rw_view3 as select b as bb, a as aa from base_tbl;
3777 insert into rw_view3 (aa,bb) values (1,'xxx')
3778 on conflict (aa) do update set bb = excluded.bb; -- OK
3779 reset session authorization;
3780 select * from base_tbl;
3786 set session authorization regress_view_user2;
3787 create view rw_view4 as select aa, bb, cc FROM rw_view1;
3788 insert into rw_view4 (aa,bb) values (1,'yyy')
3789 on conflict (aa) do update set bb = excluded.bb; -- Not allowed
3790 ERROR: permission denied for view rw_view1
3791 create view rw_view5 as select aa, bb FROM rw_view1;
3792 insert into rw_view5 (aa,bb) values (1,'yyy')
3793 on conflict (aa) do update set bb = excluded.bb; -- OK
3794 reset session authorization;
3795 select * from base_tbl;
3806 drop table base_tbl;
3807 drop user regress_view_user1;
3808 drop user regress_view_user2;
3809 -- Test single- and multi-row inserts with table and view defaults.
3810 -- Table defaults should be used, unless overridden by view defaults.
3811 create table base_tab_def (a int, b text default 'Table default',
3812 c text default 'Table default', d text, e text);
3813 create view base_tab_def_view as select * from base_tab_def;
3814 alter view base_tab_def_view alter b set default 'View default';
3815 alter view base_tab_def_view alter d set default 'View default';
3816 insert into base_tab_def values (1);
3817 insert into base_tab_def values (2), (3);
3818 insert into base_tab_def values (4, default, default, default, default);
3819 insert into base_tab_def values (5, default, default, default, default),
3820 (6, default, default, default, default);
3821 insert into base_tab_def_view values (11);
3822 insert into base_tab_def_view values (12), (13);
3823 insert into base_tab_def_view values (14, default, default, default, default);
3824 insert into base_tab_def_view values (15, default, default, default, default),
3825 (16, default, default, default, default);
3826 insert into base_tab_def_view values (17), (default);
3827 select * from base_tab_def order by a;
3829 ----+---------------+---------------+--------------+---
3830 1 | Table default | Table default | |
3831 2 | Table default | Table default | |
3832 3 | Table default | Table default | |
3833 4 | Table default | Table default | |
3834 5 | Table default | Table default | |
3835 6 | Table default | Table default | |
3836 11 | View default | Table default | View default |
3837 12 | View default | Table default | View default |
3838 13 | View default | Table default | View default |
3839 14 | View default | Table default | View default |
3840 15 | View default | Table default | View default |
3841 16 | View default | Table default | View default |
3842 17 | View default | Table default | View default |
3843 | View default | Table default | View default |
3846 -- Adding an INSTEAD OF trigger should cause NULLs to be inserted instead of
3847 -- table defaults, where there are no view defaults.
3848 create function base_tab_def_view_instrig_func() returns trigger
3852 insert into base_tab_def values (new.a, new.b, new.c, new.d, new.e);
3857 create trigger base_tab_def_view_instrig instead of insert on base_tab_def_view
3858 for each row execute function base_tab_def_view_instrig_func();
3859 truncate base_tab_def;
3860 insert into base_tab_def values (1);
3861 insert into base_tab_def values (2), (3);
3862 insert into base_tab_def values (4, default, default, default, default);
3863 insert into base_tab_def values (5, default, default, default, default),
3864 (6, default, default, default, default);
3865 insert into base_tab_def_view values (11);
3866 insert into base_tab_def_view values (12), (13);
3867 insert into base_tab_def_view values (14, default, default, default, default);
3868 insert into base_tab_def_view values (15, default, default, default, default),
3869 (16, default, default, default, default);
3870 insert into base_tab_def_view values (17), (default);
3871 select * from base_tab_def order by a;
3873 ----+---------------+---------------+--------------+---
3874 1 | Table default | Table default | |
3875 2 | Table default | Table default | |
3876 3 | Table default | Table default | |
3877 4 | Table default | Table default | |
3878 5 | Table default | Table default | |
3879 6 | Table default | Table default | |
3880 11 | View default | | View default |
3881 12 | View default | | View default |
3882 13 | View default | | View default |
3883 14 | View default | | View default |
3884 15 | View default | | View default |
3885 16 | View default | | View default |
3886 17 | View default | | View default |
3887 | View default | | View default |
3890 -- Using an unconditional DO INSTEAD rule should also cause NULLs to be
3891 -- inserted where there are no view defaults.
3892 drop trigger base_tab_def_view_instrig on base_tab_def_view;
3893 drop function base_tab_def_view_instrig_func;
3894 create rule base_tab_def_view_ins_rule as on insert to base_tab_def_view
3895 do instead insert into base_tab_def values (new.a, new.b, new.c, new.d, new.e);
3896 truncate base_tab_def;
3897 insert into base_tab_def values (1);
3898 insert into base_tab_def values (2), (3);
3899 insert into base_tab_def values (4, default, default, default, default);
3900 insert into base_tab_def values (5, default, default, default, default),
3901 (6, default, default, default, default);
3902 insert into base_tab_def_view values (11);
3903 insert into base_tab_def_view values (12), (13);
3904 insert into base_tab_def_view values (14, default, default, default, default);
3905 insert into base_tab_def_view values (15, default, default, default, default),
3906 (16, default, default, default, default);
3907 insert into base_tab_def_view values (17), (default);
3908 select * from base_tab_def order by a;
3910 ----+---------------+---------------+--------------+---
3911 1 | Table default | Table default | |
3912 2 | Table default | Table default | |
3913 3 | Table default | Table default | |
3914 4 | Table default | Table default | |
3915 5 | Table default | Table default | |
3916 6 | Table default | Table default | |
3917 11 | View default | | View default |
3918 12 | View default | | View default |
3919 13 | View default | | View default |
3920 14 | View default | | View default |
3921 15 | View default | | View default |
3922 16 | View default | | View default |
3923 17 | View default | | View default |
3924 | View default | | View default |
3927 -- A DO ALSO rule should cause each row to be inserted twice. The first
3928 -- insert should behave the same as an auto-updatable view (using table
3929 -- defaults, unless overridden by view defaults). The second insert should
3930 -- behave the same as a rule-updatable view (inserting NULLs where there are
3931 -- no view defaults).
3932 drop rule base_tab_def_view_ins_rule on base_tab_def_view;
3933 create rule base_tab_def_view_ins_rule as on insert to base_tab_def_view
3934 do also insert into base_tab_def values (new.a, new.b, new.c, new.d, new.e);
3935 truncate base_tab_def;
3936 insert into base_tab_def values (1);
3937 insert into base_tab_def values (2), (3);
3938 insert into base_tab_def values (4, default, default, default, default);
3939 insert into base_tab_def values (5, default, default, default, default),
3940 (6, default, default, default, default);
3941 insert into base_tab_def_view values (11);
3942 insert into base_tab_def_view values (12), (13);
3943 insert into base_tab_def_view values (14, default, default, default, default);
3944 insert into base_tab_def_view values (15, default, default, default, default),
3945 (16, default, default, default, default);
3946 insert into base_tab_def_view values (17), (default);
3947 select * from base_tab_def order by a, c NULLS LAST;
3949 ----+---------------+---------------+--------------+---
3950 1 | Table default | Table default | |
3951 2 | Table default | Table default | |
3952 3 | Table default | Table default | |
3953 4 | Table default | Table default | |
3954 5 | Table default | Table default | |
3955 6 | Table default | Table default | |
3956 11 | View default | Table default | View default |
3957 11 | View default | | View default |
3958 12 | View default | Table default | View default |
3959 12 | View default | | View default |
3960 13 | View default | Table default | View default |
3961 13 | View default | | View default |
3962 14 | View default | Table default | View default |
3963 14 | View default | | View default |
3964 15 | View default | Table default | View default |
3965 15 | View default | | View default |
3966 16 | View default | Table default | View default |
3967 16 | View default | | View default |
3968 17 | View default | Table default | View default |
3969 17 | View default | | View default |
3970 | View default | Table default | View default |
3971 | View default | | View default |
3974 -- Test a DO ALSO INSERT ... SELECT rule
3975 drop rule base_tab_def_view_ins_rule on base_tab_def_view;
3976 create rule base_tab_def_view_ins_rule as on insert to base_tab_def_view
3977 do also insert into base_tab_def (a, b, e) select new.a, new.b, 'xxx';
3978 truncate base_tab_def;
3979 insert into base_tab_def_view values (1, default, default, default, default);
3980 insert into base_tab_def_view values (2, default, default, default, default),
3981 (3, default, default, default, default);
3982 select * from base_tab_def order by a, e nulls first;
3984 ---+--------------+---------------+--------------+-----
3985 1 | View default | Table default | View default |
3986 1 | View default | Table default | | xxx
3987 2 | View default | Table default | View default |
3988 2 | View default | Table default | | xxx
3989 3 | View default | Table default | View default |
3990 3 | View default | Table default | | xxx
3993 drop view base_tab_def_view;
3994 drop table base_tab_def;
3995 -- Test defaults with array assignments
3996 create table base_tab (a serial, b int[], c text, d text default 'Table default');
3997 create view base_tab_view as select c, a, b from base_tab;
3998 alter view base_tab_view alter column c set default 'View default';
3999 insert into base_tab_view (b[1], b[2], c, b[5], b[4], a, b[3])
4000 values (1, 2, default, 5, 4, default, 3), (10, 11, 'C value', 14, 13, 100, 12);
4001 select * from base_tab order by a;
4003 -----+------------------+--------------+---------------
4004 1 | {1,2,3,4,5} | View default | Table default
4005 100 | {10,11,12,13,14} | C value | Table default
4008 drop view base_tab_view;
4009 drop table base_tab;