我尝试用check option
创建可更新的视图。
当过滤条件中包含连接时,我使用where in
子句来简化查询。
但是,如果我同时使用check option
和where in
声明视图,数据验证似乎不可靠。https://www.postgresql.org/docs/current/sql-createview.html
是一个错误吗?有解决方法吗?
create table tableA (
db_record_id serial NOT null PRIMARY KEY,
"name" varchar(60) NOT null UNIQUE
);
create or replace view viewSimpleA
as select * from tableA where name like 'a%'
with check option;
create or replace view viewA
as select * from tableA
where db_record_id in (
select db_record_id from tableA
where name like 'a%'
) with check option;
insert into viewSimpleA(name) values('abc'); -- OK
delete from tableA;
insert into viewA(name) values('abc'); -- KO, SQL Error [44000]: ERROR: new row violates check option for view "viewa"
delete from tableA;
insert into viewSimpleA(name) values('abc');
update viewSimpleA set name = 'fine'; -- OK: update is prevented by check option
delete from tableA;
insert table viewA(name) values('abc');
update viewA set name = 'fine'; -- KO: update is executed, view is now empty
delete from tableA;
select version(); -- PostgreSQL 13.5 (Debian 13.5-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
编辑:我在此处创建了一个bug report
2条答案
按热度按时间cnjp1d6j1#
PostGreSQL不支持在由多个表组成的视图中执行INSERT/UPDATE/DELETE操作。这包括JOIN和子查询。
rsl1atfo2#
PostgreSQL视图在满足以下条件时是可更新的: