looking for a specific record on a Postgres database

The below script will look for a string in every column of the database. To start off with, Log in to postgres and make sure that you have selected the database.

create a function by running the below script. (copy and paste as it is)

CREATE OR REPLACE FUNCTION search_whole_db(_like_pattern text)
  RETURNS TABLE(_tbl regclass, _ctid tid) AS
$func$
BEGIN
   FOR _tbl IN
      SELECT c.oid::regclass
      FROM   pg_class c
      JOIN   pg_namespace n ON n.oid = relnamespace
      WHERE  c.relkind = 'r'                           -- only tables
      AND    n.nspname !~ '^(pg_|information_schema)'  -- exclude system schemas
      ORDER BY n.nspname, c.relname
   LOOP
      RETURN QUERY EXECUTE format(
         'SELECT $1, ctid FROM %s t WHERE t::text ~~ %L'
       , _tbl, '%' || _like_pattern || '%')
      USING _tbl;
   END LOOP;
END
$func$  LANGUAGE plpgsql;

Now, run the below command, replace ‘mypattern’ with the object you are looking for

SELECT * FROM search_whole_db('mypattern');

Here’s an example:

I am looking for a VM named hosting:57669-1:HTM:Cluster1-Web5 in a vCD database. Below is the output from psql

Type "help" for help.

vcd=# CREATE OR REPLACE FUNCTION search_whole_db(_like_pattern text)
vcd-#   RETURNS TABLE(_tbl regclass, _ctid tid) AS
vcd-# $func$
vcd$# BEGIN
vcd$#    FOR _tbl IN
vcd$#       SELECT c.oid::regclass
vcd$#       FROM   pg_class c
vcd$#       JOIN   pg_namespace n ON n.oid = relnamespace
vcd$#       WHERE  c.relkind = 'r'                           -- only tables
vcd$#       AND    n.nspname !~ '^(pg_|information_schema)'  -- exclude system schemas
vcd$#       ORDER BY n.nspname, c.relname
vcd$#    LOOP
vcd$#       RETURN QUERY EXECUTE format(
vcd$#          'SELECT $1, ctid FROM %s t WHERE t::text ~~ %L'
vcd$#        , _tbl, '%' || _like_pattern || '%')
vcd$#       USING _tbl;
vcd$#    END LOOP;
vcd$# END
vcd$# $func$  LANGUAGE plpgsql;
CREATE FUNCTION
vcd=#
vcd=# SELECT * FROM search_whole_db('hosting:57669-1:HTM:Cluster1-Web5');
  _tbl  | _ctid
--------+-------
 vm_inv | (2,8)
(1 row)