PostgreSQL中使用关联数组进行批量更新

Question

假设我有一个关联数组（在另一种语言中定义），如下所示：

apply = {
  'qwer': ['tju', 'snf', 'rjtj', 'sadgg']
  'asdf': ['rtj', 'sfm', 'rtjt', 'adjdj']
  ...
  'zxcv': ['qwr', 'trj', '3w4u', '3tt3']
}

我有一张表格如下：

CREATE TABLE apples (
id integer,
name varchar(10),
key varchar(10),
value varchar(10)
);

我希望应用一个更新，如果 apples.value 在 apply 变量的列表之一中，则将 apples.key 设置为数组的键。 如果 apples.value 是 tju，则将 apples.key 设置为 qwer。

我的当前方法如下（混合使用 PostgreSQL 和任何过程性语言）：

for key in apply.keys:
  UPDATE apples SET key=$key
  FROM (SELECT unnest(array($apply[key])) AS value) AS update_table
  WHERE value=update_table.value

我希望以单条语句完成此操作。

- user1005909

---

我不相信在一个字面上的单语句中有这样做的方法。当然，使用函数（存储过程）或智能数据库库在您的代码中可以实现，但是作为纯SQL，我持怀疑态度。尽管如此，我已经点赞了这个问题，因为它很有趣，而且我希望被证明是错误的。 - alzee

@user3137702：总有办法。Postgres拥有大量的字符串函数。通常情况下，在源语言中转换为兼容的数据类型会更明智/更少出错。我添加了一个概念证明。 - Erwin Brandstetter

Answer 1

作为给定示例的概念验证，使用与您显示的字符串格式完全相同：

演示准备好的语句，就像您的客户端可能使用的那样。

PREPARE my_update AS
UPDATE apples a
SET    key = upd.key
FROM  (
   SELECT trim (split_part(key_val, ': ', 1), ' ''') AS key
        , string_to_array(translate(split_part(key_val, ': ', 2), '[]''', ''), ', ') AS val_arr
   FROM   unnest(string_to_array(trim($1, E'{}\n'), E'\n')) key_val
   ) upd
WHERE  a.value = ANY(upd.val_arr);

EXECUTE 可以在同一会话中多次执行：

EXECUTE my_update($assoc_arr${
  'qwer': ['tju', 'snf', 'rjtj', 'sadgg']
  'asdf': ['rtj', 'sfm', 'rtjt', 'adjdj']
  'zxcv': ['qwr', 'trj', '3w4u', '3tt3']
}$assoc_arr$);

SQL Fiddle.

相关：

• 在PostgreSQL中插入带单引号的文本
• 拆分给定字符串并准备case语句

但我更愿意使用原始语言处理类型，并单独传递key和val_arr。

- Erwin Brandstetter