sql - Postgres 正则表达式替换为正则表达式和替换值使用另一个表

Question

假设我有一个包含键/值的表，其中键是一个正则表达式，对应的值是替换值。例如:

table_a:

Key   Value
-----------
a     123
b     456
c     789

我需要使用 SQL 更新另一个表中的值，方法是将上表中出现的每个键替换为其对应的值。

如果我只有一个替换值，我会使用这样的东西:

UPDATE table_b 
SET    some_field = REGEXP_REPLACE((SELECT STRING_AGG(table_a.key, '|') 
                                    FROM   table_a), 'replacement value'); 

这将基于 table_a 中的所有 键 构造一个正则表达式，并用我的替换字符串替换任何匹配项。

除了使用 table_a 中相应的 value 作为替换值，我如何做类似的事情？

使用 Postgres 9.5

Answer 1

您不能在 regexp_replace() 的单次执行中做到这一点。您需要对 table_a 的所有行进行循环和多次调用 regexp_replace()。所以，你需要一个 plpgsql 函数:

create or replace function replace_all_patterns(str text)
returns text language plpgsql as $$
declare
    r record;
begin
    for r in
        select key, value
        from table_a
    loop
        str:= regexp_replace(str, r.key, r.value, 'g');
    end loop;
    return str;
end $$;

假设 table_b 包含两行:

create table table_b(some_field text);
insert into table_b values
('abc'),
('ccc');

你可以这样使用函数:

update table_b
set some_field = replace_all_patterns(some_field)
returning *;

 some_field 
------------
 123456789
 789789789
(2 rows)

值得补充的是，该函数相当昂贵，并且在某些情况下结果可能取决于 table_a 中行的顺序。