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?
-
1You're comparing 2 different things: phpMyAdmin is not a DB, it's a PHP application written to administrate MySQL DBs, while PostgreSQL is a DB, like MySQL.– m0skit0Feb 17, 2012 at 9:52
-
When you download postgress and install, pgadmin is also installed– yankitwizzyFeb 17, 2012 at 13:01
Add a comment
|
2 Answers
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.
-
Nice. I was going to suggest finding all textual columns with the System Catalogs but I like your answer better (for smallish databases) May 28, 2013 at 22:03
-
1
-
If it's a one time thing searching a dump from a backup is probably still the easiest thing. If you need it more ofter, use something like Sphinx or elasticsearch. Jun 18, 2013 at 13:07
-
it's a 'silly' answer but the one that actually works :) So, is there someone who could post a proper SQL-Query?– threeNov 26, 2013 at 7:16
--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');
-
Gives me:
Error in query (7): ERROR: function columnames(text, text) does not exist LINE 1: SELECT getMatchingCount(_tname,Columnames(_tname,_searchText... HINT: No function matches the given name and argument types. You might need to add explicit type casts. QUERY: SELECT getMatchingCount(_tname,Columnames(_tname,_searchText)) CONTEXT: PL/pgSQL function tablescount(text) line 6 at assignment
– GuySoftNov 10, 2020 at 9:51