Searching a whole postgres db for a string

Question

In phpmyadmin, I can easily search for text in the tables in my databases using the search tab. Was wondering if I can do the same with the postgres installed locally on my computer. I have looked at the menus there but I can't find anything like that. Does anyone know how I can search a whole db for a text that I enter?

Answer 1

You could probably create a procedure to this, but out of the top of my head I'd just do a database dump (pg_dump) and search the file.

Answer 2

--Iterates through all the tables in the database

CREATE OR REPLACE FUNCTION TablesCount(_searchText TEXT)
RETURNS text AS 
$$ -- here start procedural part
   DECLARE _tname text;
   DECLARE cnt int;
   BEGIN
    FOR _tname IN SELECT table_name FROM information_schema.tables where table_schema='public' and table_type='BASE TABLE'  LOOP
         cnt= getMatchingCount(_tname,Columnames(_tname,_searchText));
                                RAISE NOTICE 'Count% ', CONCAT('  ',cnt,' Table name: ', _tname);
                END LOOP;
    RETURN _tname;
   END;
$$ -- here finish procedural part
LANGUAGE plpgsql; -- language specification

-- Returns the count of tables for which the condition is met.
-- For example, if the intended text exists in any of the fields of the table,
-- then the count will be greater than 0. We can find the notifications
-- in the Messages section of the result viewer in postgres database. 

CREATE OR REPLACE FUNCTION getMatchingCount(_tname TEXT, _clause TEXT)
RETURNS int AS 
$$
Declare outpt text;
    BEGIN
    EXECUTE 'Select Count(*) from '||_tname||' where '|| _clause
       INTO outpt;
       RETURN outpt;
    END;
$$ LANGUAGE plpgsql;


--Get the fields of each table. Builds the where clause with all columns of a table. 
CREATE OR REPLACE FUNCTION Columnames(_tname text,st text)
RETURNS text AS 
$$ -- here start procedural part
DECLARE
                _name text;
                _helper text;
   BEGIN
                FOR _name IN SELECT column_name FROM information_schema.Columns WHERE table_name =_tname LOOP
                                _name=CONCAT('CAST('",_name,'" as VarChar)',' like ','''%',st,'%''', ' OR ');
                                _helper= CONCAT(_helper,_name,' ');
                END LOOP;
                RETURN CONCAT(_helper, ' 1=2');
                
   END;
$$ -- here finish procedural part
LANGUAGE plpgsql; -- language specification

-- Below function will list all the tables which contain a specific string in the database 

 select TablesCount('StringToSearch');