Bdtyktl

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?

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.

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



 select TablesCount(‘StringToSearch’);





--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