PostgreSQL 查詢表格相依姓 ( Alter Table 語法 )

在 PostgreSQL 不支援 Invalid Object 物件，在調整表格 DDL 時可能會因為有 View 或 Constraint 等限制影響無法直接刪除，這些物件可以利用 pg_depend 還有 pg_shdepend 兩張系統表來查詢相依關係。

其中 pg_depend 裡面會記錄的是各別資料庫間內部的物件關聯，而 pg_shdepend 則是會記錄跨資料庫的物件相依姓，像是使用者等等。

在兩張表格 pg_depend 和 pg_shdepend 裡面，皆有一個欄位 deptype 會告訴我們兩個物件的關係如何，以下示範是查詢 pg_depend 這張表格，篩選 deptype 為 n 的紀錄，代表著被 Reference 的物件要移除或調整時，需要指定 Cascade 或移除掉對應 Depend 的物件。

View

select 
  n.nspname AS "Schema Name",
  relname AS "Table Name",
  a.attname AS "Column Name",
  r.ev_class::regclass AS "Depend View Name"
FROM pg_catalog.pg_class c
     JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
     JOIN pg_depend d ON d.refobjid = c.oid
     JOIN pg_rewrite r ON r.oid = d.objid
     JOIN pg_attribute a ON a.attrelid=c.oid AND a.attnum = d.refobjsubid
WHERE n.nspname = 'public'
      AND c.relname = 'ta'
      AND d.deptype = 'n'
      AND classid::regclass::text = 'pg_rewrite'
;

Constraint

select 
  n.nspname AS "Schema Name",
  relname AS "Table Name",
  a.attname AS "Column Name",
  cs.conname AS "Constraint Name"
FROM pg_catalog.pg_class c
     JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
     JOIN pg_depend d ON d.refobjid = c.oid
     JOIN pg_constraint cs ON cs.oid = d.objid
     JOIN pg_attribute a ON a.attrelid=c.oid AND a.attnum = d.refobjsubid
WHERE n.nspname = 'public'
      AND c.relname = 'ta'
      AND d.deptype = 'n'
      AND classid::regclass::text = 'pg_constraint';