Postgres regexp replace using another table for both the regexp and replacement values

Question

Let's say I have a table with key/values where the key is a regular expression and the corresponding value is the replacement value. For example:

table_a:

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

I need to update a value in another table using SQL by replacing every key that appears in the table above with it's corresponding value.

If I had a single replacement value, I would use something like this:

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

This would construct a single regular expression based on all the keys in table_a and replace any occurrence with my replacement string.

How could I do something similar but use the corresponding value from table_a as the replacement value?

Using Postgres 9.5

klin · Accepted Answer · 2017-09-12 23:15:53Z

You cannot do that in a single execution of regexp_replace(). You need a loop and multiple calls of regexp_replace() for all rows of the table_a. So, you need a plpgsql function:

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 $$;

Assuming that the table_b contains two rows:

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

you can use the function in this way:

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

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

It is worth adding that the function is rather expensive and that the results in some cases may depend on the order of rows in the table_a.

Collectives™ on Stack Overflow

Postgres regexp replace using another table for both the regexp and replacement values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related