sql - 识别冲突的正则表达式

Question

我正在使用 Ruby on Rails 框架/PostgreSQL 作为数据库开发一个小项目，我需要在表中存储一些正则表达式，如下所示:

------------------------
pattern       direction
-----------------------
1\d{3}        client1        
2\d{3}        client2
31\d{2}       client3
32\d{2}       client4
4             client5
-----------------------

每个模式都将以一个或多个特定数字开始，并以“未知”数字(从 0 到 255 个附加数字)继续。

人们会通过 GUI 将新对添加到此表中，我想避免交集，因此对于我的示例 3\d{3} 不应添加，因为它确实与 31\d{2 相交} 和 32\d{2}。

是否可以在将新表达式添加到数据库表之前检查现有表达式的交集？

此外，在 GUI 中，用户会看到这些模式，如 1XXX、2XXX、31XX。我不喜欢给他们看表情。也许我根本不必使用正则表达式？但我需要按特定数字搜索最佳匹配模式，例如，数字 3291 的查询应返回“client4”，查询“4”应返回“client5”。

在我的案例中，最佳做法是什么？

Answer 1

基本上，在以下情况下不允许新条目:

• 总长度匹配。
• 前缀是条目的子字符串，反之亦然。

您可以冗余地存储总长度或即时计算它。我根据问题中显示的模式将公式封装在一个简单的 SQL 函数中:

CREATE OR REPLACE FUNCTION f_pattern_len(text)
  RETURNS int AS
$$
SELECT COALESCE(substring($1, '(\d*)}$')::int, 0) -- digits in prefix
       + length(substring($1, '^\d*'))            -- digits in pattern
$$ LANGUAGE SQL IMMUTABLE STRICT;

然后，在 INSERT 上，像这样检查:

INSERT INTO tbl (pattern, direction)
SELECT i.input, 'client66'::text
FROM  (SELECT '3\d{7}'::text AS input) i
LEFT   JOIN tbl t
    ON f_pattern_len(t.pattern) = f_pattern_len(i.input)
   AND (substring(t.pattern, '^\d*') LIKE (substring(i.input, '^\d*') || '%') OR
        substring(i.input, '^\d*') LIKE (substring(t.pattern, '^\d*') || '%'))
WHERE t.pattern IS NULL

SQL Fiddle.

您还可以将逻辑放入触发器 BEFORE INSERT ...