如何在所有表中搜索特定值（PostgreSQL）？

Question

是否可以在 PostgreSQL 中搜索每个表的每一列特定值？

类似的问题可以在此处找到对于甲骨文来说。

Answer 1

转储数据库的内容，然后使用 grep 怎么样？

$ pg_dump --data-only --inserts -U postgres your-db-name > a.tmp
$ grep United a.tmp
INSERT INTO countries VALUES ('US', 'United States');
INSERT INTO countries VALUES ('GB', 'United Kingdom');

相同的实用程序 pg_dump 可以在输出中包含列名称。只需将 --inserts 更改为 --column-inserts 即可。这样您也可以搜索特定的列名称。但如果我正在寻找列名，我可能会转储模式而不是数据。

$ pg_dump --data-only --column-inserts -U postgres your-db-name > a.tmp
$ grep country_code a.tmp
INSERT INTO countries (iso_country_code, iso_country_name) VALUES ('US', 'United  States');
INSERT INTO countries (iso_country_code, iso_country_name) VALUES ('GB', 'United Kingdom');

Answer 2

下面是一个 pl/pgsql 函数，用于查找任何列包含特定值的记录。
它将要搜索的文本格式的值、要搜索的表名数组（默认为所有表）和模式名称数组（默认为所有模式名称）作为参数。

它返回一个表结构，其中包含架构、表名称、列名称和伪列 ctid （表中行的非持久物理位置，请参阅 系统列）

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_schema.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
      EXECUTE format('SELECT ctid FROM %I.%I WHERE cast(%I as text)=%L',
       schemaname,
       tablename,
       columnname,
       needle
      )
    LOOP
      -- uncomment next line to get some progress report
      -- RAISE NOTICE 'hit in %.%', schemaname, tablename;
      RETURN NEXT;
    END LOOP;
 END LOOP;
END;
$ language plpgsql;

另请参阅 github 上的版本 基于相同的原理，但增加了一些速度和报告改进。

在测试数据库中使用的示例：

• 在公共模式中的所有表中搜索：

select * from search_columns('foobar');
 schemaname | tablename | columnname | rowctid 
------------+-----------+------------+---------
 public     | s3        | usename    | (0,11)
 public     | s2        | relname    | (7,29)
 public     | w         | body       | (0,2)
(3 rows)

• 在特定表中搜索：

 select * from search_columns('foobar','{w}');
 schemaname | tablename | columnname | rowctid 
------------+-----------+------------+---------
 public     | w         | body       | (0,2)
(1 row)

• 在从选择获得的表子集中搜索：

select * from search_columns('foobar', array(select table_name::name from information_schema.tables where table_name like 's%'), array['public']);
 schemaname | tablename | columnname | rowctid 
------------+-----------+------------+---------
 public     | s2        | relname    | (7,29)
 public     | s3        | usename    | (0,11)
(2 rows)

• 获取具有相应基表和 ctid 的结果行：

select * from public.w where ctid='(0,2)';
 title |  body  |         tsv         
-------+--------+---------------------
 toto  | foobar | 'foobar':2 'toto':1

变体

• 要测试正则表达式而不是严格相等（例如 grep），查询的这一部分：

  从 %I.%I WHERE 中选择 ctid (%I as text)=%L

  可以更改为：

  SELECT ctid FROM %I.%I WHERE cast(%I as text) ~ %L

• 对于不区分大小写的比较，您可以编写：

  SELECT ctid FROM %I.%I WHERE lower(cast(%I as text)) = lower(%L)

Answer 3

在每个表的每一列中搜索特定值

假设：

• 查找任何列在其文本表示中包含给定值的任何行 - 而不是等于< /em> 给定值。
• 返回表名称 (regclass) 和元组 ID (ctid)，因为这最简单。 （您可以轻松适应返回您想要的任何内容。）

这是一种非常简单、快速且有点肮脏的方法：

CREATE OR REPLACE FUNCTION search_whole_db(_like_pattern text, _schema text)
  RETURNS TABLE(_tbl regclass, _ctid tid)
  LANGUAGE plpgsql AS
$func$
BEGIN
   FOR _tbl IN
      SELECT c.oid::regclass
      FROM   pg_class c
      JOIN   pg_namespace n ON n.oid = relnamespace
      WHERE  c.relkind = 'r'                           -- only tables
      AND    n.nspname !~ '^(pg_|information_schema)'  -- exclude system schemas
      AND   (n.nspname = _schema OR _schema IS NULL)
      ORDER BY n.nspname, c.relname
   LOOP
      RETURN QUERY EXECUTE format(
         'SELECT $1, ctid FROM %s t WHERE t::text ~~ %L'
       , _tbl, '%' || _like_pattern || '%')
      USING _tbl;
   END LOOP;
