PostgreSQL全库搜索函数仅支持精确匹配，需实现通配搜索返回匹配的schema、表、列信息

逻辑调整说明 #

原函数拼接的动态SQL采用列值 = 检索词的精准匹配判断，只要将该等值判断替换为模糊匹配操作符，即可实现通配符检索。调整过程完全保留原有函数的表权限校验、可选的schema/表范围过滤、返回字段结构能力，不需要修改原函数的入参定义，对原有调用逻辑无破坏性影响。

优化后完整函数实现 #

CREATE OR REPLACE FUNCTION search_columns(
    needle text,
    haystack_tables name[] default '{}',
    haystack_schema name[] default '{}'
)
RETURNS table(schemaname text, tablename text, columnname text, rowctid text)
AS $$
begin
  FOR schemaname,tablename,columnname IN
      SELECT c.table_schema,c.table_name,c.column_name
      FROM information_schema.columns c
        JOIN information_schema.tables t ON
          (t.table_name=c.table_name AND t.table_schema=c.table_schema)
        JOIN information_schema.table_privileges p ON
          (t.table_name=p.table_name AND t.table_schema=p.table_schema
              AND p.privilege_type='SELECT')
        JOIN information.schemata s ON
          (s.schema_name=t.table_schema)
      WHERE (c.table_name=ANY(haystack_tables) OR haystack_tables='{}')
        AND (c.table_schema=ANY(haystack_schema) OR haystack_schema='{}')
        AND t.table_type='BASE TABLE'
  LOOP
    FOR rowctid IN
      -- 核心修改：将原等值判断=替换为ILIKE，支持%、_通配符的不区分大小写模糊匹配
      EXECUTE format('SELECT ctid FROM %I.%I WHERE cast(%I as text) ILIKE %L',
       schemaname,
       tablename,
       columnname,
       needle
      )
    LOOP
      -- 取消注释即可开启命中日志反馈
      -- RAISE NOTICE 'hit in %.%', schemaname, tablename;
      RETURN NEXT;
    END LOOP;
 END LOOP;
END;
$$ language plpgsql

使用规则 #

• 模糊匹配沿用PostgreSQL原生的SQL通配符语法：%匹配任意长度、任意类型的字符序列，_匹配单个任意字符
• 默认使用ILIKE操作符实现不区分大小写的匹配，如果业务需要严格区分大小写的检索效果，可将代码中的ILIKE替换为LIKE
• 由于通配符逻辑是透传传入的检索词，使用时需要在检索词中手动拼接对应通配符，例如要匹配所有列值包含"foo"的记录，需要传入%foo%而非直接传入foo

调用示例 #

• 全局检索所有基表中任意列值包含"foo"的记录：
  SELECT * FROM search_columns('%foo%');
• 仅在public schema的user、order表中，检索列值以"2024"开头的记录：
  SELECT * FROM search_columns('2024%', haystack_tables := '{user, order}', haystack_schema := '{public}');

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