PostgreSQL触发器更新JSON字段报错：jsonb_set参数类型不匹配

问题背景 #

现有两张PostgreSQL表：

margins表 #

create table margins (
  id serial primary key,
  margins JSON,
  created_at TIMESTAMP NOT NULL,
  institution_uuid UUID NOT NULL,
  created_by VARCHAR
);

margin_defaults表 #

create table margin_defaults (
  id serial primary key,
  model VARCHAR(255) NOT NULL,
  margin FLOAT NOT NULL,
  created_by VARCHAR(255) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

margins表的margins列存储JSON数组，示例插入语句：

insert into margins (margins, created_at, institution_uuid, created_by) VALUES ('[{"model": "samsungS23","margin": "0.5","type": "CUSTOM"},{"model": "iphone","margin": "0.2","type": "CUSTOM"},{"model": "pixel","margin": "0.2","type": "CUSTOM"}]', '2023-12-12 15:38:40.642428', '51d8060e-5a31-4575-b56d-c5100e94d614', 'test-runner') RETURNING *;

需求：当margin_defaults表插入新记录时，获取margins表中每个institution_uuid的最新记录，更新其中type为DEFAULT且model与新插入记录匹配的对象的margin值，并插入新的margins记录。

报错信息 #

编写触发器后出现如下报错：

ERROR:  function jsonb_set(jsonb, text[], double precision) does not exist
LINE 3:      jsonb_set(r.margins::jsonb, ('{' || tmp_position || ',m...
             ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
QUERY:  INSERT INTO margins (institution_uuid, margins, created_by) VALUES (
                    r.institution_uuid,
                    jsonb_set(r.margins::jsonb, ('{' || tmp_position || ',margin}')::text[], NEW.margin)::json,
                    NEW.created_by
                )
CONTEXT:  PL/pgSQL function update_margins_default_values_function() line 10 at SQL statement

现有触发器代码 #

CREATE OR REPLACE FUNCTION update_margins_default_values_function()
RETURNS TRIGGER AS $$
DECLARE r RECORD;
DECLARE tmp_position int;
BEGIN
        FOR r IN
                SELECT DISTINCT ON (b.institution_uuid) * FROM (SELECT DISTINCT ON (institution_uuid) * FROM margins ORDER BY institution_uuid, created_at DESC) as b WHERE b.margins::jsonb@>'[{"type":"DEFAULT"}]' ORDER BY institution_uuid
        LOOP
            SELECT position FROM jsonb_array_elements(r.margins::jsonb) with ordinality arr(elem, position) INTO tmp_position WHERE elem->>'model'=NEW.model AND elem->>'type'='DEFAULT';
            IF found THEN
                INSERT INTO margins (institution_uuid, margins, created_by) VALUES (
                    r.institution_uuid,
                    jsonb_set(r.margins::jsonb, ('{' || tmp_position || ',margin}')::text[], NEW.margin)::json,
                    NEW.created_by
                );
            END IF;
        END LOOP;
    RETURN 1;
END;
$$ LANGUAGE plpgsql;


CREATE OR REPLACE TRIGGER update_margins_default_values_trigger
    AFTER INSERT ON margin_defaults FOR EACH ROW EXECUTE PROCEDURE update_margins_default_values_function();

问题分析与修正 #

报错核心原因：jsonb_set函数的第三个参数要求是jsonb类型，但NEW.margin是float类型，未进行类型转换。此外还有两个隐含问题：

1. jsonb_array_elements的ordinality返回的位置从1开始计数，但JSON数组索引从0开始，直接使用会导致索引越界或修改错误元素。
2. 原查询获取最新记录的语句冗余，且未处理一个JSON数组中存在多个匹配model和type元素的情况。

修正后的触发器代码 #

CREATE OR REPLACE FUNCTION update_margins_default_values_function()
RETURNS TRIGGER AS $$
DECLARE r RECORD;
DECLARE updated_margins jsonb;
BEGIN
    -- 直接获取每个institution_uuid的最新记录，简化查询逻辑
    FOR r IN
        SELECT DISTINCT ON (institution_uuid) * 
        FROM margins 
        WHERE margins::jsonb @> '[{"type":"DEFAULT"}]'
        ORDER BY institution_uuid, created_at DESC
    LOOP
        -- 遍历并更新所有匹配的元素，支持多个匹配项的场景
        SELECT jsonb_agg(
            CASE 
                WHEN elem->>'model' = NEW.model AND elem->>'type' = 'DEFAULT'
                THEN elem || jsonb_build_object('margin', NEW.margin)
                ELSE elem
            END
        ) INTO updated_margins
        FROM jsonb_array_elements(r.margins::jsonb) AS elem;
        
        -- 仅当数组内容确实修改时插入新记录，避免无效操作
        IF updated_margins != r.margins::jsonb THEN
            INSERT INTO margins (institution_uuid, margins, created_by, created_at)
            VALUES (
                r.institution_uuid,
                updated_margins::json,
                NEW.created_by,
                NOW()
            );
        END IF;
    END LOOP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE TRIGGER update_margins_default_values_trigger
    AFTER INSERT ON margin_defaults 
    FOR EACH ROW 
    EXECUTE PROCEDURE update_margins_default_values_function();

修正说明 #

1. 类型转换：通过jsonb_build_object('margin', NEW.margin)将float类型的margin值转为jsonb类型，再合并到原JSON元素中。
2. 索引问题：用jsonb_agg重新构建数组，避免手动处理索引的麻烦，同时支持修改多个匹配元素。
3. 查询简化：去掉冗余子查询，直接通过DISTINCT ON (institution_uuid)获取每个机构的最新记录。
4. 字段补全：插入新记录时显式设置created_at为当前时间，符合表结构要求。
5. 无效操作过滤：只有当数组内容发生变化时才插入新记录，减少不必要的数据写入。

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