PostgreSQL查询外键时如何获取表所有者并用于WHERE子句

下面是修改后的SQL，已包含表schema、所有者字段，且支持在WHERE子句中按所有者过滤：

SELECT
    base_ns.nspname AS base_table_schema,
    base.relname AS base_table,
    base_owner.rolname AS base_table_owner,
    a.attname AS base_col,
    ref_ns.nspname AS referencing_table_schema,
    ref.relname AS referencing_table,
    ref_owner.rolname AS referencing_table_owner,
    UNNEST(array_agg(ref_a.attname)) AS referencing_col
FROM pg_constraint c
JOIN pg_attribute a 
    ON c.confrelid = a.attrelid 
    AND a.attnum = ANY(c.confkey)
-- 关联基表的schema与所有者
JOIN pg_class base 
    ON base.oid = c.confrelid
JOIN pg_namespace base_ns 
    ON base.relnamespace = base_ns.oid
JOIN pg_authid base_owner 
    ON base.relowner = base_owner.oid
-- 关联引用表的schema与所有者
JOIN pg_class ref 
    ON ref.oid = c.conrelid
JOIN pg_namespace ref_ns 
    ON ref.relnamespace = ref_ns.oid
JOIN pg_authid ref_owner 
    ON ref.relowner = ref_owner.oid
-- 关联引用表字段
JOIN pg_attribute ref_a 
    ON ref.oid = ref_a.attrelid 
    AND array[ref_a.attnum] <@ c.conkey
WHERE base.relname = 'table_name'
    -- 可选：过滤基表所属schema
    -- AND base_ns.nspname = 'schema_name'
    -- 可选：过滤基表所有者
    -- AND base_owner.rolname = 'target_owner'
    -- 可选：过滤引用表所有者
    -- AND ref_owner.rolname = 'target_owner'
    AND c.confrelid != c.conrelid
GROUP BY base_ns.nspname, base.relname, base_owner.rolname, a.attname, ref_ns.nspname, ref.relname, ref_owner.rolname;

关键说明 #

• 获取表所有者：PostgreSQL中pg_class表的relowner字段是表所有者的ID，关联pg_authid表的rolname字段就能得到所有者名称。这里分别关联了基表和引用表的所有者，对应结果中的base_table_owner和referencing_table_owner。
• 获取表schema：pg_class的relnamespace字段关联pg_namespace表的nspname，得到表所属的schema名称，对应你注释中需要的base_table_schema和referencing_table_schema。
• 按所有者过滤：在WHERE子句中直接添加AND base_owner.rolname = '目标所有者名'（过滤基表所有者）或AND ref_owner.rolname = '目标所有者名'（过滤引用表所有者）即可。
• 查询优化：将原查询中的子查询替换为JOIN方式，避免重复查询系统表，同时用GROUP BY+array_agg的组合替代原逻辑，让查询结构更清晰、性能更优。

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