PostgreSQLで全テーブルの文字列検索

概要

全テーブルの内、文字列型のカラムを改行を除いた上で検索します。

作成環境

• PostgreSQL 17.5-2

説明

ファンクション登録

全文検索.sql

CREATE OR REPLACE FUNCTION search_columns (
    検索キーワード TEXT[],
    検索対象スキーマ名 TEXT[] DEFAULT '{}',
    検索対象外カラム名 TEXT[] DEFAULT '{}',
    検索対象外テーブル名_部分一致 TEXT[] DEFAULT '{}',
    検索対象テーブル名 TEXT[] DEFAULT '{}'
) returns TABLE (schemaname TEXT, tablename TEXT, columnname TEXT, columnvalues TEXT) AS $$
begin
     FOR schemaname, tablename, columnname IN
         SELECT c.table_schema,c.table_name,c.column_name
         FROM information_schema.columns c
         INNER JOIN information_schema.tables t
         USING (table_name, table_schema)
         WHERE t.table_type='BASE TABLE' --ビューを除きテーブルのみに絞る
         AND c.character_octet_length IS NOT NULL --文字列型のカラムに絞る
         AND (c.table_schema=ANY($2) OR $2='{}') --特定スキーマに絞る
         AND (NOT(c.column_name=ANY($3)) OR $3='{}') --特定のカラムを検索対象から除く
         AND (NOT(c.table_name LIKE ANY(ARRAY(SELECT '%' || e || '%' FROM unnest($4) e))) OR $4='{}')--特定のテーブル名を部分一致で検索対象から除く
         AND (c.table_name=ANY($5) OR $5='{}') --特定のテーブルに絞る
     LOOP
        EXECUTE format('SELECT LEFT(''"'' || STRING_AGG(t.columnname, E''"\r\n"'') || ''"'', 100000) --文字列の取得制限はSQLクライアント次第で変更可能
                FROM (SELECT %L keyword, %I columnname 
                      FROM %I.%I
                      WHERE REGEXP_REPLACE(CAST(%I AS TEXT), E''\r|\n|\r\n'', '''') LIKE ANY(%L) --改行を除いた上で検索
                ) t
                GROUP BY t.keyword',
                $1,
                columnname,
                schemaname,
                tablename,
                columnname,
                ARRAY(SELECT '%' || e || '%' FROM unnest($1) e)
        ) INTO columnvalues;
        IF columnvalues is not null THEN
            PERFORM pg_sleep(0.3); --CPU負荷が上がり過ぎる場合
            RETURN NEXT;
        END IF;
    END LOOP;
END;
$$ language plpgsql;

使い方

1. 検索キーワードのみを指定

select * 
from search_columns('{検索キーワード1(必須),...,検索キーワードn}');

2. 対象スキーマを指定

select * 
from search_columns('{検索キーワード(必須)}', '{検索対象スキーマ名1,...,検索対象スキーマ名n}');

3. 検索不要なカラムを指定

select * 
from search_columns('{検索キーワード(必須)}', '{}', '{検索対象外のカラム名1,...,検索対象外のカラム名n}');

4. 検索不要なテーブルを指定(部分一致)

select * 
from search_columns('{検索キーワード(必須)}', '{}', '{}', '{検索対象外テーブル名(全文、または一部分),...検索対象外テーブル名n}', '{}');

5. 検索対象のテーブルを指定

select * 
from search_columns('{検索キーワード(必須)}', '{}', '{}', '{}', '{検索対象テーブル名1,...検索対象テーブル名n}');

pgAdmin4使用時の注意点

pgAdmin4で検索結果を取得する場合は、ダブルクォーテーションを
補完する引用符付け機能を無効化(Noneに設定)する必要がある。

• ファイルに結果を保存(F8キー)を利用時
  

• コピー&ペースト利用時
  

登録したファンクションの消し方

DROP FUNCTION search_columns;

参考ページ

大々的に、こちらの記事を参考にさせていただきました