正则表达式或 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 以来的默认设置一样。

正则表达式转义函数

让我们从 正则表达式 模式：

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

包裹在 括号表达式 大多数都失去了特殊含义 - 有一些例外：

• - 需要位于第一个或最后一个，或者它表示一个范围< /em> 字符数。
• ] 和 \ 必须用 \ 转义（在替换中也是如此）。

添加捕获后向引用的括号 下面我们得到这个正则表达式模式：

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

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

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

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

演示

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

返回：

test\(1\) \> Foo\*

while:

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

Answer 2

尝试这样的方法如何，用 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(n)+1 方法将不是一个好的方法。
;

 max
-----
   4
(1 row)

一个警告：我假设单用户访问数据库，而此过程是跑步（你也在跑步）。如果情况并非如此，那么 max(n)+1 方法将不是一个好的方法。

Answer 3

您可以随意更改架构吗？我认为如果您可以使用复合主键，问题就会消失：

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

显示层有责任将 Fred #0 显示为“Fred”，将 Fred #1 显示为“Fred (1)”，&c。

如果您愿意，您可以为此任务创建一个视图。这是数据：

=> 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)