Как найти конкретное значение во всех таблицах (PostgreSQL)?

вот функция 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)
      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
    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;

EDIT: этот код предназначен для PG 9.1 или новее. Кроме того, вы можете захотеть версию на 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)

поиск в подмножестве таблиц, полученных из a выберите:

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, это:

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

можно изменить на:

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

единственным известным мне инструментом, который может сделать это для SQL верстак/Дж: http://www.sql-workbench.net/

инструмент на основе Java / JDBC, который предлагает специальную (проприетарную) команду SQL для поиска по всем (или только выбранным) таблицам в базе данных:

http://www.sql-workbench.eu/manual/wb-commands.html#command-search-data
http://www.sql-workbench.eu/wbgrepdata_png.html

и если кто-то думает, что это может помочь. Вот функция @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;

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')
);

без сохранения новой процедуры можно использовать блок кода и выполнить для получения таблицы вхождений. Вы можете фильтровать результаты по имени схемы, таблицы или столбца.

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;

вот функция @Daniel Vérité с функциональностью отчетов о ходе работы. Он сообщает о прогрессе в трех направлениях:

1. путем повышения уведомления;
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;

для поиска каждого столбца каждой таблицы для определенного значения

это не определяет, как точно соответствовать.
Он также не определяет, что именно нужно вернуть.

предположим:

• найти любую строку с любым столбцом содержащих заданное значение в его текстовом представлении-в отличие от в размере данное значение.
• возвращает имя таблицы (regclass) и указатель элемента (ctid), потому что это простой.

вот мертвый простой, быстрый и немного грязный способ:

CREATE OR REPLACE FUNCTION search_whole_db(_like_pattern text)
  RETURNS TABLE(_tbl regclass, _ctid tid) 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
      ORDER BY n.nspname, c.relname
   LOOP
      RETURN QUERY EXECUTE format(
         'SELECT , ctid FROM %s t WHERE t::text ~~ %L'
       , _tbl, '%' || _like_pattern || '%')
      USING _tbl;
   END LOOP;
END
$func$  LANGUAGE plpgsql;

звоните:

SELECT * FROM search_whole_db('mypattern');

обеспечить шаблон поиска без включения %.

почему немного грязный?

если разделители и декораторы для строки в text представление может быть частью шаблона поиска, может быть ложным положительные стороны:

• колонки сепаратора: , по умолчанию
• вся строка заключена в скобки:()
• какие значения заключены в двойные кавычки "
• \ может быть добавлен как escape char

и текстовое представление некоторых столбцов может зависеть от локальных настроек - но эта двусмысленность присуща вопросу, а не моему решению.

возвращается каждая квалификационная строка после только, даже если он соответствует несколько раз (в отличие от других ответов здесь).

это поиск по всей БД, за исключением системных каталогов. Будет как правило занять много времени, чтобы закончить. Вы можете ограничиться определенными схемами / таблицами (или даже столбцами), как показано в других ответах. Или добавьте уведомления и индикатор прогресса, также продемонстрированный в другом ответе.

The regclass объект тип идентификатора представлен как имя таблицы, схема-квалифицированный, где это необходимо для устранения неоднозначности в соответствии с текущим search_path:

• найти имя таблицы, на которую ссылается, используя имя таблицы, поля и схемы

что такое ctid?

• как разложить ctid на номера страниц и строк?

вы можете избежать символов со специальным значением в поиске узор. Смотрите:

• PostgreSQL: функция escape регулярного выражения