PostgreSQL中使用JOIN与UNNEST获取触发器OF列名的问题排查

你碰到的语法错误，核心原因是PostgreSQL中使用JOIN UNNEST()时必须显式指定连接条件，或者改用CROSS JOIN来实现数组行的展开关联。

先看你的初始问题场景 #

你已经能成功查询到触发器关联的列编号数组：

SELECT t.tgattr
FROM pg_namespace n
JOIN pg_class c ON c.relnamespace = n.oid
JOIN pg_trigger t ON t.tgrelid = c.oid
WHERE n.nspname = 'public'
AND c.relname = 'ut_trigger'
AND t.tgname = 'keepnamehistorytrigger';

返回结果是数组2 3，但当你尝试用JOIN UNNEST()展开数组时，因为缺少ON子句导致语法报错。

错误的根源 #

PostgreSQL的JOIN（不带前缀的普通JOIN）要求必须有ON来指定连接条件。而这里我们只是需要把触发器行（此时只有一行）和UNNEST展开的所有行做笛卡尔积关联，所以可以用ON true来满足语法要求，或者更简洁地使用CROSS JOIN（因为CROSS JOIN本来就是笛卡尔积，不需要ON子句）。

修正后的查询（基础版） #

先实现展开数组得到两行2和3的结果：

SELECT trig_cols.col_num
FROM pg_namespace n
JOIN pg_class c ON c.relnamespace = n.oid
JOIN pg_trigger t ON t.tgrelid = c.oid
CROSS JOIN UNNEST(t.tgattr) as trig_cols(col_num)
WHERE n.nspname = 'public'
AND c.relname = 'ut_trigger'
AND t.tgname = 'keepnamehistorytrigger';

或者用JOIN ... ON true的写法：

SELECT trig_cols.col_num
FROM pg_namespace n
JOIN pg_class c ON c.relnamespace = n.oid
JOIN pg_trigger t ON t.tgrelid = c.oid
JOIN UNNEST(t.tgattr) as trig_cols(col_num) ON true
WHERE n.nspname = 'public'
AND c.relname = 'ut_trigger'
AND t.tgname = 'keepnamehistorytrigger';

保持列顺序的最终版本（你的更新优化） #

如果需要保持触发器定义时的列顺序，一定要用UNNEST(...) WITH ORDINALITY，它会给每个展开的行添加一个序号列，最后通过这个序号排序就能还原数组的原始顺序。结合关联pg_attribute获取列名的完整查询如下：

SELECT a.attname
FROM pg_namespace n
JOIN pg_class c ON c.relnamespace = n.oid
JOIN pg_trigger t ON t.tgrelid = c.oid
JOIN UNNEST(t.tgattr) WITH ORDINALITY as trig_cols(col_num, col_idx) ON true
JOIN pg_attribute a ON a.attrelid = c.oid
AND a.attnum = trig_cols.col_num
WHERE n.nspname = 'public'
AND t.tgname = 'keepnamehistorytrigger'
AND c.relname = 'ut_trigger'
ORDER BY trig_cols.col_idx;

这个查询会返回触发器关联的列名，且顺序和触发器定义时的列顺序完全一致。

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