如何在PL/pgSQL中实现存储字符串列表的子串匹配功能

基础动态SQL实现 #

如果你需要按照提到的动态拼接多条件的方式实现，可以直接用PL/pgSQL的动态查询能力拼接所有判断条件，代码如下：

CREATE OR REPLACE FUNCTION check_disallowed(input_str text)
RETURNS boolean AS $$
DECLARE
    condition_str text;
    result boolean;
BEGIN
    -- 拼接所有违禁词的匹配条件
    SELECT string_agg(
        format('position(%L in $1) > 0', illegal_string),
        ' OR '
    ) INTO condition_str
    FROM disallowed;

    -- 没有违禁词时直接返回false
    IF condition_str IS NULL THEN
        RETURN false;
    END IF;

    -- 执行动态查询
    EXECUTE format('SELECT %s', condition_str) INTO result USING input_str;
    RETURN result;
END;
$$ LANGUAGE plpgsql STABLE;

使用方式很简单，直接传入待检测字符串即可：

-- 返回true，因为包含stupid
SELECT check_disallowed('you are stupid');
-- 返回false
SELECT check_disallowed('hello world');

这个方案适合违禁词数量不多的场景，逻辑简单直接。

支持GIN索引优化的高性能实现 #

如果你的违禁词数量较多、查询频率高，建议使用PostgreSQL的pg_trgm扩展配合GIN索引优化，不需要复杂的动态拼接，性能比多OR条件的实现好很多。

第一步：启用pg_trgm扩展

CREATE EXTENSION IF NOT EXISTS pg_trgm;

第二步：给违禁词表建GIN索引

CREATE INDEX idx_disallowed_trgm ON disallowed USING gin (illegal_string gin_trgm_ops);

第三步：编写匹配函数

CREATE OR REPLACE FUNCTION check_disallowed_optimized(input_str text)
RETURNS boolean AS $$
BEGIN
    RETURN EXISTS (
        SELECT 1
        FROM disallowed
        WHERE input_str LIKE '%' || illegal_string || '%'
    );
END;
$$ LANGUAGE plpgsql STABLE;

这个实现利用了pg_trgm的三元组索引，前后通配的LIKE查询也能走索引，不需要全表扫描，即使违禁词有上万条也能保持不错的查询性能。如果需要忽略大小写匹配，把LIKE换成ILIKE即可，索引同样生效。

内容的提问来源于stack exchange，提问作者oligofren