PostgreSQL 10.7中如何在备注/描述字段检索正则表达式匹配内容

嘿，这个需求我之前帮人处理过——要在PostgreSQL 10.7里跨所有存备注、评论、描述的字段，检索出符合特定正则模式（比如你提到的SSN号码）的内容，其实可以分三步来落地，我给你详细拆解：

首先得找出数据库里哪些列是用来存文本类备注信息的，通常这类列的名称会带note、comment、description、remark这类关键词。我们可以直接查询PostgreSQL的系统表information_schema.columns来筛选：

SELECT 
    table_schema,
    table_name,
    column_name,
    data_type
FROM information_schema.columns
WHERE 
    table_schema NOT IN ('pg_catalog', 'information_schema') -- 排除系统自带的schema
    AND data_type IN ('text', 'varchar', 'character varying') -- 只扫描文本类型的列
    AND (column_name ILIKE '%note%' 
         OR column_name ILIKE '%comment%' 
         OR column_name ILIKE '%description%' 
         OR column_name ILIKE '%remark%'); -- 匹配常见的备注类列名

你可以根据自己数据库的实际命名习惯，调整ILIKE里的关键词，比如有的团队会用desc代替description，就把%desc%加进去就行。

手动一个个列去查肯定不现实，我们可以用动态SQL来自动化这个过程，有两种方式可选：

方式1：生成一次性执行的查询语句（适合临时扫描） #

如果只是偶尔扫一次，先把第一步的查询结果导出，或者用下面的SQL直接生成所有可执行的查询语句：

SELECT 
    format(
        'SELECT ''%I.%I'' AS table_column, %I AS column_value FROM %I.%I WHERE %I ~ ''^.*\\d{3}-\\d{2}-\\d{4}.*$'';',
        table_schema, table_name, column_name, table_schema, table_name, column_name
    ) AS query
FROM information_schema.columns
WHERE 
    table_schema NOT IN ('pg_catalog', 'information_schema')
    AND data_type IN ('text', 'varchar', 'character varying')
    AND (column_name ILIKE '%note%' 
         OR column_name ILIKE '%comment%' 
         OR column_name ILIKE '%description%' 
         OR column_name ILIKE '%remark%');

这里的正则^.*\\d{3}-\\d{2}-\\d{4}.*$就是用来匹配SSN格式（XXX-XX-XXXX）的，注意PostgreSQL里正则的转义字符要写两个\\。把生成的所有SQL语句复制出来批量执行，就能得到所有匹配的内容，以及对应的表和列信息。

方式2：创建PL/pgSQL函数（适合重复使用） #

如果需要经常做这类扫描，写个函数会更省心：

CREATE OR REPLACE FUNCTION scan_text_columns_for_pattern(p_pattern text)
RETURNS TABLE(
    table_schema text,
    table_name text,
    column_name text,
    matched_value text
) AS $$
DECLARE
    rec record;
    v_query text;
BEGIN
    -- 遍历所有符合条件的列
    FOR rec IN 
        SELECT table_schema, table_name, column_name
        FROM information_schema.columns
        WHERE 
            table_schema NOT IN ('pg_catalog', 'information_schema')
            AND data_type IN ('text', 'varchar', 'character varying')
            AND (column_name ILIKE '%note%' 
                 OR column_name ILIKE '%comment%' 
                 OR column_name ILIKE '%description%' 
                 OR column_name ILIKE '%remark%')
    LOOP
        -- 构造动态查询语句
        v_query := format(
            'SELECT ''%I'', ''%I'', ''%I'', %I FROM %I.%I WHERE %I ~ $1',
            rec.table_schema, rec.table_name, rec.column_name, rec.column_name, rec.table_schema, rec.table_name, rec.column_name
        );
        -- 执行查询并返回结果
        RETURN QUERY EXECUTE v_query USING p_pattern;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

创建好函数后，直接传入你的SSN正则模式就能调用：

SELECT * FROM scan_text_columns_for_pattern('^.*\d{3}-\d{2}-\d{4}.*$');

这个函数会自动遍历所有目标列，返回所有匹配的内容，以及对应的表和列信息，非常方便。

• 正则灵活调整：如果你的SSN有其他格式（比如不带分隔符的XXXYYXXXX），可以修改正则表达式，比如^.*(\d{9}|\d{3}-\d{2}-\d{4}).*$就能同时匹配两种格式。
• 性能优化：如果数据库数据量很大，全表扫描会很慢。可以给常用的备注类列创建trgm索引来加速正则匹配，比如：

  CREATE INDEX idx_your_table_note ON your_table USING gin (your_note_column gin_trgm_ops);
  

  PostgreSQL 10.7已经支持trgm索引，能大幅提升模糊匹配和正则匹配的速度。
• 权限问题：确保执行查询的用户有所有目标表的SELECT权限，否则会出现权限报错。
• 排除无关表：如果某些表肯定不需要扫描，可以在WHERE条件里加上table_name NOT IN ('table1', 'table2')来排除，减少扫描范围。

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