PostgreSQL触发器提取JSON字段报错，求解决方法

问题场景 #

有一个传递大量JSON数据的应用，将记录存入PostgreSQL的json_testing.test_table表（含json_col jsonb字段），希望通过触发器提取JSON字段到json_testing.some_other_table做分析，但触发器函数运行报错。

相关代码 #

表结构

create table if not exists json_testing.test_table
(
    json_col jsonb
);

触发器定义

create or replace trigger split_record
    after insert
    on json_testing.test_table
    FOR EACH ROW EXECUTE PROCEDURE record_splitter();

示例JSON数据

{
    "attr1": 1,
    "attr2": 2
}

报错的触发器函数

CREATE OR REPLACE FUNCTION record_splitter()
    RETURNS TRIGGER
    LANGUAGE PLPGSQL
AS
$$
BEGIN
    INSERT INTO json_testing.some_other_table(col1, col2)
    SELECT NEW->'attr_1', NEW->'attr_2';
    RETURN NEW;
END;
$$

报错信息

[2023-03-05 18:17:15] [42883] ERROR: operator does not exist: json_testing.test_table -> unknown
[2023-03-05 18:17:15] Hint: No operator matches the given name and argument types. You might need to add explicit type casts.
[2023-03-05 18:17:15] Where: PL/pgSQL function record_splitter() line 3 at SQL statement

错误原因 #

1. NEW是触发器中的行记录类型，不是JSONB对象，直接用NEW->'attr_1'会被识别为对整个行记录使用JSON操作符，导致类型不匹配，需明确引用行中的JSONB字段NEW.json_col。
2. JSON键名不匹配：示例JSON中的键是attr1/attr2，函数中写成了attr_1/attr_2，即使修复类型问题也会提取到NULL。
3. 若目标表some_other_table的col1/col2是数值类型，直接用->返回的是JSONB类型，需要转换为对应数据类型。

修正后的触发器函数 #

CREATE OR REPLACE FUNCTION record_splitter()
    RETURNS TRIGGER
    LANGUAGE PLPGSQL
AS
$$
BEGIN
    INSERT INTO json_testing.some_other_table(col1, col2)
    VALUES (
        NEW.json_col->>'attr1'::int, -- 转成整数类型（根据目标表字段类型调整）
        NEW.json_col->>'attr2'::int
    );
    -- 若目标字段接受JSONB类型，可直接用：NEW.json_col->'attr1', NEW.json_col->'attr2'
    RETURN NEW;
END;
$$

说明：

• 用NEW.json_col明确访问行中的JSONB字段，再通过JSON操作符提取键值。
• 修正键名与示例JSON保持一致：attr1/attr2。
• 使用->>提取文本值后转换为整数，确保和目标表字段类型匹配；如果目标字段是JSONB类型，直接用->即可。

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