plpgsql 全角半角変換

そういえば、こういうの書いたなーと思って… PostgreSQL で、全角と半角の変換を行うストアドプロシジャです。

CREATE FUNCTION kanahan2zen(strhan text) RETURNS text
    AS $_$DECLARE
strHan ALIAS FOR $1;
str TEXT;
i integer;

-- 通常カナ（半角２バイト）
--tZen2 VARCHAR[] := ARRAY[ "ガ" ];
tZen2 VARCHAR[] := ARRAY['ガ', 'ギ', 'グ', 'ゲ', 'ゴ', 'ザ', 'ジ', 'ズ', 'ゼ', 'ゾ', 'ダ', 'ヂ', 'ヅ', 'デ', 'ド', 'バ', 'ビ', 'ブ', 'ベ', 'ボ', 'パ', 'ピ', 'プ', 'ペ', 'ポ', 'ヴ'];
--tHan2 VARCHAR[] := ARRAY[ "ｶﾞ" ];
tHan2 VARCHAR[] := ARRAY['ｶﾞ', 'ｷﾞ', 'ｸﾞ', 'ｹﾞ', 'ｺﾞ', 'ｻﾞ', 'ｼﾞ', 'ｽﾞ', 'ｾﾞ', 'ｿﾞ', 'ﾀﾞ', 'ﾁﾞ', 'ﾂﾞ', 'ﾃﾞ', 'ﾄﾞ', 'ﾊﾞ', 'ﾋﾞ', 'ﾌﾞ', 'ﾍﾞ', 'ﾎﾞ', 'ﾊﾟ', 'ﾋﾟ', 'ﾌﾟ', 'ﾍﾟ', 'ﾎﾟ', 'ｳﾞ'];

BEGIN

str := strHan;
-- 半角２バイトの変換
for i in 1..26 loop
str := replace(str, tHan2[i], tZen2[i]);
end loop;

-- 半角１バイトの変換
str := translate(upper(str)
, 'ｱｲｳｴｵｶｷｸｹｺｻｼｽｾｿﾀﾁﾂﾃﾄﾅﾆﾇﾈﾉﾊﾋﾌﾍﾎﾏﾐﾑﾒﾓﾔﾕﾖﾗﾘﾙﾚﾛﾜｦﾝｧｨｩｪｫｯｬｭｮﾜｲｴｶｹｰ､｡･｣｢ﾞ ,<.>/?_}]*:+;{[~@|\`^=-)(&%$#""!'
, 'アイウエオカキクケコサシスセソタチツテトナニヌネノハヒフヘホマミムメモヤユヨラリルレロワヲンァィゥェォッャュョヮヰヱヵヶー、。・」「゛　，＜．＞／？＿｝］＊：＋；｛［￣＠｜￥｀＾＝－）（＆％＄＃”！');

return str;

END;
$_$
    LANGUAGE plpgsql;




--
-- TOC entry 26 (class 1255 OID 18986)
-- Dependencies: 5 590
-- Name: kanazen2han(text); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION kanazen2han(strzen text) RETURNS text
    AS $_$DECLARE
strZen ALIAS FOR $1;
str TEXT;
i integer;

-- 通常カナ（半角２バイト）
--tZen2 VARCHAR[] := ARRAY[ "ガ" ];
tZen2 VARCHAR[] := ARRAY['ガ', 'ギ', 'グ', 'ゲ', 'ゴ', 'ザ', 'ジ', 'ズ', 'ゼ', 'ゾ', 'ダ', 'ヂ', 'ヅ', 'デ', 'ド', 'バ', 'ビ', 'ブ', 'ベ', 'ボ', 'パ', 'ピ', 'プ', 'ペ', 'ポ', 'ヴ'];
--tHan2 VARCHAR[] := ARRAY[ "ｶﾞ" ];
tHan2 VARCHAR[] := ARRAY['ｶﾞ', 'ｷﾞ', 'ｸﾞ', 'ｹﾞ', 'ｺﾞ', 'ｻﾞ', 'ｼﾞ', 'ｽﾞ', 'ｾﾞ', 'ｿﾞ', 'ﾀﾞ', 'ﾁﾞ', 'ﾂﾞ', 'ﾃﾞ', 'ﾄﾞ', 'ﾊﾞ', 'ﾋﾞ', 'ﾌﾞ', 'ﾍﾞ', 'ﾎﾞ', 'ﾊﾟ', 'ﾋﾟ', 'ﾌﾟ', 'ﾍﾟ', 'ﾎﾟ', 'ｳﾞ'];

