我创建了一个PostgreSQL函数，它接收jsonb作为参数，并希望从jsonb中提取值并将其保存到db。
功能：

CREATE OR REPLACE FUNCTION public.insertorupdatevendorcontactnos(
    contactnos jsonb)
    RETURNS TABLE(vendorhistory_id bigint, vendor_id bigint) 
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$

BEGIN
   
    insert into vendorcontactnos (vendorid, key, value, createdby, createdon)
    select (contactnos->>'vendorid') :: bigint, 
            (x->>'key')::integer, 
            x->>'value', 
            (contactnos->>'createdby') :: integer, 
            NOW()
    from jsonb_array_elements(contactnos ->'items') as x;
    
    INSERT INTO public.vendorcontactnoshistory( vendorid, vendorhistoryid, key, value, createdby, createdon)
    select (contactnos->>'vendorid') :: bigint, 
            (contactnos->>'vendorhistoryid') :: bigint,
            (x->>'key')::integer, 
            x->>'value', 
            (contactnos->>'createdby') :: integer, 
            NOW()
    from jsonb_array_elements(contactnos ->'items') as x;
    
    RETURN QUERY (select (contactnos->>'vendorid') :: bigint, 
            (contactnos->>'vendorhistoryid') :: bigint);
END;
$BODY$;

调用为

select * from insertorupdatevendorcontactnos('[{"vendorid":100,
    "vendorhistoryid":1,
    "createdby":5,
    "items":[
      {"key":1, "value":"+19876543210"},
      {"key":2, "value":"+16543219870"},
      {"key":3, "value":"+13210654987"}
    ]}]');

我需要vendorcontactnos表输出如下：

id vendorid key value         createdby createdon
1  100      1   +19876543210  5         current date time
2  100      2   +16543219870  5         current date time 
3  100      3   +13210654987  5         current date time

未提取vendorid和createdby json值并将其保存到DB表。