如何在PostgreSQL数据库中全域查找指定值？

PostgreSQL没有内置的全域值搜索工具，但可以通过动态生成SQL或编写PL/pgSQL函数来实现，结合你已有的表列筛选逻辑，具体方案如下：

方案1：手动生成查询语句（适合临时查询） #

先通过information_schema.columns筛选出目标表和列，再自动生成每个列的查询语句，最后合并执行：

1. 运行以下SQL，替换'要搜索的值'为你要查找的内容，同时保留你的表/列筛选条件：

SELECT format(
  'SELECT ''%I'' AS table_catalog, ''%I'' AS table_schema, ''%I'' AS table_name, ''%I'' AS column_name, %I AS value FROM %I.%I WHERE %I::TEXT LIKE ''%%%s%%''',
  table_catalog,
  table_schema,
  table_name,
  column_name,
  column_name,
  table_schema,
  table_name,
  column_name,
  '要搜索的值' -- 替换为你的目标值
) AS search_query
FROM information_schema.columns
WHERE 1 = 1
  -- 保留你的筛选条件
  -- AND table_schema = 'ws_go_daa'
  AND table_name LIKE '%prism%'
  -- AND column_name LIKE '%itm%'
  AND column_name LIKE '%po%'
  -- 排除不适合文本搜索的类型（按需调整）
  AND data_type NOT IN ('bytea', 'jsonb', 'json');

2. 将查询结果中所有search_query字段的内容复制出来，用UNION ALL拼接后执行，就能得到所有匹配值的位置和内容。

方案2：编写PL/pgSQL函数（适合重复使用） #

如果需要频繁执行全域搜索，可以创建一个可复用的函数，自动遍历目标列并返回结果：

CREATE OR REPLACE FUNCTION search_all_tables(
  p_search_value TEXT,
  p_schema_filter TEXT DEFAULT NULL,
  p_table_filter TEXT DEFAULT NULL,
  p_column_filter TEXT DEFAULT NULL
) RETURNS TABLE(
  table_catalog TEXT,
  table_schema TEXT,
  table_name TEXT,
  column_name TEXT,
  value TEXT
) AS $$
DECLARE
  v_col RECORD;
  v_query TEXT;
BEGIN
  -- 遍历符合筛选条件的列
  FOR v_col IN
    SELECT table_catalog, table_schema, table_name, column_name
    FROM information_schema.columns
    WHERE (p_schema_filter IS NULL OR table_schema LIKE p_schema_filter)
      AND (p_table_filter IS NULL OR table_name LIKE p_table_filter)
      AND (p_column_filter IS NULL OR column_name LIKE p_column_filter)
      AND data_type NOT IN ('bytea', 'jsonb', 'json') -- 排除不适合的类型
  LOOP
    -- 生成单列查询语句，支持模糊匹配
    v_query := format(
      'SELECT ''%I'', ''%I'', ''%I'', ''%I'', %I::TEXT FROM %I.%I WHERE %I::TEXT ILIKE ''%%%s%%''',
      v_col.table_catalog,
      v_col.table_schema,
      v_col.table_name,
      v_col.column_name,
      v_col.column_name,
      v_col.table_schema,
      v_col.table_name,
      v_col.column_name,
      p_search_value
    );
    -- 执行查询并返回结果
    RETURN QUERY EXECUTE v_query;
  END LOOP;
END;
$$ LANGUAGE plpgsql;

调用示例 #

使用你需要的筛选条件和搜索值调用函数：

SELECT * FROM search_all_tables(
  '你的目标值',
  p_table_filter => '%prism%',
  p_column_filter => '%po%'
);

注意事项 #

• 性能优化：全域搜索会遍历大量数据，务必通过table_schema、table_name、column_name缩小范围，避免全库扫描。
• 大小写敏感：默认LIKE区分大小写，若需要忽略大小写，替换为ILIKE。
• 数据类型：上述方案跳过了bytea、jsonb等难以转文本的类型，若需要搜索这些类型，需单独处理（比如jsonb用jsonb_to_text）。

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