为了账号安全,请及时绑定邮箱和手机立即绑定

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

/ 猿问

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

www说 2019-09-20 17:06:56

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


Oracle 提供了类似的问题。


查看完整描述

3 回答

?
临摹微笑

如何转储数据库的内容,然后使用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');


查看完整回答
反对 回复 2019-09-20
?
拉丁的传说

这是一个pl / pgsql函数,用于查找任何列包含特定值的记录。它以文本格式搜索值,要搜索的表名数组(默认为所有表)和模式名称数组(默认为所有模式名称)作为参数。


它返回一个表结构,其中包含模式,表名,列名和伪列ctid(表中行的非持久物理位置,请参阅系统列)


CREATE OR REPLACE FUNCTION search_columns(

    needle text,

    haystack_tables name[] default '{}',

    haystack_schema name[] default '{}'

)

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) OR 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;

    END IF;

 END LOOP;

END;

$$ language plpgsql;

编辑:此代码适用于PG 9.1或更高版本。此外,您可能希望github上的版本基于相同的原则,但增加了一些速度和报告改进。


在测试数据库中使用的示例:


在公共模式中的所有表中搜索:

select * from search_columns('foobar');

 schemaname | tablename | columnname | rowctid

------------ ----------- + + ------------ + ---------

 公众| s3 | usename | (0,11)

 公众| s2 | relname | (7,29)

 公众| w | 身体| (0,2)

(3排)

在特定表格中搜索:

 select * from search_columns('foobar','{w}');

 schemaname | tablename | columnname | rowctid

------------ ----------- + + ------------ + ---------

 公众| w | 身体| (0,2)

(1排)

搜索从select中获得的表的子集:

select * from search_columns('foobar',array(select table_name :: name from information_schema.tables where table_name like's%'),array ['public']);

 schemaname | tablename | columnname | rowctid

------------ ----------- + + ------------ + ---------

 公众| s2 | relname | (7,29)

 公众| s3 | usename | (0,11)

(2排)

获取带有相应基表和ctid的结果行:

从public.w中选择*,其中ctid ='(0,2)';

 标题| 身体| TSV         

------- -------- + + ---------------------

 托托| foobar | 'foobar':2'toto':1

变种

要再次测试正则表达式而不是严格相等,比如grep,这部分查询:


SELECT ctid FROM %I.%I WHERE cast(%I as text)=%L


可能会改为:


SELECT ctid FROM %I.%I WHERE cast(%I as text) ~ %L


对于不区分大小写的比较,您可以编写:


SELECT ctid FROM %I.%I WHERE lower(cast(%I as text)) = lower(%L)


查看完整回答
反对 回复 2019-09-20
?
RISEBY

如果有人认为它可以帮助你。这是@DanielVérité的函数,另一个参数接受可以在搜索中使用的列的名称。这样可以减少处理时间。至少在我的测试中它减少了很多。


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;

Bellow是上面创建的search_function的使用示例。


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')

);


查看完整回答
反对 回复 2019-09-20

添加回答

回复

举报

0/150
提交
取消
意见反馈 帮助中心 APP下载
官方微信