PostgreSQL函数执行报错：bigint类型输入语法无效，求解决方案

核心错误原因 #

错误ERROR: invalid input syntax for type bigint: "expenseid"由运算符优先级导致：
PostgreSQL中类型转换运算符::的优先级高于JSON提取运算符->>，所以j.invoice->>'expenseid'::bigint会被解析为：先尝试将字符串'expenseid'转换为bigint类型，这显然不符合逻辑，自然报错。正确写法是先提取JSON字段值，再转换类型，需给提取表达式加括号：(j.invoice->>'expenseid')::bigint。

其他待修复问题 #

除优先级问题外，函数与调用语句还有两处不匹配：

1. JSON结构不匹配：调用传入的是单个发票对象，但函数用jsonb_array_elements(invoice)将其当作数组处理，导致无法正确提取数据；
2. 明细键名不匹配：调用的JSON中明细数组的键是list，但函数中写的是invoice ->'lineitems'，会导致找不到明细数据。

修正后的完整代码 #

修复后的PL/pgSQL函数 #

CREATE OR REPLACE FUNCTION insertorupdateinvoice(invoice jsonb)
     RETURNS void
LANGUAGE plpgsql
AS $BODY$
Declare _invoiceid bigint;
begin
  -- 插入主表：直接访问单个JSON对象，无需拆分数组
  insert into invoicemaster (expenseid, invoiceno, transactiondate, totalinvoiceamount, invoicedoc, createdby, createdon)
  select (invoice->>'expenseid')::bigint, 
         invoice->>'invoiceno', 
         (invoice->>'transactiondate')::date, 
         (invoice->>'totalinvoiceamount')::double precision, 
         invoice->>'invoicedoc', 
         (invoice->>'createdby')::bigint, 
         (invoice->>'createdon')::timestamp without time zone
  returning invoiceid into _invoiceid;
  
  -- 插入明细表：使用正确的键名`list`，修复类型转换的括号问题
  insert into lineitemmaster (invoiceid, transactiondate, merchantname, amount, departmentid, policyid, itemdescription, 
                              itemcategory, itemtype, status, isrejected, createdby, createdon)
  select _invoiceid, 
         (x->>'transactiondate')::date, 
         x->>'merchantname', 
         (x->>'amount')::double precision, 
         (x->>'departmentid')::integer, 
         (x->>'policyid')::integer, 
         x->>'itemdescription', 
         (x->>'itemcategory')::integer, 
         (x->>'itemtype')::integer, 
         (x->>'status')::boolean, 
         (x->>'isrejected')::boolean, 
         (x->>'createdby')::bigint, 
         now() -- 若JSON中包含createdon字段，可替换为(x->>'createdon')::timestamp
  from jsonb_array_elements(invoice ->'list') as x;
end;
$BODY$;

修正后的调用语句（注：若表中createdon字段非空，需补充该字段值） #

select insertorupdateinvoice('{"expenseid":1,
    "invoiceno":"04012022",
    "transactiondate":"2022-01-04",
    "totalinvoiceamount":1000.00,
    "invoicedoc":"invoicedoc",
    "createdby":"1",
    "createdon":"2022-01-04 10:00:00",
    "list":[
      {"transactiondate":"2022-01-01", "merchantname":"Apple", "amount":"100.50", "departmentid":"1","policyid":"1", "itemdescription":"iphone 14 pro max", "itemcategory":"55", "itemtype":"499", "status":"true", "isrejected":"false", "createdby":"1"},
      {"transactiondate":"2022-01-02", "merchantname":"Samsung", "amount":"1050.35", "departmentid":"2","policyid":"2", "itemdescription":"samsung galaxy tab", "itemcategory":"40", "itemtype":"50", "status":"true", "isrejected":"false", "createdby":"1"},
      {"transactiondate":"2022-01-03", "merchantname":"Big bazar", "amount":"555.75", "departmentid":"3","policyid":"3", "itemdescription":"grocerry", "itemcategory":"5", "itemtype":"90", "status":"false", "isrejected":"false", "createdby":"1"}
    ]}');

关键修复点总结 #

• 所有JSON字段提取+类型转换的表达式必须加括号：(json_field->>'key')::type，规避运算符优先级问题；
• 根据传入的JSON结构调整处理逻辑：单个对象直接访问，数组才用jsonb_array_elements；
• 确保函数中引用的JSON键名与传入的JSON完全匹配（如list vs lineitems）。

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