Get all the columns of all the tables that contain a text

I made this function thinking about your need:

Basically select all the columns of all the tables of the schema informed, and that are varchar or text.

Then it cycles through a query, and returns the query result in a table.

As all tables have different structures, I just returned the column in question, and the name of the table where the value was found:

CREATE OR REPLACE FUNCTION public.like_in_tables (valor varchar, sch varchar 
)
RETURNS table
(
coluna VARCHAR,
tabela varchar
)
 AS
$body$
    declare
       temprow record;
       cmd varchar;
       begin

       cmd := '';

        FOR temprow IN
        (
        select
x.table_name,
x.column_name
from information_schema.columns x
where x.table_schema = $2
and x.data_type in  ('character varying','text')
and x.is_updatable = 'YES'
         )
    LOOP
        cmd := cmd ||  ' SELECT '|| temprow.column_name ||'::varchar as coluna, '|| ''''|| temprow.table_name|| '''' ||'::varchar as tabela ' || ' FROM '|| temprow.table_name || ' where ' || temprow.column_name || '::varchar LIKE ' || '''' || $1 ||'''' || ' UNION '; 
    END LOOP;
        cmd := cmd || ' SELECT NULL, null ';

   RETURN QUERY EXECUTE cmd;   
       end;
$body$
LANGUAGE 'plpgsql'
CALLED ON NULL INPUT;

Using:

select * from like_in_tables('%LOPES%','public');

or

select * from like_in_tables('LOPES%','public');

As% of% this information can be obtained from Postgres , of a% called% information_schema .

For example, consider the VIEW and columns :

CREATE TABLE public.tb_foobar
(
  id INTEGER,
  name CHARACTER VARYING(100),
  description TEXT,
  moment DATE,
  flag BOOLEAN,
  blah INTEGER,
  xpto NUMERIC(5,2)
);

CREATE TABLE public.tb_xpto
(
  id INTEGER,
  alpha CHARACTER VARYING(100),
  beta TEXT
);

Retrieving all columns of type tb_foobar and tb_xpto of all tables of schema CHARACTER VARYING :

SELECT
  table_schema,
  table_name,
  column_name,
  data_type
FROM
  information_schema.columns
WHERE
  table_schema = 'public' AND
  data_type IN ('text','character varying')

Output:

| table_schema | table_name | column_name |         data_type |
|--------------|------------|-------------|-------------------|
|       public |  tb_foobar |        name | character varying |
|       public |  tb_foobar | description |              text |
|       public |    tb_xpto |       alpha | character varying |
|       public |    tb_xpto |        beta |              text |

SQLFiddle: link

Workbench has this functionality, allows you to search for content in all tables using WbGrepData .

The command would look like this:

WbGrepData -searchValue=ValorProcurado -tables=public.* -types=table;

Here the documentation: link
It is not possible to specify the type of column, but the search will be performed on all columns of all tables.