如何将元组数组作为参数实现批量UPSERT存储过程？

问题背景 #

现有标签表结构：

CREATE TABLE tag (
  tag_id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  tag_slug text UNIQUE NOT NULL,
  tag_name text NOT NULL
);

原本使用JavaScript + Knex.raw实现批量Upsert：

knex.raw(
  `
    INSERT INTO tag (tag_slug, tag_name)
    VALUES ${tags.map(() => `(?, ?)`).join(", ")}
    ON CONFLICT (tag_slug)
    DO UPDATE SET tag_name = excluded.tag_name
    RETURNING *
  `,
  tags.map((t) => [t.slug, t.text]).flat()
)

希望将逻辑转为名为upsert_tags的存储过程，期望调用方式如下（可调整签名）：

call upsert_tags(
  [
    ('first-tag', 'First Tag'),
    ('second-tag', 'Second Tag')
  ]
);

自身尝试的代码无法运行：

CREATE PROCEDURE upsert_tags(tags array)
LANGUAGE SQL
AS $$
  INSERT INTO tag (tag_slug, tag_name) 
  VALUES (unnest(tags))
  ON CONFLICT (tag_slug)
  DO UPDATE SET tag_name = excluded.tag_name
  RETURNING *
$$

注：返回值仅需tag_id，用于录入多对多关联表记录。

解决方案 #

方案1：使用自定义类型的存储过程（推荐，结构清晰） #

首先定义存储过程需要的自定义类型：

CREATE TYPE tag_pair AS (slug text, name text);

然后创建带输出参数的存储过程：

CREATE PROCEDURE upsert_tags(tags tag_pair[], OUT inserted_tag_ids int[])
LANGUAGE plpgsql
AS $$
BEGIN
  WITH upserted AS (
    INSERT INTO tag (tag_slug, tag_name)
    SELECT (t).slug, (t).name FROM unnest(tags) t
    ON CONFLICT (tag_slug)
    DO UPDATE SET tag_name = excluded.tag_name
    RETURNING tag_id
  )
  SELECT array_agg(tag_id) INTO inserted_tag_ids FROM upserted;
END;
$$;

调用示例：

CALL upsert_tags(
  ARRAY[
    ('first-tag', 'First Tag')::tag_pair,
    ('second-tag', 'Second Tag')::tag_pair
  ]
);

调用后会输出包含所有操作后tag_id的数组。

方案2：使用二维文本数组的存储过程（无需自定义类型） #

如果不想创建自定义类型，可以直接用二维文本数组作为参数：

CREATE PROCEDURE upsert_tags(tags text[][], OUT inserted_tag_ids int[])
LANGUAGE plpgsql
AS $$
BEGIN
  WITH upserted AS (
    INSERT INTO tag (tag_slug, tag_name)
    SELECT t[1], t[2] FROM unnest(tags) t
    ON CONFLICT (tag_slug)
    DO UPDATE SET tag_name = excluded.tag_name
    RETURNING tag_id
  )
  SELECT array_agg(tag_id) INTO inserted_tag_ids FROM upserted;
END;
$$;

调用示例：

CALL upsert_tags(
  ARRAY[
    ['first-tag', 'First Tag'],
    ['second-tag', 'Second Tag']
  ]
);

方案3：用函数替代存储过程（返回结果更直接） #

如果更倾向于直接返回tag_id集合，用函数会更方便：

CREATE FUNCTION upsert_tags(tags tag_pair[])
RETURNS SETOF int
LANGUAGE sql
AS $$
  INSERT INTO tag (tag_slug, tag_name)
  SELECT (t).slug, (t).name FROM unnest(tags) t
  ON CONFLICT (tag_slug)
  DO UPDATE SET tag_name = excluded.tag_name
  RETURNING tag_id;
$$;

调用示例：

SELECT * FROM upsert_tags(
  ARRAY[
    ('first-tag', 'First Tag')::tag_pair,
    ('second-tag', 'Second Tag')::tag_pair
  ]
);

原代码问题说明 #

1. 参数类型不明确：原代码中tags array未指定数组元素的具体类型，PostgreSQL无法解析元素结构
2. 数组展开方式错误：VALUES (unnest(tags))写法不正确，unnest返回的是行数据，需要用SELECT语句展开为列
3. 返回值处理问题：SQL语言的存储过程无法直接处理返回结果的聚合，改用plpgsql可以更灵活地处理输出参数

内容的提问来源于stack exchange，提问作者jrz