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

Sans enregistrer une nouvelle procédure, vous pouvez utiliser un bloc de code et exécuter pour obtenir un tableau d'occurrences. 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;

Answer 2

Si vous utilisez IntelliJ, ajoutez votre base de données à la vue Base de données, puis cliquez avec le bouton droit de la souris sur les bases de données et sélectionnez la recherche plein texte, ce qui donnera la liste de toutes les tables et de tous les champs pour votre texte spécifique.

Answer 3

Voici la fonction de @Daniel Vérité avec une fonctionnalité de rapport de progression. Elle rapporte les progrès de trois façons :

1. par RAISE NOTICE ;
2. en diminuant la valeur de la séquence {progress_seq} fournie de {nombre total de colonnes à rechercher} jusqu'à 0 ;
3. en écrivant la progression avec les tables trouvées dans un fichier texte, situé dans 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;

Answer 4

-- La fonction ci-dessous énumère toutes les tables qui contiennent une chaîne spécifique dans la base de données.

 select TablesCount(‘StringToSearch’);

--Importe toutes les tables de 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

-- Renvoie le nombre de tables pour lesquelles la condition est remplie. -- Par exemple, si le texte souhaité existe dans l'un des champs de la table, -- alors le compte sera supérieur à 0. Nous pouvons trouver les notifications -- dans la section Messages du visualisateur de résultats 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;

--Retrouver 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