blocks|key|2980769|text|转储数据库的内容，然后使用grep如何|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|2980770|$+pg_dump+--data-only+--inserts+-U+postgres+your-db-name+>+a.tmp
$+grep+United+a.tmp
INSERT+INTO+countries+VALUES+('US',+'United+States');
INSERT+INTO+countries+VALUES+('GB',+'United+Kingdom');|code-block|syntax|javascript|2980771|相同的实用程序pg_dump可以在输出中包含列名。只需将--inserts更改为--column-inserts。这样，您还可以搜索特定的列名。但是如果我要查找列名，我可能会转储模式而不是数据。|2980772|$+pg_dump+--data-only+--column-inserts+-U+postgres+your-db-name+>+a.tmp
$+grep+country_code+a.tmp
INSERT+INTO+countries+(iso_country_code,+iso_country_name)+VALUES+('US',+'United++States');
INSERT+INTO+countries+(iso_country_code,+iso_country_name)+VALUES+('GB',+'United+Kingdom');|2980773|entityMap^0|D|4|0|0|S|9|14|G|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@$9|R|A|S|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|T|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|U|8|@$9|V|A|W|B|C]|$9|X|A|Y|B|C]]|D|@]|E|$]]|$1|M|3|N|5|H|7|Z|8|@]|D|@]|E|$I|J]]|$1|O|3|-4|5|6|7|10|8|@]|D|@]|E|$]]]|P|$]]

How about dumping the contents of the database, then using <code>grep</code>?

<pre><code>$ pg_dump --data-only --inserts -U postgres your-db-name &gt; a.tmp
$ grep United a.tmp
INSERT INTO countries VALUES ('US', 'United States');
INSERT INTO countries VALUES ('GB', 'United Kingdom');
</code></pre>

The same utility, pg_dump, can include column names in the output. Just change <code>--inserts</code> to <code>--column-inserts</code>. That way you can search for specific column names, too. But if I were looking for column names, I'd probably dump the schema instead of the data.

<pre><code>$ pg_dump --data-only --column-inserts -U postgres your-db-name &gt; a.tmp
$ grep country_code a.tmp
INSERT INTO countries (iso_country_code, iso_country_name) VALUES ('US', 'United States');
INSERT INTO countries (iso_country_code, iso_country_name) VALUES ('GB', 'United Kingdom');
</code></pre>

blocks|key|277267|text|这是@Daniel+Vérité的带有进度报告功能的函数。它以三种方式报告进度：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|277268|277269|by+RAISE+NOTICE；通过将所提供的{progress_seq}序列的值从{要搜索的总列数}减至0来进行|ordered-list-item|277270|；通过将进度和找到的表一起写入位于c:\windows\temp{progress_seq}.txt.|277271|中的文本文件来执行|277272|unordered-list-item|277273|277274|_|277275|CREATE+OR+REPLACE+FUNCTION+search_columns(
++++needle+text,
++++haystack_tables+name[]+default+'{}',
++++haystack_schema+name[]+default+'{public}',
++++progress_seq+text+default+NULL
)
RETURNS+table(schemaname+text,+tablename+text,+columnname+text,+rowctid+text)
AS+$$
DECLARE
currenttable+text;
columnscount+integer;
foundintables+text[];
foundincolumns+text[];
begin
currenttable='';
columnscount+=+(SELECT+count(1)
++++++FROM+information_schema.columns+c
++++++JOIN+information_schema.tables+t+ON
++++++++(t.table_name=c.table_name+AND+t.table_schema=c.table_schema)
++++++WHERE+(c.table_name=ANY(haystack_tables)+OR+haystack_tables='{}')
++++++++AND+c.table_schema=ANY(haystack_schema)
++++++++AND+t.table_type='BASE+TABLE')::integer;
PERFORM+setval(progress_seq::regclass,+columnscount);

