PostgreSQL 找出数据库对象之间的依赖关系 – 如视图依赖

对于做业务开发的工程师来说,经常会加字段加表或是改字段改表，这本来是很正常的事情,但pg库确有一个这样的特点。

被修改的字段或被删除的表,被其他视图引用了,就没办法进行修改了，会提示表被视图引用,此时就有点懵B了。

因为我们要先删除视图之后，才能进一步对要修改的表进行操作，要是一个视图还好，多个视图，重重引用，这就相当大的工作量了。

因此，我们可以使用下边的函数，查出该表有多少个视图引用

create or replace function get_dep_oids(oid) returns oid[] as $$
declare
res oid[];
begin
select array_agg(unnest::oid) into res from
(
select unnest(regexp_matches(ev_action::text,':relid (\d+)', 'g')) from pg_rewrite where ev_class = $1
union
select unnest(regexp_matches(ev_action::text,':resorigtbl (\d+)','g')) from pg_rewrite where ev_class = $1
EXCEPT
select oid::text from pg_class where oid=$1
) t;
return res;
end;
$$ language plpgsql strict;

create or replace function recursive_get_deps_views(IN tbl oid, OUT oid oid, OUT relkind "char", OUT nspname name, OUT relname name, OUT deps oid[], OUT ori_oid oid, OUT ori_relkind "char", OUT ori_nspname name, OUT ori_relname name ) returns setof record as
$$
declare
begin
return query
with recursive a as (
select * from (
select t1.oid,t1.relkind,t2.nspname,t1.relname,get_dep_oids(t1.oid) deps,(select t1.oid from pg_class t1,pg_namespace t2 where t1.relnamespace=t2.oid and t1.oid=tbl) as ori_oid from pg_class t1, pg_namespace t2 where t1.relnamespace=t2.oid and t1.relkind in ('m','v')
) t where t.ori_oid = any(t.deps)
union
select * from (
select t1.oid,t1.relkind,t2.nspname,t1.relname,get_dep_oids(t1.oid) deps, a.oid as ori_oid from pg_class t1,pg_namespace t2,a where t1.relnamespace=t2.oid and t1.relkind in ('m','v')
) t where t.ori_oid = any(t.deps)
)
select a.oid,a.relkind,a.nspname,a.relname,a.deps,a.ori_oid,b.relkind ori_relkind, c.nspname ori_nspname,b.relname ori_relname from a,pg_class b,pg_namespace c where a.ori_oid=b.oid and b.relnamespace=c.oid order by a.nspname,a.relkind,a.relname;
end;
$$ language plpgsql strict;

测试语句

create table test(id int, name varchar(10));
create view view_test as select * from test;
alter table test alter name type varchar(100);

select * from recursive_get_deps_views('test'::regclass);

可以看到如下效果图被视图引用。

但我们要达到改表结构的效果,所以我们要把视图删除后，才能修改表结构.

在实际开发中呢，可能是有好几个视图，所以一个个删除，还有保存原来的视图结构，这样工作量就比较大了。

大家可以用下边的语句来减少工作量

--拼出创造视图的函数

CREATE OR REPLACE FUNCTION "public"."pg_get_viewdef_structure"("tablename" text)
RETURNS "pg_catalog"."varchar" AS $BODY$
declare
pu text :='';
viewname_col text := '';
viewowner_col text := '';
definition_col text := '';
res varchar;
begin
select schemaname into pu from pg_views where viewname = tablename;
select viewname into viewname_col from pg_views where viewname = tablename;
select viewowner into viewowner_col from pg_views where viewname = tablename;
select definition into definition_col from pg_views where viewname = tablename;
res:= 'create view ' || pu || '.' || viewname_col || ' as ' || CHR(13) || definition_col || CHR(13) || 'ALTER TABLE ' || pu || '.' || viewname_col || ' OWNER TO ' || viewowner_col || ';';
return res;
end;
$BODY$
LANGUAGE plpgsql VOLATILE STRICT
COST 100

--删除视图的函数
CREATE OR REPLACE FUNCTION "public"."pg_get_viewdef_drop"("tablename" text)
RETURNS "pg_catalog"."varchar" AS $BODY$
declare
viewname_col text := '';
res varchar;
begin
select viewname into viewname_col from pg_views where viewname = tablename;
res:= 'drop view ' || viewname_col || ';';
return res;
end;
$BODY$
LANGUAGE plpgsql VOLATILE STRICT
COST 100

使用效果

select pg_get_viewdef_drop(aa.viewname) as drop_col,pg_get_viewdef_structure(aa.viewname) as structure_col from pg_views aa
where viewname in (select relname from recursive_get_deps_views('test'::regclass))

先将创建脚本拿出来保存起来，再将删除脚本删除对应的视图，修改完表结构，再创建视图，这就解决问题了。

大家想想看看，假如有10个或是更多10个以上的视图，这不得把自己改死，浪费一堆时间，让自己置身于痛苦之中

，这时候就要做起流水线的工作，不停的改。但是这样修改，无疑有很多缺点，可能改错了，改漏了，一旦改少了，又要重新进行检查修改。

，使用以上方法，无疑是最节省时间跟节省劳动力的表现。提高工作效率，又可以早点下班。

以上是自己的做为资深开发的一些个人经历，把这些经验分享给大家，希望以后大家在从事开发中，可以避免不必要的麻烦，跟浪费时间精力。

要是大家喜欢我的文章的话，可以在文章下留言或是联系我，共同进步，共同探讨开发的一些案例，促进彼此间的交流，分享一些日常的开发趣事。