PostgreSQL 14：如何编写接收SELECT结果的函数适配语句级触发器？

完全可以实现让the_other_func同时支持INSERT和UPDATE语句级触发器的调用，核心是正确传递语句级触发器产生的多行伪表数据，并定义通用的函数参数类型。

1. 选择合适的参数类型 #

语句级触发器的NEW TABLE/OLD TABLE是多行数据集，不能直接传递单条记录，常用两种处理方式：

• 使用对应表的行类型数组（my_table[]），强类型更严谨
• 转换成JSONB数组，兼容性强，方便通用处理

这里以JSONB数组为例展开，兼顾灵活性和易用性。

2. 修正触发器函数与定义通用函数 #

第一步：定义the_other_func

让它接收JSONB数组参数，适配INSERT/UPDATE场景的多行数据传递：

CREATE FUNCTION the_other_func(p_records JSONB)
RETURNS JSONB
AS $$
    -- 示例逻辑：返回记录总数和第一条数据详情
    SELECT jsonb_build_object(
        'total_count', jsonb_array_length(p_records),
        'first_record', p_records -> 0
    );
$$ LANGUAGE sql;

第二步：修正INSERT触发器函数

原代码中直接SELECT * FROM my_inserted_records会因多行返回报错，需用jsonb_agg将伪表转换成JSONB数组：

CREATE OR REPLACE FUNCTION my_trigger_func()
RETURNS TRIGGER
AS $$
BEGIN
    PERFORM the_other_func(
        (SELECT jsonb_agg(to_jsonb(t)) FROM my_inserted_records t)
    );
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

第三步：创建UPDATE语句级触发器及函数

利用REFERENCING子句同时获取新旧记录伪表，按需传递给the_other_func：

-- UPDATE触发器
CREATE TRIGGER my_update_trg
AFTER UPDATE ON my_table
REFERENCING
    OLD TABLE AS my_old_records
    NEW TABLE AS my_new_records
FOR EACH STATEMENT
EXECUTE PROCEDURE my_update_trigger_func();

对应的UPDATE触发器函数：

CREATE FUNCTION my_update_trigger_func()
RETURNS TRIGGER
AS $$
BEGIN
    -- 分别传递旧记录和新记录的JSONB数组
    PERFORM the_other_func(
        (SELECT jsonb_agg(to_jsonb(t)) FROM my_old_records t)
    );
    PERFORM the_other_func(
        (SELECT jsonb_agg(to_jsonb(t)) FROM my_new_records t)
    );
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

3. 优化：通用触发器函数（兼容INSERT/UPDATE） #

可以将触发器函数合并，通过TG_OP变量判断当前操作类型，减少重复代码：

CREATE OR REPLACE FUNCTION universal_trigger_func()
RETURNS TRIGGER
AS $$
BEGIN
    CASE TG_OP
        WHEN 'INSERT' THEN
            PERFORM the_other_func(
                (SELECT jsonb_agg(to_jsonb(t)) FROM my_inserted_records t)
            );
        WHEN 'UPDATE' THEN
            PERFORM the_other_func(
                (SELECT jsonb_agg(to_jsonb(t)) FROM my_old_records t)
            );
            PERFORM the_other_func(
                (SELECT jsonb_agg(to_jsonb(t)) FROM my_new_records t)
            );
    END CASE;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

统一调用该函数创建触发器：

-- INSERT触发器
CREATE TRIGGER my_insert_trg
AFTER INSERT ON my_table
REFERENCING
    NEW TABLE AS my_inserted_records
FOR EACH STATEMENT
EXECUTE PROCEDURE universal_trigger_func();

-- UPDATE触发器
CREATE TRIGGER my_update_trg
AFTER UPDATE ON my_table
REFERENCING
    OLD TABLE AS my_old_records
    NEW TABLE AS my_new_records
FOR EACH STATEMENT
EXECUTE PROCEDURE universal_trigger_func();

关键注意事项 #

• PostgreSQL 14完全支持语句级触发器的REFERENCING子句（该特性从PostgreSQL 10开始引入）
• 必须用jsonb_agg或array_agg将多行伪表转换成集合/数组，否则会因返回多行报错
• 如果偏好强类型处理，可将the_other_func的参数改为my_table[]（行类型数组），触发器中用array_agg(t)传递数据

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