++FOR+schemaname,tablename,columnname+IN
++++++SELECT+c.table_schema,c.table_name,c.column_name
++++++FROM+information_schema.columns+c
++++++JOIN+information_schema.tables+t+ON
++++++++(t.table_name=c.table_name+AND+t.table_schema=c.table_schema)
++++++WHERE+(c.table_name=ANY(haystack_tables)+OR+haystack_tables='{}')
++++++++AND+c.table_schema=ANY(haystack_schema)
++++++++AND+t.table_type='BASE+TABLE'
++LOOP
++++EXECUTE+format('SELECT+ctid+FROM+%25I.%25I+WHERE+cast(%25I+as+text)=%25L',
+++++++schemaname,
+++++++tablename,
+++++++columnname,
+++++++needle
++++)+INTO+rowctid;
++++IF+rowctid+is+not+null+THEN
++++++RETURN+NEXT;
++++++foundintables+=+foundintables+%7C%7C+tablename;
++++++foundincolumns+=+foundincolumns+%7C%7C+columnname;
++++++RAISE+NOTICE+'FOUND!+%25,+%25,+%25,+%25',+schemaname,tablename,columnname,+rowctid;
++++END+IF;
+++++++++IF+(progress_seq+IS+NOT+NULL)+THEN+
++++++++PERFORM+nextval(progress_seq::regclass);
++++END+IF;
++++IF(currenttable<>tablename)+THEN++
++++currenttable=tablename;
+++++IF+(progress_seq+IS+NOT+NULL)+THEN+
++++++++RAISE+NOTICE+'Columns+left+to+look+in:+%25;+looking+in+table:+%25',+currval(progress_seq::regclass),+tablename;
++++++++EXECUTE+'COPY+(SELECT+unnest(string_to_array(''Current+table+(column+'+%7C%7C+columnscount-currval(progress_seq::regclass)+%7C%7C+'+of+'+%7C%7C+columnscount+%7C%7C+'):+'+%7C%7C+tablename+%7C%7C+'\n\nFound+in+tables/columns:\n'+%7C%7C+COALESCE(
++++++++(SELECT+string_agg(c1+%7C%7C+'/'+%7C%7C+c2,+'\n')+FROM+(SELECT+unnest(foundintables)+AS+c1,unnest(foundincolumns)+AS+c2)+AS+t1)
++++++++,+'')+%7C%7C+''',''\n'')))+TO+''c:\WINDOWS\temp\'+%7C%7C+progress_seq+%7C%7C+'.txt''';
++++END+IF;
++++END+IF;
+END+LOOP;
END;
$$+language+plpgsql;|code-block|syntax|javascript|277276|entityMap^0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|V|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|W|8|@]|9|@]|A|$]]|$1|C|3|D|5|E|7|X|8|@]|9|@]|A|$]]|$1|F|3|G|5|E|7|Y|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|Z|8|@]|9|@]|A|$]]|$1|J|3|-4|5|K|7|10|8|@]|9|@]|A|$]]|$1|L|3|-4|5|6|7|11|8|@]|9|@]|A|$]]|$1|M|3|N|5|6|7|12|8|@]|9|@]|A|$]]|$1|O|3|P|5|Q|7|13|8|@]|9|@]|A|$R|S]]|$1|T|3|-4|5|6|7|14|8|@]|9|@]|A|$]]]|U|$]]

Here's @Daniel Vérité's function with progress reporting functionality.
It reports progress in three ways:

<ol>
<li>by RAISE NOTICE;</li>
<li>by decreasing value of supplied {progress_seq} sequence from
{total number of colums to search in} down to 0;</li>
<li>by writing the progress along with found tables into text file,
located in c:\windows\temp\{progress_seq}.txt.</li>
</ol>

_

<pre><code>CREATE OR REPLACE FUNCTION search_columns(
 needle text,
 haystack_tables name[] default '{}',
 haystack_schema name[] default '{public}',
 progress_seq text default NULL
)
RETURNS table(schemaname text, tablename text, columnname text, rowctid text)
AS $$
DECLARE
currenttable text;
columnscount integer;
foundintables text[];
foundincolumns text[];
begin
currenttable='';
columnscount = (SELECT count(1)
 FROM information_schema.columns c
 JOIN information_schema.tables t ON
 (t.table_name=c.table_name AND t.table_schema=c.table_schema)
 WHERE (c.table_name=ANY(haystack_tables) OR haystack_tables='{}')
 AND c.table_schema=ANY(haystack_schema)
 AND t.table_type='BASE TABLE')::integer;
PERFORM setval(progress_seq::regclass, columnscount);

 FOR schemaname,tablename,columnname IN
 SELECT c.table_schema,c.table_name,c.column_name
 FROM information_schema.columns c
 JOIN information_schema.tables t ON
 (t.table_name=c.table_name AND t.table_schema=c.table_schema)
 WHERE (c.table_name=ANY(haystack_tables) OR haystack_tables='{}')
 AND c.table_schema=ANY(haystack_schema)
 AND t.table_type='BASE TABLE'
 LOOP
 EXECUTE format('SELECT ctid FROM %I.%I WHERE cast(%I as text)=%L',
 schemaname,
 tablename,
 columnname,
 needle
 ) INTO rowctid;
 IF rowctid is not null THEN
 RETURN NEXT;
 foundintables = foundintables || tablename;
 foundincolumns = foundincolumns || columnname;
 RAISE NOTICE 'FOUND! %, %, %, %', schemaname,tablename,columnname, rowctid;
 END IF;
 IF (progress_seq IS NOT NULL) THEN 
 PERFORM nextval(progress_seq::regclass);
 END IF;
 IF(currenttable&lt;&gt;tablename) THEN 
 currenttable=tablename;
 IF (progress_seq IS NOT NULL) THEN 
 RAISE NOTICE 'Columns left to look in: %; looking in table: %', currval(progress_seq::regclass), tablename;
 EXECUTE 'COPY (SELECT unnest(string_to_array(''Current table (column ' || columnscount-currval(progress_seq::regclass) || ' of ' || columnscount || '): ' || tablename || '\n\nFound in tables/columns:\n' || COALESCE(
 (SELECT string_agg(c1 || '/' || c2, '\n') FROM (SELECT unnest(foundintables) AS c1,unnest(foundincolumns) AS c2) AS t1)
 , '') || ''',''\n''))) TO ''c:\WINDOWS\temp\' || progress_seq || '.txt''';
 END IF;
 END IF;
 END LOOP;
