33.9. ユーザ定義の集約

PostgreSQLにおける集約関数は、状態値と状態遷移関数で表現されています。 つまり、1つの集約は入力項目が処理される度に変更される状態として定義することができます。 新しい集約関数を定義するためには、状態値のデータ型、状態の初期値、そして状態遷移関数を選択します。 状態遷移関数はただの普通の関数で、集約以外の文脈でも使うことができます。 求められる集約の結果が動いている状態値の中に保持しなければならないデータと違う場合は、最終関数も指定することができます。

したがって、集約のユーザに見える引数と結果のデータ型に加え、引数と結果の型のどちらとも違う内部状態値のデータ型があります。

もし最終関数を使わない集約を定義したとすると、それぞれの行の列値で動いている関数を計算する集約ができます。 Sumはそのような集約の一例です。 Sumは0から始まり常に現在の行の値をその時点までの総和に追加します。 たとえば、もしSum集約を複素数のデータ型で動作するようにしたければ、そのデータ型の加算関数だけが必要になります。 集約定義は以下のようになります。

CREATE AGGREGATE complex_sum (
    sfunc = complex_add,
    basetype = complex,
    stype = complex,
    initcond = '(0,0)'
);

SELECT complex_sum(a) FROM test_complex;

 complex_sum
-------------
 (34,53.9)

(実際の使用では、この集約にはsumとだけ名付け、complex型の列にどのsum関数を適用するかはPostgreSQLにまかせることになるでしょう。)

上記のsumの定義は、もし非NULLの入力値がなければ0（初期状態）を返します。 その場合本来は代わりにNULLを返したいのではないかと思いますし、 標準SQLではsumがそのように動作することを期待しています。 そうするためには単にinitcond句を省略すれば、初期状態がNULLになります。 通常はそうすると、sfuncがNULL状態の入力をチェックしなければいけないということを意味しますが、sumや、その他max、minのような簡単な集約にとっては、最初の非NULL入力値を状態変数に挿入し、2番目の非NULL入力値で状態遷移関数をあてはめ始めれば十分です。 PostgreSQLは、もし初期状態がNULLで状態遷移関数が"厳密(strict)"とマークされている場合、自動的にそれを実行します（つまりNULL入力では呼び出されないようになります）。

もう1つの"厳密"な状態遷移関数のデフォルト動作としては、NULL入力値が現われると前の状態値が変わらずに維持されるということがあります。 したがって、NULL値は無視されます。 もしNULL入力に対し他の動作が必要な場合は、単に状態遷移関数を非厳密として定義し、NULL入力の検査を行うようにコーディングし、必要なことをすればよいのです。

Avg(平均値計算)はもっと複雑な集約の一例です。 それには2つの変動する状態が必要になります。入力の合計と入力数のカウントです。 最終的な結果はこれらの値を割算することによって得られます。 平均値計算は典型的に二要素の配列を状態遷移値として使って実装されます。 たとえば、avg(float8)の組み込みの実装は以下のようになっています。

CREATE AGGREGATE avg (
    sfunc = float8_accum,
    basetype = float8,
    stype = float8[],
    finalfunc = float8_avg,
    initcond = '{0,0}'
);

集約関数は多様状態遷移関数や多様最終関数を使用することができます。 これにより、同じ関数を使用して複数の集約を実装することができます。 項33.2.5に多様関数の説明があります。 もう少し細かくいうと、集約関数自身は、単一の集約定義で複数の入力データ型を扱うことができるように、多様基本型と多様状態型を指定することができるということです。 以下に多様集約の例を示します。

CREATE AGGREGATE array_accum (
    sfunc = array_append,
    basetype = anyelement,
    stype = anyarray,
    initcond = '{}'
);

ここで、集約を行なう呼び出し用の実際の状態は、その実際の入力型がその要素となる、配列型です。

以下に異なる2つの実データ型を引数として使用した出力例を示します。

SELECT attrelid::regclass, array_accum(attname)
    FROM pg_attribute
    WHERE attnum > 0 AND attrelid = 'pg_user'::regclass
    GROUP BY attrelid;

 attrelid |                                 array_accum
----------+-----------------------------------------------------------------------------
 pg_user  | {usename,usesysid,usecreatedb,usesuper,usecatupd,passwd,valuntil,useconfig}
(1 row)

SELECT attrelid::regclass, array_accum(atttypid)
    FROM pg_attribute
    WHERE attnum > 0 AND attrelid = 'pg_user'::regclass
    GROUP BY attrelid;

 attrelid |         array_accum
----------+------------------------------
 pg_user  | {19,23,16,16,16,25,702,1009}
(1 row)

更に詳しい情報は、CREATE AGGREGATEコマンドを参照してください。