Comment rechercher une valeur spécifique dans toutes les tables (PostgreSQL) ?

Question

Est-il possible de rechercher chaque colonne de chaque tableau pour une valeur particulière dans PostgreSQL ?

Une question similaire est disponible ici pour Oracle.

Answer 1

Que diriez-vous de vider le contenu de la base de données, puis d'utiliser 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');

Le même utilitaire, pg_dump, peut inclure les noms de colonnes dans la sortie. Il suffit de modifier --inserts a --column-inserts . De cette façon, vous pouvez également rechercher des noms de colonnes spécifiques. Mais si je cherchais des noms de colonnes, je viderais probablement le schéma plutôt que les données.

$ 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

Voici un Fonction pl/pgsql qui localise les enregistrements dont une colonne contient une valeur spécifique. Elle prend comme arguments la valeur à rechercher au format texte, un tableau de noms de tables à rechercher (par défaut, toutes les tables) et un tableau de noms de schémas (par défaut, tous les schémas).

Il retourne une structure de table avec le schéma, le nom de la table, le nom de la colonne et la pseudo-colonne. ctid (emplacement physique non durable de la ligne dans le tableau, voir Colonnes du système )

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;

Voir également le version sur github basé sur le même principe mais ajoutant quelques améliorations en termes de vitesse et de reporting.

Exemples d'utilisation dans une base de données de test :

• Recherche dans toutes les tables du schéma public :

  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)

• Recherche dans une table spécifique :

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

• Recherche dans un sous-ensemble de tables obtenu à partir d'une sélection :

  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)

• Obtenir une ligne de résultat avec la table de base et le ctid correspondants :

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

Variantes

• Pour tester contre une expression régulière au lieu d'une égalité stricte, comme grep, cette partie de la requête :

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

  peut être changé en :

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

• Pour des comparaisons insensibles à la casse, vous pourriez écrire :

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

Answer 3

pour rechercher une valeur particulière dans chaque colonne de chaque tableau

Cela ne définit pas la manière de correspondre exactement.
Elle ne définit pas non plus ce qu'il faut rendre exactement.

Assumant :

• Trouver n'importe quelle ligne avec n'importe quelle colonne contenant la valeur donnée dans sa représentation textuelle - par opposition à égalant la valeur donnée.
• Renvoie le nom de la table ( regclass ) et l'ID du tuple ( ctid ), car c'est le plus simple.

Voici une méthode très simple, rapide et légèrement sale :

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;

Appelez :

SELECT * FROM search_whole_db('mypattern');

Fournir le modèle de recherche sans l'entourer % .

Pourquoi un peu sale ?

Si les séparateurs et les décorateurs de la rangée en text peut faire partie du modèle de recherche, il peut y avoir des faux positifs :

• séparateur de colonnes : , par défaut
• La ligne entière est entre parenthèses : ()
• certaines valeurs sont entre guillemets "
• \ peut être ajouté comme caractère d'échappement

Et la représentation textuelle de certaines colonnes peut dépendre des paramètres locaux - mais cette ambiguïté est inhérente à la question, pas à ma solution.

Chaque ligne qualifiée est retournée une fois seulement, même lorsqu'il correspond à plusieurs reprises (contrairement aux autres réponses ici).

Cela permet de rechercher dans l'ensemble de la BD, à l'exception des catalogues système. En général prendre beaucoup de temps pour terminer . Vous pourriez vouloir restreindre à certains schémas / tables (ou même colonnes) comme démontré dans d'autres réponses. Ou ajouter des avis et un indicateur de progression, également démontré dans une autre réponse.

Le site regclass Le type d'identifiant de l'objet est représenté par le nom de la table, qualifié par le schéma si nécessaire, afin d'éviter toute ambiguïté en fonction de l'identifiant actuel de l'objet. search_path :

• Trouver le nom de la table référencée en utilisant le nom de la table, du champ et du schéma.

Quel est le ctid ?

• Comment décomposer le ctid en numéros de page et de ligne ?

Vous pouvez vouloir échapper les caractères ayant une signification spéciale dans le modèle de recherche. Voir :

• Fonction d'échappement pour les expressions régulières ou les motifs LIKE

Answer 4

Il existe un moyen d'y parvenir sans créer une fonction ou utiliser un outil externe. En utilisant la fonction query_to_xml() qui peut exécuter dynamiquement une requête à l'intérieur d'une autre requête, il est possible de rechercher un texte dans plusieurs tables. Ceci est basé sur ma réponse pour récupérer le rowcount pour toutes les tables :

Pour rechercher la chaîne de caractères foo sur toutes les tables d'un schéma, on peut utiliser la méthode suivante :

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

Notez que l'utilisation de xmltable requiert Postgres 10 ou une version plus récente. Pour les versions plus anciennes de Postgres, cela peut également être fait en utilisant 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)

L'expression commune du tableau ( WITH ... ) n'est utilisé que par commodité. Il parcourt en boucle toutes les tables de la base de données public schéma. Pour chaque table, la requête suivante est exécutée par l'intermédiaire de l'application query_to_xml() fonction :

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

La clause where est utilisée pour s'assurer que la génération coûteuse du contenu XML n'est effectuée que pour les lignes qui contiennent la chaîne de recherche. Cela peut donner quelque chose comme ceci :

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

La conversion de la ligne complète en jsonb est fait, de sorte que dans le résultat on puisse voir quelle valeur appartient à quelle colonne.

La méthode ci-dessus pourrait donner quelque chose comme ceci :

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

Un exemple en ligne pour Postgres 10+

Exemple en ligne pour les anciennes versions de Postgres

Answer 5

Et si quelqu'un pense que ça peut aider. Voici la fonction de @Daniel Vérité, avec un autre paramètre qui accepte les noms des colonnes qui peuvent être utilisées dans la recherche. De cette façon, cela diminue le temps de traitement. Au moins dans mon test, il a beaucoup diminué.

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;

Voici un exemple d'utilisation de la fonction search_function créée ci-dessus.

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