I am a newbie to PostgreSQL. I have created below PostgreSQL function and getting an error as below:

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$;

I am executing function as below:

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 I am getting as below:

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

CodePudding user response：

So, I got an solution for this one as below as do not use jsonb name while extracting the values.

    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$;