Making a select in all tables of a schema

Question

It is possible to search for a certain value in columns of other tables?

Example:

I have a number: 23028908

I want to search in my bank, all columns that have this value.

Is that possible? How it can be done?

Answer 1

The following function implements what you need:

CREATE OR REPLACE FUNCTION search_columns(
    needle text,
    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 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;

To search all tables of a schema at a certain value:

select * from search_columns('23028908');

To search for a value in a specific table, by dynamic name:

select * from search_columns('23028908', {tabela});

To search for a value in tables whose names come from a select:

select * from grep_columns('23028908', array(select table_name::name from information_schema.tables where table_name like 'pessoa%'), array['public']);

More details in the original answer, which I translate here.