PostgreSQL 13.7中JSONB列更新触发器报错问题求助

错误原因 #

函数中直接执行了SELECT语句但未指定结果去向，PL/pgSQL不允许无目的地执行SELECT——如果不需要保留结果，需用PERFORM替代；如果需要结果，需将其存入变量。此外原函数还存在两个关键问题：

• 错误地查询了整个random.model表，触发器应仅处理当前更新的行（OLD和NEW记录）
• INSERT语句中硬编码字符串，未实际代入OLD/NEW行的真实数据

修正方案 #

1. 重新编写触发器函数 #

以下函数针对当前更新行处理JSONB数据，正确构造变更记录：

CREATE OR REPLACE FUNCTION track_model_update()
  RETURNS TRIGGER 
  LANGUAGE PLPGSQL
  AS
$$
DECLARE
    old_lists jsonb;
    new_lists jsonb;
BEGIN
    -- 从当前更新的旧行、新行中提取JSONB数组并构造目标结构
    SELECT jsonb_agg(jsonb_build_object('name', elem #>> '{name}', 'value', elem #>> '{value}'))
    INTO old_lists
    FROM jsonb_array_elements(OLD.inputs #> '{lists}') elem;

    SELECT jsonb_agg(jsonb_build_object('name', elem #>> '{name}', 'value', elem #>> '{value}'))
    INTO new_lists
    FROM jsonb_array_elements(NEW.inputs #> '{lists}') elem;

    -- 当value字段发生变化时写入追踪表
    IF NEW.value <> OLD.value THEN
         INSERT INTO random.model_tracker(userid, modelid, datetime, oldjsoninput, newjsoninput, action)
         VALUES (
             OLD.user_id,
             OLD.id,
             NOW(),
             jsonb_build_object('lists', old_lists),
             jsonb_build_object('lists', new_lists),
             'UPDATE'
         );
    END IF;

    RETURN NEW;
END;
$$;

2. 创建触发器（避免与函数同名） #

CREATE TRIGGER model_update_trigger
AFTER UPDATE
ON random.model
FOR EACH ROW
EXECUTE PROCEDURE track_model_update();

关键修正点 #

• 用SELECT ... INTO将JSONB提取结果存入变量，解决"无结果去向"的报错
• 仅处理当前更新的OLD/NEW行，而非全表查询
• 用jsonb_build_object和jsonb_agg构造正确的JSON结构，替代硬编码字符串，确保存入真实的变更数据
• 函数与触发器使用不同名称，避免命名混淆

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