☰

在整个 postgres 数据库中搜索一个字符串

tag-icon • tag-icon postgresql pgadmin
在整个 postgres 数据库中搜索一个字符串

在 phpmyadmin 中，我可以使用搜索选项卡轻松地在数据库中的表中搜索文本。我想知道我是否可以对我计算机上本地安装的 postgres 执行相同的操作。我查看了那里的菜单，但找不到类似的东西。有人知道我如何在整个数据库中搜索我输入的文本吗？

答案1

您可能可以为此创建一个过程，但我首先想到的只是执行数据库转储（pg_dump）并搜索文件。

答案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');

相关内容