PostgreSQL 15如何修改函数实现带前缀的依赖视图批量重建？

问题背景 #

我正在使用PostgreSQL 15重建视图，数据库中存在view_a、view_b、view_c等视图。针对特定项目，需要创建这些视图的带前缀副本（如project_view_a），同时保留原视图。现有PL/pgSQL函数仅能处理直接匹配模式的视图，无法同步原视图所依赖的其他视图——例如原视图test依赖v_firsttry，运行函数后需同时生成prefix_test和prefix_v_firsttry，当前函数无法实现该需求。

原函数代码 #

CREATE OR REPLACE FUNCTION public.create_project_views(prefix text, view_pattern text)
 RETURNS void
 LANGUAGE plpgsql
AS $function$
DECLARE
  view_name TEXT;
  view_def TEXT;
BEGIN
  FOR view_name IN SELECT table_name FROM information_schema.views WHERE table_schema = 'public' AND table_name LIKE (view_pattern || '%') AND table_name NOT LIKE (prefix || '_%') LOOP
    view_def := pg_get_viewdef(view_name, true);
    view_def := regexp_replace(view_def, '^CREATE VIEW ' || view_name, 'CREATE VIEW ' || prefix || '_' || view_name);
    EXECUTE 'CREATE OR REPLACE VIEW ' || prefix || '_' || view_name || ' AS ' || view_def;
  END LOOP;
END;
$function$;

优化方案 #

要实现依赖视图的同步复制，核心是递归遍历视图的依赖链，按依赖顺序创建带前缀的视图，并替换定义中的原视图引用。以下是优化后的函数：

CREATE OR REPLACE FUNCTION public.create_project_views(prefix text, view_pattern text)
 RETURNS void
 LANGUAGE plpgsql
AS $function$
DECLARE
  -- 存储需要处理的视图（原名称）
  todo_views TEXT[];
  -- 当前处理的视图名称
  current_view TEXT;
  -- 已处理的视图，避免重复处理
  processed_views TEXT[] := '{}'::TEXT[];
  -- 视图定义
  view_def TEXT;
  -- 存储依赖的视图名称
  dep_view TEXT;
BEGIN
  -- 初始化：加入所有匹配模式且未带前缀的视图
  SELECT array_agg(table_name) INTO todo_views
  FROM information_schema.views
  WHERE table_schema = 'public'
    AND table_name LIKE (view_pattern || '%')
    AND table_name NOT LIKE (prefix || '_%');

  -- 递归处理所有依赖视图
  WHILE array_length(todo_views, 1) > 0 LOOP
    -- 取出第一个待处理视图
    current_view := todo_views[1];
    todo_views := array_remove(todo_views, current_view);

    -- 如果已处理过，跳过
    IF current_view = ANY(processed_views) THEN
      CONTINUE;
    END IF;

    -- 收集当前视图依赖的所有public schema下的视图
    FOR dep_view IN
      SELECT DISTINCT refobjname::TEXT
      FROM pg_depend d
      JOIN pg_class c ON d.objid = c.oid
      JOIN pg_namespace ns ON c.relnamespace = ns.oid
      JOIN pg_class refc ON d.refobjid = refc.oid
      JOIN pg_namespace refns ON refc.relnamespace = refns.oid
      WHERE ns.nspname = 'public'
        AND c.relname = current_view
        AND c.relkind = 'v' -- 仅视图
        AND refns.nspname = 'public'
        AND refc.relkind = 'v' -- 仅视图
        AND refobjname::TEXT NOT LIKE (prefix || '_%') -- 排除已带前缀的视图
    LOOP
      -- 如果依赖视图未在待处理和已处理列表中，加入待处理
      IF dep_view <> ANY(todo_views) AND dep_view <> ANY(processed_views) THEN
        todo_views := array_append(todo_views, dep_view);
      END IF;
    END LOOP;

    -- 获取视图定义，并替换所有引用的原视图为带前缀版本
    view_def := pg_get_viewdef(current_view, true);
    -- 替换定义中所有出现的原视图名称（匹配独立的标识符，避免部分匹配）
    view_def := regexp_replace(view_def, '\m' || current_view || '\M', prefix || '_' || current_view, 'g');
    -- 替换其他依赖视图的引用
    FOR dep_view IN
      SELECT DISTINCT refobjname::TEXT
      FROM pg_depend d
      JOIN pg_class c ON d.objid = c.oid
      JOIN pg_namespace ns ON c.relnamespace = ns.oid
      JOIN pg_class refc ON d.refobjid = refc.oid
      JOIN pg_namespace refns ON refc.relnamespace = refns.oid
      WHERE ns.nspname = 'public'
        AND c.relname = current_view
        AND c.relkind = 'v'
        AND refns.nspname = 'public'
        AND refc.relkind = 'v'
        AND refobjname::TEXT NOT LIKE (prefix || '_%')
    LOOP
      view_def := regexp_replace(view_def, '\m' || dep_view || '\M', prefix || '_' || dep_view, 'g');
    END LOOP;

    -- 创建带前缀的视图
    EXECUTE 'CREATE OR REPLACE VIEW ' || prefix || '_' || current_view || ' AS ' || view_def;

    -- 标记为已处理
    processed_views := array_append(processed_views, current_view);
  END LOOP;
END;
$function$;

调用示例 #

要给所有以view开头的视图创建带project前缀的副本，并同步所有依赖视图，执行：

SELECT create_project_views('project', 'view');

关键说明 #

• 递归依赖处理：函数会自动遍历所有层级的依赖视图，确保所有被引用的视图都生成带前缀的副本
• 引用替换：使用正则表达式的\m和\M锚点（匹配单词边界），避免错误替换视图名称的部分字符
• 执行顺序：通过待处理列表确保先创建被依赖的视图，再创建依赖它的视图，避免出现"视图不存在"的错误
• 幂等性：已存在的带前缀视图会被CREATE OR REPLACE覆盖，可重复执行

内容的提问来源于stack exchange，提问作者Djabone