如何在所有表(PostgreSQL)中搜索特定值？
是否可以在每个表的每一列中搜索PostgreSQL中的特定值？
甲骨文的here也有类似的问题。
回答 8
Stack Overflow用户
发布于 2011-03-18 19:56:00
转储数据库的内容，然后使用grep如何
$ 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');相同的实用程序pg_dump可以在输出中包含列名。只需将--inserts更改为--column-inserts。这样，您还可以搜索特定的列名。但是如果我要查找列名，我可能会转储模式而不是数据。
$ 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');Stack Overflow用户
发布于 2018-04-25 22:21:53
搜索每个表的每一列以查找特定值的
这并没有定义如何精确匹配。
它也没有确切定义要返回的内容。
假设：
- 查找在其文本表示形式中包含给定值的任何列的任何行，而不是等于给定值。
- 返回表名称(
regclass)和元组ID (ctid)，因为这是最简单的。
这是一种非常简单、快速但略显肮脏的方式：
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;呼叫：
SELECT * FROM search_whole_db('mypattern');提供搜索模式，但不包含%。
为什么有点脏？
如果text表示中行的分隔符和装饰符可以是搜索模式的一部分，则可能存在误报：
转义字符列分隔符:使用default
- whole将行括起来parentheses:
()
- some将值括在双引号中可以将
"
\添加为转义字符
一些列的文本表示可能取决于本地设置-但这种歧义是问题固有的，而不是我的解决方案。
每个符合条件的行只返回一次，即使它匹配多次(与此处的其他答案相反)。
这将搜索整个数据库，但系统目录除外。通常情况下，需要很长时间来完成。您可能希望将其限制为某些模式/表(甚至列)，如其他答案中所示。或者添加通知和进度指示器，这在另一个答案中也有说明。
regclass对象标识符类型表示为表名，在需要根据当前search_path消除歧义的地方采用模式限定
什么是ctid
您可能希望在搜索模式中转义具有特殊含义的字符。请参见：
Stack Overflow用户
发布于 2019-07-01 23:04:29
有一种方法可以在不创建函数或使用外部工具的情况下实现此目的。通过使用Postgres的query_to_xml()函数，可以在另一个查询中动态运行查询，可以跨多个表搜索文本。这是基于我的答案to retrieve the rowcount for all tables
要在模式中的所有表中搜索字符串foo，可以使用以下命令：
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请注意，使用xmltable需要Postgres10或更高版本。对于较早的Postgres版本，也可以使用xpath()完成此操作。
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)使用公用表表达式(WITH ...)只是为了方便起见。它遍历public模式中的所有表。对于每个表，通过query_to_xml()函数运行以下查询：
select to_jsonb(t)
from some_table t
where t::text like '%foo%';where子句用于确保只为包含搜索字符串的行生成昂贵的XML内容。这可能会返回如下内容：
<table xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<row>
<table_row>{"id": 42, "some_column": "foobar"}</table_row>
</row>
</table>完成了完整行到jsonb的转换，这样就可以在结果中看到哪个值属于哪一列。
上面的代码可能会返回如下内容：
table_name | table_row
-------------+----------------------------------------
public.foo | {"id": 1, "some_column": "foobar"}
public.bar | {"id": 42, "another_column": "barfoo"}https://stackoverflow.com/questions/5350088
复制相似问题
腾讯云开发者