END;
$$ language plpgsql;
</code></pre>

blocks|key|2980828|text|如果有人认为这能帮上忙。这是@Daniel+Vérité的函数，另一个参数可以接受搜索中使用的列的名称。这样可以减少处理时间。至少在我的测试中它减少了很多。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2980829|CREATE+OR+REPLACE+FUNCTION+search_columns(
++++needle+text,
++++haystack_columns+name[]+default+'{}',
++++haystack_tables+name[]+default+'{}',
++++haystack_schema+name[]+default+'{public}'
)
RETURNS+table(schemaname+text,+tablename+text,+columnname+text,+rowctid+text)
AS+$$
begin
++FOR+schemaname,tablename,columnname+IN
++++++SELECT+c.table_schema,c.table_name,c.column_name
++++++FROM+information_schema.columns+c
++++++JOIN+information_schema.tables+t+ON
++++++++(t.table_name=c.table_name+AND+t.table_schema=c.table_schema)
++++++WHERE+(c.table_name=ANY(haystack_tables)+OR+haystack_tables='{}')
++++++++AND+c.table_schema=ANY(haystack_schema)
++++++++AND+(c.column_name=ANY(haystack_columns)+OR+haystack_columns='{}')
++++++++AND+t.table_type='BASE+TABLE'
++LOOP
++++EXECUTE+format('SELECT+ctid+FROM+%25I.%25I+WHERE+cast(%25I+as+text)=%25L',
+++++++schemaname,
+++++++tablename,
+++++++columnname,
+++++++needle
++++)+INTO+rowctid;
++++IF+rowctid+is+not+null+THEN
++++++RETURN+NEXT;
++++END+IF;
+END+LOOP;
END;
$$+language+plpgsql;|code-block|syntax|javascript|2980830|下面是上面创建的search_function的用法示例。|2980831|SELECT+*+FROM+search_columns('86192700'
++++,+array(SELECT+DISTINCT+a.column_name::name+FROM+information_schema.columns+AS+a
++++++++++++INNER+JOIN+information_schema.tables+as+b+ON+(b.table_catalog+=+a.table_catalog+AND+b.table_schema+=+a.table_schema+AND+b.table_name+=+a.table_name)
++++++++WHERE+
++++++++++++a.column_name+iLIKE+'%25cep%25'+
++++++++++++AND+b.table_type+=+'BASE+TABLE'
++++++++++++AND+b.table_schema+=+'public'
++++)

++++,+array(SELECT+b.table_name::name+FROM+information_schema.columns+AS+a
++++++++++++INNER+JOIN+information_schema.tables+as+b+ON+(b.table_catalog+=+a.table_catalog+AND+b.table_schema+=+a.table_schema+AND+b.table_name+=+a.table_name)
++++++++WHERE+
++++++++++++a.column_name+iLIKE+'%25cep%25'+
++++++++++++AND+b.table_type+=+'BASE+TABLE'
++++++++++++AND+b.table_schema+=+'public')
);|2980832|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

And if someone think it could help. Here is @Daniel Vérité's function, with another param that accept names of columns that can be used in search. This way it decrease the time of processing. At least in my test it reduced a lot.

<pre><code>CREATE OR REPLACE FUNCTION search_columns(
 needle text,
 haystack_columns name[] default '{}',
 haystack_tables name[] default '{}',
 haystack_schema name[] default '{public}'
)
RETURNS table(schemaname text, tablename text, columnname text, rowctid text)
AS $$
begin
 FOR schemaname,tablename,columnname IN
 SELECT c.table_schema,c.table_name,c.column_name
 FROM information_schema.columns c
 JOIN information_schema.tables t ON
 (t.table_name=c.table_name AND t.table_schema=c.table_schema)
 WHERE (c.table_name=ANY(haystack_tables) OR haystack_tables='{}')
 AND c.table_schema=ANY(haystack_schema)
 AND (c.column_name=ANY(haystack_columns) OR haystack_columns='{}')
 AND t.table_type='BASE TABLE'
 LOOP
 EXECUTE format('SELECT ctid FROM %I.%I WHERE cast(%I as text)=%L',
 schemaname,
 tablename,
 columnname,
 needle
 ) INTO rowctid;
 IF rowctid is not null THEN
 RETURN NEXT;
 END IF;
 END LOOP;
END;
$$ language plpgsql;
</code></pre>

Bellow is an example of usage of the search_function created above.

<pre><code>SELECT * FROM search_columns('86192700'
 , array(SELECT DISTINCT a.column_name::name FROM information_schema.columns AS a
 INNER JOIN information_schema.tables as b ON (b.table_catalog = a.table_catalog AND b.table_schema = a.table_schema AND b.table_name = a.table_name)
 WHERE 
 a.column_name iLIKE '%cep%' 
 AND b.table_type = 'BASE TABLE'
 AND b.table_schema = 'public'
 )

 , array(SELECT b.table_name::name FROM information_schema.columns AS a
 INNER JOIN information_schema.tables as b ON (b.table_catalog = a.table_catalog AND b.table_schema = a.table_schema AND b.table_name = a.table_name)
 WHERE 
 a.column_name iLIKE '%cep%' 
 AND b.table_type = 'BASE TABLE'
 AND b.table_schema = 'public')
);
</code></pre>