BEGIN

str := strZen;
-- 半角２バイトの変換
for i in 1..26 loop
str := replace(str, tZen2[i], tHan2[i]);
end loop;

-- 半角１バイトの変換
str := translate(upper(str)
, 'アイウエオカキクケコサシスセソタチツテトナニヌネノハヒフヘホマミムメモヤユヨラリルレロワヲンァィゥェォッャュョヮヰヱヵヶー、。・」「゛　，＜．＞／？＿｝］＊：＋；｛［￣＠｜￥｀＾＝－）（＆％＄＃”！'
, 'ｱｲｳｴｵｶｷｸｹｺｻｼｽｾｿﾀﾁﾂﾃﾄﾅﾆﾇﾈﾉﾊﾋﾌﾍﾎﾏﾐﾑﾒﾓﾔﾕﾖﾗﾘﾙﾚﾛﾜｦﾝｧｨｩｪｫｯｬｭｮﾜｲｴｶｹｰ､｡･｣｢ﾞ ,<.>/?_}]*:+;{[~@|\`^=-)(&%$#""!');

return str;

END;
$_$
    LANGUAGE plpgsql;

Postgresql 値比較備忘録

　今回は、PostgreSQL の Stored Procedure にて、こんなコードではまった。

  ...
  IF TG_OP = 'UPDATE' THEN
    IF OLD.name <> NEW.name THEN
       UPDATE ...
    END IF;
  END IF;
  ...

この場合、OLD.name か、NEW.name が null の場合に条件に合致しない。正しくは

  ...
  IF TG_OP = 'UPDATE' THEN
    IF COALESCE(OLD.name,'') <> COALESCE(NEW.name,'') THEN
       UPDATE ...
    END IF;
  END IF;
  ...

SPI で insert or update

　よくSQLでは、レコードが存在していれば更新し、存在していなければ挿入したいという事がありますが、それをPostgreSQL SPIで実現するための備忘録。尚、コードはバッファ・オーバーランに関して無考慮なので、そのまま踏襲しないように。

  bool record_insert_hoge( 
    int id,
    const char* namae
  ) {
    char buf[ 512 ];
    sprintf( buf, "insert into hoge (id, namae) values (%d,'%s');", id, namae );
    return (SPI_OK_INSERT == SPI_execute( buf, false, 1 ));
  }

  bool record_update_hoge(
    int id,
    const char* namae
  ) {
    char buf[ 512 ];
    sprintf( buf, "update hoge set namae='%s' where id=%d returning *;", namae, id );
    if( SPI_OK_UPDATE_RETURNING != SPI_execute( buf, false, 1 ) ) {
      return false;
    }
    return (SPI_processed == 1);
  }

  bool record_insert_or_update_hoge(
    int id,
    const char* namae
  ) {
    if( !record_update_hoge( id, namae ) ) {
      return record_insert_hoge( id, namae );
     }
    return true;
  }

OKかたづいた

　結局、仕様をよく読まずにコーディングしてハマっていた…という事だった。
palloc は現メモリコンテキストに、SPI_palloc は上位メモリコンテキストに、でもSPI_pfree は、互換性のためだけに存在して、pfree でメモリは解放。名前の付け方、わかんねーよ…と他人のせいにしてみる…。ちくしょー(;_;)。
　今まで、フリーしたメモリ領域に無断でアクセスしていたため、他のプロセスやスレッドに再利用されている場合に限りセグメンテーション・フォルトを起こしていた。という事のようだ。ようやく安定稼働して、これで公開しても大丈夫だろう。バグ直した通知しなきゃ…。

原因がわかった

　かたづいてなかったの続き、ようやく原因がわかった。postgresql の SPI_finish の呼び出し時期が早かったようだ。しかし、まだ釈然としない。SPI_finish の説明では、呼び出し元のメモリコンテキストにスイッチし、その呼び出し元のメモリコンテキスト内では、palloc で確保されたメモリは有効であると記述されているが、その状況を待たずして pfree されてしまっていたという事のようだ。
　一時的に利用するだけなので自動変数で良いと推察される所にわざわざ palloc と pfree を利用する公式のサンプルコードにしても、その意図がわからなくて不安になります。
　今渡こそかたづくだろ…。

PostGIS の領域判定

