PostgreSQL でカタカナ変換（ユーザ定義関数）

概要

• Version 8.4.13
• PostgreSQL にはカタカナを変換する関数がないのでユーザ定義関数（ストアドプロシージャ）を作成する。
• PHP の mb_convert_kana() と仕様を合わせました。

準備

PL/pgSQL（ユーザ定義関数）が使用可能か調べる。
データベースにログインして下のコマンドを実行。

select lanname from pg_language;

出力結果

 lanname  
----------
 internal
 c
 sql
(3 行)

"plpgsql" が見つからないので作成する。

create language plpgsql;

出力結果

CREATE LANGUAGE

# select lanname from pg_language;
 lanname  
----------
 internal
 c
 sql
 plpgsql
(4 行)

"plpgsql" が追加されました。

ユーザ定義関数

php の mb_convert_kana() のような関数を作成しました。
http://php.net/manual/ja/function.mb-convert-kana.php

mb_convert_kana.sql

create or replace function mb_convert_kana(text, varchar) returns text
as $$
  DECLARE
    str alias for $1;
    option alias for $2;
    ret text;
    option_str text;
    i   int;
    loop_count int;
    option_v boolean;
    get_char varchar;
    hira_dakuten_zen varchar = '゛';
    kata_dakuten_zen varchar = '゛';
    kata_dakuten_han varchar = 'ﾞ';
    hira_zen varchar = 'あいうえおかきくけこさしすせそたちつてとなにぬねのはひふへほまみむめもやゆよらりるれろわをんぁぃぅぇぉっゃゅょわいえー';
    kata_zen varchar = 'アイウエオカキクケコサシスセソタチツテトナニヌネノハヒフヘホマミムメモヤユヨラリルレロワヲンァィゥェォッャュョヮヰヱー';
    kata_han varchar = 'ｱｲｳｴｵｶｷｸｹｺｻｼｽｾｿﾀﾁﾂﾃﾄﾅﾆﾇﾈﾉﾊﾋﾌﾍﾎﾏﾐﾑﾒﾓﾔﾕﾖﾗﾘﾙﾚﾛﾜｦﾝｧｨｩｪｫｯｬｭｮﾜｲｴｰ';
    daku_hira_zen varchar[] = array['が', 'ぎ', 'ぐ', 'げ', 'ご', 'ざ', 'じ', 'ず', 'ぜ', 'ぞ', 'だ', 'ぢ', 'づ', 'で', 'ど', 'ば', 'び', 'ぶ', 'べ', 'ぼ', 'ぱ', 'ぴ', 'ぷ', 'ぺ', 'ぽ', 'ぶ'];
    daku_kata_zen varchar[] = array['ガ', 'ギ', 'グ', 'ゲ', 'ゴ', 'ザ', 'ジ', 'ズ', 'ゼ', 'ゾ', 'ダ', 'ヂ', 'ヅ', 'デ', 'ド', 'バ', 'ビ', 'ブ', 'ベ', 'ボ', 'パ', 'ピ', 'プ', 'ペ', 'ポ', 'ヴ'];
    daku_kata_han varchar[] = array['ｶﾞ', 'ｷﾞ', 'ｸﾞ', 'ｹﾞ', 'ｺﾞ', 'ｻﾞ', 'ｼﾞ', 'ｽﾞ', 'ｾﾞ', 'ｿﾞ', 'ﾀﾞ', 'ﾁﾞ', 'ﾂﾞ', 'ﾃﾞ', 'ﾄﾞ', 'ﾊﾞ', 'ﾋﾞ', 'ﾌﾞ', 'ﾍﾞ', 'ﾎﾞ', 'ﾊﾟ', 'ﾋﾟ', 'ﾌﾟ', 'ﾍﾟ', 'ﾎﾟ', 'ｳﾞ'];
    num_zen varchar = '０１２３４５６７８９';
    num_han varchar = '0123456789';
    eng_zen varchar = 'ＡＢＣＤＥＦＧＨＩＪＫＬＭＮＯＰＱＲＳＴＵＶＷＸＹＺ';
    eng_han varchar = 'abcdefghijklmnopqrstuvwxyz';
  BEGIN
    ret = str;
    option_str = option;
    loop_count := 0;
    option_v = false;

    option_str = replace(option_str, 'a', 'rn');
    option_str = replace(option_str, 'A', 'RN');

    LOOP
      EXIT WHEN length(option_str) <= loop_count;
      get_char = substr(option_str, loop_count + 1, 1);
      if get_char = 'V' then
      	option_v = true;
      end if;
      loop_count := loop_count + 1;
    END LOOP;

    loop_count := 0;
    LOOP
      EXIT WHEN length(option_str) <= loop_count;
      -- postgres の substr() の from は 1 から始まる
      get_char = substr(option_str, loop_count + 1, 1);

	  if get_char = 'c' then
	    -- 2バイトで変換
	    -- 26 : count(hira_zen)
	    for i in 1..26 loop
          ret = replace(ret, daku_kata_zen[i], daku_hira_zen[i]);
	    end loop;
	    -- 1バイトで変換
        ret = translate(ret, kata_zen, hira_zen);
      elsif get_char = 'C' then
        for i in 1..26 loop
          ret = replace(ret, daku_hira_zen[i], daku_kata_zen[i]);
        end loop;
        ret = translate(ret, hira_zen, kata_zen);
      elsif get_char = 'k' then
        for i in 1..26 loop
          ret = replace(ret, daku_kata_zen[i], daku_kata_han[i]);
        end loop;
        ret = translate(ret, kata_zen, kata_han);
      elsif get_char = 'K' then
        if option_v then
          for i in 1..26 loop
            ret = replace(ret, daku_kata_han[i], daku_kata_zen[i]);
          end loop;
          ret = translate(ret, kata_han, kata_zen);
        else
          ret = translate(ret, kata_han, kata_zen);
          ret = translate(ret, kata_dakuten_han, kata_dakuten_zen);
        end if;
      elsif get_char = 'h' then
        for i in 1..26 loop
          ret = replace(ret, daku_hira_zen[i], daku_kata_han[i]);
        end loop;
        ret = translate(ret, hira_zen, kata_han);
      elsif get_char = 'H' then
        if option_v then
          for i in 1..26 loop
            ret = replace(ret, daku_kata_han[i], daku_hira_zen[i]);
          end loop;
          ret = translate(ret, kata_han, hira_zen);
        else
          ret = translate(ret, kata_han, hira_zen);
          ret = translate(ret, kata_dakuten_han, hira_dakuten_zen);
        end if;
      elsif get_char = 'n' then
        ret = translate(ret, num_zen, num_han);
      elsif get_char = 'N' then
        ret = translate(ret, num_han, num_zen);
      elsif get_char = 'r' then
        ret = translate(ret, eng_zen, eng_han);
      elsif get_char = 'R' then
        ret = translate(ret, eng_han, eng_zen);
      elsif get_char = 's' then
        ret = replace(ret, '　', ' ');
      elsif get_char = 'S' then
        ret = replace(ret, ' ', '　');
	  end if;
      loop_count := loop_count + 1;
    END LOOP;

    return ret;
  END;
$$ language 'plpgsql';

上記のSQLファイルをデータベースにインポートしてください。

psql DatabaseName < /.../mb_convert_kana.sql

データベースに登録されたかどうかは下のコマンドで確認できます。

select proname from pg_proc where proname = 'mb_convert_kana';

出力結果

     proname     
-----------------
 mb_convert_kana

データベースから削除するコマンドはこちらです。

drop function mb_convert_kana(text, varchar);

使用例

# select mb_convert_kana('Ａ１', 'a');
 mb_convert_kana 
-----------------
 a1

# select mb_convert_kana('ｶﾞ', 'KV');
 mb_convert_kana 
-----------------
 ガ

最後に

• ユーザ定義関数はデータベースに登録されるので関数が定義されていることが他の人から分かりづらくなります。プロジェクトで扱うときはデータベースの .sql を管理したり仕様書に記載しておくと良いと思います。