postgressql - 错误：类型为bigint："column_name"的输入语法无效

Question

我是PostgreSQL的新手。我已经创建了下面的PostgreSQL函数，并得到如下错误:

ERROR:  invalid input syntax for type bigint: "expenseid"

CREATE OR REPLACE FUNCTION insertorupdateinvoice(invoice jsonb)
RETURNS void
LANGUAGE plpgsql
AS $BODY$
Declare _invoiceid bigint;
begin
--Consider all columns in specialist table as character varying and code column as integer.
insert into invoicemaster (expenseid, invoiceno, transactiondate, totalinvoiceamount, invoicedoc, createdby, createdon)
select j.invoice->>'expenseid'::bigint, 
j.invoice->>'invoiceno', 
(j.invoice->>'transactiondate')::date, 
j.invoice->>'totalinvoiceamount'::double precision, 
j.invoice->>'invoicedoc', 
j.invoice->>'createdby'::bigint, 
(j.invoice->>'createdon')::timestamp without time zone
from jsonb_array_elements(invoice) as j(invoice)
returning invoiceid into _invoiceid;

insert into lineitemmaster (invoiceid, transactiondate, merchantname, amount, departmentid, policyid, itemdescription, 
itemcategory, itemtype, status, isrejected, createdby, createdon)
select _invoiceid::bigint, 
x.invoice->>'transactiondate'::date, 
x.invoice->>'merchantname', 
x.invoice->>'amount'::double precision, 
x.invoice->>'departmentid'::integer, 
x.invoice->>'policyid'::integer, 
x.invoice->>'itemdescription', 
x.invoice->>'itemcategory'::integer, 
x.invoice->>'itemtype'::integer, 
x.invoice->>'status'::boolean, 
x.invoice->>'isrejected'::boolean, 
x.invoice->>'createdby'::bigint, 
(x.invoice->>'createdon')::timestamp without time zone
from jsonb_array_elements(invoice ->'lineitems') as x;
end;
$BODY$;

我执行的函数如下:

select * from insertorupdateinvoice('{"expenseid":1,
"invoiceno":"04012022",
"transactiondate":"2022-01-04",
"totalinvoiceamount":1000.00,
"invoicedoc":"invoicedoc",
"createdby":"1",
"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"}
]}');

错误我得到如下:

ERROR:  invalid input syntax for type bigint: "expenseid"
LINE 2:   select j.invoice->>'expenseid'::bigint, 
^
QUERY:  insert into invoicemaster (expenseid, invoiceno, transactiondate, totalinvoiceamount, invoicedoc, createdby, createdon)
select j.invoice->>'expenseid'::bigint, 
j.invoice->>'invoiceno', 
(j.invoice->>'transactiondate')::date, 
j.invoice->>'totalinvoiceamount'::double precision, 
j.invoice->>'invoicedoc', 
j.invoice->>'createdby'::bigint, 
(j.invoice->>'createdon')::timestamp without time zone
from jsonb_array_elements(invoice) as j(invoice)
returning invoiceid
CONTEXT:  PL/pgSQL function insertorupdateinvoice(jsonb) line 5 at SQL statement
SQL state: 22P02

Answer 1

所以，我得到了一个解决方案，如下不使用jsonb名称，同时提取的值。

CREATE OR REPLACE FUNCTION public.insertorupdateinvoice(
invoice jsonb)
RETURNS void
LANGUAGE 'plpgsql'
COST 100
VOLATILE PARALLEL UNSAFE
AS $BODY$
Declare _invoiceid bigint;
begin
insert into invoicemaster (expenseid, invoiceno, transactiondate, totalinvoiceamount, invoicedoc, createdby, createdon)
values ( (invoice->>'expenseid') :: bigint, 
(invoice->>'invoiceno') :: character, 
(invoice->>'transactiondate') :: date, 
(invoice->>'totalinvoiceamount') :: double precision, 
(invoice->>'invoicedoc') :: character, 
(invoice->>'createdby') :: bigint,
NOW()
)
returning invoiceid into _invoiceid;
insert into lineitemmaster (invoiceid, transactiondate, merchantname, amount, departmentid, policyid, itemdescription, 
itemcategory, itemtype, status, isrejected, createdby, createdon)
select _invoiceid::bigint, 
(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()
from jsonb_array_elements(invoice ->'lineitems') as x;
end;
$BODY$;