ユーザ定義型を作成してみよう(提案)

こんにちは。ぬこ＠横浜(@nuko_yokoham)です。
このエントリはPostgreSQL Advent Calendar 2012 : ATNDの12/3用として登録しました。

先日のPgDay 2012のs87さんのショートセッションで 漢数字型 ksj が簡単に紹介されたというのもあるので、今日はその漢数字型を例に、ユーザ定義型の作成について記したいと思います。

漢数字型 ksj とは

概要

その名のとおり、漢数字を表現形式とする整数型です。
特に実用性のある型というわけではないのですが、面白そうなので作成してみました。
(たぶん、OSS-DBMS/商用DBMSの中でも漢数字による演算をサポートしているものはないと思ったので)

漢数字型では

• 漢数字による入力および出力
• 漢数字による四則演算
• 集合演算(max, mix, sum)
• WHERE句による評価

など、普通の整数型でお馴染みの操作が可能になっています。

なんの役に立つのか？とは聞かないようにｗ

実行例

まず、ksj EXTENSIONを登録します。

ksjdb=# CREATE EXTENSION ksj;
CREATE EXTENSION
ksjdb=# \dx ksj
                     List of installed extensions
 Name | Version | Schema |                 Description                 
------+---------+--------+---------------------------------------------
 ksj  | 1.0     | public | data type for kanji representation integer 
(1 row)

ksjdb=#

四則演算の例です。 加算、減算、乗算、除算、剰余を求めます。

ksjdb=# SELECT '九拾八'::ksj + '七'::ksj;
 ?column? 
----------
 壱百五
(1 row)

ksjdb=# SELECT '九拾八'::ksj - '七'::ksj;
 ?column? 
----------
 九拾壱
(1 row)

ksjdb=# SELECT '九拾八'::ksj * '七'::ksj;
  ?column?  
------------
 六百八拾六
(1 row)

ksjdb=# SELECT '九拾八'::ksj / '七'::ksj;
 ?column? 
----------
 壱拾四
(1 row)

ksjdb=# SELECT '九拾八'::ksj % '七'::ksj;
 ?column? 
----------
 零
(1 row)

ksjdb=# 

漢数字型の列をもつテーブルを作成してデータを格納します。

ksjdb=# CREATE TABLE test (i int, k ksj);
CREATE TABLE
ksjdb=# \d test
     Table "public.test"
 Column |  Type   | Modifiers 
--------+---------+-----------
 i      | integer | 
 k      | ksj     | 

ksjdb=# INSERT INTO test VALUES
(520, '五百弐拾'),(1000, '壱千'),(321, '参百弐拾壱');
INSERT 0 3
ksjdb=# SELECT * FROM test;
  i   |     k      
------+------------
  520 | 五百弐拾
 1000 | 壱千
  321 | 参百弐拾壱
(3 rows)

ksjdb=#

漢数字型の列 k に対して、集合関数 sum(), max(), min() を実行します。

ksjdb=# SELECT sum(k), max(k), min(k) FROM test;
      sum       | max  |    min     
----------------+------+------------
 壱千八百四拾壱 | 壱千 | 参百弐拾壱
(1 row)

ksjdb=# 

WHERE句での評価の例。

ksjdb=# SELECT i, k FROM test WHERE k < '五百';
  i  |     k      
-----+------------
 321 | 参百弐拾壱
(1 row)

ksjdb=#

インデクスも使えます。

ksjdb=# CREATE INDEX k_idx ON test (k);
CREATE INDEX
ksjdb=# SET enable_seqscan = off;
SET
ksjdb=# EXPLAIN SELECT i, k FROM test WHERE k < '五百';
                            QUERY PLAN                            
------------------------------------------------------------------
 Index Scan using k_idx on test  (cost=0.00..8.27 rows=1 width=8)
   Index Cond: (k < '五百'::ksj)
(2 rows)

ksjdb=#

型を作成してみよう