END
$func$;

如果未指定模式，则搜索整个数据库：（系统模式始终被排除。）

SELECT * FROM search_whole_db('mypattern');

仅对于给定模式：

SELECT * FROM search_whole_db('mypattern', 'myschema');

提供搜索模式，不包含 %。
您可能想要转义模式中的特殊字符。请参阅：

• 正则表达式或 LIKE 模式的转义函数

为什么“有点脏”？

如果 text 表示中的行的分隔符和修饰符可以是搜索模式的一部分，则可能会出现误报。特殊字符有：

• 列分隔符：, 默认情况下
• 整行括在括号中：()
• 某些值括在双引号中 "
• \ 可以添加为转义字符

另外，某些列的文本表示可能取决于本地设置 - 但这种歧义是问题所固有的，而不仅仅是我的解决方案

返回每个符合条件的行<。仅一次，即使它匹配多次（与此处的其他答案相反）

搜索整个数据库通常需要很长时间才能完成< /em>。您可能想要限制某些模式/表（甚至列），如其他答案中所示。或者添加通知和进度指示器，这也在另一个答案中演示了

。类型表示为表名，在必要时根据当前 search_path 进行模式限定：

• 如何检查给定架构中是否存在表
• 使用表、字段和架构名称查找引用的表名称

什么是 <代码>ctid？

• 如何将 ctid 分解为页号和行号？

您可能希望在搜索模式中转义具有特殊含义的字符。请参阅：

• 正则表达式或 LIKE 模式的转义函数

Answer 4

有一种方法可以在不创建函数或使用外部工具的情况下实现此目的。通过使用 Postgres 的 query_to_xml() 函数（可以在另一个查询中动态运行一个查询），可以跨多个表搜索文本。这是基于我的答案检索所有表的行数：

搜索字符串foo 在架构中的所有表中，可以使用以下内容：

