错误：无法更改视图或规则使用的列的类型 详细信息：视图上的规则_RETURN依赖于列“status”。

Question

我正在尝试在Postgres中将类型为整数的列更新为数值（2）

ALTER TABLE employee_status
    ALTER COLUMN status TYPE numeric(2);

但出现了错误信息 ERROR: 无法更改视图或规则使用的列的类型 DETAIL: 视图v_employee_details上的规则_RETURN依赖于列"status"

不删除数据的情况下，我该怎么做才能实现这个目标？有没有办法禁用规则或视图？

- not-a-bug

Answer 1

唯一可能的方法是删除并重新创建视图。但这没有问题，您可以使用 pg_get_viewdef 函数轻松获取视图定义。 如果您有很多相关视图，请参考这个答案以正确的顺序获取所有相关视图。不必担心 _RETURN 规则：这只是PostgreSQL中实现视图的一个实现细节，作为名为_RETURN的ON SELECT DO INSTEAD规则。

do $$            
  declare v_employee_details_def text;
  declare exec_text text;
begin          
  v_employee_details_def := pg_get_viewdef('v_employee_details');
  drop view v_employee_details;
  
  -- do your other stuff
  
  exec_text := format('create view v_employee_details as %s', 
      v_employee_details_def);
  execute exec_text;
end $$;

- Laurenz Albe

---

如果我删除视图然后修改列，它应该可以工作吗？那么规则_RETURN呢？规则会被删除并重新创建吗？ - not-a-bug

我已经添加了关于“_RETURN”的信息。如果视图定义能够与修改后的表一起正常运行，那么您应该能够在之后重新创建视图。 - Laurenz Albe

在我的情况下，即使删除了视图，问题仍然存在，我需要删除其他地方吗？ - undefined

@TalesMGodois 嗯，你需要删除所有依赖于你想修改的列的视图。 - undefined

所以，我终于解决了。我已经删除了视图，但是那并没有起作用，Prisma仍然认为视图存在。我所做的就是手动恢复了所有迁移，即Prisma说有问题的部分，我都手动恢复了。然后，我删除了迁移文件夹，重新运行了Prisma迁移开发命令，然后问题就解决了。 - undefined

Answer 2

我找到了一种更简单的解决方案，但可能不是最佳实践。

请使用以下代码：UPDATE pg_attribute SET atttypmod = 35+4 WHERE attrelid = 'TABLE1'::regclass AND attname = 'COL1'; 您需要具有超级用户或类似权限才能更改Postgres系统表 - pg_attribute。这里有一个解释链接：链接！

- Alex

Answer 3

这是我根据@LaurenzAlbe的答案编写的完整SQL查询语句：

do $$            
  declare view_record record;
begin          

  drop table if exists public.__temp_views;

  --at first create temporary table with all view definitions
  create table public.__temp_views(schemaname text, viewname text, definition text, level float);

  --then insert recursive views with levels
  insert into public.__temp_views(schemaname, viewname, definition, level)

  WITH RECURSIVE viewids AS (
   /* all views that don't depend on other views */
   SELECT t.oid, 1 as level
   FROM pg_class t
      JOIN pg_rewrite AS r ON r.ev_class = t.oid
   WHERE r.rulename = '_RETURN'
     AND t.relkind = 'v'
     AND t.relnamespace NOT IN ('pg_catalog'::regnamespace,
                                'information_schema'::regnamespace,
                                'pg_toast'::regnamespace)
     AND NOT EXISTS (
            /* depends on a view */
            SELECT 1
            FROM pg_depend AS d
               JOIN pg_class AS t2 ON d.refobjid = t2.oid
            WHERE d.objid = r.oid
              AND d.classid = 'pg_rewrite'::regclass
              AND d.refclassid = 'pg_class'::regclass
              AND d.deptype = 'n'
              AND d.refobjsubid <> 0
              AND t2.relkind = 'v'
         )
     AND NOT EXISTS (
            /* depends on an extension */
            SELECT 1
            FROM pg_depend
            WHERE objid = t.oid
              AND classid = 'pg_class'::regclass
              AND refclassid = 'pg_extension'::regclass
              AND deptype = 'e'
         )
  UNION ALL
    /* all views that depend on these views */
    SELECT t.oid, viewids.level + 1
    FROM pg_class AS t
        JOIN pg_rewrite AS r ON r.ev_class = t.oid
        JOIN pg_depend AS d ON d.objid = r.oid
        JOIN viewids ON viewids.oid = d.refobjid
    WHERE t.relkind = 'v'
      AND r.rulename = '_RETURN'
      AND d.classid = 'pg_rewrite'::regclass                            
      AND d.refclassid = 'pg_class'::regclass
      AND d.deptype = 'n'
      AND d.refobjsubid <> 0
  )
  /* order the views by level, eliminating duplicates */
  SELECT 'public', oid::regclass, pg_get_viewdef(oid::regclass), max(level)
  FROM viewids
  GROUP BY oid
  ORDER BY max(level);

  --then drop all views
  FOR view_record IN SELECT * from public.__temp_views
  LOOP
    execute format('DROP VIEW IF EXISTS %s.%s CASCADE;', quote_ident(view_record.schemaname), quote_ident(view_record.viewname));
  END LOOP;

  -- then do your other stuff here
  -- for example i'm changing column that depending by views
  alter table public.connected_accounts 
  alter column provider TYPE text;

  ALTER TABLE public.connected_accounts
  ADD CONSTRAINT auth_providers_id_key
  FOREIGN KEY (provider)
  REFERENCES public.auth_providers (id) not valid;

  --then recreate all the views recursively
  FOR view_record IN SELECT * from public.__temp_views
  LOOP
    execute format('CREATE OR REPLACE VIEW %s.%s AS %s;', quote_ident(view_record.schemaname), quote_ident(view_record.viewname), view_record.definition);
  END LOOP;

  --finally drop temporary table
  drop table public.__temp_views;

end $$;

希望能对某人有所帮助。对我来说，这是一种救命之道，因为我有很多相互依赖的观点。谢谢！

- saike

---

谢谢 @saike，你用这个神奇的脚本救了我一命。 - undefined

Answer 4

我也在寻找避免删除/丢弃的方法，因为我认为应该有更简单的方法。但是，没有避免删除/丢弃的方法。然而，通过UI（pgAdmin 4），删除/丢弃和重新创建很容易：

1. 右键单击特定视图 -> 属性

2. 复制视图的代码

3. 备份您的数据库（以防万一）

4. 再次右键单击特定视图，然后选择“删除/丢弃”

5. 创建新视图并输入您之前复制出来的代码。完成。

- bobt