Comment rechercher une valeur spécifique dans tous les tableaux (PostgreSQL)?

Voici une de fonctions pl/pgsql qui localise les dossiers où une colonne contient une valeur spécifique.
Il prend comme arguments la valeur à rechercher dans le format de texte, un tableau de noms de table à rechercher dans (par défaut, toutes les tables) et un tableau de noms de schéma (par défaut tous les noms de schéma).

Elle retourne un tableau de structure avec le schéma, le nom de la table, le nom de la colonne et de la pseudo-colonne ctid (non-durable de l'emplacement physique de la ligne dans le tableau, voir Système De Colonnes)

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;

MODIFIER: ce code est pour PG 9.1 ou une version plus récente. Aussi, vous voudrez peut-être la version sur github sur le même principe, mais en ajoutant un peu de vitesse et d'amélioration du reporting.

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

De recherche dans tous les tableaux figurant dans le schéma public:

sélectionnez * à partir de search_columns ("foobar"); 
schemaname | tablename | columnname | rowctid 
------------+-----------+------------+--------- 
public | s3 | un nom | (0,11) 
public | s2 | relname | (7,29) 
public | w | corps | (0,2) 
(3 lignes) 

Recherche dans une table spécifique:

 sélectionnez * à partir de search_columns('foobar','{w}'); 
schemaname | tablename | columnname | rowctid 
------------+-----------+------------+--------- 
public | w | corps | (0,2) 
(1 ligne) 

De recherche dans un sous-ensemble de tableaux obtenus à partir d'une sélection:

sélectionnez * à partir de search_columns('foobar', array(select table_name::nom de information_schema.des tables où table_name comme s'%'), array['public']); 
schemaname | tablename | columnname | rowctid 
------------+-----------+------------+--------- 
public | s2 | relname | (7,29) 
public | s3 | un nom | (0,11) 
(2 lignes) 

Obtenir une ligne de résultat correspondant à la table de base et et ctid:

select * from public.w où ctid='(0,2)'; 
titre | corps | tsv 
-------+--------+--------------------- 
toto | foobar | 'foobar':2 'toto':1 

Pour tester de nouveau une expression régulière au lieu de stricte égalité, comme grep, ce:

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

peut être changé:

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

Et si quelqu'un pense que cela pourrait aider. Voici @Daniel-Vérité de la fonction, avec un autre param qui acceptent des noms de colonnes qui peuvent être utilisés dans la recherche. De cette manière, il diminue le temps de traitement. Au moins dans mon test il réduit beaucoup.

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;

Soufflet est un exemple d'utilisation de la search_function créé 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')
);

à la recherche de toutes les colonnes d'une table pour une valeur particulière

Cela ne définit pas la façon de faire correspondre exactement.

Ni faut-il définir ce qu'il doit retourner exactement.

En supposant que:

• Trouver une ligne avec une colonne contenant la valeur donnée dans le texte, la représentation - par opposition à égaler la valeur donnée.
• Retourner le nom de la table (regclass) et le pointeur d'élément (ctid), parce que c'est plus simple.

Voici une mort 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;

Appel:

SELECT * FROM search_whole_db('mypattern');

Fournir le motif de recherche, sans enfermer %.

Pourquoi légèrement sale?

Si des séparateurs et des décorateurs pour la ligne text représentation peut être une partie du modèle de recherche, il peut y avoir des faux positifs:

• séparateur de colonnes: , par défaut
• toute la ligne est mise entre parenthèses:()
• certaines valeurs sont entourées de guillemets doubles "
• \ peut être ajouté que l'évasion char

Et le texte de la représentation de certaines colonnes peuvent locales dépendent de paramètres - mais cette ambiguïté est inhérente à la question, pas à ma solution.

Chaque ligne éligible est retourné une fois, même lorsqu'elle correspond à plusieurs reprises (par opposition à d'autres réponses ici).

Cette recherche l'ensemble de la DB, sauf pour les catalogues système. Généralement prendre beaucoup de temps pour terminer. Vous pouvez restreindre à certains schémas, tableaux (ou même des colonnes) comme démontré dans d'autres réponses. Ou ajouter des notes et un indicateur de progrès, a également démontré dans une autre réponse.

La regclass identificateur d'objet type est représenté comme nom de table, de schéma-qualifiés, si nécessaire, pour lever l'ambiguïté, selon l'actuel search_path:

• Trouvez référencés nom de la table à l'aide du tableau, le champ et le nom du schéma

Quel est le ctid?

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

Vous pourriez vouloir pour échapper les caractères ayant une signification particulière dans le modèle de recherche. Voir:

• Fonction d'échappement pour l'expression régulière ou de motifs

-- Au-dessous de la fonction liste de toutes les tables qui contiennent une chaîne de caractères dans la base de données

 select TablesCount(‘StringToSearch’);

--Parcourt toutes les tables dans la base de données

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

-- Retourne le nombre de tables pour laquelle la condition est remplie.
-- Par exemple, si le texte existe dans l'un des champs de la table,
-- ensuite le compte sera supérieure à 0. Nous pouvons trouver les notifications
- dans la section Messages du résultat spectateur dans la base de données 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;

--Obtenir les champs de chaque table. Construit la clause where avec toutes les colonnes d'une table.

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