将子字符串与存储的字符串列表进行匹配

Question

所以，在最近的Twitch泄漏之后，每个人都在讨论他们最喜欢的坏代码。有一点很突出，那就是一个用来识别"非法术语"的巨型SQL语句。这让我开始思考如何"正确地"实现这个功能。

如果你有一个字符串表，你想要匹配子字符串，你如何在PL/pgSQL中写这个?我假设任何SQL实现都应该具有为这种元编程创建函数/过程的过程能力，基本上可以创建和执行如下的SQL:

admin@localhost:words> SELECT 'XabcY' LIKE '%abc%' OR 'XabcY' LIKE '%xyz%' as matches;
+-----------+
| matches   |
|-----------|
| True      |
+-----------+

所以更具体地说，给定表disallowed中的字符串列表:

| illegal_string |
|----------------|
| stupid         |
| witless        |
| moron          |
| commie-lover   |

如何在PL/pgSQL中创建一个动态查询，如果其中任何一个匹配给定的字符串，在执行时返回true ?它不需要在Twitch中使用ILIKE来检查给定的单词是否包含子字符串，所以使用position也很好，但它应该是高性能/可调的使用gin索引和什么的。

相关问题。

Answer 1

有两种可能:

1. 可以使用LIKE ANY(array)操作符

postgres=# select 'Ahoj' like any (ARRAY['Ah%', 'Na%']);
┌──────────┐
│ ?column? │
╞══════════╡
│ t        │
└──────────┘
(1 row)
postgres=# select 'Nazdar' like any (ARRAY['Ah%', 'Na%']);
┌──────────┐
│ ?column? │
╞══════════╡
│ t        │
└──────────┘
(1 row)

2. 你可以使用正则表达式:

postgres=# select 'Ahoj' ~ '^(Ah|Na)';
┌──────────┐
│ ?column? │
╞══════════╡
│ t        │
└──────────┘
(1 row)
postgres=# select 'Nazdar' ~ '^(Ah|Na)';
┌──────────┐
│ ?column? │
╞══════════╡
│ t        │
└──────────┘
(1 row)

所以最后你不需要动态SQL。

也有ANSI/SQL语法:

postgres=# select 'Nazdar' similar to '(Ah|Na)%';
┌──────────┐
│ ?column? │
╞══════════╡
│ t        │
└──────────┘
(1 row)

你可以这样写:

DECLARE pw text[];
BEGIN
pw := (SELECT array_agg('%' || disallowed || '%'
FROM disallowed);
IF EXISTS(SELECT * FROM foo WHERE c LIKE ANY (pw)) THEN
RAISE NOTICE 'there are some disallowed words';
END IF;
...

我对表演不确定。对于较大的表，您需要三元组索引，或者更好的是使用全文而不是子字符串搜索。

Answer 2

我不认为你需要PL/pgSQL或动态SQL。从不允许的单词表中生成一组文本，并将集合项作为正则表达式进行匹配。也许不是很高性能使用regexp魔法,但我希望简单。下面的查询当然可以参数化。

select '<text to examine>' ~* 
any(select illegal_string from disallowed) as rude;

select 'You Stupido MORONE!' ~* 
any(select illegal_string from disallowed) as rude;
-- yields true.

您可能希望仅将搜索限制为整个单词。然后使用regexp集合，并像这样塑造它:

select 'You Stupido MORONE!' ~* 
any(select 'm'||illegal_string||'M' from disallowed) as rude;
-- yields false

SQL Fiddle here