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

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

il renvoie une structure de table avec Schéma, nom de la table, nom de la colonne et pseudo-colonne ctid (emplacement physique non durable de la ligne dans la table, voir système 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;

EDIT : ce code est pour PG 9.1 ou plus récent. En outre, vous pourriez vouloir la version sur github basé sur le même principe, mais en ajoutant une certaine vitesse et des améliorations de rapport.

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

recherche dans tous les tableaux 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 un tableau 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 tableaux obtenus à partir d'un 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)

obtenir une ligne de résultat avec la table de base correspondante et et ctid:

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

tester à nouveau une expression régulière au lieu d'une égalité stricte, comme grep, ceci:

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

peut être remplacé par:

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

le seul outil que je connaisse qui puisse faire cela est: SQL Workbench / J: http://www.sql-workbench.net/

un outil basé sur Java / JDBC qui offre une "commande" SQL (propriétaire) spéciale pour rechercher à travers toutes les tables (ou juste sélectionnées) dans une base de données:

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

http://www.sql-workbench.eu/wbgrepdata_png.html

et si quelqu'un pense que ça pourrait aider. Voici la fonction de @Daniel Vérité, avec un autre param qui accepte les noms de colonnes qui peuvent être utilisées 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')
);

sans stocker une nouvelle procédure, vous pouvez utiliser un bloc de code et exécuter pour obtenir une table d'occurences. Vous pouvez filtrer les résultats par schéma, table ou nom de colonne.

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;

Voici la fonction de @Daniel Vérité avec la fonctionnalité de rapport d'étape. Il rend compte des progrès de trois façons:

1. par SOULEVER AVIS;
2. par diminution de la valeur de la séquence {progress_seq} fournie {nombre total de colonnes à rechercher dans} 0;
3. en écrivant le progrès avec les tables trouvées dans le fichier de texte, situé à 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;

pour rechercher chaque colonne de chaque table pour une valeur particulière

cela ne définit pas comment correspondre exactement.

Il ne définit pas non plus ce qu'il faut retourner exactement.

en supposant:

• trouver n'importe quelle ligne avec n'importe quelle colonne contenant la valeur donnée dans sa représentation de texte - par opposition à = la valeur donnée.
• renvoie le nom de la table ( regclass ) et le pointeur d'article ( ctid ), parce que c'est le plus simple.

Voici une voie 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 , 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');

fournit le motif de recherche sans inclure % .

pourquoi un peu sale?

si les séparateurs et les décorateurs pour la rangée dans text la représentation peut faire partie du motif de recherche, il peut y avoir des faux positifs:

• séparateur de colonne: , par défaut
• la rangée entière est entre parenthèses: ()
• certaines valeurs sont incluses entre guillemets "
• \ peut être ajouté comme escape char

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

chaque ligne de qualification est retournée une seule fois seulement, même si elle correspond à plusieurs reprises (par opposition à d'autres réponses ici).

ceci recherche la base de données entière à l'exception des catalogues système. Généralement prendre beaucoup de temps pour finir . Vous pouvez restreindre à certains schémas, tableaux (ou même des colonnes) comme démontré dans d'autres réponses. Ou ajouter des avis et un indicateur de progrès, également démontré dans une autre réponse.

le type d'identificateur d'objet regclass est représenté en tant que nom de table, qualifié de schéma lorsque cela est nécessaire pour désambiguer selon l'actuel search_path :

• trouver le nom de la table de référence en utilisant le nom de la table, du champ et du schéma

Qu'est-ce que le ctid ?

• Comment puis-je décomposer l'itcp en Numéros de page et de ligne?

vous pourriez vouloir échapper aux caractères ayant une signification particulière dans le motif de recherche. Voir:

• PostgreSQL: Expression régulière fonction d'échappement