ここからは、 ksjの実装をサンプルに型作成のステップを説明します。
ksjのソースや登録用のSQLスクリプトなどはGithubに登録している(https://github.com/nuko-yokohama/ksj)ので、興味があれば参照してみてください。

型を作る

http://www.postgresql.jp/document/9.2/html/sql-createtype.htmlコマンドでksj型を作成します。

CREATE TYPE ksj;

これで漢数字型は完成！・・・とはなりません(笑)

この時点ではこの型は何も出来ることはありません。では、なぜこれを最初に作るのかというと、後述の入出力関数の定義に ksj という型が必要になるからです。とりあえずは最初にダミーの型を作る、というものだと覚えておいても差し支えはないでしょう。

入出力関数

ksj型に入力する/ksj型から出力する関数を作成します。これを実装すると、ksj型への値の入力、ksj型からの後の取得が可能になります。
ksj型では実は内部で普通のintegerで値を管理しています。漢数字文字列から整数型に変換するC言語関数、整数型から漢数字文字列に変換するC言語関数(ksj_in(), ksj_out())を作成します。これらの変換関数はksj2int.c, int2ksj.c で実装しています。

そして、その関数をCREATE FUNCTIONで登録します。

CREATE FUNCTION ksj_in(cstring)
RETURNS ksj
AS 'MODULE_PATHNAME'
LANGUAGE C STRICT IMMUTABLE;

CREATE FUNCTION ksj_out(ksj)
RETURNS cstring
AS 'MODULE_PATHNAME'
LANGUAGE C STRICT IMMUTABLE;

上記の関数定義を見るとわかるように、CREATE FUNCTIONの関数内引数や、RETURNS句に"ksj"を指定する箇所があります、最初にCREATE TYPEで何もしないksj型を作成したのはこの関数で指定する必要があったためです。

ksj型の再定義

ksj_in, ksj_outという関数を定義したので、ksj型を再定義します。

CREATE TYPE ksj (
        INTERNALLENGTH = 4,
        INPUT = ksj_in,
        OUTPUT = ksj_out,
        STORAGE = plain
);

上記のうち重要なのは、INPUTとOUTPUTの指定です。ここに先ほど登録したksj_in, ksj_outを設定します。

さて、これでksj型に対する入出力は可能になりました。
しかし、この状態では演算や比較はできません。

四則演算の追加

まず、四則演算を追加してみます。
漢数字型の四則演算用のC言語関数を作成します。例えばksj_add()はこんなコードになります。

PG_FUNCTION_INFO_V1(ksj_add);
Datum ksj_add(PG_FUNCTION_ARGS);

Datum ksj_add(PG_FUNCTION_ARGS) {
    int32* ksj_l = (int32*) PG_GETARG_POINTER(0);
    int32* ksj_r = (int32*) PG_GETARG_POINTER(1);

    char* result;


    result = (char*) palloc(sizeof(int32));
    *((int32*) result) = ( *ksj_l + *ksj_r);

    PG_RETURN_POINTER(result);
}

ksjは内部表現としてintegerで管理しているので、四則演算時にもintegerで受け取る関数を作成します。
返却領域を確保して、あとは返却領域に加算結果を代入し返却することになります。
このC言語関数を用いて、SQL関数を以下のように定義します。

CREATE FUNCTION ksj_add(ksj, ksj)
RETURNS ksj
AS 'MODULE_PATHNAME'
LANGUAGE C IMMUTABLE STRICT;

さて、これでksj型に対して演算を行う関数ができました。

ksjdb=# SELECT ksj_add('百壱','拾六');
  ksj_add   
------------
 壱百壱拾七
(1 row)

ksjdb=# 

このままでも演算そのものは出来るのですが、せっかくなので演算子も定義してちょっと格好良くします。
演算子の定義のためにCREATE OPERATORを使います。
さきほど作成したksj_add()を　ksj型 + ksj型 という加算演算子として定義します。

CREATE OPERATOR + (
    leftarg = ksj,
    rightarg = ksj,
    procedure = ksj_add,
    commutator = +
);

上記の定義は演算子記号を"+"、左辺をksj型、右辺をksj型、演算用の関数ををksj_addと指定しています。
これで"+"記号を使った加算操作が可能になります。

ksjdb=# SELECT '百壱'::ksj + '拾六'::ksj;
  ?column?  
------------
 壱百壱拾七
(1 row)

ksjdb=# 

あとは、減算、乗算、除算、剰余といった操作の関数を作成し、それぞれの関数を用いた演算子をCREATE OPERATORで作成すれば四則演算は完成です。

集合関数

数値を扱う型なので、SUMやMAXといった集合演算もやはり扱えるようにしたいものです。
集合演算は四則演算とはまた別に定義が必要になります。
MAX関数を例に説明します。
MAXの処理を行うために、これもC言語の関数を用意します。

PG_FUNCTION_INFO_V1(ksj_max);
Datum ksj_max(PG_FUNCTION_ARGS);

Datum ksj_max(PG_FUNCTION_ARGS) {
    int32* ksj_l = (int32*) PG_GETARG_POINTER(0);
    int32* ksj_r = (int32*) PG_GETARG_POINTER(1);

    char* result;

    result = (char*) palloc(sizeof(int32));
    if (*ksj_l > *ksj_r ) {
        *((int32*) result) = *ksj_l;
    } else {
        *((int32*) result) = *ksj_r;
    }

    PG_RETURN_POINTER(result);
}

この関数では2つの引数を用意し、引数の大小を比較し、大きい方を返却するというシンプルなものです。MINは逆の比較をすればOKです。
で、このC言語関数をCREATE FUNCTIONで登録し、さらにhttp://www.postgresql.jp/document/9.2/html/sql-createaggregate.htmlでこの関数を使った集合演算の定義をします。

CREATE FUNCTION ksj_max(ksj, ksj)
RETURNS ksj
AS 'MODULE_PATHNAME'
LANGUAGE C IMMUTABLE STRICT;

CREATE AGGREGATE max (ksj)
(
    sfunc = ksj_max,
    stype = ksj,
    initcond = '零'
);

sfuncは集合演算時に各行で実行される関数を指定します。stypeには対象となる型を指定します。initcodには集合演算の初期値情報を指定します。ksj(の現状の版)では0か正の整数のみを扱うので、初期値としては最小の値となる0を指定します。MINの場合には、ksjで現状取りうる最大値をinitcodに指定しておきます。

なお、SUMについては単純に加算するだけなので、実は四則演算時に使ったksj_add()のC言語関数がそのまま流用できます。なので、SUMはhttp://www.postgresql.jp/document/9.2/html/sql-createaggregate.htmlの定義だけでOKです。

CREATE AGGREGATE sum (ksj)
(
    sfunc = ksj_add,
    stype = ksj,
    initcond = '零'
);

なお、現在のksjではAVG(平均)はまだサポートしていません。AVGのような集約演算の場合には、件数と合計値といった2つの情報を引き継ぐ必要があり、ちょっと実装が面倒なのでまだ実装してません。気が向いたら次期バージョンでは実装するかもしれませんが・・・。

比較演算

四則演算、集合演算の実装まで終わりました。
しかし、まだ重要な機能が実装されていません。そう、今のままではksjに対する比較ができません。

そこで比較演算を実装します。
ksjでは通常使うと思われる、比較演算子

• "="比較 (等しい)
• "<>"比較 (等しくない)
• "<"比較 (より大きい)
• "<="比較 (以上)
• "<"比較 (より小さい)
• "<="比較 (以下)

を実装することにします。

比較演算用のC言語関数を用意するのは今までと同じですが、比較演算子の場合には以下の様な常套手段があるようです。

• 比較用の内部関数を作成しておくと便利

この常套手段にしたがって、以下の様な3種類の値(-1, 0, 1)を返却する関数ksj_cmp_internalを作成しておきます。

static int
ksj_cmp_internal(int32 a, int32 b)
{
    if (a < b)
        return -1;
    if (a > b)
        return 1;
    return 0;
}

で、この関数を各比較演算子を実装するC言語関数から呼び出します。
=比較の場合なら、

PG_FUNCTION_INFO_V1(ksj_eq);
Datum ksj_eq(PG_FUNCTION_ARGS);

Datum
ksj_eq(PG_FUNCTION_ARGS)
{
    int32* a = (int32*) PG_GETARG_POINTER(0);
    int32* b = (int32*) PG_GETARG_POINTER(1);

    PG_RETURN_BOOL(ksj_cmp_internal( *a, *b) == 0);
}

という感じで実装します。
>比較の場合なら

PG_FUNCTION_INFO_V1(ksj_lt);
Datum ksj_lt(PG_FUNCTION_ARGS);

Datum
ksj_lt(PG_FUNCTION_ARGS)
{
    int32* a = (int32*) PG_GETARG_POINTER(0);
    int32* b = (int32*) PG_GETARG_POINTER(1);

    PG_RETURN_BOOL(ksj_cmp_internal( *a, *b) < 0);
}

という感じで。つまり、ksj_cmp_internalの呼び出し結果の評価方法を演算子ごとに変更するということです。
C言語関数の実装が終わったら、これを用いる演算子定義をまた作成します。
=比較演算の場合はこんな漢字で。

CREATE FUNCTION ksj_eq(ksj, ksj)
RETURNS bool
AS 'MODULE_PATHNAME'
LANGUAGE C IMMUTABLE STRICT;

CREATE OPERATOR = (
    leftarg = ksj,
    rightarg = ksj,
    procedure = ksj_eq,
    commutator = =,
    RESTRICT = eqsel
);

commutorやRESTRICTの説明はPostgreSQL文書http://www.postgresql.jp/document/9.2/html/sql-createoperator.htmlを見てください。

こうして各比較演算子用のC言語関数、SQL関数定義、演算子定義を実装します。

インデクス

さて、これで比較演算子も揃いました。
しかし、実はこのままではksj型のカラム・演算結果に対してインデクスを設定することができません！
ksj型ではB-treeインデクスが使えればいいので、B-treeインデクスを使用可能にするため、さらに定義を追加します。

まず、B-treeにおける比較関数を作成します。といっても、今回の場合には上記で使ったksj_cmp_internal関数のラッパ関数を使えばOKです。

PG_FUNCTION_INFO_V1(ksj_cmp);
Datum ksj_cmp(PG_FUNCTION_ARGS);

Datum
ksj_cmp(PG_FUNCTION_ARGS)
{
    int32* a = (int32*) PG_GETARG_POINTER(0);
    int32* b = (int32*) PG_GETARG_POINTER(1);

    PG_RETURN_INT32(ksj_cmp_internal( *a, *b));
}

この関数をSQL関数として登録し、この関数を使って演算子クラスをCREATE OPERATOR CLASSで定義します。

-- B-treeサポートルーチン
CREATE FUNCTION ksj_cmp(ksj, ksj)
RETURNS integer
AS 'MODULE_PATHNAME'
LANGUAGE C IMMUTABLE STRICT;

-- ksj演算子クラス
CREATE OPERATOR CLASS ksj_ops
    DEFAULT FOR TYPE ksj USING btree AS
        OPERATOR        1       < ,
        OPERATOR        2       <= ,
        OPERATOR        3       = ,
        OPERATOR        4       >= ,
        OPERATOR        5       > ,
        FUNCTION        1       ksj_cmp(ksj, ksj);

このあたりの説明はPostgreSQL文書にも書いてはあるのですが、いささか分かりにくい(CREATE OPERATOR CLASSがインデクス使用に関わるというのが直感的に分かりにくい)気はしています。
もちろん、PostgreSQL文書のCREATE OPERATOR CLASSの説明には「演算子クラスは、特定のデータ型がインデックスでどのように使用されるかを定義します。」とは書いてありますが、コマンド名からは直感的には分かりにくい気がする・・・。個人的にはこのコマンドは「インデクス戦略の作成」みたいな名前のほうが分かりやすい気もするのですが。
CREATE OPERATOR CLASSの"OPERATOR"の後の謎の数字は何？という疑問があると思いますが、これはインデックス拡張機能へのインタフェースの表35-2に書いてあるものをそのまま適用しています。
最後のFUNCTONの後の数字は、上記の数字とは別の意味を持っています。これはインデックス拡張機能へのインタフェースの表35-7に従い、「2つのキーを比較し、最初のキーが2番目のキーより小さいか、等しいか、大きいかを示す、0未満、0、もしくは0より大きい整数を返します。」というサポート関数の番号1を指定しています。

ここは非常に分かりにくい部分ですが、ともあれこの定義を行うことで、ksj型に対してB-treeインデクスによる検索が可能になります。

型変換

これは必須というわけではないのですが、ksjの場合は整数型との相互の型変換を定義しておくといろいろ捗ります。
といってもksjの場合は内部表現がintegerなので、型変換用のC言語関数では内部表現をそのまま返却するだけでOKです。

// ksj -> int32
PG_FUNCTION_INFO_V1(ksj_to_int32);
Datum ksj_to_int32(PG_FUNCTION_ARGS);

Datum
ksj_to_int32(PG_FUNCTION_ARGS)
{
    int32* a = (int32*) PG_GETARG_POINTER(0);

    PG_RETURN_INT32( *a );
}

// int32 -> ksj
PG_FUNCTION_INFO_V1(int32_to_ksj);
Datum int32_to_ksj(PG_FUNCTION_ARGS);

Datum
int32_to_ksj(PG_FUNCTION_ARGS)
{
    int32 a =  PG_GETARG_INT32(0);
    char* result = (char*) palloc(sizeof(int32));

    memcpy(result, &a, sizeof(int32));

    PG_RETURN_POINTER(result);
}

で、この関数を使って型変換をCREATE CASTで定義します。

-- CAST関数とCAST定義
CREATE FUNCTION ksj_to_int32(ksj)
RETURNS integer
AS 'MODULE_PATHNAME'
LANGUAGE C IMMUTABLE STRICT;

CREATE CAST (ksj AS integer) WITH FUNCTION ksj_to_int32(ksj);

CREATE FUNCTION int32_to_ksj(integer)
RETURNS ksj
AS 'MODULE_PATHNAME'
LANGUAGE C IMMUTABLE STRICT;

CREATE CAST (integer AS ksj) WITH FUNCTION int32_to_ksj(integer);

これで、整数値とksjの間での型変換が可能になります。

ksjdb=# SELECT 101::ksj;
  ksj   
--------
 壱百壱
(1 row)

ksjdb=# SELECT '壱百壱'::ksj::int;
 int4 
------
  101
(1 row)

ksjdb=# 

EXTENSIONとしてパッケージ化

一通りの機能を実装したので、これをEXTENSIONとしてパッケージ化します。
今回の場合は、今まで紹介した登録用の各種SQLスクリプトを ksj--1.0.sql という名前のファイルにまとめます。
そして以下の様な制御用ファイル ksj.control にまとめます。

# ksj extension
comment = 'data type for kanji representation integer '
default_version = '1.0'
module_pathname = '$libdir/ksj'
relocatable = true

パッケージ化の詳細は関連するオブジェクトを拡張としてパッケージ化を参照してください。
これで、CREATE EXTENSIONによってパッケージの登録が、DROP EXTENSIONによってパッケージの削除が出来るようになります。

リグレッションテスト

パッケージ化した後、余力があればこのEXTENSIONをテストするためのリグレッションテストを組み込むといいでしょう。
ksj にもリグレッションテストが組み込んであるので、make installcheckでテストをすることができます。
(ちなみに9.2だと失敗します・・・原因は途中でEXPLAIN結果を比較している箇所があり、9.1ではIndexScanだったプランが9.2ではIndexOnlyScanが選択され、期待結果ファイルと実行結果に差分が出るためです。次期バージョンでは修正予定です)
リグレッションテストの組み込みについては、リグレッションテストも参考にしてください。

まとめ

• CREATE TYPEを実行するだけで型は生成できるがそれでは不十分。
  • 生成した型に対して四則演算、集合演算、比較演算、インデクス定義、型変換など、さまざまな定義を追加する必要がある。
  • こういう簡単な型でも実装はそれなりに面倒。
• こうした拡張機能に対するきちんとしたレクチャをしてくれる人がいるといいなあ・・・
  • JPUGの勉強会とかで誰かやってくれないかなあ・・・

おわりに

型拡張の機構はPostgreSQLの拡張機能の強力さを示すものだと思います。実装は少し面倒ですが、使い所によっては非常に有効なものになるかもしれません。
そして、こんな実用性のない面白い型も作れるところが個人的には好きではあります。