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)
