Блог

Пытаюсь обработать запрос Put / Patch в приведенном ниже SP вместе с insert, если получены новые первичные значения, если я получаю ограничение уникального ключа, которое в нашем случае является item_id

Первичный ключ: ItemId

1. Если нет ограничения уникального ключа, вставьте в представление
2. Если возникает ограничение уникального ключа, т.е. json, который имеет тот же ItemId, обновляет его свойства, а именно. количество, статус и т. Д.

Хранимая процедура

 CREATE OR REPLACE FUNCTION sp_post_items1(i_data jsonb)
RETURNS TABLE(
   fulfiller_id varchar, 
   item_id varchar, 
   order_id varchar, 
   status_id integer, 
   item_updated_time timestamp without time zone)
AS $function$
DECLARE
    itemId1 varchar := null;
    statusId1 integer := 1000;
    quantity1 numeric;
begin
--SELECT t->>'itemId' itemId ,t->>'statusId' statusId,(t->>'quantity')::numeric quantity INTO itemId1,statusId1,quantity1 FROM jsonb_array_elements(i_data  -> 'items') t ;
   INSERT INTO vw_item_status_detail(
      fulfiller_id,
      item_id,
      order_id,
      status_id,
      sku_code,
      decoration_technology,
      quantity,
      item_updated_time)
   SELECT 
      i_data->>'fulfillerId' fulfillerId, 
      t->>'itemId' itemId, 
      i_data->>'orderId' orderId, 
      1000,
      t->>'skuCode' skuCode,
      t->>'decorationTechnology' decorationTechnology,
      (t->>'quantity')::numeric quantity ,
      NOW()
   FROM jsonb_array_elements(i_data  -> 'items') t ;
   exception when unique_violation then
  update vw_item_status_detail v1 set quantity = coalesce (quantity1 , V1.quantity ), status_id = coalesce (statusId1, V1.status_id ), item_updated_time = now() where v1.item_id = itemId1 ; 
  
   RETURN QUERY SELECT 
         v.fulfiller_id fulfiller_id,
         v.item_id item_id,
         v.order_id order_id,
         v.status_id status_id, 
         v.item_updated_time item_updated_time 
   FROM vw_item_status_detail v 
   WHERE (v.order_id = (SELECT i_data->>'orderId') )
   AND (v.fulfiller_id = (SELECT i_data->>'fulfillerId'));
END;
$function$
LANGUAGE plpgsql;

  

Пример вызова sp вместе с JSON

 select * from sp_post_items1('{"orderId": "newtestput1",
 "fulfillerId":"kv0fdt6cx7",
"orderDetailsUrl":"het",
"items":[
    {
        "attributes":
            [{"name":"OracleSku","value":"DWj"},{"name":"taskId","value":"33a1595-e36769876c52"},{"name":"height","value":"5.5"},
            {"name":"width","value":"32.004"},{"name":"productFamily","value":"DWT"},{"name":"template","value":"GI-AST70W"},
            {"name":"labelInfo","value":"DWU-ROHS,LMQ-DBLU-BLU"},{"name":"decorationTechnology","value":"laserEngraving"},
            {"name":"material","value":"Rubber LMQ   LHY"},{"name":"XYZ_barcode","value":"21234348.1"},{"name":"orderType","value":"text"},
            {"name":"scheduledShipDate","value":"2020-09-12T23:59:00"},{"name":"orderReference","value":"21235677.9"},{"name":"docRefUrl","value":""},{"name":"additionalInfo","value":""}],
        "decorationTechnology":"laserEngraving","itemDescription":"Test Sku for Oracle testing",
"itemId":"item1",
        "manufacturingUrl":"htighess",
        "skuCode":"CIM-QYXB3789","productName":"Test Sku for Oracle testing","quantity":"2000","taskId":"33a1ea44-1f45-4f2d-9595-e36769876c52"
    },
    {
        "attributes":
            [{"name":"OracleSku","value":"DWT-XXX-B3LUX-BB-C"},{"name":"taskId","value":"33a1ea44-1f45-4f6c52"},{"name":"height","value":"5.5"},
            {"name":"width","value":"32.004"},{"name":"productFamily","value":"DWT"},{"name":"template","value":"GIFTSET-DWT-INDX-AST70W"},
            {"name":"labelInfo","value":"DWK-DBLU-BLU"},{"name":"decorationTechnology","value":"laserEngraving"},
            {"name":"material","value":"Rubber LMQ   LHY"},{"name":"XYZ_barcode","value":"21234348.1"},{"name":"orderType","value":"text"},
            {"name":"scheduledShipDate","value":"2020-09-12T23:59:00"},{"name":"orderReference","value":"21235677.9"},{"name":"docRefUrl","value":""},{"name":"additionalInfo","value":""}],
        "decorationTechnology":"laserEngraving","itemDescription":"Test Sku for Oracle testing",
"itemId":"item2",
        "manufacturingUrl":"httpdfg",
        "skuCode":"CIM-QYXB3789","productName":"Test Sku for Oracle testing","quantity":"1000","taskId":"33a1edfge36769876c52"
    }
]
}'::jsonb)
  

пробовал использовать цикл for, но это не самый оптимизированный способ обработки такого сценария

Проверенный подход: 1.To создать временную таблицу , но не удалось

В этом случае мы можем использовать комбинацию предложений user from / not in для вставки, а также для обновления