MogDB 支持视图 DML 更新吗？

我来答

2021-07-06

MogDB 支持视图 DML 更新吗？

测试了一下 MogDB，发现视图底层是单表时，也不支持 DML。

测试用例如下：

mogdb=> create table ye (id number,name varchar2(20),dept varchar2(20));
CREATE TABLE
                                  ^
mogdb=> create or replace view en as select * from ye where id > 10;
CREATE VIEW

mogdb=> select * from ye;
 id | name | dept 
----+------+------
(0 rows)

mogdb=> insert into ye values(1,'e','c');
INSERT 0 1
mogdb=> insert into ye values(10,'j','k');
INSERT 0 1
mogdb=> select * from en;
 id | name | dept 
----+------+------
(0 rows)

mogdb=> insert into ye values(20,'t','v');
INSERT 0 1
mogdb=> select * from en;
 id | name | dept 
----+------+------
 20 | t    | v
(1 row)

mogdb=> create or replace view en as select * from ye where id > 4;
CREATE VIEW
mogdb=> select * from en;
 id | name | dept 
----+------+------
 10 | j    | k
 20 | t    | v
(2 rows)

mogdb=> insert into en values(50,'t');
ERROR:  cannot insert into view "en"
HINT:  You need an unconditional ON INSERT DO INSTEAD rule or an INSTEAD OF INSERT trigger.

我来答

添加附件

问题补充

1条回答

默认

Mogdb2 or opengauss 2.0

anbob=# create table test5(id int,name varchar(100),owner varchar(100));
CREATE TABLE
anbob=# insert into test5 values(1,'anbob','anbob');
INSERT 0 1
anbob=# insert into test5 values(2,'anbob2','anbob');
INSERT 0 1
anbob=# insert into test5 values(3,'anbob3','weejar');
INSERT 0 1
anbob=# select * from test5;
 id |  name  | owner
----+--------+--------
  1 | anbob  | anbob
  2 | anbob2 | anbob
  3 | anbob3 | weejar
(3 rows)

anbob=# create view v_test5 as select * from test5 where owner='anbob';
CREATE VIEW
anbob=# select * from v_test5;
 id |  name  | owner
----+--------+-------
  1 | anbob  | anbob
  2 | anbob2 | anbob
(2 rows)

anbob=# update v_test5 set name='a1' where id=1;
ERROR:  cannot update view "v_test5"
HINT:  You need an unconditional ON UPDATE DO INSTEAD rule or an INSTEAD OF UPDATE trigger.
anbob=# insert into v_test5 values(3,'anbob','anbob1');
ERROR:  cannot insert into view "v_test5"
HINT:  You need an unconditional ON INSERT DO INSTEAD rule or an INSTEAD OF INSERT trigger.
anbob=# insert into v_test5 values(3,'anbob','anbob');
ERROR:  cannot insert into view "v_test5"
HINT:  You need an unconditional ON INSERT DO INSTEAD rule or an INSTEAD OF INSERT trigger.

anbob=# select * from version();
                                                                                version

---------------------------------------------------------------------------------------------------------------------------------------------------
--------------------
 PostgreSQL 9.2.4 (openGauss 2.0.0 build 78689da9) compiled at 2021-03-31 21:04:03 commit 0 last mr   on x86_64-unknown-linux-gnu, compiled by g++
(GCC) 7.3.0, 64-bit
(1 row)

PostgreSQL 9.2 view 默认是不支持更新，好像是从9.3版本开始支持的, Opengauss是基于9.2 目前该功能未引入。至少目前postgresql 13是可以的。

postgresql 13

weejar=# create table test5(id int,name varchar(100),owner varchar(100));
CREATE TABLE
weejar=#
weejar=#  insert into test5 values(1,'anbob','anbob');
INSERT 0 1
weejar=#
weejar=#  insert into test5 values(2,'anbob2','anbob');
INSERT 0 1
weejar=#
weejar=#  insert into test5 values(3,'anbob3','weejar');
INSERT 0 1
weejar=#
weejar=# create view v_test5 as select * from test5 where owner='anbob';
CREATE VIEW
weejar=# select * from v_test5;
 id |  name  | owner
----+--------+-------
  1 | anbob  | anbob
  2 | anbob2 | anbob
(2 行记录)


weejar=# update v_test5 set name='a1' where id=1;
UPDATE 1
weejar=#  select * from v_test5;
 id |  name  | owner
----+--------+-------
  2 | anbob2 | anbob
  1 | a1     | anbob
(2 行记录)


weejar=#  insert into v_test5 values(3,'anbob','anbob1');
INSERT 0 1
weejar=#  select * from v_test5;
 id |  name  | owner
----+--------+-------
  2 | anbob2 | anbob
  1 | a1     | anbob
(2 行记录)


weejar=#  select * from test5;
 id |  name  | owner
----+--------+--------
  2 | anbob2 | anbob
  3 | anbob3 | weejar
  1 | a1     | anbob
  3 | anbob  | anbob1
(4 行记录)


weejar=# insert into v_test5 values(3,'anbob','anbob');
INSERT 0 1
weejar=#  select * from test5;
 id |  name  | owner
----+--------+--------
  2 | anbob2 | anbob
  3 | anbob3 | weejar
  1 | a1     | anbob
  3 | anbob  | anbob1
  3 | anbob  | anbob
(5 行记录)


weejar=#  select * from v_test5;
 id |  name  | owner
----+--------+-------
  2 | anbob2 | anbob
  1 | a1     | anbob
  3 | anbob  | anbob
(3 行记录)


weejar=# select version();
                          version
------------------------------------------------------------
 PostgreSQL 13.1, compiled by Visual C++ build 1914, 64-bit
(1 行记录)

– enjoy –

有用 0

回答交流

提交

问题信息

请登录之后查看

邀请回答

暂无人订阅该标签，敬请期待~~

墨值悬赏