PostgreSQL视图原理分析（1）如何使用

PostgreSQL 创建视图的语法为：

CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] [ RECURSIVE ] VIEW `name` [ ( column_name [, ...] ) ]
    [ WITH ( view_option_name [= view_option_value] [, ... ] ) ]
    AS query
    [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]

可以使用 CREATE OR REPLACE 来替换已经存在的视图，但目前 PG 对 REPLACE VIEW 的限制有点多，新查询产生的列必须与旧查询的列，在顺序上一致、列的名字上一致且列的类型一致，给 VIEW 增加新的列是允许的。不像ORACLE，可以使用任意的查询替换旧的VIEW。PG这个限制从 08 年一直到现在，做成ORACLE那样 VIEW 可以更改列的类型等操作从技术上来说是可行的，PG 官方一直没有做这个功能，参考：
https://dba.stackexchange.com/questions/62813/why-is-it-not-possible-to-alter-a-view-with-a-create-or-replace
https://dba.stackexchange.com/questions/77564/postgresql-drop-column-from-view
如果想更改VIEW中列的类型，只能DROP VIEW进行重建，可能会有许多依赖进行处理，参考文章：
https://www.mssqltips.com/sqlservertip/8060/steps-to-change-a-postegresql-column-datatype-when-included-in-a-view/

REPLACE VIEW 只会改变 VIEW 的 SELECT 规则，VIEW 的其它属性、如权限、所有者、非 SELECT 规则都不会被改变。

临时视图

临时视图使用 TEMP | TEMPORARY 关键字创建，临时视图的生命周期和隔离级别都是 session 级别的，普通的 VIEW 默认创建在当前 schema 下，可以手动指定其 schema，临时 VIEW 创建在 pg_temp_ 开头的临时 schema 下，你不能手动指定其 schema。

递归视图

递归视图使用 RECURSIVE 创建，功能类似于带 RECURSIVE 的 CTE ，视图可以引用它自身进行递归查询。参考教程：
https://neon.tech/postgresql/postgresql-views/postgresql-recursive-view

可更新视图

对于简单的视图，会自动成为可更新视图，可更新视图允许你像修改表一样，对视图做 INSERT、UPDATE、DELETE DML 操作，对可更新视图做 DML 操作时，PG会自动将操作转为对视图底层引用的基表的操作。
视图要想成为可更新视图，必须足够简单，需要满足下面的条件：

• FROM 子句只能有一项，可以是表，也可以是一个可更新视图。
• 顶层查询不能包含 WITH，DISTINCT，GROUP BY，HAVING，LIMIT，OFFSET，UNION，INTERSECT，EXCEPT子句。
• 视图的查询列表中不能包含窗口函数、聚合函数、set-returning 函数。
  可更新视图不是所有列都是可更新的，可能会包含一些可更新的列，也可能包含一些不可更新的列。只有直接引用自基表的列是可更新的，如果有不可更新的列，可以对这个 VIEW 进行 DELETE，但不能进行 UPDATE 或 INSERT。

如果你对一个带有 WHERE 子句的可更新视图做 INSERT、UPDATE 操作，默认情况下，即使插入或更新的数据不满足 WHERE 条件，也是可以的。你可以通过给视图添加 CHECK OPTION 属性改变这一默认行为。

• WITH LOCAL CHECK OPTION：只检查是否满足当前 VIEW 定义的 WHERE 条件。
• WITH CASCADED CHECK OPTION：级联检查，还会连带检查当前 VIEW 所依赖的 VIEW 的 WHERE 条件（如果有的话）。
  更具体的介绍可以参考 PG 文档：
  https://www.postgresql.org/docs/10/sql-createview.html
  或者教程：
  https://neon.tech/postgresql/postgresql-views/postgresql-updatable-views

视图相关元数据

视图涉及的主要 Catalog 有 pg_class、pg_attribute、pg_rewrite、pg_depend等表。
由于视图具有类似表的结构，因此创建视图会像创建表一样，在 pg_class 中进行记录，并在 pg_attribute 中记录视图定义的查询列表（target list）。
举例：

CREATE TABLE employees ( employee_id SERIAL PRIMARY KEY, full_name VARCHAR NOT NULL, manager_id INT );
INSERT INTO employees (employee_id, full_name, manager_id) VALUES (1, 'Michael North', NULL), (2, 'Megan Berry', 1), (3, 'Sarah Berry', 1), (4, 'Zoe Black', 1), (5, 'Tim James', 1), (6, 'Bella Tucker', 2), (7, 'Ryan Metcalfe', 2), (8, 'Max Mills', 2), (9, 'Benjamin Glover', 2), (10, 'Carolyn Henderson', 3), (11, 'Nicola Kelly', 3), (12, 'Alexandra Climo', 3), (13, 'Dominic King', 3), (14, 'Leonard Gray', 4), (15, 'Eric Rampling', 4), (16, 'Piers Paige', 7), (17, 'Ryan Henderson', 7), (18, 'Frank Tucker', 8), (19, 'Nathan Ferguson', 8), (20, 'Kevin Rampling', 8);

创建视图：

CREATE VIEW employees_full AS SELECT * FROM employees;

这时查看 pg_class 表和 pg_attribute 表：

select c.relname, c.relkind, a.attname, a.atttypid from pg_class c inner join pg_attribute a on c.oid = a.attrelid where c.oid = 'employees_full'::regclass;

可以看到 pg_class 中存储了一条 relkind 为 ‘v’ 的记录，并且视图的每一列都有存储在 pg_attribute 中。
PG的视图使用规则系统实现，因此会存储一条 名为 _RETURN 的重写规则在 pg_rewrite 中：

select rulename, ev_class from pg_rewrite where ev_class = 'employees_full'::regclass;

创建一个视图会创建许多依赖关系，如会存在 pg_rewrite::_RETURN -> pg_class::employees_full -> pg_namespace 依赖关系，这些依赖关系会记录在 pg_depend 表中。

视图相关命令和函数

# 查看当前系统中所定义的视图
\dv

# 查看当前系统中所定义的视图，显示更多的列
\dv+

# 查看某个视图的信息
\dv viewname
\dv+ viewname

# 查看某个视图的信息，连带视图的定义
\d viewname
\d+ viewname

# 查看某个视图的定义
select pg_get_viewdef('viewname');