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

Question

这是一个用于 postgres 9.6 的 plpgsql 函数。它会尝试 INSERT 一行，如果插入没有失败(由于违反键约束)，那么它会运行更多的命令。

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
$$

此函数处理单个记录，但您如何修改它以处理成批的数千条记录？

我找到了一个 answer ，这建议将 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 函数 here它们似乎与此相关，但我无法弄清楚到底要使用哪个。我想做的事情有可能吗？在任何地方使用 JSON 数组 而不是 JSON 对象 是否可以实现这一点？

Answer 1

数千条记录

1. 创建一个输入行的临时表，由您的值 $1、$2、$3 组成.最快的上传方式是COPY - 或 \copy meta-command of psql如果数据不在同一台机器上。让我们假设这张表:

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

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

2. 将您的命令与数据修改 CTE 链接起来。正如我们在您的previous question下确定的那样, 在此特定操作中没有要处理的竞争条件。

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;

第 1 步和第 2 步必须在同一 session (不一定是同一事务)中运行，因为临时表的范围绑定(bind)到同一 session .

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

相关:

• COPY command: copy only specific columns from csv

只是一些记录

有多种选择。将 JSON 数组传递给函数的想法就是其中之一。如果对象与目标表匹配，则可以使用 json_populate_recordset()在单个 INSERT 查询中。或者只使用没有函数包装器的 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() 具有多个参数，可以在单个查询中完成所有操作:

• Unnest multiple arrays in parallel

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