CTE的RETURNING子句中如何引用交叉连接的列别名？

这个问题的核心是PostgreSQL的RETURNING子句有作用域限制：它只能访问目标表的列，或者INSERT语句中SELECT列表里明确声明的列。你原来的写法里，不仅存在列数不匹配的问题（目标表something只有one和two两列，但SELECT返回了3列），而且RETURNING也无法识别t——因为它没有被正确纳入INSERT的上下文。

正确的写法示例 #

WITH cte AS (
  INSERT INTO something (one, two)
  SELECT 
    i->>'a',          -- 对应目标表的one列
    o.somefield,      -- 对应目标表的two列
    d.key AS t        -- 额外声明t列，不插入到目标表，但可被RETURNING引用
  FROM jsonb_array_elements('[{"a": 1, "b": 2},{"a":3, "b": 4}]') AS i
  CROSS JOIN jsonb_each('{"c": 1, "d": 2}') AS d(key, value)
  JOIN someothercte o ON o.value = d.value
  RETURNING id, t;   -- 现在可以正常引用t了
)
-- 后续可以使用cte的数据，比如：
SELECT * FROM cte;

原理说明 #

• 当你在INSERT ... SELECT的SELECT列表中添加额外的列（比如这里的t）时，PostgreSQL会保留这些列的上下文，允许RETURNING子句引用它们，即使这些列没有被插入到目标表中。
• 这样既满足了插入数据到something表的需求，又能在CTE中直接获取到交叉连接里的d.key值，无需额外关联查询。

备选方案（不推荐，仅作参考） #

如果不想在SELECT里加额外列，也可以通过插入后的id回查源数据，但这种方法需要重复执行关联逻辑，效率更低：

WITH inserted AS (
  INSERT INTO something (one, two)
  SELECT i->>'a', o.somefield
  FROM jsonb_array_elements('[{"a": 1, "b": 2},{"a":3, "b": 4}]') AS i
  CROSS JOIN jsonb_each('{"c": 1, "d": 2}') AS d(key, value)
  JOIN someothercte o ON o.value = d.value
  RETURNING id, one, two
)
SELECT ins.id, d.key AS t
FROM inserted ins
JOIN jsonb_array_elements('[{"a": 1, "b": 2},{"a":3, "b": 4}]') AS i ON ins.one = i->>'a'
JOIN jsonb_each('{"c": 1, "d": 2}') AS d(key, value)
JOIN someothercte o ON o.value = d.value AND ins.two = o.somefield;

内容的提问来源于stack exchange，提问作者S. Schenk