postgresql - 将数组类型列扩展为引用表中的数据(元数据查询)

Question

我正在使用 Postgres 的元数据表 ( system catalogs ) 构建查询以获取我需要的一些信息。 pg_constraint catalog 有一个名为 conkey 的列，它是 int2[] 类型，它引用 pg_attribute .attnum.

我的问题不是关于系统目录本身，而是关于如何将这个 int2[] 数组扩展为实际列名数组。例如:

a) pg_constraint:
conname | conrelid | conkey
const1  | 123      | {2, 3}

b) pg_class:
oid | relname
123 | table1

c) pg_attribute:
attrelid | attname | attnum
123      | colA    | 1
123      | colB    | 2
123      | colC    | 3

如何在预期结果中获取const_columns？

pseudo-query:
select b.relname as table, a.conname as constraint,
    /******> a.conkey expanded to c.attname <******/ as const_columns
    from pg_constraint a, pg_class b, pg_attribute c
    where a.conrelid = b.oid
    and c.attrelid = b.oid
    and a.conkey = c.attnum;

expected result:
table  | constraint | const_columns
table1 | const1     | {colB, colC}

Answer 1

select
    b.relname as table,
    a.conname as constraint,
    array_agg(c.attname) as const_columns
from pg_constraint a, pg_class b, pg_attribute c
where
    a.conrelid = b.oid
    and c.attrelid = b.oid
    and c.attnum in (select unnest(a.conkey))
group by b.relname, a.conname

或者使用数组运算符:

    and array[c.attnum] <@ a.conkey