如何在所有表中搜索特定值（PostgreSQL）？

Question

是否可以在PostgreSQL中搜索每个表的每一列中的特定值？Oracle 提供了类似的问题。

Answer 1

如果有人认为它可以帮助你。这是@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')

);