　ここギコさんの記事からパクらせていただきました。
PostgreSQL には、GIST という領域型のインデックスがあり、これを利用できるとクエリを劇的に速くすることができます。さて、地図で利用する座標系は、いろいろあって、広い領域を扱おうと思うとJGD2000などの緯度経度の座標系を扱う事になると思います。ところが、点から半径１０ｋｍ圏内といった指定をするためには、距離を緯度経度に変換しなければならず、面倒です。しかも、長さは緯度により変化するので、厄介です。
　以下の関数は、指定図形を指定座標系(SRID)と、その単位により拡張した領域を返すものです。

CREATE OR REPLACE FUNCTION expand_pseudo(geometry, integer, double precision)
  RETURNS geometry AS
$BODY$
DECLARE
  sid integer; 
  geo geometry;
BEGIN
  -- SRIDを退避
  sid := SRID($1);
  -- 指定のSRIDにGeometryを変換する
  geo := Transform( $1, $2 );
  -- 指定SRIDの単位系で図形を拡張する
  geo := expand( geo, $3 );
  -- 元のSRIDへ変換する
  geo := Transform( geo, sid );
  -- バウンディング・ボックスを返す
  RETURN Envelope( geo );
END
$BODY$
  LANGUAGE 'plpgsql' IMMUTABLE
  COST 100;

　いちいち、geo という変数に代入していますが、連結した方が効率は良いかもしれません。何をやっているか、コメントを書いて、効率よりもわかり易さを重点に書いてます。

　使い方は、

DECLARE
  geo geometry;
  rec record;
BEGIN
  -- JGD2000: 4612
  -- 世界測地日本公共座標１２系: 2454
  geo := GeomFromString('POINT(140.223412,42.123456)', 4612);
  geo := expand_pseudo( geo, 2454, 10000 );
  FOR rec IN select * from hoge_table where geom && expand_pseudo( geo, 2454, 10000 ) LOOP
    --- 何か
  END LOOP;
END

という感じです。

plpgsql で配列

　plpgsql で配列を扱います。配列要素の削除とか、基本的な関数が足りなくて、俺が関数付け足さなきゃダメって事？と思うけど、とてもそんな時間は取れない。直前のポストの cat_multistrings の使い方を提示するのが目的なので、そこに絞ります（前置きが長いって…）。

配列の宣言は、型の後に[]を付加する。

DECLARE
  new_geom geometry;
  geoms geometry[];
BEGIN

配列の初期化

  geoms := string_to_array('',',');

要素の追加

  geoms := array_append( geoms, new_geom );

cat_multilinestrings

　世間では３連休なんだけど、家で仕事・・・orz。マネジメントもするけど、諸々の事情で、多岐にわたってコーディングもしなければならない。
　PostGis で、MULTILINESTRING 型の図形を連結したいと思ったのだが、plpgsqlで関数を書くことに・・・。

CREATE OR REPLACE FUNCTION cat_multilinestrings(geoms geometry[])
  RETURNS geometry AS
$BODY$
DECLARE
  i integer;
  j integer;
  n integer;
  sid integer;
  pstr text;
  tg geometry;
BEGIN
  n := array_upper( geoms, 1 );
  if n < 1 then
    RAISE EXCEPTION 'cat_multilinestring: empty input';
  elseif n < 2 then
    RETURN geoms[1];
  end if;
  pstr := '';
  sid := SRID(geoms[1]);
  FOR i IN 1..n LOOP
    if GeometryType( geoms[i] ) != 'MULTILINESTRING' then
      RAISE EXCEPTION 'cat_multilinestring: invalid geometry type %1 in %2', GeometryType(geoms[i]), i;
    end if;
    if sid != GetSRID(geoms[i]) then
      tg := Transform(geoms[i],sid);
    else
      tg := geoms[i];
    end if;
    FOR j IN 1..NumPoints(tg) LOOP
      pstr := pstr || ',' || X(PointN(tg,j))::varchar || ' ' || Y(PointN(tg,j))::varchar;
    END LOOP;
  END LOOP;
  -- 先頭のカンマを除去
  pstr := substr( pstr, 2 );
  RETURN GeomFromText( 'MULTILINESTRING((' || pstr || '))', sid );
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;

　バッファ長の問題で、あまり長い図形を連結すると空になるという問題があります。plpgsqlにおける配列の取り扱いについては、次のポストで書きます。