Postgresql find a string inside the columns of all tables

Question

I want to search for a string that is inside of a column of some table. The system has about 200 tables. I need to search all columns of all tables to achieve what I want.

Is it possible in Postgresql 12?

Answer 1

If all that is needed are the tables and columns in which the value occurs, then the following query can be used:

WITH
parameters(search_pattern, schemas) AS (
  VALUES ('regex', '{public}'::text[])),
text_columns AS (
  SELECT t.table_schema,
         t.table_name,
         c.column_name
    FROM parameters p
    JOIN information_schema.tables t
      ON     t.table_type = 'BASE TABLE'
         AND t.table_schema = ANY(p.schemas)
    JOIN information_schema.columns c
      ON     t.table_schema = c.table_schema
         AND t.table_name = c.table_name
    WHERE     c.data_type IN ('character', 'character varying', 'text'))
SELECT t.table_schema,
       t.table_name,
       t.column_name
  FROM parameters p
  CROSS JOIN text_columns t
  WHERE (xpath('/table/row/has_string/text()',
              query_to_xml(format($$SELECT true AS has_string FROM %I.%I WHERE %I ~ %L LIMIT 1$$,
                                  t.table_schema, t.table_name, t.column_name, p.search_pattern),
                           false, false, '')))[1]::text::boolean
  ORDER BY t.table_schema, t.table_name, t.column_name;

Replace regex with a regular expression that will be true if the desired string occurs within the column and expand the schema list if needed.

The query works by searching each character type column of each table for the pattern. For efficiency, the search of each column terminates as soon as a match is found.