PostgreSQL 修改字段类型但是存在视图依赖

其实视图的存在与否在数据库界一直是一个话题。用好视图可以简化程序的很多代码，用不好视图不仅会给维护带来很多的不便，也会造成很大的性能问题。下面我从维护方面给出案例，以及当存在这种问题的时候，如何去解决这个问题。

假设有如下的表和视图
CREATE TABLE view_test (id int,cname varchar(10),des text);
CREATE VIEW vw_view_testa AS SELECT * FROM view_test WHERE id > 2;
CREATE VIEW vw_view_testb AS SELECT * FROM view_test WHERE id < 3;
INSERT INTO view_test(id,cname,des) VALUES (1,'a','value a'),(2,'b','value b'),(3,'c','value c'),(4,'d','value d'),(5,'e','value e');

现在我想修改表的字段 cname 从 varchar(10) 到 varchar(50)
ALTER TABLE view_test ALTER COLUMN cname TYPE VARCHAR(50);

ERROR:  cannot alter type of a column used by a view or rule
DETAIL:  rule _RETURN on view vw_view_testa depends on column "cname"
说明：这个时候会提示存在视图依赖无法修改字段的长度。当视图依赖成为一种嵌套的时候，这种问题在系统升级的时候会严重的影响升级的速度。那么有没有更好的办法去兼容呢。好的架构师需要从根本上解决，一般的业务公司只能是考虑做兼容。

下面是解决视图依赖的解决方案和步骤以及操作案例

CREATE TABLE public.deps_saved_ddl (
  deps_id serial,
  deps_view_schema name,
  deps_view_name name,
  deps_ddl_to_run text,
  PRIMARY KEY (deps_id));
ALTER TABLE public.deps_saved_ddl OWNER TO postgres;

-- 将所有与表依赖的视图进行转储
CREATE OR REPLACE FUNCTION public.deps_save_and_drop_dependencies(p_view_schema name, p_view_name name)
  RETURNS pg_catalog.void AS $BODY$
  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::char,0
                  UNION
                  SELECT dep_schema::name, dep_name::name, dep_type::char, 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
                 INNER JOIN pg_class ref_cl ON dep.refobjid = ref_cl.oid
                 INNER JOIN pg_namespace ref_nsp ON ref_cl.relnamespace = ref_nsp.oid
                 INNER JOIN pg_rewrite rwr ON dep.objid = rwr.oid
                 INNER JOIN pg_class rwr_cl ON rwr.ev_class = rwr_cl.oid
                 INNER JOIN pg_namespace rwr_nsp ON rwr_cl.relnamespace = rwr_nsp.oid
                 WHERE dep.deptype = 'n' AND dep.classid = 'pg_rewrite'::regclass ) deps
          INNER 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 DISTINCT p_view_schema, p_view_name, indexdef
    FROM pg_indexes
    WHERE schemaname = v_curr.obj_schema AND tablename = v_curr.obj_name;

    INSERT INTO deps_saved_ddl(deps_view_schema, deps_view_name, deps_ddl_to_run)
    SELECT DISTINCT tablename, rulename, definition
    FROM pg_rules
    WHERE schemaname = v_curr.obj_schema AND tablename = v_curr.obj_name;

    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
    INNER JOIN pg_namespace n ON n.oid = c.relnamespace
    INNER 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
  INNER JOIN pg_attribute a ON c.oid = a.attrelid
  INNER JOIN pg_namespace n ON n.oid = c.relnamespace
  INNER 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 || '.' || quote_ident(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 || '.' || quote_ident(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 || '.' || quote_ident(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 || '.' || quote_ident(v_curr.obj_name);
 
END loop;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

ALTER FUNCTION public.deps_save_and_drop_dependencies(p_view_schema name, p_view_name name) OWNER TO postgres;

-- 对所有之前创建的视图进行重建
CREATE OR REPLACE FUNCTION "public"."deps_restore_dependencies"("p_view_schema" name, "p_view_name" name)
  RETURNS "pg_catalog"."void" AS $BODY$
  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;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
 
ALTER FUNCTION public.deps_restore_dependencies(p_view_schema name, p_view_name name) OWNER TO postgres;

操作案例
BEGIN;
SELECT deps_save_and_drop_dependencies('public', 'view_test');
ALTER TABLE view_test ALTER COLUMN cname TYPE VARCHAR(50);
SELECT deps_restore_dependencies('public', 'view_test');
COMMIT;

查看表的结构字段 cname 的长度是否是 50
[postgres@localhost data]$ psql
psql (12.8)
Type "help" for help.

postgres=# \c cloud_test
You are now connected to database "cloud_test" as user "postgres".
cloud_test=# \d view_test
                    Table "public.view_test"
 Column |         Type          | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
 id     | integer               |           |          |
 cname  | character varying(50) |           |          |
 des    | text                  |           |          |

cloud_test=#