PostgreSQLで英数字の全角・半角・大文字、小文字を区別せずに検索するための関数を定義する

こんにちは、kumaです！

この間実務で、PostgreSQLで英数字の全角・半角を区別せずに検索できるようにしてほしい。という要望をお客様からいただいて、実際に実装を行ったのでその方法をアウトプットしたいと思います！

この記事が、似たようなことを実現したい方のお役に立てれば幸いです💦

環境

• CentOS 7.6
• PostgreSQL 12.3

結論

以下のSQL文を入力する。

drop function if exists no_separate_translate(text);
create function no_separate_translate(
    p_src text
) returns text as $$
declare
    text_result text := upper(p_src);
    text_zenkaku_nums text := '０１２３４５６７８９';
    text_hankaku_nums text := '0123456789';
    text_zenkaku_upper_alphabets text := 'ＡＢＣＤＥＦＧＨＩＪＫＬＭＮＯＰＱＲＳＴＵＶＷＸＹＺ';
    text_hankaku_upper_alphabets text := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
begin
    text_result := translate(
        text_result
        , text_hankaku_nums || text_hankaku_upper_alphabets        
        , text_zenkaku_nums || text_zenkaku_upper_alphabets
    );  

    return text_result;
end;
$$ language plpgsql immutable

PostgreSQLでの関数の作り方

以下のような書き方で書きます。

create function 関数名(引数1, 引数の型, ・・・）

returns 戻り値の型 as $$

declare
  引数の定義
begin
  関数の処理
end;

$$

結局今回の関数では何をやっているか？

text_result text := upper(p_src);

text_resultに、p_srcの中身を大文字にしています。

text_zenkaku_nums text := '０１２３４５６７８９';
text_hankaku_nums text := '0123456789';
text_zenkaku_upper_alphabets text := 'ＡＢＣＤＥＦＧＨＩＪＫＬＭＮＯＰＱＲＳＴＵＶＷＸＹＺ';
text_hankaku_upper_alphabets text := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';

変数に全角数字と半角数字、全角英語と半角英語を格納しています。

text_result := translate(text_result, text_hankaku_nums || text_hankaku_upper_alphabets, text_zenkaku_nums || text_zenkaku_upper_alphabets); 

||で文字列の結合をおこなっています。
translate（置換対象の文字列, 検索文字列, 置換文字列）で置換対象の文字列中に入っている検索文字列を、置換文字列に置換します。
例： translate（"hogehoge", "ho", "ab"） → "abgeabge"

最後に

今回みたいに、DBMSの標準機能で実現できないことを関数を定義して対応することも今後ありうるので、少しずつ理解を深めて自分の武器を増やしていきたいと思います！