Read Only View

-- 创建基础表
CREATE TABLE t_ro (a int, b text);
INSERT INTO t_ro VALUES (1, 'hello'), (2, 'world');

-- 创建只读视图，SELECT 成功
CREATE VIEW ro_view AS SELECT * FROM t_ro WITH READ ONLY;
SELECT * FROM ro_view ORDER BY a;
-- 期望输出：
--  a |   b
-- ---+-------
--  1 | hello
--  2 | world

-- INSERT 被阻止
INSERT INTO ro_view VALUES (3, 'fail');
-- 期望输出：ERROR: cannot modify view "ro_view"
-- HINT: The view is defined as read-only.

-- UPDATE 被阻止
UPDATE ro_view SET b = 'fail' WHERE a = 1;
-- 期望输出：ERROR: cannot modify view "ro_view"
-- HINT: The view is defined as read-only.

-- DELETE 被阻止
DELETE FROM ro_view WHERE a = 1;
-- 期望输出：ERROR: cannot modify view "ro_view"
-- HINT: The view is defined as read-only.

-- MERGE with INSERT action
MERGE INTO ro_view USING (SELECT 4 AS a, 'merge_ins' AS b) AS src
ON (ro_view.a = src.a)
WHEN NOT MATCHED THEN INSERT VALUES (src.a, src.b);
-- 期望输出：ERROR: cannot modify view "ro_view"
-- HINT: The view is defined as read-only.

-- MERGE with UPDATE action
MERGE INTO ro_view USING (SELECT 1 AS a, 'merge_upd' AS b) AS src
ON (ro_view.a = src.a)
WHEN MATCHED THEN UPDATE SET b = src.b;
-- 期望输出：ERROR: cannot modify view "ro_view"
-- HINT: The view is defined as read-only.

-- 重新创建视图时保留 WITH READ ONLY：DML 仍然被阻止
CREATE OR REPLACE VIEW ro_view AS SELECT a, b FROM t_ro WITH READ ONLY;
INSERT INTO ro_view VALUES (3, 'fail');
-- 期望输出：ERROR: cannot modify view "ro_view"
-- HINT: The view is defined as read-only.

-- 重新创建视图时不指定 WITH READ ONLY：只读属性被清除，视图变为可更新
CREATE OR REPLACE VIEW ro_view AS SELECT a, b FROM t_ro;
INSERT INTO ro_view VALUES (3, 'now_writable');
SELECT * FROM ro_view ORDER BY a;
-- 期望输出：
--  a |      b
-- ---+--------------
--  1 | hello
--  2 | world
--  3 | now_writable

-- 创建时基础表不存在，视图为占位符
CREATE FORCE VIEW force_ro_view AS SELECT * FROM nonexistent_for_ro WITH READ ONLY;
-- 期望输出：WARNING: View created with compilation errors

-- 创建基础表
CREATE TABLE nonexistent_for_ro (a int, b text);

-- 显式编译视图
ALTER VIEW force_ro_view COMPILE;

-- 编译成功后 DML 被阻止
INSERT INTO force_ro_view VALUES (1, 'fail');
-- 期望输出：ERROR: cannot modify view "force_ro_view"
-- HINT: The view is defined as read-only.

CREATE RECURSIVE VIEW ro_recursive_view (a) AS
    SELECT 1
    UNION ALL
    SELECT a + 1 FROM ro_recursive_view WHERE a < 3
WITH READ ONLY;

SELECT * FROM ro_recursive_view ORDER BY a;
-- 期望输出：
--  a
-- ---
--  1
--  2
--  3

INSERT INTO ro_recursive_view VALUES (99);
-- 期望输出：ERROR: cannot modify view "ro_recursive_view"
-- HINT: The view is defined as read-only.

SELECT relname, reloptions
FROM pg_class
WHERE relname IN ('ro_view', 'ro_recursive_view', 'force_ro_view')
ORDER BY relname;
-- 期望输出：
--       relname      |    reloptions
-- -------------------+------------------
--  force_ro_view     | {read_only=true}
--  ro_recursive_view | {read_only=true}
--  ro_view           |

DROP VIEW IF EXISTS ro_view;
DROP VIEW IF EXISTS force_ro_view;
DROP TABLE t_ro;
DROP TABLE IF EXISTS nonexistent_for_ro;