如何在PgAdmin4中查找已删除列的关联对象？

• 查询视图、函数、触发器中的引用
  执行以下SQL，搜索所有包含student.scale的数据库对象定义：

  SELECT
      objtype,
      objname,
      definition
  FROM pg_catalog.pg_proc
  JOIN pg_catalog.pg_namespace ON pg_proc.pronamespace = pg_namespace.oid
  WHERE definition LIKE '%student.scale%'
  UNION ALL
  SELECT
      'VIEW' AS objtype,
      table_name AS objname,
      view_definition AS definition
  FROM information_schema.views
  WHERE view_definition LIKE '%student.scale%'
  UNION ALL
  SELECT
      'TRIGGER FUNCTION' AS objtype,
      tgname AS objname,
      pg_get_triggerdef(t.oid) AS definition
  FROM pg_catalog.pg_trigger t
  JOIN pg_catalog.pg_proc p ON t.tgfoid = p.oid
  WHERE pg_get_triggerdef(t.oid) LIKE '%student.scale%';
  

  结果会列出所有依赖该列的函数、视图、触发器，直接定位问题对象。

• 排查物化视图
  如果使用了物化视图，执行以下查询检查引用：

  SELECT
      'MATERIALIZED VIEW' AS objtype,
      matviewname AS objname,
      definition
  FROM pg_catalog.pg_matviews
  WHERE definition LIKE '%student.scale%';
  

• 检查数据库规则
  数据库规则也可能引用该列，执行：

  SELECT
      'RULE' AS objtype,
      rulename AS objname,
      pg_get_ruledef(r.oid) AS definition
  FROM pg_catalog.pg_rewrite r
  JOIN pg_catalog.pg_class c ON r.ev_class = c.oid
  WHERE pg_get_ruledef(r.oid) LIKE '%student.scale%'
    AND relname = 'student';
  

如果以上查询未找到结果，可以尝试放宽匹配条件（比如只搜索scale），避免因别名或大小写问题遗漏。这些查询可直接在PgAdmin4的查询工具中运行，AWS RDS PostgreSQL完全兼容这些系统表查询。

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