触发器中使用多CTE插入数据失败，如何正确引用CTE属性？

你的问题出在VALUES子句无法直接引用CTE结果集的字段——VALUES仅适用于直接提供字面量或单一行的独立表达式，而CTE是独立的临时结果集，必须通过SELECT语句来访问其中的数据。

以下是两种可行的修正写法：

方案1：交叉连接单行CTE（触发器场景最常用） #

如果每个CTE都只会返回一行数据（这在触发器中很常见，比如通过NEW/OLD记录关联查询其他表），可以用CROSS JOIN（或隐式逗号连接）合并所有CTE的结果，再通过SELECT提取字段插入：

WITH prop1 AS (
  -- 示例：通过触发器触发的记录关联查询，确保返回单行
  SELECT attribute AS attr1 FROM table1 WHERE id = NEW.trigger_id
), prop2 AS (
  SELECT attribute AS attr2 FROM table2 WHERE code = NEW.some_code
), prop3 AS (
  SELECT attribute AS attr3 FROM table3 WHERE ref = NEW.ref_value
)
INSERT INTO target_table (col1, col2, col3)
SELECT p1.attr1, p2.attr2, p3.attr3
FROM prop1 p1
CROSS JOIN prop2 p2
CROSS JOIN prop3 p3;

方案2：关联连接多行CTE #

如果某个CTE可能返回多行，或者需要通过特定条件关联多个CTE的结果，使用对应的JOIN类型（如INNER JOIN）替代交叉连接：

WITH prop1 AS (
  SELECT id, attribute AS attr1 FROM table1 WHERE ...
), prop2 AS (
  SELECT table1_id, attribute AS attr2 FROM table2 WHERE ...
), prop3 AS (
  SELECT table1_id, attribute AS attr3 FROM table3 WHERE ...
)
INSERT INTO target_table (col1, col2, col3)
SELECT p1.attr1, p2.attr2, p3.attr3
FROM prop1 p1
INNER JOIN prop2 p2 ON p1.id = p2.table1_id
INNER JOIN prop3 p3 ON p1.id = p3.table1_id;

触发器函数完整示例 #

把上述逻辑封装到触发器函数中：

CREATE OR REPLACE FUNCTION insert_target_trigger()
RETURNS TRIGGER AS $$
BEGIN
  WITH prop1 AS (
    SELECT attribute AS attr1 FROM table1 WHERE id = NEW.trigger_id
  ), prop2 AS (
    SELECT attribute AS attr2 FROM table2 WHERE code = NEW.some_code
  ), prop3 AS (
    SELECT attribute AS attr3 FROM table3 WHERE ref = NEW.ref_value
  )
  INSERT INTO target_table (col1, col2, col3)
  SELECT p1.attr1, p2.attr2, p3.attr3
  FROM prop1 p1, prop2 p2, prop3 p3; -- 隐式交叉连接，等价于CROSS JOIN
  
  RETURN NEW; -- 触发器需返回NEW/OLD，根据触发时机调整
END;
$$ LANGUAGE plpgsql;

-- 创建触发器
CREATE TRIGGER trigger_after_insert_source
AFTER INSERT ON source_table
FOR EACH ROW EXECUTE FUNCTION insert_target_trigger();

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