postgresql-视图

视图概述

视图（View）本质上是一个存储在数据库中的查询语句。视图本身不包含数据，也被称为
虚拟表。我们在创建视图时给它指定了一个名称，然后可以像表一样对其进行查询

使用视图的好处

创建视图

PostgreSQL 使用 CREATE VIEW 语句创建视图：

CREATE VIEW view_name AS query;
1

其中，view_name 是视图的名称；AS 之后是视图的查询语句，可以是简单查询或者复杂的
查询。以下语句创建了一个包含员工详细信息的视图：

create view emp_details_view
as select
 e.employee_id,
 e.job_id,
 e.manager_id,
 e.department_id,
 d.location_id,
 e.first_name,
 e.last_name,
 e.salary,
 e.commission_pct,
 d.department_name,
 j.job_title
from employees e
join departments d on (e.department_id = d.department_id)
join jobs j on (j.job_id = e.job_id);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

-- 使用视图查询数据
select * from emp_details_view
where department_name = 'IT';
1
2
3

修改视图

如果需要修改视图定义中的查询，可以使用 CREATE OR REPLACE 语句：

CREATE OR REPLACE VIEW 视图名称
AS
查询语句;
1
2
3

--PostgreSQL 目前只支持追加视图定义中的字段，不支持减少字段或者修改字段的名称或顺
--序。例如，我们可以为视图 emp_details_view 增加一个字段 hire_date：
create or replace view emp_details_view
as select
 e.employee_id,
 e.job_id,
 e.manager_id,
 e.department_id,
 d.location_id,
 e.first_name,
 e.last_name,
 e.salary,
 e.commission_pct,
 d.department_name,
 j.job_title,
 e.hire_date
from employees e
join departments d on (e.department_id = d.department_id)
join jobs j on (j.job_id = e.job_id);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

--另外，PostgreSQL 还提供了 ALTER VIEW 语句修改视图的属性。例如以下语句用于修改视图的名称
--该语句将视图 emp_details_view 重命名为 emp_info_view。
ALTER VIEW emp_details_view RENAME TO emp_info_view;

1
2
3
4

ALTER VIEW 语句还提供了其他的修改功能，例如设置字段的默认值、修改视图所属的模
式等，具体可以参考官方文档

删除视图

使用 DROP VIEW 语句删除一个已有的视图：

DROP VIEW [ IF EXISTS ] name [ CASCADE | RESTRICT ];
1

其中，IF EXISTS 可以避免删除一个不存在的视图时产生错误；CASCADE 表示级联删除依
赖于该视图的对象；RESTRICT 表示如果存在依赖对象则提示错误信息，这是默认值

递归视图

--视图的定义中也可以使用 recursive来创建递归视图
-- column_names：字段名称
create recursive view 视图名 (column_names) as 查询语句;
-- 递归视图需要指定字段的名称 column_names。以上语句实际上等价于以下sql
CREATE VIEW view_name AS
WITH RECURSIVE cte_name (column_names) AS (query)
SELECT column_names FROM cte_name;
1
2
3
4
5
6
7

-- 递归视图的创建
CREATE RECURSIVE VIEW employee_path(employee_id, employee_name, path) AS
 SELECT employee_id, CONCAT(first_name, ',', last_name), CONCAT(first_name,
',', last_name) AS path
 FROM employees
 WHERE manager_id IS NULL
 UNION ALL
 SELECT e.employee_id, CONCAT(e.first_name, ',', e.last_name),
CONCAT(ep.path, '->', e.first_name, ',', e.last_name)
 FROM employee_path ep
 JOIN employees e ON ep.employee_id = e.manager_id;
1
2
3
4
5
6
7
8
9
10
11

-- 查询视图
select * from employee_path ep ;
1
2

可更新视图

如果一个视图满足以下条件：
• 视图定义的 FROM 子句中只包含一个表或者可更新视图；
• 视图定义的最顶层查询语句中不包含以下子句：GROUP BY、HAVING、LIMIT、OFFSET、
DISTINCT、WITH、UNION、INTERSECT 以及 EXCEPT；
• SELECT 列表中不包含窗口函数、集合函数或者聚合函数（例如 SUM、COUNT、AVG
等）。
那么该视图被称为可更新视图（updatable view），意味着我们可以对其执行 INSERT、
UPDATE 以及 DELETE 语句，PostgreSQL 会将这些操作转换为对底层表的操作。

-- 创建视图
create view employees_it as
select employee_id,
 first_name,
 last_name,
 email,
 phone_number,
 hire_date,
 job_id,
 manager_id,
 department_id
from employees
where department_id = 60;
1
2
3
4
5
6
7
8
9
10
11
12
13

-- 查询视图信息
select employee_id,first_name, last_name from employees_it;
1
2

-- 通过视图 employees_it 为 employees 表增加一个员工：
insert into employees_it
(employee_id, first_name, last_name, email, phone_number, hire_date, job_id,
manager_id, department_id)
VALUES(209, 'Tony', 'Dong', 'DONG', '590.423.5568', '2020-05-06', 'IT_PROG',
103, 60);

1
2
3
4
5
6
7

select * from employees_it;
1

WITH CHECK OPTION

为了防止通过视图插入或者修改视图不可见的数据，可以使用WITH CHECK OPTION选项：

create or replace view employees_it as
select employee_id,
 first_name,
 last_name,
 email,
 phone_number,
 hire_date,
 job_id,
 manager_id,
 department_id
from employees
where department_id = 60
with check option;
1
2
3
4
5
6
7
8
9
10
11
12
13

执行结果显示违反检查选项，无法插入数据。
WITH CASCADED CHECK OPTION 选项会对视图以及它所依赖的其他视图进行级联检查；
WITH LOCAL CHECK OPTION 选项只对当前视图进行检查。默认为 CASCADED。