更新更改的值
插入新值删除表格行
行输入中不存在（比较具有group_id和item_id的行）
如果你想做upsert，你必须用唯一约束upsert。所以有两个唯一约束。主键（id），（group_id，item_id）。冲突插入需要考虑这两个唯一约束。
因为你想把items[]类型传递给函数，所以这也意味着任何不在输入函数参数中的id也会被删除。

drop table if exists items cascade;
begin;
CREATE TABLE items(
   id bigint GENERATED BY DEFAULT as identity PRIMARY KEY,
   group_id INT NOT NULL,
   item_id INT NOT NULL,
   name TEXT
   ,unique(group_id,item_id)
);
insert into items values 
(38,1,1283,'original_38'),
(39,1,1471,'original_39'),
(40,1,1333,'original_40'),
(42,1,1332,'original_42');
end;

主要功能：

CREATE OR REPLACE FUNCTION update_items (in_items items[])
    RETURNS boolean
    AS $FUNC$
DECLARE
    iter items;
    saved_ids bigint[];
BEGIN
    saved_ids := (SELECT ARRAY (SELECT (unnest(in_items)).id));
    DELETE FROM items
    WHERE NOT (id = ANY (saved_ids));
    FOREACH iter IN ARRAY in_items LOOP
        INSERT INTO items
        SELECT
            iter.*
        ON CONFLICT (id)
            DO NOTHING;
        INSERT INTO items
        SELECT
            iter.*
        ON CONFLICT (group_id,
            item_id)
            DO UPDATE SET
                name = EXCLUDED.name;
        RAISE NOTICE 'rec.groupid: %, rec.items_id:%', iter.group_id, iter.item_id;
    END LOOP;
    RETURN TRUE;
END
$FUNC$
LANGUAGE plpgsql;

称之为：

SELECT
    *
FROM
    update_items ('{"(38, 1, 1283, Name1) "," (39, 1, 1471, Name2) "," (40, 1, 1333, Name3)"}'::items[]);

参考文献：

• Iterating over integer[] in PL/pgSQL
• How to match elements in an array of composite type?
• PostgreSQL中的IN与ANY运算符

如果有人想做同样的事情，下面是我如何用DELETE缺失的行实现UPSERT的。

CREATE OR REPLACE FUNCTION update_items(in_rows items[]) RETURNS INT AS $$
DECLARE
  in_groups INTEGER[];
  in_group_id INTEGER;
  in_item_ids INTEGER[];
BEGIN
  -- get single group id from input rows, fail if multiple group ids present in input
  in_groups = (SELECT ARRAY (SELECT distinct(group_id) FROM UNNEST(in_rows)));
  IF ARRAY_LENGTH(in_groups,1)>1 THEN
    RAISE EXCEPTION 'Multiple group_ids found in input items: %', in_groups;
  END IF;
  in_group_id = in_groups[1];
  -- delete items of this group that are absent in in_rows
  in_item_ids := (SELECT ARRAY (SELECT (UNNEST(in_rows)).item_id));
  DELETE FROM items
    WHERE 
      master_code <> ANY (in_item_ids)
      AND group_id = in_group_id;
  -- upsert in_rows
  INSERT INTO items 
    SELECT * FROM UNNEST(in_rows)
    ON CONFLICT (group_id,item_d)
      DO UPDATE SET
        parent_group_id = EXCLUDED.parent_group_id,
        mat_centre_id = EXCLUDED.mat_centre_id,
        NAME = EXCLUDED.NAME,
        opening_date = EXCLUDED.opening_date;
  RETURN in_group_id;
  -- return success of transaction (boolean)
END;
$$ LANGUAGE plpgsql;

此函数用于删除in_rows中缺少的行