postgreSQL - ERROR: cannot extract elements from an object-CodePudding

I am a newbie to PostgreSQL. I have the below function and it is giving me an error. basically, I am passing JSON as a parameter to the function, extracting values, and inserting it into 2 tables. it is one to many relationships. so based on that one invoice has multiple line items:

PostgreSQL function:

    -- FUNCTION: public.insertorupdateinvoice(jsonb)

    -- DROP FUNCTION IF EXISTS public.insertorupdateinvoice(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)
      select (j.invoice->>'expenseid')::bigint, 
             j.invoice->>'invoiceno'::character, 
             (j.invoice->>'transactiondate')::date, 
             (j.invoice->>'totalinvoiceamount')::double precision, 
             j.invoice->>'invoicedoc'::character, 
             (j.invoice->>'createdby')::bigint,
             NOW()
      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,
            NOW()
      from jsonb_array_elements(invoice ->'lineitems') as x;
    end;
    $BODY$;

    ALTER FUNCTION public.insertorupdateinvoice(jsonb)
        OWNER TO postgres;

calling function as :

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

getting error as below:

ERROR:  cannot extract elements from an object
CONTEXT:  SQL statement "insert into invoicemaster (expenseid, invoiceno, transactiondate, totalinvoiceamount, invoicedoc, createdby, createdon)
      select (j.invoice->>'expenseid')::bigint, 
             j.invoice->>'invoiceno'::character, 
             (j.invoice->>'transactiondate')::date, 
             (j.invoice->>'totalinvoiceamount')::double precision, 
             j.invoice->>'invoicedoc'::character, 
             (j.invoice->>'createdby')::bigint,
             NOW()
      from jsonb_array_elements(invoice) as j(invoice)
      returning invoiceid"
PL/pgSQL function insertorupdateinvoice(jsonb) line 5 at SQL statement
SQL state: 22023

Thanks

CodePudding user response：

try this :

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', 
           (invoice->>'transactiondate') :: date, 
           (invoice->>'totalinvoiceamount') :: double precision, 
           invoice->>'invoicedoc', 
           (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$;

see dbfiddle