Решим сегодня простую, казалось бы, задачу: как на PostgreSQL можно в строке провести замены по набору пар строк. То есть в исходной строке 'abcdaaabbbcccdcba'
заменить, например, {'а' -> 'x', 'bb' -> 'y', 'ccc' -> 'z'}
и получить 'xbcdxxxybzdcbx'
.
Фактически, мы попробуем создать аналог str_replace или strtr.
![Find and Replace Find and Replace](https://habrastorage.org/getpro/habr/upload_files/553/8eb/23b/5538eb23bef791287e25d5f0272100e4.png)
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
.
Вот и все!