PostgreSQL: escape-функция регулярного выражения

Чтобы отказаться от чтения всей проблемы, мой основной вопрос:
Есть ли в PostgreSQL функция для экранирования символов регулярных выражений в строке?

Я исследовал документацию, но не смог найти такую ​​функцию.

Вот полная проблема:

В базе данных PostgreSQL у меня есть столбец с уникальными именами. У меня также есть процесс, который периодически вставляет имена в это поле, и, для предотвращения дублирования, если ему нужно ввести имя, которое уже существует, он добавляет пробел и круглые скобки со счетчиком в конец.

то есть имя, имя (1), имя (2), имя (3) и т. д.

В существующем виде я использую следующий код, чтобы найти следующий номер, добавляемый в серию (написанный в plpgsql):

var_name_id := 1;

SELECT CAST(substring(a.name from E'\\((\\d+)\\)$') AS int)
INTO var_last_name_id
FROM my_table.names a
WHERE a.name LIKE var_name || ' (%)'
ORDER BY CAST(substring(a.name from E'\\((\\d+)\\)$') AS int) DESC
LIMIT 1;

IF var_last_name_id IS NOT NULL THEN
    var_name_id = var_last_name_id + 1;
END IF;

var_new_name := var_name || ' (' || var_name_id || ')';

(var_name содержит имя, которое я пытаюсь вставить.)

Пока это работает, но проблема заключается в WHERE заявление:

WHERE a.name LIKE var_name || ' (%)'

Эта проверка не подтверждает, что % речь идет о числе, и оно не учитывает несколько круглых скобок, как в чем-то вроде "Name ((1))", и, если бы существовал какой-либо случай, было бы выброшено исключение.

WHERE утверждение действительно должно быть чем-то вроде:

WHERE a.r1_name ~* var_name || E' \\(\\d+\\)'

Но var_name может содержать символы регулярного выражения, что приводит к приведенному выше вопросу: есть ли в PostgreSQL функция, которая экранирует символы регулярного выражения в строке, поэтому я мог бы сделать что-то вроде:

WHERE a.r1_name ~* regex_escape(var_name) || E' \\(\\d+\\)'

Любые предложения приветствуются, включая возможную переработку моего решения для дубликатов имен.

Источник

3 ответа

Решение

Как насчет того, чтобы попробовать что-то подобное, подставив var_name для моего жестко запрограммированного 'John Bernard':

create table my_table(name text primary key);
insert into my_table(name) values ('John Bernard'), 
                                  ('John Bernard (1)'), 
                                  ('John Bernard (2)'), 
                                  ('John Bernard (3)');


select max(regexp_replace(substring(name, 13), ' |\(|\)', '', 'g')::integer+1) 
from my_table 
where substring(name, 1, 12)='John Bernard' 
      and substring(name, 13)~'^ \([1-9][0-9]*\)$';

 max
-----
   4
(1 row)

одно предостережение: я предполагаю однопользовательский доступ к базе данных во время выполнения этого процесса (как и вы в своем подходе). Если это не так, то max(n)+1 подход не будет хорошим.

Источник

Чтобы ответить на вопрос в верхней части:

Escape-функция регулярного выражения

Давайте начнем с полного списка символов со специальным значением в шаблонах регулярных выражений:

!$()*+.:<=>?[\]^{|}-

Заключенные в квадратные выражения, большинство из них теряют свое особое значение, за некоторыми исключениями:

  • - должен быть первым или последним, или это означает диапазон символов.
  • ] а также \ должен быть сбежал с \,

После добавления скобок (для обратной ссылки) ниже мы получаем этот шаблон регулярного выражения:

([!$()*+.:<=>?[\\\]^{|}-])

Используя его, эта функция экранирует все специальные символы обратной косой чертой (\) - тем самым убирая особое значение:

CREATE OR REPLACE FUNCTION f_regexp_escape(text)
  RETURNS text AS
$func$
SELECT regexp_replace($1, '([!$()*+.:<=>?[\\\]^{|}-])', '\\\1', 'g')
$func$  LANGUAGE sql IMMUTABLE;

демонстрация 

SELECT f_regexp_escape('test(1) > Foo*');

Возвращает:

test\(1\) \> Foo\*

И пока:

SELECT 'test(1) > Foo*' ~ 'test(1) > Foo*';

возвращается FALSE что может стать неожиданностью для наивных пользователей,

SELECT 'test(1) > Foo*' ~ f_regexp_escape('test(1) > Foo*')

Возвращает TRUE как и сейчас.

LIKE функция выхода

Для полноты, подвеска для LIKE шаблоны, где только три символа являются особенными:

\%_

Руководство:

Экранирующим символом по умолчанию является обратный слеш, но другой можно выбрать с помощью ESCAPE пункт.

Эта функция принимает значение по умолчанию:

CREATE OR REPLACE FUNCTION f_like_escape(text)
  RETURNS text AS
$func$
SELECT replace(replace(replace($1
         , '\', '\\')  -- must come 1st
         , '%', '\%')
         , '_', '\_');
$func$  LANGUAGE sql IMMUTABLE;

Мы могли бы использовать более элегантный regexp_replace() здесь, но только для нескольких персонажей, каскад replace() функции быстрее.

демонстрация 

SELECT f_like_escape('20% \ 50% low_prices');

Возвращает:

20\% \\ 50\% low\_prices
Источник

Вы можете изменить схему? Я думаю, что проблема исчезла бы, если бы вы могли использовать составной первичный ключ:

name text not null,
number integer not null,
primary key (name, number)

Затем уровень отображения становится обязанностью отображать Fred #0 как "Fred", Fred #1 как "Fred (1)" и т. Д.

Если вам нравится, вы можете создать представление для этой обязанности. Вот данные:

=> select * from foo;
  name  | number 
--------+--------
 Fred   |      0
 Fred   |      1
 Barney |      0
 Betty  |      0
 Betty  |      1
 Betty  |      2
(6 rows)

Вид:

create or replace view foo_view as
select *,
case
  when number = 0 then
    name
  else
    name || ' (' || number || ')'
end as name_and_number
from foo;

И результат:

=> select * from foo_view;
  name  | number | name_and_number 
--------+--------+-----------------
 Fred   |      0 | Fred
 Fred   |      1 | Fred (1)
 Barney |      0 | Barney
 Betty  |      0 | Betty
 Betty  |      1 | Betty (1)
 Betty  |      2 | Betty (2)
(6 rows)
Источник
Другие вопросы по тегам