regex - 正则表达式或 LIKE 模式的转义函数

Question

放弃阅读整个问题，我的基本问题是:
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+\\)'

非常感谢任何建议，包括可能对我的重名解决方案进行修改。

Answer 1

要解决顶部的问题:

假设standard_conforming_strings = on ，就像它是自 Postgres 9.1 以来的默认设置。

正则表达式转义函数

让我们从 regular expression 中具有特殊含义的字符的完整列表开始模式:

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

包裹在 bracket expression 中它们中的大多数都失去了它们的特殊含义 - 除了少数异常(exception):

• - 必须是第一个或最后一个，或者表示字符的范围。
• ] 和 \ 必须使用 \ 进行转义(在替换中也是如此)。

添加后capturing parentheses for the back reference下面我们得到这个正则表达式模式:

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

使用它，此函数使用反斜杠 (\) 转义所有特殊字符 - 从而消除特殊含义:

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

在 Postgres 10 或更高版本中添加PARALLEL SAFE(因为它是)以允许使用它的并行查询。

演示

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 的吊坠模式，其中只有三个字符是特殊的:

\%_

The manual:

The default escape character is the backslash but a different one can be selected by using the ESCAPE clause.

此函数采用默认值:

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

我们也可以在这里使用更优雅的 regexp_replace()，但是对于少数字符，级联 replace() 函数更快。

同样，Postgres 10 或更高版本中的 PARALLEL SAFE。

演示

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

返回:

20\% \\ 50\% low\_prices