Jak wyszukać konkretną wartość we wszystkich tabelach (PostgreSQL)?

Oto funkcja pl / pgsql, która lokalizuje rekordy, w których każda kolumna zawiera określoną wartość. Jako argumenty przyjmuje wartość do przeszukiwania w formacie tekstowym, tablicę nazw tabel do przeszukiwania (domyślnie wszystkie tabele) oraz tablicę nazw schematów (domyślnie wszystkie nazwy schematów).

Zwraca strukturę tabeli ze schematem, nazwą tabeli, nazwą kolumny i pseudolumną ctid (nietrwałe fizyczne położenie wiersza w tabeli, zobacz System Kolumny )

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 : ten kod jest dla PG 9.1 lub nowszy. Możesz również chcieć wersję na github opartą na tej samej zasadzie, ale dodającą kilka usprawnień szybkości i raportowania.

Przykłady użycia w testowej bazie danych:

Szukaj we wszystkich tabelach w publicznym schemacie:

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)

Szukaj w konkretnej tabeli:

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

Szukaj w podzbiorze tabel uzyskanych z select:

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)

Get a result row with the corresponding base tabela I i ctid:

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

Aby ponownie przetestować Wyrażenie regularne zamiast ścisłej równości, jak grep, to:

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

Można zmienić na:

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

Jedyne narzędzie jakie znam to: SQL Workbench / J: http://www.sql-workbench.net/

Narzędzie oparte na Javie / JDBC, które oferuje specjalne (własne) "polecenie" SQL do przeszukiwania wszystkich (lub tylko wybranych) tabel w bazie danych:

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

I jeśli ktoś myśli, że to może pomóc. Oto funkcja @ Daniel Vérité, z innym param, który akceptuje nazwy kolumn, które mogą być używane w wyszukiwaniu. W ten sposób skraca czas przetwarzania. Przynajmniej w moim teście znacznie się zmniejszyło.

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;

Poniżej znajduje się przykład użycia funkcji search_function utworzonej powyżej.

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

Bez zapisywania nowej procedury można użyć bloku kodu i wykonać, aby uzyskać tabelę zdarzeń. Wyniki można filtrować według schematu, nazwy tabeli lub kolumny.

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;

Oto funkcja @ Daniel Vérité z funkcją raportowania postępów. Raportuje postępy na trzy sposoby:

1. by RAISE NOTICE;
2. poprzez zmniejszenie wartości dostarczonej sekwencji {progress_seq} z {total number of colums to search in} down To 0;
3. zapisując postęp wraz ze znalezionymi tabelami do pliku tekstowego, położony w 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;

Aby przeszukać każdą kolumnę każdej tabeli pod kątem określonej wartości

To nie określa, jak dokładnie dopasować.
Nie określa też dokładnie, co należy zwrócić.

Zakładając:

• Znajdź dowolny wiersz z dowolną kolumną zawierające podaną wartość w swojej reprezentacji tekstowej-w przeciwieństwie do równania podanej wartości.
• zwraca nazwę tabeli (regclass) i wskaźnik elementu (ctid), ponieważ to najprostsze.

Oto martwy prosty, szybki i nieco brudny sposób:

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 $1, ctid FROM %s t WHERE t::text ~~ %L'
       , _tbl, '%' || _like_pattern || '%')
      USING _tbl;
   END LOOP;
END
$func$  LANGUAGE plpgsql;

Wywołanie:

SELECT * FROM search_whole_db('mypattern');

Podaj wzorzec wyszukiwania bez zamykania %.

Dlaczego lekko brudne?

Jeśli separatory i dekoratory dla wiersza w reprezentacji text mogą być częścią wzorca wyszukiwania, mogą występować fałszywe alarmy:

• separator kolumn: , domyślnie
• cały wiersz jest zamknięty w nawiasach: ()
• niektóre wartości są zamknięte w podwójnych cudzysłowach "
• \ może być dodany jako escape char

A tekstowa reprezentacja niektórych kolumn może zależeć od lokalnych ustawień - ale ta dwuznaczność jest nieodłączna dla pytania, a nie dla mojego rozwiązania.

Każdy wiersz kwalifikujący jest zwracany raz tylko, nawet jeśli pasuje kilka razy(w przeciwieństwie do innych odpowiedzi tutaj).

Przeszukuje cały DB z wyjątkiem katalogów systemowych. Będzie zazwyczaj poświęć dużo czasu, aby zakończyć. Możesz ograniczyć się do określonych schematów / tabel (a nawet kolumn), jak pokazano w innych odpowiedziach. Lub dodać ogłoszenia i wskaźnik postępu, również wykazany w innej odpowiedzi.

Typ identyfikatora obiektu regclass jest reprezentowany jako nazwa tabeli, schema-kwalifikowany w razie potrzeby do disambiguate zgodnie z bieżącym search_path:

• Znajdź wskazaną nazwę tabeli za pomocą tabeli, pola i schematu nazwa

Co to jest ctid?

• jak rozłożyć ctid na numery stron i wierszy?

Możesz chcieć uciec od znaków o specjalnym znaczeniu we wzorze wyszukiwania. Zobacz:

• PostgreSQL: funkcja escape wyrażeń regularnych