Postgresql学习笔记之——逻辑结构管理之视图

一、视图的定义

视图就是有查询语句定义的虚拟表。对于用户来说，视图就是一张表。从视图中看到的数据可以来自数据库中的一个或多个表，也可以来自外部表。

视图使用的原因：
1.可以使复杂的查询已于理解和使用。
2.安全。视图可以在定义时隐藏一些数据。但实际表数据没有影响。
3.把函数返回的结果映射成视图。

在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 ]
1
2
3
4

语法解析：

1.TEMPORARY or TEMP
如果执行此参数，则创建的是临时视图，在当前会话结束后临时视图会自动删除，当前会话中如果有与临时视图相同名称的表，那么它在当前会话中是不可见的，除非使用了schema名作为前缀指定。

如果视图创建所依赖的表是临时表，那么不管TEMPORARY是否置顶，视图都是临时视图。

2.RECURSIVE
创建一个 recursive （递归）视图语法如下：

CREATE RECURSIVE VIEW [ schema . ] view_name (column_names) AS SELECT ...;
1

它等效于：

CREATE VIEW [ schema . ] view_name AS WITH RECURSIVE view_name (column_names) AS (SELECT ...) SELECT column_names FROM view_name;
1

必须为recursive（递归）视图指定列名。

3.name
要创建的视图的名称(可选模式限定)。

4.column_name
用于视图列的可选名称列。如果没有给出，则从查询中推断列名。

5.WITH ( view_option_name [= view_option_value] [, … ] )
此子句指定视图的可选参数;支持以下参数:

（1）check_option (string)
这个参数可以是局部的，也可以是级联的，它等价于WITH [ CASCADED | LOCAL ] CHECK OPTION (见下面)。可以使用ALTER VIEW在现有视图上更改此选项。
（2）security_barrier (boolean)
如果视图打算提供行级安全性，则应该使用此方法。

6.query
一个SELECT或VALUES命令，它将提供视图的列和行。

7.WITH [ CASCADED | LOCAL ] CHECK OPTION
此选项控制可自动更新视图的行为。当指定此选项时，将检查视图上的插入和更新命令，以确保新行满足视图定义条件(也就是说，检查新行以确保它们在视图中可见)。如果不是，更新将被拒绝。如果没有指定CHECK选项，则允许视图上的INSERT和UPDATE命令创建在视图中不可见的行。支持以下检查选项:

（1）LOCAL
新行仅根据直接在视图本身中定义的条件进行检查。不会检查在基础视图上定义的任何条件(除非它们还指定了CHECK选项)。

（2）CASCADED
根据视图和所有基础视图的条件检查新行。如果指定了CHECK OPTION ，并且没有指定LOCAL或者CASCADED，那么则为CASCADED

CHECK OPTION 不能与RECURSIVE （递归）视图一起使用。

Note that the CHECK OPTION is only supported on views that are automatically updatable, and do not have INSTEAD OF triggers or INSTEAD rules. If an automatically updatable view is defined on top of a base view that has INSTEAD OF triggers, then the LOCAL CHECK OPTION may be used to check the conditions on the automatically updatable view, but the conditions on the base view with INSTEAD OF triggers will not be checked (a cascaded check option will not cascade down to a trigger-updatable view, and any check options defined directly on a trigger-updatable view will be ignored). If the view or any of its base relations has an INSTEAD rule that causes the INSERT or UPDATE command to be rewritten, then all check options will be ignored in the rewritten query, including any checks from automatically updatable views defined on top of the relation with the INSTEAD rule.

示例

创建一张用户表 ”users“，其中包含了敏感字段 ”password“，创建一张视图将敏感字段排除掉：

postgres=# create table users(id int primary key,user_name varchar(40),password varchar(256),user_email text,user_mark text);
CREATE TABLE
postgres=# create view vw_user as select id,user_name,user_email,user_mark from users;
CREATE VIEW

1
2
3
4
5

也可以用TEMPORARY或者 TEMP 来创建一张临时视图，当session结束时，视图也会消失：

postgres=# create temp view vw_user_temp as select id,user_name,user_email,user_mark from users;
CREATE VIEW
postgres=# \d
                   List of relations
   Schema   |         Name          | Type  |  Owner   
------------+-----------------------+-------+----------
 pg_temp_13 | vw_user_temp          | view  | postgres
 public     | users                 | table | postgres
 public     | vw_user               | view  | postgres
(3 rows)

1
2
3
4
5
6
7
8
9
10
11

现在可以在数据库中看到创建的临时视图 vw_user_temp，现在退出session，然后从新进入数据库，可以看到创建的临时视图已经消失：

postgres=#  \q
[postgres@local ~]$ psql
psql (12.1)
Type "help" for help.