blocks|key|275611|text|在不存储新过程的情况下，您可以使用代码块并执行来获取事件表。您可以按架构、表或列名过滤结果。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|275612|DO+$$
DECLARE
++value+int+:=+0;
++sql+text+:=+'The+constructed+select+statement';
++rec1+record;
++rec2+record;
BEGIN
++DROP+TABLE+IF+EXISTS+_x;
++CREATE+TEMPORARY+TABLE+_x+(
++++schema_name+text,+
++++table_name+text,+
++++column_name+text,
++++found+text
++);
++FOR+rec1+IN+
++++++++SELECT+table_schema,+table_name,+column_name
++++++++FROM+information_schema.columns+
++++++++WHERE+table_name+<>+'_x'
++++++++++++++++AND+UPPER(column_name)+LIKE+UPPER('%25%25')++++++++++++++++++
++++++++++++++++AND+table_schema+<>+'pg_catalog'
++++++++++++++++AND+table_schema+<>+'information_schema'
++++++++++++++++AND+data_type+IN+('character+varying',+'text',+'character',+'char',+'varchar')
++++++++LOOP
++++sql+:=+concat('SELECT+',+rec1."column_name",+'+AS+"found"+FROM+',rec1."table_schema"+,+'.',rec1."table_name"+,+'+WHERE+UPPER(',rec1."column_name"+,+')+LIKE+UPPER(''','%25my_substring_to_find_goes_here%25'+,+''')');
++++RAISE+NOTICE+'%25',+sql;
++++BEGIN
++++++++FOR+rec2+IN+EXECUTE+sql+LOOP
++++++++++++RAISE+NOTICE+'%25',+sql;
++++++++++++INSERT+INTO+_x+VALUES+(rec1."table_schema",+rec1."table_name",+rec1."column_name",+rec2."found");
++++++++END+LOOP;
++++EXCEPTION+WHEN+OTHERS+THEN
++++END;
++END+LOOP;
++END;+$$;

SELECT+*+FROM+_x;|code-block|syntax|javascript|275613|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Without storing a new procedure you can use a code block and execute to obtain a table of occurences. You can filter results by schema, table or column name.

<pre><code>DO $$
DECLARE
 value int := 0;
 sql text := 'The constructed select statement';
 rec1 record;
 rec2 record;
BEGIN
 DROP TABLE IF EXISTS _x;
 CREATE TEMPORARY TABLE _x (
 schema_name text, 
 table_name text, 
 column_name text,
 found text
 );
 FOR rec1 IN 
 SELECT table_schema, table_name, column_name
 FROM information_schema.columns 
 WHERE table_name &lt;&gt; '_x'
 AND UPPER(column_name) LIKE UPPER('%%') 
 AND table_schema &lt;&gt; 'pg_catalog'
 AND table_schema &lt;&gt; 'information_schema'
 AND data_type IN ('character varying', 'text', 'character', 'char', 'varchar')
 LOOP
 sql := concat('SELECT ', rec1."column_name", ' AS "found" FROM ',rec1."table_schema" , '.',rec1."table_name" , ' WHERE UPPER(',rec1."column_name" , ') LIKE UPPER(''','%my_substring_to_find_goes_here%' , ''')');
 RAISE NOTICE '%', sql;
 BEGIN
 FOR rec2 IN EXECUTE sql LOOP
 RAISE NOTICE '%', sql;
 INSERT INTO _x VALUES (rec1."table_schema", rec1."table_name", rec1."column_name", rec2."found");
 END LOOP;
 EXCEPTION WHEN OTHERS THEN
 END;
 END LOOP;
 END; $$;

SELECT * FROM _x;
</code></pre>

blocks|key|273717|text|搜索每个表的每一列以查找特定值的|type|unstyled|depth|inlineStyleRanges|entityRanges|data|273718|blockquote|273719|273720|273721|这并没有定义如何精确匹配。|273722|它也没有确切定义要返回的内容。|273723|假设：|273724|273725|查找在其文本表示形式中包含给定值的任何列的任何行，而不是等于给定值。|unordered-list-item|offset|length|style|BOLD|273726|返回表名称(regclass)和元组ID+(ctid)，因为这是最简单的。|CODE|273727|273728|这是一种非常简单、快速但略显肮脏的方式：|273729|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+!~+'%5E(pg_%7Cinformation_schema)'++--+exclude+system+schemas
++++++ORDER+BY+n.nspname,+c.relname
+++LOOP
++++++RETURN+QUERY+EXECUTE+format(
+++++++++'SELECT+$1,+ctid+FROM+%25s+t+WHERE+t::text+~~+%25L'
+++++++,+_tbl,+'%25'+%7C%7C+_like_pattern+%7C%7C+'%25')
++++++USING+_tbl;
+++END+LOOP;
END
$func$++LANGUAGE+plpgsql;|code-block|syntax|javascript|273730|呼叫：|273731|SELECT+*+FROM+search_whole_db('mypattern');|273732|提供搜索模式，但不包含%25。|273733|为什么有点脏？|273734|如果text表示中行的分隔符和装饰符可以是搜索模式的一部分，则可能存在误报：|273735|转义字符列分隔符:使用default|273736|whole将行括起来parentheses:()|273737|
|273738|some将值括在双引号中可以将"|273739|273740|\添加为转义字符|273741|273742|一些列的文本表示可能取决于本地设置-但这种歧义是问题固有的，而不是我的解决方案。|273743|每个符合条件的行只返回一次，即使它匹配多次(与此处的其他答案相反)。|273744|这将搜索整个数据库，但系统目录除外。通常情况下，需要很长时间来完成。您可能希望将其限制为某些模式/表(甚至列)，如其他答案中所示。或者添加通知和进度指示器，这在另一个答案中也有说明。|273745|regclass对象标识符类型表示为表名，在需要根据当前search_path消除歧义的地方采用模式限定|273746|273747|Find+the+referenced+table+name+using+table,+field+and+schema+name|273748|273749|什么是ctid|273750|273751|How+do+I+decompose+ctid+into+page+and+row+numbers?|273752|273753|您可能希望在搜索模式中转义具有特殊含义的字符。请参见：|273754|273755|Escape+function+for+regular+expression+or+LIKE+patterns|273756|273757|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/questions/15618312/find-the-referenced-table-name-using-table-field-and-schema-name/15625024#15625024|1|https://dba.stackexchange.com/a/66007/3684|2|https://stackoverflow.com/questions/5144036/escape-function-for-regular-expression-or-like-patterns/45741630#45741630^0|0|0|0|0|0|0|0|0|K|E|0|0|11|6|8|M|4|0|0|0|K|0|0|0|3|0|0|0|D|B|1|0|0|7|0|0|12|2|4|0|0|I|0|0|O|M|2|0|0|0|G|F|1|0|0|0|8|0|1|0|0|0|14|0|0|D|0|O|9|0|0|8|S|B|0|0|0|1T|0|0|0|3|4|0|0|0|1E|1|0|0|0|0|0|1J|2|0|0^^$0|@$1|2|3|4|5|6|7|2Q|8|@]|9|@]|A|$]]|$1|B|3|-4|5|C|7|2R|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|2S|8|@]|9|@]|A|$]]|$1|E|3|-4|5|6|7|2T|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|2U|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|2V|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|2W|8|@]|9|@]|A|$]]|$1|L|3|-4|5|6|7|2X|8|@]|9|@]|A|$]]|$1|M|3|N|5|O|7|2Y|8|@$P|2Z|Q|30|R|S]]|9|@]|A|$]]|$1|T|3|U|5|O|7|31|8|@$P|32|Q|33|R|S]|$P|34|Q|35|R|V]|$P|36|Q|37|R|V]]|9|@]|A|$]]|$1|W|3|-4|5|6|7|38|8|@]|9|@]|A|$]]|$1|X|3|Y|5|6|7|39|8|@$P|3A|Q|3B|R|S]]|9|@]|A|$]]|$1|Z|3|10|5|11|7|3C|8|@]|9|@]|A|$12|13]]|$1|14|3|15|5|6|7|3D|8|@$P|3E|Q|3F|R|S]]|9|@]|A|$]]|$1|16|3|17|5|11|7|3G|8|@]|9|@]|A|$12|13]]|$1|18|3|19|5|6|7|3H|8|@$P|3I|Q|3J|R|S]|$P|3K|Q|3L|R|V]]|9|@]|A|$]]|$1|1A|3|1B|5|6|7|3M|8|@$P|3N|Q|3O|R|S]]|9|@]|A|$]]|$1|1C|3|1D|5|6|7|3P|8|@$P|3Q|Q|3R|R|S]|$P|3S|Q|3T|R|V]]|9|@]|A|$]]|$1|1E|3|1F|5|6|7|3U|8|@$P|3V|Q|3W|R|S]]|9|@]|A|$]]|$1|1G|3|1H|5|O|7|3X|8|@$P|3Y|Q|3Z|R|S]|$P|40|Q|41|R|V]]|9|@]|A|$]]|$1|1I|3|1J|5|6|7|42|8|@]|9|@]|A|$]]|$1|1K|3|1L|5|O|7|43|8|@$P|44|Q|45|R|S]|$P|46|Q|47|R|V]]|9|@]|A|$]]|$1|1M|3|1J|5|6|7|48|8|@]|9|@]|A|$]]|$1|1N|3|1O|5|O|7|49|8|@$P|4A|Q|4B|R|S]|$P|4C|Q|4D|R|V]]|9|@]|A|$]]|$1|1P|3|-4|5|6|7|4E|8|@]|9|@]|A|$]]|$1|1Q|3|1R|5|6|7|4F|8|@$P|4G|Q|4H|R|S]]|9|@]|A|$]]|$1|1S|3|1T|5|6|7|4I|8|@$P|4J|Q|4K|R|S]]|9|@]|A|$]]|$1|1U|3|1V|5|6|7|4L|8|@$P|4M|Q|4N|R|S]]|9|@]|A|$]]|$1|1W|3|1X|5|6|7|4O|8|@$P|4P|Q|4Q|R|V]|$P|4R|Q|4S|R|V]]|9|@]|A|$]]|$1|1Y|3|-4|5|6|7|4T|8|@]|9|@]|A|$]]|$1|1Z|3|20|5|O|7|4U|8|@]|9|@$P|4V|Q|4W|1|4X]]|A|$]]|$1|21|3|-4|5|6|7|4Y|8|@]|9|@]|A|$]]|$1|22|3|23|5|6|7|4Z|8|@$P|50|Q|51|R|V]]|9|@]|A|$]]|$1|24|3|-4|5|6|7|52|8|@]|9|@]|A|$]]|$1|25|3|26|5|O|7|53|8|@]|9|@$P|54|Q|55|1|56]]|A|$]]|$1|27|3|-4|5|6|7|57|8|@]|9|@]|A|$]]|$1|28|3|29|5|6|7|58|8|@]|9|@]|A|$]]|$1|2A|3|-4|5|6|7|59|8|@]|9|@]|A|$]]|$1|2B|3|2C|5|O|7|5A|8|@]|9|@$P|5B|Q|5C|1|5D]]|A|$]]|$1|2D|3|-4|5|6|7|5E|8|@]|9|@]|A|$]]|$1|2E|3|-4|5|6|7|5F|8|@]|9|@]|A|$]]]|2F|$2G|$5|2H|2I|2J|A|$2K|2L]]|2M|$5|2H|2I|2J|A|$2K|2N]]|2O|$5|2H|2I|2J|A|$2K|2P]]]]

<blockquote>
 to search every column of every table for a particular value
</blockquote>

This does not define how to match exactly. 
Nor does it define what to return exactly.

Assuming:

<ul>
<li>Find any row with any column containing the given value in its text representation - as opposed to equaling the given value.</li>
<li>Return the table name (<code>regclass</code>) and the tuple ID (<code>ctid</code>), because that's simplest.</li>
</ul>

Here is a dead simple, fast and slightly dirty way:

<pre><code>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;
</code></pre>

Call:

<pre><code>SELECT * FROM search_whole_db('mypattern');
</code></pre>

Provide the search pattern without enclosing <code>%</code>.

Why slightly dirty?

If separators and decorators for the row in <code>text</code> representation can be part of the search pattern, there can be false positives:

<ul>
<li>column separator: <code>,</code> by default</li>
<li>whole row is enclosed in parentheses:<code>()</code></li>
<li>some values are enclosed in double quotes <code>"</code></li>
<li><code>\</code> may be added as escape char</li>
</ul>

And the text representation of some columns may depend on local settings - but that ambiguity is inherent to the question, not to my solution.

Each qualifying row is returned once only, even when it matches multiple times (as opposed to other answers here).

This searches the whole DB except for system catalogs. Will typically take a long time to finish. You might want to restrict to certain schemas / tables (or even columns) like demonstrated in other answers. Or add notices and a progress indicator, also demonstrated in another answer.

The <code>regclass</code> object identifier type is represented as table name, schema-qualified where necessary to disambiguate according to the current <code>search_path</code>:

<ul>
<li><a href="https://stackoverflow.com/questions/15618312/find-the-referenced-table-name-using-table-field-and-schema-name/15625024#15625024">Find the referenced table name using table, field and schema name</a></li>
</ul>

What is the <code>ctid</code>?

<ul>
<li><a href="https://dba.stackexchange.com/a/66007/3684">How do I decompose ctid into page and row numbers?</a></li>
</ul>

You might want to escape characters with special meaning in the search pattern. See:

<ul>
<li><a href="https://stackoverflow.com/questions/5144036/escape-function-for-regular-expression-or-like-patterns/45741630#45741630">Escape function for regular expression or LIKE patterns</a></li>
</ul>

blocks|key|279301|text|--+Below函数将列出数据库中包含特定字符串的所有表|type|unstyled|depth|inlineStyleRanges|entityRanges|data|279302|+select+TablesCount(‘StringToSearch’);|code-block|syntax|javascript|279303|--遍历数据库中的所有表|279304|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%25+',+CONCAT('++',cnt,'+Table+name:+',+_tname);
++++++++++++++++END+LOOP;
++++RETURN+_tname;
+++END;
$$+--+here+finish+procedural+part
LANGUAGE+plpgsql;+--+language+specification|279305|--返回满足条件的表数。--例如，如果目标文本存在于表的任何字段中，--则计数将大于0。我们可以在postgres数据库的结果查看器的Messages部分中找到通知。|279306|CREATE+OR+REPLACE+FUNCTION+**getMatchingCount**(_tname+TEXT,+_clause+TEXT)
RETURNS+int+AS+
$$
Declare+outpt+text;
++++BEGIN
++++EXECUTE+'Select+Count(*)+from+'%7C%7C_tname%7C%7C'+where+'%7C%7C+_clause
+++++++INTO+outpt;
+++++++RETURN+outpt;
++++END;
$$+LANGUAGE+plpgsql;|279307|--获取每个表的字段。生成包含表中所有列的where子句。|279308|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+','''%25',st,'%25''',+'+OR+');
++++++++++++++++++++++++++++++++_helper=+CONCAT(_helper,_name,'+');
++++++++++++++++END+LOOP;
++++++++++++++++RETURN+CONCAT(_helper,+'+1=2');

+++END;
$$+--+here+finish+procedural+part
LANGUAGE+plpgsql;+--+language+specification|279309|entityMap^0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|W|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|X|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|Y|8|@]|9|@]|A|$]]|$1|M|3|N|5|D|7|Z|8|@]|9|@]|A|$E|F]]|$1|O|3|P|5|6|7|10|8|@]|9|@]|A|$]]|$1|Q|3|R|5|D|7|11|8|@]|9|@]|A|$E|F]]|$1|S|3|-4|5|6|7|12|8|@]|9|@]|A|$]]]|T|$]]

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

<pre><code> select TablesCount(‘StringToSearch’);
</code></pre>

--Iterates through all the tables in the database

<pre><code>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
</code></pre>

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

<pre><code>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;
</code></pre>

--Get the fields of each table. Builds the where clause with all columns of a table. 

<pre><code>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
</code></pre>

blocks|key|277404|text|有一种方法可以在不创建函数或使用外部工具的情况下实现此目的。通过使用Postgres的query_to_xml()函数，可以在另一个查询中动态运行查询，可以跨多个表搜索文本。这是基于我的答案to+retrieve+the+rowcount+for+all+tables|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|277405|要在模式中的所有表中搜索字符串foo，可以使用以下命令：|277406|with+found_rows+as+(
++select+format('%25I.%25I',+table_schema,+table_name)+as+table_name,
+++++++++query_to_xml(format('select+to_jsonb(t)+as+table_row+
++++++++++++++++++++++++++++++from+%25I.%25I+as+t+
++++++++++++++++++++++++++++++where+t::text+like+''%25%25foo%25%25''+',+table_schema,+table_name),+
++++++++++++++++++++++true,+false,+'')+as+table_rows
++from+information_schema.tables+
++where+table_schema+=+'public'
)
select+table_name,+x.table_row
from+found_rows+f
++left+join+xmltable('//table/row'+
+++++++++++++++++++++passing+table_rows
+++++++++++++++++++++++columns
+++++++++++++++++++++++++table_row+text+path+'table_row')+as+x+on+true|code-block|syntax|javascript|277407|请注意，使用xmltable需要Postgres10或更高版本。对于较早的Postgres版本，也可以使用xpath()完成此操作。|277408|with+found_rows+as+(
++select+format('%25I.%25I',+table_schema,+table_name)+as+table_name,
+++++++++query_to_xml(format('select+to_jsonb(t)+as+table_row+
++++++++++++++++++++++++++++++from+%25I.%25I+as+t+
++++++++++++++++++++++++++++++where+t::text+like+''%25%25foo%25%25''+',+table_schema,+table_name),+
++++++++++++++++++++++true,+false,+'')+as+table_rows
++from+information_schema.tables+
++where+table_schema+=+'public'
)
select+table_name,+x.table_row
from+found_rows+f
+++cross+join+unnest(xpath('/table/row/table_row/text()',+table_rows))+as+r(data)|277409|使用公用表表达式(WITH+...)只是为了方便起见。它遍历public模式中的所有表。对于每个表，通过query_to_xml()函数运行以下查询：|277410|select+to_jsonb(t)
from+some_table+t
where+t::text+like+'%25foo%25';|277411|where子句用于确保只为包含搜索字符串的行生成昂贵的XML内容。这可能会返回如下内容：|277412|<table+xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<row>
++<table_row>{"id":+42,+"some_column":+"foobar"}</table_row>
</row>
</table>|277413|完成了完整行到jsonb的转换，这样就可以在结果中看到哪个值属于哪一列。|277414|上面的代码可能会返回如下内容：|277415|table_name+++%7C+++table_row
-------------%2B----------------------------------------
public.foo+++%7C++{"id":+1,+"some_column":+"foobar"}
public.bar+++%7C++{"id":+42,+"another_column":+"barfoo"}|277416|Online+example+for+Postgres+10%2B|277417|Online+example+for+older+Postgres+versions|277418|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/a/38684225/330315|1|https://dbfiddle.uk/?rdbms=postgres_11&fiddle=650d8630432ab161310d81c3af5d6657|2|https://dbfiddle.uk/?rdbms=postgres_9.6&fiddle=d36f3d68154864556ba821ccbd8ac93e^0|17|E|2N|13|0|0|F|3|0|0|6|8|0|0|9|8|U|6|1G|E|0|0|0|0|7|5|0|0|0|0|V|1|0|0|16|2|0^^$0|@$1|2|3|4|5|6|7|1K|8|@$9|1L|A|1M|B|C]]|D|@$9|1N|A|1O|1|1P]]|E|$]]|$1|F|3|G|5|6|7|1Q|8|@$9|1R|A|1S|B|C]]|D|@]|E|$]]|$1|H|3|I|5|J|7|1T|8|@]|D|@]|E|$K|L]]|$1|M|3|N|5|6|7|1U|8|@$9|1V|A|1W|B|C]]|D|@]|E|$]]|$1|O|3|P|5|J|7|1X|8|@]|D|@]|E|$K|L]]|$1|Q|3|R|5|6|7|1Y|8|@$9|1Z|A|20|B|C]|$9|21|A|22|B|C]|$9|23|A|24|B|C]]|D|@]|E|$]]|$1|S|3|T|5|J|7|25|8|@]|D|@]|E|$K|L]]|$1|U|3|V|5|6|7|26|8|@]|D|@]|E|$]]|$1|W|3|X|5|J|7|27|8|@]|D|@]|E|$K|L]]|$1|Y|3|Z|5|6|7|28|8|@$9|29|A|2A|B|C]]|D|@]|E|$]]|$1|10|3|11|5|6|7|2B|8|@]|D|@]|E|$]]|$1|12|3|13|5|J|7|2C|8|@]|D|@]|E|$K|L]]|$1|14|3|15|5|6|7|2D|8|@]|D|@$9|2E|A|2F|1|2G]]|E|$]]|$1|16|3|17|5|6|7|2H|8|@]|D|@$9|2I|A|2J|1|2K]]|E|$]]|$1|18|3|-4|5|6|7|2L|8|@]|D|@]|E|$]]]|19|$1A|$5|1B|1C|1D|E|$1E|1F]]|1G|$5|1B|1C|1D|E|$1E|1H]]|1I|$5|1B|1C|1D|E|$1E|1J]]]]

There is a way to achieve this without creating a function or using an external tool. By using Postgres' <code>query_to_xml()</code> function that can dynamically run a query inside another query, it's possible to search a text across many tables. This is based on my answer <a href="https://stackoverflow.com/a/38684225/330315">to retrieve the rowcount for all tables</a>:

To search for the string <code>foo</code> across all tables in a schema, the following can be used:

<pre><code>with found_rows as (
 select format('%I.%I', table_schema, table_name) as table_name,
 query_to_xml(format('select to_jsonb(t) as table_row 
 from %I.%I as t 
 where t::text like ''%%foo%%'' ', table_schema, table_name), 
 true, false, '') as table_rows
 from information_schema.tables 
 where table_schema = 'public'
)
select table_name, x.table_row
from found_rows f
 left join xmltable('//table/row' 
 passing table_rows
 columns
 table_row text path 'table_row') as x on true
</code></pre>

Note that the use of <code>xmltable</code> requires Postgres 10 or newer. For older Postgres version, this can be also done using xpath().

<pre><code>with found_rows as (
 select format('%I.%I', table_schema, table_name) as table_name,
 query_to_xml(format('select to_jsonb(t) as table_row 
 from %I.%I as t 
 where t::text like ''%%foo%%'' ', table_schema, table_name), 
 true, false, '') as table_rows
 from information_schema.tables 
 where table_schema = 'public'
)
select table_name, x.table_row
from found_rows f
 cross join unnest(xpath('/table/row/table_row/text()', table_rows)) as r(data)
</code></pre>

The common table expression (<code>WITH ...</code>) is only used for convenience. It loops through all tables in the <code>public</code> schema. For each table the following query is run through the <code>query_to_xml()</code> function:

<pre><code>select to_jsonb(t)
from some_table t
where t::text like '%foo%';
</code></pre>

The where clause is used to make sure the expensive generation of XML content is only done for rows that contain the search string. This might return something like this:

<pre class="lang-xml prettyprint-override"><code>&lt;table xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"&gt;
&lt;row&gt;
 &lt;table_row&gt;{"id": 42, "some_column": "foobar"}&lt;/table_row&gt;
&lt;/row&gt;
&lt;/table&gt;
</code></pre>

The conversion of the complete row to <code>jsonb</code> is done, so that in the result one could see which value belongs to which column. 

The above might return something like this:

<pre class="lang-none prettyprint-override"><code>table_name | table_row
-------------+----------------------------------------
public.foo | {"id": 1, "some_column": "foobar"}
public.bar | {"id": 42, "another_column": "barfoo"}
</code></pre>

<a href="https://dbfiddle.uk/?rdbms=postgres_11&amp;fiddle=650d8630432ab161310d81c3af5d6657" rel="noreferrer">Online example for Postgres 10+</a>

<a href="https://dbfiddle.uk/?rdbms=postgres_9.6&amp;fiddle=d36f3d68154864556ba821ccbd8ac93e" rel="noreferrer">Online example for older Postgres versions</a>

blocks|key|3194443|text|如果你使用的是IntelliJ将你的数据库添加到数据库视图，然后右击数据库并选择全文搜索，它将列出你的特定文本的所有表和所有字段。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3194444|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

If you're using IntelliJ add your DB to Database view then right click on databases and select full text search, it will list all tables and all fields for your specific text.

Is it possible to search every column of every table for a particular value in PostgreSQL?

A similar question is available <a href="https://stackoverflow.com/questions/208493/search-all-fields-in-all-tables-for-a-specific-value-oracle">here</a> for Oracle.

How to search a specific value in all tables (PostgreSQL)?

PostgreSQL

数据库

是否可以在每个表的每一列中搜索PostgreSQL中的特定值？甲骨文的也有类似的问题。

问如何在所有表(PostgreSQL)中搜索特定值？
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在所有表(PostgreSQL)中搜索特定值？EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在所有表(PostgreSQL)中搜索特定值？
EN