PostgreSQLで全てのテーブル、カラムを検索対象として任意の値を検索する

概要

Constraintsが貼られていない、ER図などもないといったプロジェクトで、あるテーブルのカラム値が、他のテーブルのどのカラムの値と関連付けられているのかを調査する場合の最終手段。
表題の通り、全テーブル、カラムを検索対象として任意の値を検索する。

注意

結構時間がかかるし、テーブルが大きければ負荷がかかるかもしれないので、当然本番環境ではやらないほうがよい。

環境

• PostgreSQL 9.6

手順

Editorモードにする

postgres> \e

Editorが開いたら以下を入力し、:wqで保存して閉じる。

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;

以下を実行して検索。

postgres> select * from search_columns('検索したい文字列');

-- 検索結果が以下のように表示される
 schemaname |      tablename      |  columnname   | rowctid
------------+---------------------+---------------+---------
 public     | sample_table_001 | sample_column_001 | (0,1)
 public     | sample_table_002 | sample_column_002 | (0,2)