with found_rows as (
  select format('%I.%I', table_schema, table_name) as table_name,
         query_to_xml(format('select to_jsonb(t) as table_row 
                              from %I.%I as t 
                              where t::text like ''%%foo%%'' ', table_schema, table_name), 
                      true, false, '') as table_rows
  from information_schema.tables 
  where table_schema = 'public'
)
select table_name, x.table_row
from found_rows f
  left join xmltable('//table/row' 
                     passing table_rows
                       columns
                         table_row text path 'table_row') as x on true

请注意，使用 xmltable 需要 Postgres 10 或更高版本。对于较旧的 Postgres 版本，也可以使用 xpath() 来完成。

with found_rows as (
  select format('%I.%I', table_schema, table_name) as table_name,
         query_to_xml(format('select to_jsonb(t) as table_row 
                              from %I.%I as t 
                              where t::text like ''%%foo%%'' ', table_schema, table_name), 
                      true, false, '') as table_rows
  from information_schema.tables 
  where table_schema = 'public'
)
select table_name, x.table_row
from found_rows f
   cross join unnest(xpath('/table/row/table_row/text()', table_rows)) as r(data)

公共表表达式 (WITH ...) 只是为了方便而使用。它循环访问 public 架构中的所有表。对于每个表，通过 query_to_xml() 函数运行以下查询：

select to_jsonb(t)
from some_table t
where t::text like '%foo%';

where 子句用于确保仅针对包含搜索字符串的行生成昂贵的 XML 内容。这可能会返回如下内容：

<table xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<row>
  <table_row>{"id": 42, "some_column": "foobar"}</table_row>
</row>
</table>

完整行到 jsonb 的转换已完成，以便在结果中可以看到哪个值属于哪一列。

上面的代码可能会返回如下内容：

table_name   |   table_row
-------------+----------------------------------------
public.foo   |  {"id": 1, "some_column": "foobar"}
public.bar   |  {"id": 42, "another_column": "barfoo"}

Postgres 10+ 的在线示例

旧版 Postgres 的在线示例

Answer 5

如果您使用 IntelliJ 将数据库添加到数据库视图，然后右键单击数据库并选择全文搜索，它将列出您的特定文本的所有表和所有字段。

Answer 6

无需存储新过程，您就可以使用代码块并执行来获取事件表。您可以按架构、表或列名称过滤结果。

DO $
DECLARE
  value int := 0;
  sql text := 'The constructed select statement';
  rec1 record;
  rec2 record;
BEGIN
  DROP TABLE IF EXISTS _x;
  CREATE TEMPORARY TABLE _x (
    schema_name text, 
    table_name text, 
    column_name text,
    found text
  );
  FOR rec1 IN 
        SELECT table_schema, table_name, column_name
        FROM information_schema.columns 
        WHERE table_name <> '_x'
                AND UPPER(column_name) LIKE UPPER('%%')                  
                AND table_schema <> 'pg_catalog'
                AND table_schema <> 'information_schema'
                AND data_type IN ('character varying', 'text', 'character', 'char', 'varchar')
        LOOP
    sql := concat('SELECT ', rec1."column_name", ' AS "found" FROM ',rec1."table_schema" , '.',rec1."table_name" , ' WHERE UPPER(',rec1."column_name" , ') LIKE UPPER(''','%my_substring_to_find_goes_here%' , ''')');
    RAISE NOTICE '%', sql;
    BEGIN
        FOR rec2 IN EXECUTE sql LOOP
            RAISE NOTICE '%', sql;
            INSERT INTO _x VALUES (rec1."table_schema", rec1."table_name", rec1."column_name", rec2."found");
        END LOOP;
    EXCEPTION WHEN OTHERS THEN
    END;
  END LOOP;
  END; $;

SELECT * FROM _x;

Answer 7

如果有人认为这有帮助。这是@Daniel Vérité 的函数，其中另一个参数接受可在搜索中使用的列名称。这样就减少了处理时间。至少在我的测试中它减少了很多。

CREATE OR REPLACE FUNCTION search_columns(
    needle text,
    haystack_columns name[] default '{}',
    haystack_tables name[] default '{}',
    haystack_schema name[] default '{public}'
)
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)
      WHERE (c.table_name=ANY(haystack_tables) OR haystack_tables='{}')
        AND c.table_schema=ANY(haystack_schema)
        AND (c.column_name=ANY(haystack_columns) OR haystack_columns='{}')
        AND t.table_type='BASE TABLE'
  LOOP
    EXECUTE format('SELECT ctid FROM %I.%I WHERE cast(%I as text)=%L',
       schemaname,
       tablename,
       columnname,
       needle
    ) INTO rowctid;
    IF rowctid is not null THEN
      RETURN NEXT;
    END IF;
 END LOOP;
END;
$ language plpgsql;

下面是上面创建的 search_function 的使用示例。

SELECT * FROM search_columns('86192700'
    , array(SELECT DISTINCT a.column_name::name FROM information_schema.columns AS a
            INNER JOIN information_schema.tables as b ON (b.table_catalog = a.table_catalog AND b.table_schema = a.table_schema AND b.table_name = a.table_name)
        WHERE 
            a.column_name iLIKE '%cep%' 
            AND b.table_type = 'BASE TABLE'
            AND b.table_schema = 'public'
    )

    , array(SELECT b.table_name::name FROM information_schema.columns AS a
            INNER JOIN information_schema.tables as b ON (b.table_catalog = a.table_catalog AND b.table_schema = a.table_schema AND b.table_name = a.table_name)
        WHERE 
            a.column_name iLIKE '%cep%' 
            AND b.table_type = 'BASE TABLE'
            AND b.table_schema = 'public')
);

Answer 8

这是@Daniel Vérité 的带有进度报告功能的函数。
它以三种方式报告进展情况：

1. 通过“RAISE NOTICE”；
2. 通过减少提供的 {progress_seq} 序列的值
   {要搜索的总列数}降至 0；
3. 通过将进度与找到的表格一起写入文本文件，
   位于 c:\windows\temp\{progress_seq}.txt。

_

CREATE OR REPLACE FUNCTION search_columns(
    needle text,
    haystack_tables name[] default '{}',
    haystack_schema name[] default '{public}',
    progress_seq text default NULL
)
RETURNS table(schemaname text, tablename text, columnname text, rowctid text)
AS $
DECLARE
currenttable text;
columnscount integer;
foundintables text[];
foundincolumns text[];
begin
currenttable='';
columnscount = (SELECT count(1)
      FROM information_schema.columns c
      JOIN information_schema.tables t ON
        (t.table_name=c.table_name AND t.table_schema=c.table_schema)
      WHERE (c.table_name=ANY(haystack_tables) OR haystack_tables='{}')
        AND c.table_schema=ANY(haystack_schema)
        AND t.table_type='BASE TABLE')::integer;
PERFORM setval(progress_seq::regclass, columnscount);

  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)
      WHERE (c.table_name=ANY(haystack_tables) OR haystack_tables='{}')
        AND c.table_schema=ANY(haystack_schema)
        AND t.table_type='BASE TABLE'
  LOOP
    EXECUTE format('SELECT ctid FROM %I.%I WHERE cast(%I as text)=%L',
       schemaname,
       tablename,
       columnname,
       needle
    ) INTO rowctid;
    IF rowctid is not null THEN
      RETURN NEXT;
      foundintables = foundintables || tablename;
      foundincolumns = foundincolumns || columnname;
      RAISE NOTICE 'FOUND! %, %, %, %', schemaname,tablename,columnname, rowctid;
    END IF;
         IF (progress_seq IS NOT NULL) THEN 
        PERFORM nextval(progress_seq::regclass);
    END IF;
    IF(currenttable<>tablename) THEN  
    currenttable=tablename;
     IF (progress_seq IS NOT NULL) THEN 
        RAISE NOTICE 'Columns left to look in: %; looking in table: %', currval(progress_seq::regclass), tablename;
        EXECUTE 'COPY (SELECT unnest(string_to_array(''Current table (column ' || columnscount-currval(progress_seq::regclass) || ' of ' || columnscount || '): ' || tablename || '\n\nFound in tables/columns:\n' || COALESCE(
        (SELECT string_agg(c1 || '/' || c2, '\n') FROM (SELECT unnest(foundintables) AS c1,unnest(foundincolumns) AS c2) AS t1)
        , '') || ''',''\n''))) TO ''c:\WINDOWS\temp\' || progress_seq || '.txt''';
    END IF;
    END IF;
 END LOOP;
END;
$ language plpgsql;

Answer 9

-- 下面的函数将列出数据库中包含特定字符串的所有表

 select TablesCount(‘StringToSearch’);

-- 迭代数据库中的所有表

CREATE OR REPLACE FUNCTION **TablesCount**(_searchText TEXT)
RETURNS text AS 
$ -- here start procedural part
   DECLARE _tname text;
   DECLARE cnt int;
   BEGIN
    FOR _tname IN SELECT table_name FROM information_schema.tables where table_schema='public' and table_type='BASE TABLE'  LOOP
         cnt= getMatchingCount(_tname,Columnames(_tname,_searchText));
                                RAISE NOTICE 'Count% ', CONCAT('  ',cnt,' Table name: ', _tname);
                END LOOP;
    RETURN _tname;
   END;
$ -- here finish procedural part
LANGUAGE plpgsql; -- language specification

-- 返回满足条件的表的数量。
-- 例如，如果表的任何字段中存在预期文本，
-- 那么计数就会大于0。我们可以找到通知
-- 在 postgres 数据库结果查看器的消息部分。

CREATE OR REPLACE FUNCTION **getMatchingCount**(_tname TEXT, _clause TEXT)
RETURNS int AS 
$
Declare outpt text;
    BEGIN
    EXECUTE 'Select Count(*) from '||_tname||' where '|| _clause
       INTO outpt;
       RETURN outpt;
    END;
$ LANGUAGE plpgsql;

--获取每个表的字段。使用表的所有列构建 where 子句。

CREATE OR REPLACE FUNCTION **Columnames**(_tname text,st text)
RETURNS text AS 
$ -- here start procedural part
DECLARE
                _name text;
                _helper text;
   BEGIN
                FOR _name IN SELECT column_name FROM information_schema.Columns WHERE table_name =_tname LOOP
                                _name=CONCAT('CAST(',_name,' as VarChar)',' like ','''%',st,'%''', ' OR ');
                                _helper= CONCAT(_helper,_name,' ');
                END LOOP;
                RETURN CONCAT(_helper, ' 1=2');

   END;
$ -- here finish procedural part
LANGUAGE plpgsql; -- language specification

Answer 10

希望这会对某人有所帮助
我已经在 PostgreSQL 14 上的 DBeaver 中检查过这一点，并且它可以进行

1. 字符串搜索

do $
DECLARE
    find_count int;
   search_text varchar := '''%test string%''';
   command1 varchar;
   cur information_schema."columns"%rowtype;
begin
    FOR cur IN
        select c.* 
        from information_schema."columns" c 
        where c.table_schema = 'public'
            and c.udt_name in ('varchar','text')
    loop
       sql_command = FORMAT('select count(1) from %s where upper("%s") like upper(%s)', cur.table_name, cur.column_name, search_text);
        
       EXECUTE command1 INTO find_count;
       
       if (find_count>0) then
         raise notice '%', command1;
       end if;
     
    END LOOP;   
END;
$;

2. 数字搜索

do $
DECLARE
   find_count int;
   search_int int := 4890;
   sql_command varchar;
   cur information_schema."columns"%rowtype;
begin
    FOR cur IN
        select c.* 
        from information_schema."columns" c 
        where c.table_schema = 'public'
            and c.udt_name in ('float8','int8','int4','numeric')
    loop
       sql_command = FORMAT('select count(1) from %s where "%s" = %s', cur.table_name, cur.column_name, search_int::text);
       
       EXECUTE sql_command INTO find_count;
       
       if (find_count > 0) then
         raise notice '%', sql_command;
       end if;
     
    END LOOP;   
END;
$;