GBase 8c 视图依赖的DDL操作合集

如果日常使用中表被视图引用的话，特定场景下，部分DDL操作是不能直接执行的，比如修改被视图引用的字段的类型，删除表等，而新增字段是可以操作，主要原因是视图引用了表的字段，修改的话视图也需要变化。下面演示一下这部分内容，被视图引用的表进行DDL操作会有什么表现。然后再看看怎么操作才能修改表字段等。

CREATE TABLE t1 (id int,name varchar(20));
CREATE TABLE t2 (id int,name varchar(20));

CREATE OR REPLACE VIEW v1 as select * from t1;
CREATE OR REPLACE VIEW v2 as select a.* from t1 a inner join t2 b on a.id = b.id;

CREATE OR REPLACE VIEW v3 as select a.* from v1 a inner join v2 b on a.id = b.id inner join t1 c on a.id = c.id;

2.1 删除表

drop table t1;
drop table t2;

       从执行结果提示来看，DROP TABLE是没有执行成功的，因为有视图依赖。可以通过DROP …CASCADE来一起将依赖的视图删除，但是一般情况下我们不想将视图删除。

2.2 修改字段

ALTER TABLE T1 MODIFY NAME VARCHAR(30);
ALTER TABLE T2 MODIFY NAME VARCHAR(30);

       从执行结果的提示来看，t1、t2表都修改成功了，t2很好理解，因为没有视图使用到t2的字段，虽然视图使用了t2表，但只是用来关联，视图的字段并没有使用t2表的字段，所以t2表的字段类型能修改成功。t1表能修改成功是因为级联也修改了v2视图的定义，解决了被视图引用的表定义不能修改的情况。

插入数据测试视图查询结果如下

insert into t1 values(1,'12345678901234567890');

insert into t2 values(2,'09876543210987654321');

insert into t1 values(11,'123456789012345678901234567890');

insert into t2 values(22,'098765432109876543210987654321');

insert into t2 values(1,'098765432109876543210987654321');

select * from t1;

select * from t2;

select * from v1;

select * from v2;

2.3 新增字段

ALTER TABLE  T1 ADD COMMENT VARCHAR(30);
ALTER TABLE  T2 ADD COMMENT VARCHAR(30);

select * from v2;
CREATE OR REPLACE VIEW v2 as select a.* from t1 a inner join t2 b on a.id = b.id;
select * from v2;

2.4 查找t2表关联的视图

       通过with recursive xx as循环语句获取所有相关视图。

with recursive rec_view as(
    select c.nspname as schemaname,b.relname,rel_oid,b.relkind,d.oid as ori_oid,d.relname ori_name
,0as level  --level防止死循环
from(
    select unnest(regexp_matches(ev_action::text,':relid (d+)','g'))::oid  rel_oid,ev_class --rel_oid 被依赖对象 ，ev_class 视图名称
    from pg_rewrite 
    union 
    select unnest(regexp_matches(ev_action::text,':resorigtbl (d+)','g'))::oid,ev_class
    from pg_rewrite 
) deptbl                   --pg_write获取依赖关系
    inner join pg_class b       --被依赖对象获取表名等信息
    on deptbl.rel_oid = b.oid
    inner join pg_namespace c
    on b.relnamespace = c.oid
    inner join pg_class d     --视图获取视图名等信息，且用于排除pg_write获取的自身对象，即rel_oid <> ev_class
    on deptbl.ev_class = d.oid
    and deptbl.rel_oid <> d.oid
    where  b.relname ='t2'--指定表名t2
union all
    select c.nspname,b.relname,deptbl.rel_oid,b.relkind,d.oid as ori_oid,d.relname ori_name,level+1
from(
    select unnest(regexp_matches(ev_action::text,':relid (d+)','g'))::oid  rel_oid,ev_class
    from pg_rewrite 
  union 
    select unnest(regexp_matches(ev_action::text,':resorigtbl (d+)','g'))::oid,ev_class
    from pg_rewrite 
) deptbl 
    inner join pg_class b
    on deptbl.rel_oid = b.oid
    inner join pg_namespace c
    on b.relnamespace = c.oid
    inner join pg_class d
    on deptbl.ev_class = d.oid
    and deptbl.rel_oid <> d.oid
    inner join rec_view e          --循环语句关联条件
    on deptbl.rel_oid = e.ori_oid
    where level <=10--level防止死循环
)
select * from rec_view;

       从结果看，t2表相关视图是v2,v3两个视图，假如需要备份v2,v3到文本中，可以使用gs_dump的方式。

gs_dump postgres -s -t v2 -t v3 -c -f view.ddl -p 5432