Busca de um valor em todas as tabelas e colunas no PostgreSQL 🐘

Fazer SELECT ... WHERE coluna ILIKE '%valor%' manualmente em dezenas de tabelas não é prático!

A solução!?

Podemos simplesmente percorrer todas as tabelas (ignorando views para evitar erros) e checar colunas de tipos textuais (varchar, text, char, json, jsonb).

O script abaixo cria uma tabela temporária com os resultados da busca.

DO $$
DECLARE
    r RECORD;
    v_sql   TEXT;
    v_needle TEXT := 'jonathasrocha';  -- termo a procurar
BEGIN
    -- tabela temporária para guardar achados
    CREATE TEMP TABLE IF NOT EXISTS search_hits (
        schema_name text,
        table_name  text,
        column_name text,
        row_count   integer
    ) ON COMMIT DROP;

    TRUNCATE search_hits;

    FOR r IN
        SELECT c.table_schema, c.table_name, c.column_name
        FROM information_schema.columns c
        JOIN information_schema.tables  t
          ON t.table_schema = c.table_schema
         AND t.table_name   = c.table_name
        WHERE t.table_type = 'BASE TABLE'
          AND c.table_schema NOT IN ('pg_catalog','information_schema')
          AND c.data_type IN ('character varying','text','character','json','jsonb')
    LOOP
        v_sql := format(
            'INSERT INTO search_hits(schema_name, table_name, column_name, row_count)
             SELECT %L, %L, %L, COUNT(*)
             FROM %I.%I
             WHERE %I::text ILIKE $1',
            r.table_schema, r.table_name, r.column_name,
            r.table_schema, r.table_name,
            r.column_name
        );

        BEGIN
            EXECUTE v_sql USING '%' || v_needle || '%';
        EXCEPTION
            WHEN others THEN
                RAISE NOTICE 'Ignorando %.% (%.%): %',
                    r.table_schema, r.table_name, r.column_name, SQLERRM;
        END;
    END LOOP;
END $$ LANGUAGE plpgsql;

-- Consulta na tabela de resultados
SELECT *
FROM search_hits
WHERE row_count > 0
ORDER BY row_count DESC, schema_name, table_name, column_name;

---

Exemplo da saída

O resultado será uma lista de schemas, tabelas e colunas onde o valor foi encontrado, junto com a quantidade de ocorrências:

 schema_name | table_name | column_name | row_count
-------------+------------+-------------+-----------
 public      | user       | email       |        2
 public      | orders     | client      |        1

---

Com esse snippet em praticidade, mapeando onde está um dado dentro do PostgreSQL.

Use sem rodeio e sem limites, vai por mim... Facilita muitoo!

---

👉 Já precisou “caçar” um dado perdido no banco e não soube como?