sql - 在不使用子查询的情况下从同一个表中的正则表达式匹配更新

Question

我想用正则表达式匹配同一个表的列的结果填充两列。

提取数组中的匹配项非常简单:

select regexp_matches(description, '(?i)^(https?://\S{4,220}\.(?:jpe?g|png))\s(.*)$') matches from room;

(注意只有部分行匹配，不是全部匹配)

但是为了进行更新，我没有找到比

更简单的方法

1) 重复可笑的正则表达式:

update room r set
    link=(regexp_matches(description, '(?i)^(https?://\S{4,220}\.(?:jpe?g|png))\s(.*)$'))[1],
    description=(regexp_matches(description, '(?i)^(https?://\S{4,220}\.(?:jpe?g|png))\s(.*)$'))[2]
where description ~ '(?i)^(https?://\S{4,220}\.(?:jpe?g|png))\s(.*)$';

2) 带有子查询和 id 连接的查询，看起来很复杂而且可能不是最有效的:

update room r set link=matches[1], description=matches[2] from (
    select id, regexp_matches(description, '(?i)^(https?://\S{4,220}\.(?:jpe?g|png))\s(.*)$') matches from room
) s where matches is not null and r.id=s.id;

这里的正确解决方案是什么？我怀疑 postgresql 的一个神奇的数组函数，或者另一个与正则表达式相关的函数，或者更简单的函数可以做到这一点。

Answer 1

从 9.5 开始，您可以使用 following syntax :

with p(pattern) as (
  select '(?in)^(https?://\S{4,220}\.(?:jpe?g|png))\s(.*)$'
)
update room
set    (link, description) = (select m[1], m[2]
                              from   regexp_matches(description, pattern) m)
from   p
where  description ~ pattern;

这种方式 regexp_matches() 只执行一次，但这将执行你的正则表达式两次。如果你想避免这种情况，无论如何你都需要使用连接。 Or, you could do :

update room
set    (link, description) = (
  select coalesce(m[1], l), coalesce(m[2], d)
  from   (select link l, description d) s,
         regexp_matches(d, '(?in)^(https?://\S{4,220}\.(?:jpe?g|png))\s(.*)$') m
);

但这无论如何都会“触及”每一行。当没有匹配项时，它不会修改 link 和 description 的值。