PostgreSQL 12中能否搜索所有表所有列中的指定字符串？

完全可以实现这个需求。PostgreSQL的系统表能帮你遍历所有用户表和字符类型列，结合动态SQL就能批量执行搜索。下面是几种实用的实现方式：

方式一：生成并手动执行查询语句 #

先生成针对每一个字符列的查询语句，你可以把结果复制出来执行，或者用UNION ALL合并：

SELECT format(
  'SELECT ''%I'' AS table_name, ''%I'' AS column_name, %I AS column_value FROM %I.%I WHERE %I LIKE ''%%%s%%''',
  table_name,
  column_name,
  column_name,
  table_schema,
  table_name,
  column_name,
  '你的目标字符串' -- 替换成你要搜索的内容
) AS search_query
FROM information_schema.columns
WHERE table_schema NOT IN ('pg_catalog', 'information_schema') -- 排除系统内置库
  AND data_type IN ('character varying', 'text', 'character'); -- 只搜索字符类型列

方式二：用动态SQL自动执行合并后的查询 #

这种方式会自动生成并执行所有查询，直接返回所有匹配结果：

DO $$
DECLARE
  search_sql text;
  target_str text := '你的目标字符串'; -- 替换成目标字符串
BEGIN
  WITH query_list AS (
    SELECT format(
      'SELECT ''%I'' AS table_name, ''%I'' AS column_name, %I AS column_value FROM %I.%I WHERE %I LIKE ''%%%s%%''',
      table_name,
      column_name,
      column_name,
      table_schema,
      table_name,
      column_name,
      target_str
    ) AS query
    FROM information_schema.columns
    WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
      AND data_type IN ('character varying', 'text', 'character')
  )
  SELECT string_agg(query, ' UNION ALL ') INTO search_sql FROM query_list;

  IF search_sql IS NOT NULL THEN
    EXECUTE search_sql;
  END IF;
END $$;

方式三：创建可复用的函数 #

如果需要多次执行搜索，可以创建一个函数，后续直接调用即可：

CREATE OR REPLACE FUNCTION search_all_tables(target_str text)
RETURNS TABLE(table_name text, column_name text, column_value text) AS $$
DECLARE
  col_record record;
BEGIN
  -- 遍历所有符合条件的列
  FOR col_record 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 ('character varying', 'text', 'character')
  LOOP
    -- 动态执行查询并返回结果
    RETURN QUERY EXECUTE format(
      'SELECT ''%I''::text, ''%I''::text, %I::text FROM %I.%I WHERE %I LIKE ''%%%s%%''',
      col_record.table_name,
      col_record.column_name,
      col_record.column_name,
      col_record.table_schema,
      col_record.table_name,
      col_record.column_name,
      target_str
    );
  END LOOP;
END;
$$ LANGUAGE plpgsql;

-- 调用示例
SELECT * FROM search_all_tables('你的目标字符串');

注意事项 #

• 执行用户需要拥有所有目标表的SELECT权限，否则会提示权限不足
• 建议排除系统库（pg_catalog、information_schema），避免返回大量无关结果
• 只针对字符类型列搜索，非字符列（如数字、日期）搜索字符串会报错或无意义
• 200张表的搜索时间取决于数据量大小，尽量在业务低峰期执行

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