sql - 如何在 UPDATE 语句中使用 regexp_matches()？

Question

我正在尝试清理一个包含各种条目的非常困惑的 varchar 列的表:

<u><font color="#0000FF"><a href="http://virginialidar.com/index-3.html#.VgLbFPm6e73" target="_blank">VA Lidar</a></font></u> OR <u><font color="#0000FF"><a href="https://inport.nmfs.noaa.gov/inport/item/50122" target="_blank">InPort Metadata</a></font></u>

我想通过仅保留 html 链接来更新该列，如果有多个链接，则用逗号分隔它们。理想情况下，我会做这样的事情:

UPDATE mytable
SET column = array_to_string(regexp_matches(column,'(?<=href=").+?(?=\")','g') , ',');

但不幸的是，这会在 Postgres 10 中返回一个错误:

ERROR: set-returning functions are not allowed in UPDATE

我假设 regexp_matches() 是上述的集合返回函数。关于如何实现这一目标的任何想法？

Answer 1

注意事项

1.
您不需要将相关子查询基于基表的单独实例(如建议的其他答案)。那将白白做更多的工作。

2.
对于简单的情况，ARRAY 构造函数 比 array_agg() 便宜.见:

• Why is array_agg() slower than the non-aggregate ARRAY() constructor?

3.
我使用不带 lookahead and lookbehind constraints 的正则表达式和括号代替:href="([^"]+)

参见查询 1。

这是有效的，因为 带括号的子表达式 被 regexp_matches() 捕获(以及其他几个 Postgres regexp 函数)。所以我们可以用简单的括号替换更复杂的约束。 The manual on regexp_match() :

If a match is found, and the pattern contains no parenthesizedsubexpressions, then the result is a single-element text arraycontaining the substring matching the whole pattern. If a match isfound, and the *pattern* contains parenthesized subexpressions, then theresult is a text array whose n'th element is the substring matchingthe n'th parenthesized subexpression of the pattern

And for regexp_matches() :

This function returns no rows if there is no match, one row if thereis a match and the g flag is not given, or N rows if there are Nmatches and the g flag is given. Each returned row is a text arraycontaining the whole matched substring or the substrings matchingparenthesized subexpressions of the pattern, just as described abovefor regexp_match.

4.
regexp_matches() 返回一组数组 ( setof text[] ) 是有原因的:正则表达式不仅可以在单个字符串中匹配多次(因此集合)，它可以还可以为每个带有多个捕获括号的单个匹配项生成多个字符串(因此数组)。不会出现在 this 正则表达式中，结果中的每个数组都包含一个元素。但 future 的读者不应被引入陷阱:

将生成的一维数组提供给 array_agg() 时(或 ARRAY 构造函数)生成二维数组——这甚至是可能的，因为 Postgres 9.5 添加了 array_agg() 的变体。接受数组输入。见:

• Is there something like a zip() function in PostgreSQL that combines two arrays?

然而，quoting the manual:

inputs must all have same dimensionality, and cannot be empty or 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<> fiddle here (带有扩展测试用例)