如何在PostgreSQL指定数据库和模式中查找字符串所在表

要在agriculture数据库的fruit模式下查找包含字符串"banana"的表，你可以通过查询系统元数据生成动态SQL，或者创建PL/pgSQL函数自动遍历检查所有文本类型字段，以下是两种可行方案：

方案1：生成查询语句手动执行 #

首先切换到目标数据库：

\c agriculture

执行以下SQL生成针对每个文本字段的查询语句：

SELECT format(
    'SELECT ''%I'' AS table_name, ''%I'' AS column_name FROM %I.%I WHERE %I LIKE ''%s'' LIMIT 1',
    table_name,
    column_name,
    table_schema,
    table_name,
    column_name,
    '%banana%'
) AS query
FROM information_schema.columns
WHERE table_schema = 'fruit'
  AND data_type IN ('text', 'character varying', 'character');

该查询会返回一系列SELECT语句，每个语句对应fruit模式下的一个文本字段。执行这些语句，有返回结果的行就对应包含"banana"的表和字段。

方案2：创建函数自动返回结果 #

创建一个PL/pgSQL函数，自动遍历目标模式下的所有文本字段并检查是否包含目标字符串：

CREATE OR REPLACE FUNCTION find_string_in_schema(p_schema text, p_string text)
RETURNS TABLE(table_name text, column_name text) AS $$
DECLARE
    v_query text;
    v_rec record;
BEGIN
    FOR v_rec IN
        SELECT table_name, column_name
        FROM information_schema.columns
        WHERE table_schema = p_schema
          AND data_type IN ('text', 'character varying', 'character')
    LOOP
        v_query := format(
            'SELECT ''%I'', ''%I'' FROM %I.%I WHERE %I LIKE ''%s'' LIMIT 1',
            v_rec.table_name,
            v_rec.column_name,
            p_schema,
            v_rec.table_name,
            v_rec.column_name,
            '%' || p_string || '%'
        );
        BEGIN
            RETURN QUERY EXECUTE v_query;
        EXCEPTION
            WHEN others THEN
                CONTINUE; -- 跳过权限不足或其他错误的表
        END;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

创建完成后，直接调用函数即可得到结果：

SELECT * FROM find_string_in_schema('fruit', 'banana');

注意事项 #

• 查询使用LIKE，是大小写敏感的，若需忽略大小写，替换为ILIKE即可。
• 若目标模式下表和字段较多，查询可能会较慢，建议在业务低峰期执行。
• 执行该操作需要具备fruit模式下对应表的查询权限，以及访问information_schema的权限。

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