PGSQL调用函数报invalid input syntax for type json错误如何解决？

问题根源 #

你遇到的两次报错分别对应两个错误：

1. 首次调用时将PostgreSQL的类型转换语法::jsonb[]写入了JSON字符串内部，导致JSON格式不符合规范，触发语法错误
2. 第二次调用时JSON格式合法，但函数内部变量类型与JSON操作的返回值类型不匹配：你将_data定义为PostgreSQL原生数组类型jsonb[]，但通过#>>操作符从JSON中提取的数组是JSON格式的字符串，二者无法直接赋值

修正方案 #

1. 修正函数定义 #

你需要调整函数内部的变量类型和赋值逻辑，有两种可选方案：

方案A：直接存储JSON数组（推荐，逻辑更简单）

CREATE OR REPLACE FUNCTION public.my_func(
    data json)
    RETURNS SETOF json 
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
    ROWS 1000

AS $BODY$
DECLARE
    _id bigint;
    _data jsonb; -- 改为jsonb类型存储JSON数组
BEGIN
    -- #>>返回text类型，需要显式转bigint，赋值用:=更符合plpgsql规范
    _id := (data #>> '{id}')::bigint;
    -- 提取data字段转为jsonb类型
    _data := (data -> 'data')::jsonb;
    
    -- 此处补充你后续的业务逻辑
    RETURN NEXT json_build_object('id', _id, 'data', _data);
    RETURN;
END
$BODY$;

方案B：将JSON数组转为PostgreSQL原生jsonb数组

如果你确实需要使用PG原生数组操作，可以用jsonb_array_elements+array_agg转换：

CREATE OR REPLACE FUNCTION public.my_func(
    data json)
    RETURNS SETOF json 
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
    ROWS 1000

AS $BODY$
DECLARE
    _id bigint;
    _data jsonb[];
BEGIN
    _id := (data #>> '{id}')::bigint;
    -- 将JSON数组转为PG原生jsonb数组
    SELECT array_agg(elem) INTO _data 
    FROM jsonb_array_elements((data -> 'data')::jsonb) elem;
    
    -- 此处补充你后续的业务逻辑
    RETURN NEXT json_build_object('id', _id, 'data', to_jsonb(_data));
    RETURN;
END
$BODY$;

2. 修正调用语句 #

不要将PG的类型转换语法写入JSON字符串内部，直接传入合法JSON即可：

select * from my_func ('{
    "id":"26",
    "data":[
        {"id":223,"title":"A"},
        {"id":284,"title":"B","count":1495}
    ]
}'::json) as info;

优化建议 #

PostgreSQL目前更推荐使用jsonb类型替代json类型，jsonb支持索引、查询效率更高，你可以直接将函数入参改为jsonb类型，减少后续类型转换的代码。

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