Postgres:当只有某些行是有效的正则表达式时，选择使用列作为正则表达式

Question

TLDR

如何使用列值 ('input' ~ t.somecolumn) 进行正则表达式匹配查询，其中只有已知的行子集在该列中具有有效的正则表达式？

完整示例

• 有一个blocked_items表，包括两个 varchar 列：type和value，
• 其中一种类型是DOMAIN_REGEX，然后value始终包含正确的正则表达式，
• 但是：对于其他类型的value不需要是正则表达式，当被视为正则表达式时可能会导致错误。

为了检查域是否被阻止，我调用此查询并将有问题的 URL 作为参数传递$1：

SELECT 1 FROM blocked_items WHERE type = 'DOMAIN_REGEX' AND $1 ~ value LIMIT 1

问题：在某些数据库实例上，如果具有另一个type的行具有value不是有效的正则表达式，则查询将失败。在一个数据库上，此查询正常运行，而在另一个实例上，无论输入如何，都会抛出：invalid regular expression: quantifier operand invalid。

示例测试数据：

| type         | value               |
|--------------+---------------------|
| EMAIL        | [e-mail]   |
| DOMAIN_REGEX | testd.com         |

问题

我知道我的错误原因是数据库引擎可以选择首先检查第二个条件($1 ~ value)——我已经检查了查询的EXPLAIN，实际上这两个数据库实例是不同的。

有没有办法我可以

• 强制数据库先检查type列，所以正则表达式过滤器始终有效？
以
• 不同的方式形成查询以忽略非正则表达式value条目的错误？或者先检查它是否是有效的正则表达式？
• 以另一种方式解决此问题？

我知道更改模式或使用LIKE可能就足够了，但现在我偶然发现了这个，我很好奇是否有使用像这样的正则表达式的解决方案:)

Answer 1

您应该能够使用case强制操作顺序：

SELECT 1
FROM blocked_items
WHERE (CASE WHEN type <> 'DOMAIN_REGEX' THEN false
ELSE $1 ~ value
END)
LIMIT 1;

通常，SQL(和Postgres)对表达式的求值顺序几乎没有控制。 但是，CASE在许多情况下应提供这种控制。

Answer 2

你是对的，模式不是很好。如果您仍然必须保留架构，则可以尝试 CASE/WHEN，https://www.postgresqltutorial.com/postgresql-case/