存在引用关系的表或视图快速添加字段

有时候需要对存在引用关系的视图或者表进行新增字段，存在引用关系的表是不允许直接drop掉，如果需要新增字段的话，只能把存在引用关系的表逐一解除，但存在很多表的时候，这时候就很困难了。下面分享一个快速新增字段的sql脚本。

/*
视图依赖关系记录以及保存，修改完毕后，重新执行所有视图函数
*/
--drop table deps_saved_ddl cascade ;

/*1.创建表保存相关信息*/
create table if not exists deps_saved_ddl
(
  deps_id          serial primary key,
  deps_view_schema varchar(255),
  deps_view_name   varchar(255),
  deps_ddl_to_run  text,
  deps_type        char
);


/*创建依赖保存和删除依赖函数*/
create or replace function 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, deps_type)
  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, '''', '''''') || ''';', 'c'
  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, deps_type)
  select p_view_schema, p_view_name, 'COMMENT ON COLUMN ' || n.nspname || '.' || c.relname || '.' || a.attname || ' IS ''' || replace(d.description, '''', '''''') || ''';', 'c'
  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, deps_type)
  select p_view_schema, p_view_name, 'GRANT ' || privilege_type || ' ON ' || table_schema || '."' || table_name || '" TO ' || grantee, 'g'
  from information_schema.role_table_grants
  where table_schema = v_curr.obj_schema and table_name = v_curr.obj_name;

  insert into deps_saved_ddl(deps_view_schema, deps_view_name, deps_ddl_to_run, deps_type)
  select p_view_schema, p_view_name, pg_get_indexdef(idx.oid), 'i'
  from pg_index ind
  join pg_class idx on idx.oid = ind.indexrelid
  join pg_class tbl on tbl.oid = ind.indrelid
  left join pg_namespace ns on ns.oid = tbl.relnamespace
  where tbl.relname = v_curr.obj_name and ns.nspname = v_curr.obj_schema;

  if v_curr.obj_type = 'v' then
    insert into deps_saved_ddl(deps_view_schema, deps_view_name, deps_ddl_to_run, deps_type)
    select p_view_schema, p_view_name, 'CREATE VIEW ' || v_curr.obj_schema || '."' || v_curr.obj_name || '" AS ' || view_definition, 'v'
    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, deps_type)
    select p_view_schema, p_view_name, 'CREATE MATERIALIZED VIEW ' || v_curr.obj_schema || '."' || v_curr.obj_name || '" AS ' || definition, 'm'
    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;

-- save foreign keys
for v_curr in
(
  SELECT ref_nsp.nspname ref_schema, ref_cl.relname ref_name, const.conname conname, pg_catalog.pg_get_constraintdef(const.oid, true) as condef
    FROM pg_catalog.pg_constraint const
    JOIN pg_class ref_cl on const.conrelid = ref_cl.oid
    JOIN pg_namespace ref_nsp on ref_cl.relnamespace = ref_nsp.oid
    join pg_class orig_cl on const.confrelid = orig_cl.oid
    join pg_namespace orig_nsp on orig_cl.relnamespace = orig_nsp.oid

    WHERE
      orig_cl.relname = p_view_name AND
      orig_nsp.nspname = p_view_schema AND
      const.contype = 'f'
) loop
  insert into deps_saved_ddl(deps_view_schema, deps_view_name, deps_ddl_to_run, deps_type)
    select p_view_schema, p_view_name,
      'ALTER TABLE ' || v_curr.ref_schema || '."' || v_curr.ref_name || '" ADD CONSTRAINT "' || v_curr.conname || '" ' || v_curr.condef || ';', 'f';
  execute 'ALTER TABLE ' || v_curr.ref_schema || '."' || v_curr.ref_name || '" DROP CONSTRAINT "' || v_curr.conname || '"';
end loop;
end;
$$
LANGUAGE plpgsql;


--恢复依赖关系

create or replace function 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;
--1. 创建表格保存依赖关系和ddl
--2. 保存依赖关系和去掉依赖关系
--select deps_save_and_drop_dependencies('xxxx','xxxx');
--4. drop 原视图或表
--drop  view xxxxxxx;

--5. 根据新增字段后的DDL创建视图
--   create view xxxxx;
--6. 如果是表格，那么先插入数据
--7. 恢复所有与之依赖关系的视图
--select deps_restore_dependencies('public','municipality');

赏

Your recognition is the best gift

支付宝

微信