如何将JSON数组传入PostgreSQL函数插入表并解决参数格式报错

1. 类型不匹配：你定义的函数参数是json[]（PostgreSQL原生数组类型，元素为JSON对象），但你传入的是单个JSON格式的数组字符串，两者语法规则完全不同，所以触发数组格式错误。
2. 函数内部逻辑错误：json_array_elements入参要求是单个JSON数组类型的值，你给它传PostgreSQL的json[]数组，逻辑不匹配，就算类型传对了也跑不通。

直接用单条INSERT批量插入，不用循环遍历，执行效率远高于逐行插入：

-- 建表语句不变
create table mytable(col1 text, col2 boolean, col3 boolean);

-- 修改函数参数为json类型（直接接收JSON数组格式的入参）
create or replace function fun1(vja json)
returns void as $$
begin
  insert into mytable(col1, col2, col3)
  select 
    (elem ->> 'col1')::text, -- 用->>先取text值再转类型，避免运算符优先级导致的转换错误
    (elem ->> 'col2')::boolean,
    (elem ->> 'col3')::boolean
  from json_array_elements(vja) elem;
end;
$$ language plpgsql;

调用方式直接传合法的JSON数组字符串即可，注意JSON语法不允许数组最后一个元素后加多余逗号：

select fun1('[
    {"col1": "turow1@af.com", "col2": false, "col3": true}, 
    {"col1": "xy2@af.com", "col2": false, "col3": true}
]');

如果你确实需要用PostgreSQL的json[]数组作为参数，修改如下：

create or replace function fun1(vja json[])
returns void as $$
declare
  v json;
begin
  foreach v in array vja
  loop
    insert into mytable(col1, col2, col3)
    values(
      (v ->> 'col1')::text,
      (v ->> 'col2')::boolean,
      (v ->> 'col3')::boolean
    );
  end loop;
end;
$$ language plpgsql;

传参要符合PostgreSQL数组的语法，每个JSON对象作为数组元素：

select fun1(array[
    '{"col1": "turow1@af.com", "col2": false, "col3": true}'::json,
    '{"col1": "xy2@af.com", "col2": false, "col3": true}'::json
]);

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