由于PG系数据库的视图存在类似于表结构的字段定义，执行表或视图DDL语句会有以下问题：
1、删除表、删除字段、修改表字段类型，必须先删除依赖该表的所有视图
2、删除视图，也必须先删除依赖该视图的所有视图
3、表增加字段，不需要先删除依赖视图，但需要更新视图定义（create or replace view），否则视图查询将不包含新增字段
4、修改表字段长度，不需要先删除依赖视图，但若视图中的字段长度小于表字段长度，查询视图可能出现value too long错误。因此需要先删除、再重建视图（该场景不支持create or replace view方式更新）。
若需要重建依赖视图，可参考以下方案。
先创建相关表和函数，再按如下步骤进行数据库对象的DDL变更，以sch1.test为例：
BEGIN;
--设一个不存在的schema，以便获取到的视图DDL里包含schema前缀
set search_path=abcd;
--保存对象上的依赖视图DDL，并删除依赖视图
select public.deps_save_and_drop_dependencies('sch1', 'test');
--执行对象DDL变更，此处案例为修改test表字段类型。若此步骤被慢SQL阻塞，则需要终止相关慢SQL以使本条语句继续执行。若此步骤执行报错导致本事务异常中断，则结束事务从“BEGIN”处开始重新操作即可。
alter table sch1.test alter col1 type varchar(30);
--完成对象DDL变更后，恢复依赖视图
select public.deps_restore_dependencies('sch1', 'test');
COMMIT;
create table public.deps_saved_ddl
(
deps_id serial primary key,
deps_view_schema varchar(255),
deps_view_name varchar(255),
deps_ddl_to_run text
);
create or replace function public.deps_save_and_drop_dependencies(p_view_schema varchar, p_view_name varchar) returns void as
$$
declare
v_curr record;
begin
for v_curr in
(
select obj_schema, obj_name, obj_type from
(
with recursive recursive_deps(obj_schema, obj_name, obj_type, depth) as
(
select p_view_schema, p_view_name, null::varchar, 0
union
select dep_schema::varchar, dep_name::varchar, dep_type::varchar, recursive_deps.depth + 1 from
(
select ref_nsp.nspname ref_schema, ref_cl.relname ref_name,
rwr_cl.relkind dep_type,
rwr_nsp.nspname dep_schema,
rwr_cl.relname dep_name
from pg_depend dep
join pg_class ref_cl on dep.refobjid = ref_cl.oid
join pg_namespace ref_nsp on ref_cl.relnamespace = ref_nsp.oid
join pg_rewrite rwr on dep.objid = rwr.oid
join pg_class rwr_cl on rwr.ev_class = rwr_cl.oid
join pg_namespace rwr_nsp on rwr_cl.relnamespace = rwr_nsp.oid
where dep.deptype = 'n'
and dep.classid = 'pg_rewrite'::regclass
) deps
join recursive_deps on deps.ref_schema = recursive_deps.obj_schema and deps.ref_name = recursive_deps.obj_name
where (deps.ref_schema != deps.dep_schema or deps.ref_name != deps.dep_name)
)
select obj_schema, obj_name, obj_type, depth
from recursive_deps
where depth > 0
) t
group by obj_schema, obj_name, obj_type
order by max(depth) desc
) loop
insert into public.deps_saved_ddl(deps_view_schema, deps_view_name, deps_ddl_to_run)
select p_view_schema, p_view_name, 'COMMENT ON ' ||
case
when c.relkind = 'v' then 'VIEW'
when c.relkind = 'm' then 'MATERIALIZED VIEW'
else ''
end
|| ' ' || n.nspname || '.' || c.relname || ' IS ''' || replace(d.description, '''', '''''') || ''';'
from pg_class c
join pg_namespace n on n.oid = c.relnamespace
join pg_description d on d.objoid = c.oid and d.objsubid = 0
where n.nspname = v_curr.obj_schema and c.relname = v_curr.obj_name and d.description is not null;
insert into public.deps_saved_ddl(deps_view_schema, deps_view_name, deps_ddl_to_run)
select p_view_schema, p_view_name, 'COMMENT ON COLUMN ' || n.nspname || '.' || c.relname || '.' || a.attname || ' IS ''' || replace(d.description, '''', '''''') || ''';'
from pg_class c
join pg_attribute a on c.oid = a.attrelid
join pg_namespace n on n.oid = c.relnamespace
join pg_description d on d.objoid = c.oid and d.objsubid = a.attnum
where n.nspname = v_curr.obj_schema and c.relname = v_curr.obj_name and d.description is not null;
insert into public.deps_saved_ddl(deps_view_schema, deps_view_name, deps_ddl_to_run)
select p_view_schema, p_view_name, 'GRANT ' || privilege_type || ' ON ' || table_schema || '.' || table_name || ' TO ' || grantee
from information_schema.role_table_grants
where table_schema = v_curr.obj_schema and table_name = v_curr.obj_name;
if v_curr.obj_type = 'v' then
insert into public.deps_saved_ddl(deps_view_schema, deps_view_name, deps_ddl_to_run)
select p_view_schema, p_view_name, 'CREATE VIEW ' || v_curr.obj_schema || '.' || v_curr.obj_name || ' AS ' || view_definition
from information_schema.views
where table_schema = v_curr.obj_schema and table_name = v_curr.obj_name;
elsif v_curr.obj_type = 'm' then
insert into public.deps_saved_ddl(deps_view_schema, deps_view_name, deps_ddl_to_run)
select p_view_schema, p_view_name, 'CREATE MATERIALIZED VIEW ' || v_curr.obj_schema || '.' || v_curr.obj_name || ' AS ' || definition
from gs_matviews
where schemaname = v_curr.obj_schema and matviewname = v_curr.obj_name;
end if;
execute 'DROP ' ||
case
when v_curr.obj_type = 'v' then 'VIEW'
when v_curr.obj_type = 'm' then 'MATERIALIZED VIEW'
end
|| ' ' || v_curr.obj_schema || '.' || v_curr.obj_name;
end loop;
end;
$$
LANGUAGE plpgsql;
create or replace function public.deps_restore_dependencies(p_view_schema varchar, p_view_name varchar) returns void as
$$
declare
v_curr record;
begin
for v_curr in
(
select deps_ddl_to_run
from public.deps_saved_ddl
where deps_view_schema = p_view_schema and deps_view_name = p_view_name
order by deps_id desc
) loop
execute v_curr.deps_ddl_to_run;
end loop;
delete from public.deps_saved_ddl
where deps_view_schema = p_view_schema and deps_view_name = p_view_name;
end;
$$
LANGUAGE plpgsql;
若无需保留原版SQL，则忽略此节，与G100 2.2.10相同操作即可。
G100 2.2.15，新增pg_get_viewdef(true,oid)函数，可获取视图原版DDL（未格式化，无隐式转换、增加括号等）。为保留该原版SQL，则需对上述方法进行调整。
public.matviews和public.views视图。系统视图information_schema.views、pg_catalog.gs_matviews采用pg_get_viewdef(oid) 方式获取视图DDL，需修改为pg_get_viewdef(true,oid)。保险起见，我们不直接修改系统函数，而是新增public.matviews和public.views视图。deps_save_and_drop_dependencies函数定义，增加set search_path命令以指定对象搜索路径。deps_restore_dependencies函数恢复视图，因为视图原版DDL中可能并未包含schema前缀，调用该函数可能导致创建视图时报错（对象不存在）。先创建相关表和函数，再按如下步骤进行数据库对象的DDL变更，以sch1.test为例：
BEGIN;
--保存对象上的依赖视图DDL，并删除依赖视图
select public.deps_save_and_drop_dependencies('sch1','test');
--执行对象DDL变更，此处案例为修改test表字段类型。若此步骤被慢SQL阻塞，则需要终止相关慢SQL以使本条语句继续执行。若此步骤执行报错导致本事务异常中断，则结束事务从“BEGIN”处开始重新操作即可。
alter table sch1.test alter col1 type varchar(30);
--完成对象DDL变更后，导出依赖视图的DDL
\copy (select deps_ddl_to_run from public.deps_saved_ddl order by deps_id desc ) to '/tmp/deps_saved_ddl.sql' with (format csv,quote ' ');
--去除依赖视图DDL中的^M符号，注意以下语句不能直接复制，^M需要重新输入，输入方式是先Ctrl + v ，然后Ctrl + M
\! sed -i 's/^M//g' /tmp/deps_saved_ddl.sql
--执行依赖视图DDL
\i /tmp/deps_saved_ddl.sql
END;
create or replace view public.matviews as
SELECT n.nspname AS schemaname, c.relname AS matviewname,
pg_get_userbyid(c.relowner) AS matviewowner, t.spcname AS tablespace,
c.relhasindex AS hasindexes, pg_get_viewdef(true,c.oid) AS definition
FROM pg_class c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace
WHERE c.relkind = 'm'::"char";
create or replace view public.views as
SELECT current_database()::information_schema.sql_identifier AS table_catalog,
nc.nspname::information_schema.sql_identifier AS table_schema,
c.relname::information_schema.sql_identifier AS table_name,
CASE
WHEN pg_has_role(c.relowner, 'USAGE'::text) THEN pg_get_viewdef(true, c.oid)
ELSE NULL::text
END::information_schema.character_data AS view_definition,
CASE
WHEN 'check_option=cascaded'::text = ANY (c.reloptions) THEN 'CASCADED'::text
WHEN 'check_option=local'::text = ANY (c.reloptions) THEN 'LOCAL'::text
ELSE 'NONE'::text
END::information_schema.character_data AS check_option,
CASE
WHEN (pg_relation_is_updatable(c.oid::regclass, false) & 20) = 20 THEN 'YES'::text
ELSE 'NO'::text
END::information_schema.yes_or_no AS is_updatable,
CASE
WHEN (pg_relation_is_updatable(c.oid::regclass, false) & 8) = 8 THEN 'YES'::text
ELSE 'NO'::text
END::information_schema.yes_or_no AS is_insertable_into,
CASE
WHEN (EXISTS ( SELECT 1 AS "?column?"
FROM pg_trigger
WHERE pg_trigger.tgrelid = c.oid AND (pg_trigger.tgtype::integer & 81) = 81)) THEN 'YES'::text
ELSE 'NO'::text
END::information_schema.yes_or_no AS is_trigger_updatable,
CASE
WHEN (EXISTS ( SELECT 1 AS "?column?"
FROM pg_trigger
WHERE pg_trigger.tgrelid = c.oid AND (pg_trigger.tgtype::integer & 73) = 73)) THEN 'YES'::text
ELSE 'NO'::text
END::information_schema.yes_or_no AS is_trigger_deletable,
CASE
WHEN (EXISTS ( SELECT 1 AS "?column?"
FROM pg_trigger
WHERE pg_trigger.tgrelid = c.oid AND (pg_trigger.tgtype::integer & 69) = 69)) THEN 'YES'::text
ELSE 'NO'::text
END::information_schema.yes_or_no AS is_trigger_insertable_into
FROM pg_namespace nc, pg_class c
WHERE c.relnamespace = nc.oid AND c.relkind = 'v'::"char" AND NOT pg_is_other_temp_schema(nc.oid) AND (pg_has_role(c.relowner, 'USAGE'::text) OR has_table_privilege(c.oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER'::text) OR has_any_column_privilege(c.oid, 'SELECT, INSERT, UPDATE, REFERENCES'::text));
create table public.deps_saved_ddl
(
deps_id serial primary key,
deps_view_schema varchar(255),
deps_view_name varchar(255),
deps_ddl_to_run text
);
create or replace function public.deps_save_and_drop_dependencies(p_view_schema varchar, p_view_name varchar) returns void as
$$
declare
v_curr record;
begin
for v_curr in
(
select obj_schema, obj_name, obj_type from
(
with recursive recursive_deps(obj_schema, obj_name, obj_type, depth) as
(
select p_view_schema, p_view_name, null::varchar, 0
union
select dep_schema::varchar, dep_name::varchar, dep_type::varchar, recursive_deps.depth + 1 from
(
select ref_nsp.nspname ref_schema, ref_cl.relname ref_name,
rwr_cl.relkind dep_type,
rwr_nsp.nspname dep_schema,
rwr_cl.relname dep_name
from pg_depend dep
join pg_class ref_cl on dep.refobjid = ref_cl.oid
join pg_namespace ref_nsp on ref_cl.relnamespace = ref_nsp.oid
join pg_rewrite rwr on dep.objid = rwr.oid
join pg_class rwr_cl on rwr.ev_class = rwr_cl.oid
join pg_namespace rwr_nsp on rwr_cl.relnamespace = rwr_nsp.oid
where dep.deptype = 'n'
and dep.classid = 'pg_rewrite'::regclass
) deps
join recursive_deps on deps.ref_schema = recursive_deps.obj_schema and deps.ref_name = recursive_deps.obj_name
where (deps.ref_schema != deps.dep_schema or deps.ref_name != deps.dep_name)
)
select obj_schema, obj_name, obj_type, depth
from recursive_deps
where depth > 0
) t
group by obj_schema, obj_name, obj_type
order by max(depth) desc
) loop
insert into public.deps_saved_ddl(deps_view_schema, deps_view_name, deps_ddl_to_run)
select p_view_schema, p_view_name, 'COMMENT ON ' ||
case
when c.relkind = 'v' then 'VIEW'
when c.relkind = 'm' then 'MATERIALIZED VIEW'
else ''
end
|| ' ' || n.nspname || '.' || c.relname || ' IS ''' || replace(d.description, '''', '''''') || ''';'
from pg_class c
join pg_namespace n on n.oid = c.relnamespace
join pg_description d on d.objoid = c.oid and d.objsubid = 0
where n.nspname = v_curr.obj_schema and c.relname = v_curr.obj_name and d.description is not null;
insert into public.deps_saved_ddl(deps_view_schema, deps_view_name, deps_ddl_to_run)
select p_view_schema, p_view_name, 'COMMENT ON COLUMN ' || n.nspname || '.' || c.relname || '.' || a.attname || ' IS ''' || replace(d.description, '''', '''''') || ''';'
from pg_class c
join pg_attribute a on c.oid = a.attrelid
join pg_namespace n on n.oid = c.relnamespace
join pg_description d on d.objoid = c.oid and d.objsubid = a.attnum
where n.nspname = v_curr.obj_schema and c.relname = v_curr.obj_name and d.description is not null;
insert into public.deps_saved_ddl(deps_view_schema, deps_view_name, deps_ddl_to_run)
select p_view_schema, p_view_name, 'GRANT ' || privilege_type || ' ON ' || table_schema || '.' || table_name || ' TO ' || grantee|| ' ;'
from information_schema.role_table_grants
where table_schema = v_curr.obj_schema and table_name = v_curr.obj_name;
if v_curr.obj_type = 'v' then
insert into public.deps_saved_ddl(deps_view_schema, deps_view_name, deps_ddl_to_run)
select p_view_schema, p_view_name, 'set search_path='||v_curr.obj_schema||' \; CREATE VIEW ' || v_curr.obj_schema || '.' || v_curr.obj_name || ' AS ' || view_definition|| ' ;'
from public.views
where table_schema = v_curr.obj_schema and table_name = v_curr.obj_name;
elsif v_curr.obj_type = 'm' then
insert into public.deps_saved_ddl(deps_view_schema, deps_view_name, deps_ddl_to_run)
select p_view_schema, p_view_name, 'set search_path='||v_curr.obj_schema||' \; CREATE MATERIALIZED VIEW ' || v_curr.obj_schema || '.' || v_curr.obj_name || ' AS ' || definition|| ' ;'
from public.matviews
where schemaname = v_curr.obj_schema and matviewname = v_curr.obj_name;
end if;
execute 'DROP ' ||
case
when v_curr.obj_type = 'v' then 'VIEW'
when v_curr.obj_type = 'm' then 'MATERIALIZED VIEW'
end
|| ' ' || v_curr.obj_schema || '.' || v_curr.obj_name;
end loop;
end;
$$
LANGUAGE plpgsql;
操作步骤、函数与G100 2.2.10基本一致，仅需替换deps_save_and_drop_dependencies函数定义中的gs_matviews为pg_matviews。
先创建相关表和函数，再按如下步骤进行数据库对象的DDL变更，以sch1.test为例：
BEGIN;
--设一个不存在的schema，以便获取到的视图DDL里包含schema前缀
set search_path=abcd;
--保存对象上的依赖视图DDL，并删除依赖视图
select public.deps_save_and_drop_dependencies('sch1', 'test');
--执行对象DDL变更，此处案例为修改test表字段类型。若此步骤被慢SQL阻塞，则需要终止相关慢SQL以使本条语句继续执行。若此步骤执行报错导致本事务异常中断，则结束事务从“BEGIN”处开始重新操作即可。
alter table sch1.test alter col1 type varchar(30);
--完成对象DDL变更后，恢复依赖视图
select public.deps_restore_dependencies('sch1', 'test');
COMMIT;
create table public.deps_saved_ddl
(
deps_id serial primary key,
deps_view_schema varchar(255),
deps_view_name varchar(255),
deps_ddl_to_run text
);
create or replace function public.deps_save_and_drop_dependencies(p_view_schema varchar, p_view_name varchar) returns void as
$$
declare
v_curr record;
begin
for v_curr in
(
select obj_schema, obj_name, obj_type from
(
with recursive recursive_deps(obj_schema, obj_name, obj_type, depth) as
(
select p_view_schema, p_view_name, null::varchar, 0
union
select dep_schema::varchar, dep_name::varchar, dep_type::varchar, recursive_deps.depth + 1 from
(
select ref_nsp.nspname ref_schema, ref_cl.relname ref_name,
rwr_cl.relkind dep_type,
rwr_nsp.nspname dep_schema,
rwr_cl.relname dep_name
from pg_depend dep
join pg_class ref_cl on dep.refobjid = ref_cl.oid
join pg_namespace ref_nsp on ref_cl.relnamespace = ref_nsp.oid
join pg_rewrite rwr on dep.objid = rwr.oid
join pg_class rwr_cl on rwr.ev_class = rwr_cl.oid
join pg_namespace rwr_nsp on rwr_cl.relnamespace = rwr_nsp.oid
where dep.deptype = 'n'
and dep.classid = 'pg_rewrite'::regclass
) deps
join recursive_deps on deps.ref_schema = recursive_deps.obj_schema and deps.ref_name = recursive_deps.obj_name
where (deps.ref_schema != deps.dep_schema or deps.ref_name != deps.dep_name)
)
select obj_schema, obj_name, obj_type, depth
from recursive_deps
where depth > 0
) t
group by obj_schema, obj_name, obj_type
order by max(depth) desc
) loop
insert into deps_saved_ddl(deps_view_schema, deps_view_name, deps_ddl_to_run)
select p_view_schema, p_view_name, 'COMMENT ON ' ||
case
when c.relkind = 'v' then 'VIEW'
when c.relkind = 'm' then 'MATERIALIZED VIEW'
else ''
end
|| ' ' || n.nspname || '.' || c.relname || ' IS ''' || replace(d.description, '''', '''''') || ''';'
from pg_class c
join pg_namespace n on n.oid = c.relnamespace
join pg_description d on d.objoid = c.oid and d.objsubid = 0
where n.nspname = v_curr.obj_schema and c.relname = v_curr.obj_name and d.description is not null;
insert into deps_saved_ddl(deps_view_schema, deps_view_name, deps_ddl_to_run)
select p_view_schema, p_view_name, 'COMMENT ON COLUMN ' || n.nspname || '.' || c.relname || '.' || a.attname || ' IS ''' || replace(d.description, '''', '''''') || ''';'
from pg_class c
join pg_attribute a on c.oid = a.attrelid
join pg_namespace n on n.oid = c.relnamespace
join pg_description d on d.objoid = c.oid and d.objsubid = a.attnum
where n.nspname = v_curr.obj_schema and c.relname = v_curr.obj_name and d.description is not null;
insert into deps_saved_ddl(deps_view_schema, deps_view_name, deps_ddl_to_run)
select p_view_schema, p_view_name, 'GRANT ' || privilege_type || ' ON ' || table_schema || '.' || table_name || ' TO ' || grantee
from information_schema.role_table_grants
where table_schema = v_curr.obj_schema and table_name = v_curr.obj_name;
if v_curr.obj_type = 'v' then
insert into deps_saved_ddl(deps_view_schema, deps_view_name, deps_ddl_to_run)
select p_view_schema, p_view_name, 'CREATE VIEW ' || v_curr.obj_schema || '.' || v_curr.obj_name || ' AS ' || view_definition
from information_schema.views
where table_schema = v_curr.obj_schema and table_name = v_curr.obj_name;
elsif v_curr.obj_type = 'm' then
insert into deps_saved_ddl(deps_view_schema, deps_view_name, deps_ddl_to_run)
select p_view_schema, p_view_name, 'CREATE MATERIALIZED VIEW ' || v_curr.obj_schema || '.' || v_curr.obj_name || ' AS ' || definition
from pg_matviews
where schemaname = v_curr.obj_schema and matviewname = v_curr.obj_name;
end if;
execute 'DROP ' ||
case
when v_curr.obj_type = 'v' then 'VIEW'
when v_curr.obj_type = 'm' then 'MATERIALIZED VIEW'
end
|| ' ' || v_curr.obj_schema || '.' || v_curr.obj_name;
end loop;
end;
$$
LANGUAGE plpgsql;
create or replace function public.deps_restore_dependencies(p_view_schema varchar, p_view_name varchar) returns void as
$$
declare
v_curr record;
begin
for v_curr in
(
select deps_ddl_to_run
from deps_saved_ddl
where deps_view_schema = p_view_schema and deps_view_name = p_view_name
order by deps_id desc
) loop
execute v_curr.deps_ddl_to_run;
end loop;
delete from deps_saved_ddl
where deps_view_schema = p_view_schema and deps_view_name = p_view_name;
end;
$$
LANGUAGE plpgsql;