postgres=# \d
                   List of relations
   Schema   |         Name          | Type  |  Owner   
------------+-----------------------+-------+----------
 public     | users                 | table | postgres
 public     | vw_user               | view  | postgres
(2 rows)

1
2
3
4
5
6
7
8
9
10
11
12
13

视图定义时也可以将列名进行重定义，例如将原表中的列重定义为：

postgres=# create view vw_user_01 (no,name,email,mark) as select id,user_name,user_email,user_mark from users;
CREATE VIEW
postgres=# \d+ vw_user_01
                                 View "public.vw_user_01"
 Column |         Type          | Collation | Nullable | Default | Storage  | Description 
--------+-----------------------+-----------+----------+---------+----------+-------------
 no     | integer               |           |          |         | plain    | 
 name   | character varying(40) |           |          |         | extended | 
 email  | text                  |           |          |         | extended | 
 mark   | text                  |           |          |         | extended | 
View definition:
 SELECT users.id AS no,
    users.user_name AS name,
    users.user_email AS email,
    users.user_mark AS mark
   FROM users;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

三、可更新的视图

还是使用上面创建的users表：

postgres=# create table users(id int primary key,user_name varchar(40),password varchar(256),user_email text,user_mark text);
CREATE TABLE
postgres=# create view vw_user as select id,user_name,user_email,user_mark from users;
CREATE VIEW

1
2
3
4
5

然后插入几条数据：

postgres=# insert into users values(1,'zhang','zhang123','zhang@163.com','hello'),(2,'zhang2','zhang123','zhang2@163.com','hello2'),(3,'zhang3','zhang123','zhang3@163.com','hello3'),(4,'zhang4','zhang123','zhang4@163.com','hello4');
INSERT 0 4
postgres=# select * from users;
 id | user_name | password |   user_email   | user_mark 
----+-----------+----------+----------------+-----------
  1 | zhang     | zhang123 | zhang@163.com  | hello
  2 | zhang2    | zhang123 | zhang2@163.com | hello2
  3 | zhang3    | zhang123 | zhang3@163.com | hello3
  4 | zhang4    | zhang123 | zhang4@163.com | hello4
(4 rows)

postgres=# select * from vw_user;
 id | user_name |   user_email   | user_mark 
----+-----------+----------------+-----------
  1 | zhang     | zhang@163.com  | hello
  2 | zhang2    | zhang2@163.com | hello2
  3 | zhang3    | zhang3@163.com | hello3
  4 | zhang4    | zhang4@163.com | hello4
(4 rows)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

通过视图进行数据更新：

postgres=# update vw_user set user_name='Lee' where id=1;
UPDATE 1
postgres=# select * from vw_user;
 id | user_name |   user_email   | user_mark 
----+-----------+----------------+-----------
  2 | zhang2    | zhang2@163.com | hello2
  3 | zhang3    | zhang3@163.com | hello3
  4 | zhang4    | zhang4@163.com | hello4
  1 | Lee       | zhang@163.com  | hello
(4 rows)
postgres=# delete from vw_user where id=4;
DELETE 1
postgres=# select * from vw_user;
 id | user_name |   user_email   | user_mark 
----+-----------+----------------+-----------
  2 | zhang2    | zhang2@163.com | hello2
  3 | zhang3    | zhang3@163.com | hello3
  1 | Lee       | zhang@163.com  | hello
(3 rows)

postgres=# select * from users ;
 id | user_name | password |   user_email   | user_mark 
----+-----------+----------+----------------+-----------
  2 | zhang2    | zhang123 | zhang2@163.com | hello2
  3 | zhang3    | zhang123 | zhang3@163.com | hello3
  1 | Lee       | zhang123 | zhang@163.com  | hello
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

在Postgresql 12版本中可以直接通过视图进行数据更新或者删除的，直接更新或者删除的是表users。

官方文档解释：
简单视图是可自动更新的:系统将允许以与常规表相同的方式在视图上使用INSERT、UPDATE和DELETE语句。如果一个视图满足以下所有条件，它将自动更新:

1.视图的FROM列表中必须有精确的列，它必须是一个表或另一个可更新视图。

2.视图定义不能在顶层包含WITH、DISTINCT、GROUP BY、HAVING、LIMIT或OFFSET子句。

3.视图定义不能在顶层包含集合操作(UNION、INTERSECT或EXCEPT)。

4.视图的选择列表不能包含任何聚合、窗口函数或集返回函数。

满足以上条件的简单视图是可以直接进行更新、插入和删除的。

PS:在PG 9.X中直接通过视图是无法进行更新或者删除的，需要通过创建对应的规则 RULE 来进行间接更新或删除。