Решим сегодня простую, казалось бы, задачу: как на PostgreSQL можно в строке провести замены по набору пар строк. То есть в исходной строке 'abcdaaabbbcccdcba' заменить, например, {'а' -> 'x', 'bb' -> 'y', 'ccc' -> 'z'} и получить 'xbcdxxxybzdcbx'.

Фактически, мы попробуем создать аналог str_replace или strtr.

Callback Hell

Первое, что приходит на ум - это сделать цепочку из вложенных вызовов replace:

SELECT
  replace( -- ... и так 100500 раз
    replace(
      replace(
        'abcdaaabbbcccdcba' -- исходная строка
      , 'a'
      , 'x'
      )
    , 'bb'
    , 'y'
    )
  , 'ccc'
  , 'z'
  );

Такой код настолько же эффективен, насколько и нерасширяем.

Рекурсия

Зайдем на проблему с другой стороны.

Мы хотим последовательно в цикле заменять одну подстроку на другую - а за "циклы" в SQL отвечает рекурсия:

WITH RECURSIVE rpl AS (
  SELECT
    row_number() OVER() i -- нумеруем наши замены
  , *
  FROM
    (
      VALUES -- список замен теперь легко расширяем
        ('a',   'x')
      , ('bb',  'y')
      , ('ccc', 'z')
    ) T(f, t)
)
, R AS (
  SELECT
    1::bigint i
  , 'abcdaaabbbcccdcba' s -- исходная строка
UNION ALL
  SELECT
    i + 1
  , replace(R.s, rpl.f, rpl.t) -- заменяем i-ю пару
  FROM
    R
  NATURAL JOIN -- USING(i)
    rpl
)
SELECT
  s
FROM
  R
ORDER BY
  i DESC -- возвращаем результат последнего шага
LIMIT 1;

strtr

Оба эти варианта обладают неприятной особенностью повторной замены - то есть каждый следующий шаг опирается на результат предыдущего, как в str_replace. Поэтому при замене исходной строки 'aaa' с набором {'a' -> 'b', 'b' -> 'c'} мы получим 'ccc', а вовсе не 'bbb'.

Чтобы обойти этот недостаток, воспользуемся разбиением строки сразу по всему набору заменяемых подстрок с помощью регулярных выражений:

WITH src(s) AS (
  VALUES('abcdaaabbbcccdcba') -- исходная строка
)
, rpl AS (
  SELECT -- набор замен в виде json-объекта
    '{
       "a"   : "x"
     , "bb"  : "y"
     , "ccc" : "z"
     }'::json
)
, rpl_re AS (
  SELECT
    '(' || string_agg(k, '|') || ')' re -- '(a|bb|ccc)'
  FROM
    json_object_keys((TABLE rpl)) k -- получаем все ключи для замен
)
, spl AS (
  SELECT
    T.*
  FROM
    src
  , rpl_re
  , unnest( -- совместный unnest двух разноразмерных массивов
      regexp_split_to_array(s, re) -- тут части "между" ключами
    , ARRAY( -- тут сами ключи
        SELECT
          m[1]
        FROM
          regexp_matches(s, re, 'g') m
      )
    ) T(part, key)
)
SELECT
  string_agg(concat(part, (TABLE rpl) ->> key), '') -- подставляем найденные ключи по словарю замен
FROM
  spl;

В нашем примереunnest(regexp_split_to_array, ARRAY(regexp_matches[1])) вернет следующий результат:

part | key
 --- | a
 bcd | a
 --- | a
 --- | a
 --- | bb
   b | ccc
 dcb | a
 --- | ---

После чего нам только остается для каждого ключа выполнить подстановку на целевое значение и собрать строку обратно через string_agg.

Вот и все!