首页
学习
活动
专区
工具
TVP
发布
社区首页 >问答首页 >如何在所有表(PostgreSQL)中搜索特定值?

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

Stack Overflow用户
提问于 2011-03-18 17:25:25
回答 8查看 156.3K关注 0票数 137

是否可以在每个表的每一列中搜索PostgreSQL中的特定值?

甲骨文的here也有类似的问题。

EN

回答 8

Stack Overflow用户

回答已采纳

发布于 2011-03-18 19:56:00

转储数据库的内容,然后使用grep如何

代码语言:javascript
复制
$ 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。这样,您还可以搜索特定的列名。但是如果我要查找列名,我可能会转储模式而不是数据。

代码语言:javascript
复制
$ 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');
票数 153
EN

Stack Overflow用户

发布于 2018-04-25 22:21:53

搜索每个表的每一列以查找特定值的

这并没有定义如何精确匹配。

它也没有确切定义要返回的内容。

假设:

  • 查找在其文本表示形式中包含给定值的任何列的任何行,而不是等于给定值。
  • 返回表名称(regclass)和元组ID (ctid),因为这是最简单的。

这是一种非常简单、快速但略显肮脏的方式:

代码语言:javascript
复制
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;

呼叫:

代码语言:javascript
复制
SELECT * FROM search_whole_db('mypattern');

提供搜索模式,但不包含%

为什么有点脏?

如果text表示中行的分隔符和装饰符可以是搜索模式的一部分,则可能存在误报:

转义字符列分隔符:使用default

  • whole将行括起来parentheses:()

  • some将值括在双引号中可以将"

  • \添加为转义字符

一些列的文本表示可能取决于本地设置-但这种歧义是问题固有的,而不是我的解决方案。

每个符合条件的行只返回一次,即使它匹配多次(与此处的其他答案相反)。

这将搜索整个数据库,但系统目录除外。通常情况下,需要很长时间来完成。您可能希望将其限制为某些模式/表(甚至列),如其他答案中所示。或者添加通知和进度指示器,这在另一个答案中也有说明。

regclass对象标识符类型表示为表名,在需要根据当前search_path消除歧义的地方采用模式限定

什么是ctid

您可能希望在搜索模式中转义具有特殊含义的字符。请参见:

票数 11
EN

Stack Overflow用户

发布于 2019-07-01 23:04:29

有一种方法可以在不创建函数或使用外部工具的情况下实现此目的。通过使用Postgres的query_to_xml()函数,可以在另一个查询中动态运行查询,可以跨多个表搜索文本。这是基于我的答案to retrieve the rowcount for all tables

要在模式中的所有表中搜索字符串foo,可以使用以下命令:

代码语言:javascript
复制
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()完成此操作。

代码语言:javascript
复制
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()函数运行以下查询:

代码语言:javascript
复制
select to_jsonb(t)
from some_table t
where t::text like '%foo%';

where子句用于确保只为包含搜索字符串的行生成昂贵的XML内容。这可能会返回如下内容:

代码语言:javascript
复制
<table xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<row>
  <table_row>{"id": 42, "some_column": "foobar"}</table_row>
</row>
</table>

完成了完整行到jsonb的转换,这样就可以在结果中看到哪个值属于哪一列。

上面的代码可能会返回如下内容:

代码语言:javascript
复制
table_name   |   table_row
-------------+----------------------------------------
public.foo   |  {"id": 1, "some_column": "foobar"}
public.bar   |  {"id": 42, "another_column": "barfoo"}

Online example for Postgres 10+

Online example for older Postgres versions

票数 8
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/5350088

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档