使用JSON参数的Postgres批量插入函数

Question

这是一个针对Postgres 9.6的plpgsql函数。它尝试INSERT一行数据，如果插入不会失败（由于键约束违规），则运行几个额外的命令。请保留HTML标记。

CREATE FUNCTION foo(int, text, text)
  RETURNS void AS
$$
BEGIN
  INSERT INTO table1 (id, val1, val2) VALUES ($1, $2, $3) ON CONFLICT DO NOTHING;
  IF FOUND THEN
    INSERT INTO table2 (table1_id, val1) VALUES ($1, $2);
    UPDATE table3 SET (val2, time) = ($3, now()) WHERE table1_id = $1;
  END IF;
END
$$

这个函数处理单个记录，但是你如何修改它以处理成千上万条记录的批处理？

我找到了一个答案，建议将每个3个函数参数都变成数组。但是否有一种方法可以传递更接近于我的应用程序中记录的方式的参数呢？

例如，理想的解决方案是我的应用程序代码调用select foo($1)，其中参数$1是一个JSON对象数组，其中每个内部对象是要插入的记录。

[ 
  { "id": "1", "val1": "1-val1", "val2": "1-val2" },
  { "id": "2", "val1": "2-val1", "val2": "2-val2" },
  { "id": "3", "val1": "3-val1", "val2": "3-val2" },
  { "id": "4", "val1": "4-val1", "val2": "4-val2" }
]

第二个最好的选择是我的应用程序代码调用select foo($1, $2, $3, $4)，其中每个参数都是对应要插入的记录的JSON对象。

{ "id": "1", "val1": "1-val1", "val2": "1-val2" }  // This would be $1
{ "id": "2", "val1": "2-val1", "val2": "2-val2" }  // This would be $2

我正在查看Postgres提供的各种JSON函数，这里有相关内容，但我无法确定应该使用哪个函数。我的目标是否可行？在任何地方使用JSON数组而不是JSON对象是否可以实现这一目标？

- user779159

---

是的，我现在尝试了一下，它可以工作，谢谢！ - user779159

Answer 1

针对数千条记录

1. 创建一个包含你的数据值$1, $2, $3的输入行的临时表。最快的上传方式是使用COPY命令，如果数据不在同一台机器上则可以使用psql的\copy元命令。假设这是我们的表格：

CREATE TEMP TABLE tmp(id int PRIMARY KEY, val1 text, val2 text);

我添加了一个PK约束，这完全是可选的，但它确保我们处理唯一的非null int值。如果您可以保证输入数据的正确性，则不需要该约束。

2. 使用数据修改CTE链接您的命令。正如我们在您的上一个问题中所确定的那样，在此特定操作中没有竞争条件需要处理。

WITH ins1 AS (
   INSERT INTO table1 AS t1 (id, val1, val2)
   SELECT id, val1, val2 FROM tmp ON CONFLICT DO NOTHING
   RETURNING t1.id, t1.val1, t1.val2  -- only actually inserted rows returned
   )
, ins2 AS (
   INSERT INTO table2 (table1_id, val1)
   SELECT id, val1 FROM ins1
   )
UPDATE table3 t3
SET    val2 = i.val2
     , time = now()
FROM   ins1 i
WHERE  t3.table1_id = i.id;

第一步和第二步必须在同一会话中运行（不一定是相同的事务），因为临时表的范围与同一会话绑定。

请注意，UPDATE 只依赖于第一个 INSERT，第二个 INSERT 的成功是有保障的，因为没有 ON CONFLICT DO NOTHING，如果第二个 INSERT 中有任何冲突，则整个操作将被回滚。

相关：

• COPY 命令：从 csv 文件中只复制特定列

仅针对几条记录

有多种选择。将 JSON 数组传递给函数是其中之一。如果对象与目标表匹配，则可以在单个 INSERT 查询中使用 json_populate_recordset()。或者只使用 INSERT (作为预处理语句) 而不使用函数包装器。

INSERT INTO target_tbl  -- it's ok to omit target columns here
SELECT *
FROM   json_populate_recordset(null::target_tbl,  -- use same table type
          json '[{ "id": "1", "val1": "1-val1", "val2": "1-val2" },
                 { "id": "2", "val1": "2-val1", "val2": "2-val2" },
                 { "id": "3", "val1": "3-val1", "val2": "3-val2" },
                 { "id": "4", "val1": "4-val1", "val2": "4-val2" }]');

---

如果只有少量列，您也可以为每个列传递一个数组，并在并行循环中进行遍历。您可以使用数组索引上的简单循环来实现此操作。自Postgres 9.4以来，还有一个方便的 unnest() 函数可以使用多个参数在单个查询中完成所有操作：

• 并行展开多个数组

最佳解决方案取决于您所拥有的数据格式。

- Erwin Brandstetter

---

我看到了你在 https://dev59.com/BGIk5IYBdhLWcg3wkvBn#19147320 关于循环的回答。我该如何循环遍历通过 json_populate_recordset 返回的 recordset （就是你上面回答中的 只需几个记录 部分），以便我可以对每次迭代的循环进行 INSERT INTO、IF FOUND THEN 等操作？ - user779159

@user779159：你不需要循环。像第一个查询中演示的那样链接数据修改CTE。这比为每一行循环要快得多，也更安全。 - Erwin Brandstetter

实际上，我认为最好的结果是将您提出的 json_populate_recordset 和您编写的 CTE 结合起来。因此，在 CTE 中，如果我用 FROM recordset 替换 FROM tmp，在您的意见中这将是一个有效的解决方案？ - user779159

@user779159：是的，这就是我想说的。你可以使用第二个查询的结果，在第一个查询中使用json_populate_recordset()代替临时表。 - Erwin Brandstetter

完美的答案，我正在寻找使用数组或JSON的方法来完成它，而你提到了如何同时完成两种方式！我还学习到了使用临时表和COPY的想法。 - Andy