如何在UPDATE语句中使用regexp_matches（）？

笔记

1.
您不需要将相关子查询建立在基表的单独实例上（就像到目前为止提出的两个答案一样）。那会白费力气。

2.
在简单的情况下，ARRAY构造函数比便宜array_agg()。看到：

• 为什么array_agg（）比非聚合ARRAY（）构造函数慢？

3.
我使用的正则表达式没有先行约束和后括号，而是：href="([^"]+)

请参阅查询1。

之所以有效，是因为带括号的子表达式由regexp_matches()（和其他几个Postgres regexp函数）捕获。因此，我们可以用普通括号代替更复杂的约束。手册regexp_match()：

如果找到匹配项，并且不pattern包含带括号的子表达式，则结果为单元素文本数组，其中包含与整个模式匹配的子字符串。如果找到匹配项，并且*pattern*包含括号内的子表达式，则结果是一个文本数组，其n第th个元素是n与模式的第一个括号内的子表达式匹配的子字符串

对于regexp_matches()：

如果没有匹配项，则此函数不返回任何行；如果存在匹配项且未给出g标志，则返回N一行；如果存在N个匹配项且给出g标志，则返回一行。每个返回的行都是一个文本数组，其中包含整个匹配的子字符串或与该模式的带括号的子表达式匹配的子字符串，正如上面针对所述regexp_match。

4.由于以下原因
regexp_matches()返回一组数组（setof text[]）：正则表达式不仅可以在单个字符串中多次匹配（因此set），而且还可以为每个单个匹配产生多个字符串并带有多个捕获括号（因此该数组））。此正则表达式不会发生，结果中的每个数组都包含一个元素。但是，将来的读者不应陷入陷阱：

当将生成的一维数组提供给生成二维数组的array_agg()（或ARRAY构造函数）时，这只是可能的，因为Postgres 9.5添加了array_agg()接受数组输入的变体。看到：

• PostgreSQL中是否有类似zip（）函数的东西，它结合了两个数组？

但是，引用手册：

输入必须全部具有相同的维数，并且不能为空或NULL

我认为这永远不会失败，因为同一个正则表达式总是产生相同数量的数组元素。我们总是产生一个要素。但这可能与其他正则表达式不同。如果是这样，则有多种选择：

1. 只能使用的第一个元素(regexp_matches(...))[1]。参见查询2。

2. 嵌套数组并string_agg()在基础元素上使用。参见查询3。

每种方法在这里也适用。

查询1

UPDATE tbl t
SET    col = (
   SELECT array_to_string(ARRAY(SELECT regexp_matches(col, 'href="([^"]+)', 'g')), ',')
   );

不匹配的列设置为''（空字符串）。

查询2

UPDATE tbl
SET    col = (
   SELECT string_agg(t.arr[1], ',')
   FROM   regexp_matches(col, 'href="([^"]+)', 'g') t(arr)
   );

不匹配的列设置为NULL。

查询3

UPDATE tbl
SET    col = (
   SELECT string_agg(elem, ',')
   FROM   regexp_matches(col, 'href="([^"]+)', 'g') t(arr)
        , unnest(t.arr) elem
   );

不匹配的列设置为NULL。

db <>在这里摆弄（带有扩展的测试用例）