PostgreSQL 9.3 版本支持视图更新操作，也就是说可以在 views 上执行 UPDATE/INSERT/DELETE 操作，但这种视图必须是简单的而且还有其它限制条件，例如视图创建中只允许引用单张表，等等，接下来会介绍这些限制条件，先通过一个简单的实验验证下：

可更新视图测试

1.1 创建测试表

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

francs=> create table test_view1 (id int4 primary key ,name character varying(64),creat_time timestamp without time zone);
CREATE TABLE
  
francs=> insert into test_view1 select generate_series(1,100000),'a_' || generate_series(1,100000),clock_timestamp();
INSERT 0 100000
  
francs=> select * from test_view1 limit 3;
id | name |     creat_time    
----+------+----------------------------
 1 | a_1 | 2013-05-18 15:25:25.815398
 2 | a_2 | 2013-05-18 15:25:25.816195
 3 | a_3 | 2013-05-18 15:25:25.816219
(3 rows)

1.2 创建视图

1
2

francs=> create view view1_test as select id,name from test_view1;
CREATE VIEW

1.3 查看表，视图大小

1
2
3
4
5
6
7
8
9
10
11

francs=> select pg_relation_size('test_view1');
pg_relation_size
------------------
     4825088
(1 row)
  
francs=> select pg_relation_size('view1_test');
pg_relation_size
------------------
        0
(1 row)

备注：视图占用 0 字节，说明本身不存数据，这与物化视图不同。

1.4 更新视图

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

francs=> select * from view1_test where id=1;
id | name
----+------
 1 | a_1
(1 row)
  
francs=> update view1_test set name='a_111' where id=1;
UPDATE 1
  
francs=> select * from view1_test where id=1;
id | name 
----+-------
 1 | a_111
(1 row)

备注：视图 view1_test 果然可以更新，接下来看表中的数据是否被更新。

1.5 验证表数据

1
2
3
4
5

francs=> select * from test_view1 where id=1;
id | name |     creat_time    
----+-------+----------------------------
 1 | a_111 | 2013-05-18 15:25:25.815398
(1 row)

备注：更新视图后，表中对应的数据被更新了，原理：当视图被更新时，PostgreSQL 会将视图上的 INSERT/UPDATE/DELETE 语句传送到视图引用的基表。回到本文开始的问题，只有简单的视图才支持可更新操作，并且有很多限制，如下：

可更新视图的限制

• The view must have exactly one entry in its FROM list, which must be a table or another updatable view.
• The view definition must not contain WITH, DISTINCT, GROUP BY, HAVING, LIMIT, or OFFSET clauses at the top level.
• The view definition must not contain set operations (UNION, INTERSECT or EXCEPT) at the top level.
• All columns in the view’s select list must be simple references to columns of the underlying relation. They cannot be expressions, literals or functions. System columns cannot be referenced, either.
• No column of the underlying relation can appear more than once in the view’s select list.
• The view must not have the security_barrier property.

备注：以上来自手册，不翻译了。

做这个实验时想到一个问题，假如赋给一个用户对这张视图的 select, update 权限，而不赋予这个户对这张视图所引用的表的 select ,update 权限，那么这个用户是否能更新视图呢？接着实验。

可更新视图权限

2.1 创建测试用户并赋权

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

postgres=# create role user1 LOGIN encrypted password 'user1' NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT ;
CREATE ROLE
  
postgres=# /c francs francs
You are now connected to database "francs" as user "francs".

francs=> grant connect on database francs to user1 ;
GRANT
  
francs=> grant select,update on view1_test to user1;
GRANT
  
francs=> grant usage on schema francs to user1;
GRANT

2.2 测试 user1 权限

1
2
3
4
5
6
7
8
9
10
11

francs=> /c francs user1;
You are now connected to database "francs" as user "user1".
  
francs=> select * from francs.test_view1 limit 1;
ERROR: permission denied for relation test_view1
  
francs=> select * from francs.view1_test limit 1;
id | name
----+------
 2 | a_2
(1 row)

备注：user1 能查询视图，但不能查询基表。

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

francs=> update francs.test_view1 set name='update' where id=3;
ERROR: permission denied for relation test_view1
  
francs=> update francs.view1_test set name='update' where id=2;
UPDATE 1
  
francs=> select * from francs.view1_test where id=2;
id | name 
----+--------
 2 | update
(1 row)
  
francs=> select * from francs.view1_test where id=2;
id | name 
----+--------
 2 | update
(1 row)

备注：user1 能更新视图，但不能直接更新基表。

总结

上面简单的演示了可更新视图，非常重要的特性，在使用过程中可能会碰到更多问题，以后补充。

参考

• http://www.postgresql.org/docs/9.3/static/sql-createview.html
• http://www.depesz.com/2012/12/11/waiting-for-9-3-support-automatically-updatable-views/