postgresqlでsqlのユーザ関数を定義できることは知っていましたが、手を出したことはありませんでした。

http://www.postgresql.jp/document/pg721doc/programmer/plpgsql.html

半角カナと全角カナを区別せずに検索するという仕様があるとします。アプリ(javaとかphpとか)でDBからデータを全部取ってきてから判定するのはいろんな意味で無駄が多くなります。

postgresqlのsql関数を試すチャンスと思い試してみたのが以下です。

-- # 引数の文字列の半角カナを全角カナに変換する関数
-- drop function kana2full(varchar);
create function kana2full(varchar) returns varchar as
'
declare
  p1  alias for $1;
  str varchar;
  len int;
  i int;
  zenkakus varchar;
  hankakus varchar;
begin
  str := p1;
  str := replace(str, ''ｶﾞ'', ''ガ'');
  str := replace(str, ''ｷﾞ'', ''ギ'');
  str := replace(str, ''ｸﾞ'', ''グ'');
  str := replace(str, ''ｹﾞ'', ''ゲ'');
  str := replace(str, ''ｺﾞ'', ''ゴ'');
str := replace(str, ''ｻﾞ'', ''ザ'');
str := replace(str, ''ｼﾞ'', ''ジ'');
str := replace(str, ''ｽﾞ'', ''ズ'');
str := replace(str, ''ｾﾞ'', ''ゼ'');
str := replace(str, ''ｿﾞ'', ''ゾ'');
str := replace(str, ''ﾀﾞ'', ''ダ'');
str := replace(str, ''ﾁﾞ'', ''ヂ'');
str := replace(str, ''ﾂﾞ'', ''ヅ'');
str := replace(str, ''ﾃﾞ'', ''デ'');
str := replace(str, ''ﾄﾞ'', ''ド'');
str := replace(str, ''ﾊﾞ'', ''バ'');
str := replace(str, ''ﾋﾞ'', ''ビ'');
str := replace(str, ''ﾌﾞ'', ''ブ'');
str := replace(str, ''ﾍﾞ'', ''ベ'');
str := replace(str, ''ﾎﾞ'', ''ボ'');
str := replace(str, ''ﾊﾟ'', ''パ'');
str := replace(str, ''ﾋﾟ'', ''ピ'');
str := replace(str, ''ﾌﾟ'', ''プ'');
str := replace(str, ''ﾍﾟ'', ''ペ'');
str := replace(str, ''ﾎﾟ'', ''ポ'');
str := replace(str, ''ｳﾞ'', ''ヴ'');
zenkakus := ''アイウエオカキクケコサシスセソタチツテトナニヌネノハヒフヘホマミムメモヤユヨラリルレロワヲンァィゥェォッャュョ'';
hankakus := ''ｱｲｳｴｵｶｷｸｹｺｻｼｽｾｿﾀﾁﾂﾃﾄﾅﾆﾇﾈﾉﾊﾋﾌﾍﾎﾏﾐﾑﾒﾓﾔﾕﾖﾗﾘﾙﾚﾛﾜｦﾝｧｨｩｪｫｯｬｭｮ'';
len := length(zenkakus);
for i in 0 .. len loop
str := replace(str, substr(hankakus, i, 1), substr(zenkakus, i, 1));
end loop;
return str;
end;
'
language 'plpgsql'
;
-- # 引数の文字列の全角カナを半角カナに変換する関数
-- drop function kana2half(varchar);
create function kana2half(varchar) returns varchar as
'
declare
  p1  alias for $1;
  str varchar;
  len int;
  i int;
  zenkakus varchar;
  hankakus varchar;
begin
  str := p1;
  str := replace(str, ''ガ'', ''ｶﾞ'');
  str := replace(str, ''ギ'', ''ｷﾞ'');
  str := replace(str, ''グ'', ''ｸﾞ'');
  str := replace(str, ''ゲ'', ''ｹﾞ'');
  str := replace(str, ''ゴ'', ''ｺﾞ'');
str := replace(str, ''ザ'', ''ｻﾞ'');
str := replace(str, ''ジ'', ''ｼﾞ'');
str := replace(str, ''ズ'', ''ｽﾞ'');
str := replace(str, ''ゼ'', ''ｾﾞ'');
str := replace(str, ''ゾ'', ''ｿﾞ'');
str := replace(str, ''ダ'', ''ﾀﾞ'');
str := replace(str, ''ヂ'', ''ﾁﾞ'');
str := replace(str, ''ヅ'', ''ﾂﾞ'');
str := replace(str, ''デ'', ''ﾃﾞ'');
str := replace(str, ''ド'', ''ﾄﾞ'');
str := replace(str, ''バ'', ''ﾊﾞ'');
str := replace(str, ''ビ'', ''ﾋﾞ'');
str := replace(str, ''ブ'', ''ﾌﾞ'');
str := replace(str, ''ベ'', ''ﾍﾞ'');
str := replace(str, ''ボ'', ''ﾎﾞ'');
str := replace(str, ''パ'', ''ﾊﾟ'');
str := replace(str, ''ピ'', ''ﾋﾟ'');
str := replace(str, ''プ'', ''ﾌﾟ'');
str := replace(str, ''ペ'', ''ﾍﾟ'');
str := replace(str, ''ポ'', ''ﾎﾟ'');
str := replace(str, ''ヴ'', ''ｳﾞ'');
zenkakus := ''アイウエオカキクケコサシスセソタチツテトナニヌネノハヒフヘホマミムメモヤユヨラリルレロワヲンァィゥェォッャュョ'';
hankakus := ''ｱｲｳｴｵｶｷｸｹｺｻｼｽｾｿﾀﾁﾂﾃﾄﾅﾆﾇﾈﾉﾊﾋﾌﾍﾎﾏﾐﾑﾒﾓﾔﾕﾖﾗﾘﾙﾚﾛﾜｦﾝｧｨｩｪｫｯｬｭｮ'';
len := length(zenkakus);
for i in 0 .. len loop
str := replace(str, substr(zenkakus, i, 1), substr(hankakus, i, 1));
end loop;
return str;
end;
'
language 'plpgsql'
;

• 追記:20080414 id:umitanuki さんからの指摘で、「ヴ」に対応しました。ありがとうございます。特殊なのは「ヴ」だけなのかな？「ッ」も抜けてましたので追加しました。

select * from boards where kana2full(name) = 'プログラマ';

みたいに使えると思います。

注意

実際のプロジェクトの仕様が半角カナと全角カナを区別しないようにする必要が無くなってしまったこともあり、全然テストしていません。m(_ _)m

バグを指摘いただければ、このエントリを修正します。

データベースのcharsetがutf-8の状態で簡単な確認をしましたが、他のcharsetではどうなんでしょう？