CREATE AGGREGATE name ( [ argmode ] [ argname ] arg_data_type [ , ... ] ) (
SFUNC = sfunc,
STYPE = state_data_type
[ , SSPACE = state_data_size ]
[ , FINALFUNC = ffunc ]
[ , FINALFUNC_EXTRA ]
[ , INITCOND = initial_condition ]
[ , MSFUNC = msfunc ]
[ , MINVFUNC = minvfunc ]
[ , MSTYPE = mstate_data_type ]
[ , MSSPACE = mstate_data_size ]
[ , MFINALFUNC = mffunc ]
[ , MFINALFUNC_EXTRA ]
[ , MINITCOND = minitial_condition ]
[ , SORTOP = sort_operator ]
)
CREATE AGGREGATE name ( [ [ argmode ] [ argname ] arg_data_type [ , ... ] ]
ORDER BY [ argmode ] [ argname ] arg_data_type [ , ... ] ) (
SFUNC = sfunc,
STYPE = state_data_type
[ , SSPACE = state_data_size ]
[ , FINALFUNC = ffunc ]
[ , FINALFUNC_EXTRA ]
[ , INITCOND = initial_condition ]
[ , HYPOTHETICAL ]
)
または以下の旧構文
CREATE AGGREGATE name (
BASETYPE = base_type,
SFUNC = sfunc,
STYPE = state_data_type
[ , SSPACE = state_data_size ]
[ , FINALFUNC = ffunc ]
[ , FINALFUNC_EXTRA ]
[ , INITCOND = initial_condition ]
[ , MSFUNC = msfunc ]
[ , MINVFUNC = minvfunc ]
[ , MSTYPE = mstate_data_type ]
[ , MSSPACE = mstate_data_size ]
[ , MFINALFUNC = mffunc ]
[ , MFINALFUNC_EXTRA ]
[ , MINITCOND = minitial_condition ]
[ , SORTOP = sort_operator ]
)CREATE AGGREGATEは、新しい集約関数を定義します。 配布物には基本的、かつ、よく使用される集約関数がいくつか含まれています。これらの集約関数については、項9.20に文書化されています。 新しい型を定義する場合、またはまだ提供されていない集約関数が必要な場合、必要な機能を実現するためにCREATE AGGREGATEを使うことができます。
スキーマ名が付けられている場合（例えば、CREATE AGGREGATE myschema.myagg ...）、集約関数は指定されたスキーマに作成されます。 スキーマ名がなければ、集約関数は現在のスキーマに作成されます。
集約関数は名前と入力データ型(複数可)の組み合わせによって識別されます。 演算の対象となる入力データ型が異なっていれば、同じスキーマ内に同じ名前の集約関数があっても構いません。 1つのスキーマ内では、集約関数の名前と入力データ型の組み合わせは、通常の関数の名前と入力データ型の組み合わせと異なる必要があります。 この動作は通常の関数名についてオーバーロードする時と同じです(CREATE FUNCTIONを参照してください)。
単純な集約関数は1つか2つの通常の関数から作られます。 状態遷移関数sfuncと最終計算関数ffunc(省略可能)です。 これらは以下のように使われます。
sfunc( 内部状態, 次のデータ値 ) ---> 次の内部状態 ffunc( 内部状態 ) ---> 集約の結果
PostgreSQLは、集約の現在の内部状態を保持するstypeデータ型の一時変数を作成します。 それぞれの入力行に対して、集約引数の値が計算され、現在の状態値と新しい引数値で状態遷移関数が呼び出され、新しい内部状態変数が計算されます。 全ての行が処理されると、最終関数が1回呼び出され、集約の出力値が計算されます。 最終関数がない場合は、終了時の状態値がそのまま返されます。
集約関数は、初期条件、つまり内部状態値の初期値を提供することができます。 これはtext型の値としてデータベースに格納されますが、状態値データ型の定数として有効な外部表現でなければいけません。 初期状態が与えられていない場合、状態値はNULLから始まります。
状態遷移関数が"strict"と宣言されている場合、NULLを入力値にして呼び出すことはできません。
そのような遷移関数では、集約は次のように実行されます。
NULL入力値を持つ行は無視されます。
（関数は呼び出されず、前回の状態値が保持されます。）
初期状態値がNULLである場合、初めて入力行がすべて非NULL入力値であった時にその最初の引数の値で状態値を置き換え、以後、すべて非NULL入力値のそれぞれの行について、遷移関数が呼び出されます。
このような動作は、maxのような集約を実装するには便利です。
ただし、state_data_typeが最初のarg_data_typeと同じ時にのみ有効であることに注意してください。
これらの型が異なる時は、非NULL初期値を供給するか、strictでない遷移関数を使わなければいけません。
状態遷移関数がstrictでない場合は、それぞれの入力行に対してその関数が無条件に呼び出されるので、NULL入力とNULL状態値を自分で処理しなければいけません。 これは、関数の作成者が、集約関数におけるNULL値の扱いを完全に制御できることを意味します。
最終関数が"strict"と宣言されていると、終了状態値がNULLの時は、最終関数が呼び出されません。
その場合、NULLという結果が自動的に出力されます
（もちろんこれは、strictな関数の一般的な動作に過ぎません）。
いずれにせよ、最終関数はNULLを返すことができます。
例えば、avgの最終関数は、入力が0行だとわかるとNULLを返します。
最終関数を、状態値だけでなく、集約の入力値に対応する追加パラメータも取るように宣言すると便利なことがあります。
こうすることの主な理由に、最終関数が多様型で、状態値のデータ型だけでは結果の型を決定するのに不十分である、ということがあります。
これらの追加パラメータは必ずNULLとして渡されます(従ってFINALFUNC_EXTRAオプションが使われている場合、最終関数はstrictであってはいけません)が、それでも有効なパラメータです。
最終関数は、現在の呼び出しでの実際の引数の型を特定するために、例えばget_fn_expr_argtypeを使うことができます。
集約は、項35.10.1に記述されているように移動集約モードをサポートすることができます。 このためには、MSFUNC、MINVFUNC、MSTYPEのパラメータを指定する必要があり、またオプションでMSPACE、MFINALFUNC、MFINALFUNC_EXTRA、MINITCONDのパラメータを指定できます。 MINVFUNCを除き、これらのパラメータは、Mのない単純集約の対応するパラメータのように動作しますが、集約について逆変換関数を含む別の実装を定義します。
パラメータのリストにORDER BYを含む構文は、順序集約と呼ばれる特別な種類の集約を作ります。 またHYPOTHETICALが指定されている場合は、仮想集約が作られます。 これらの集約は、ソートされた値のグループに対して、順序に依存した方法で作用するため、入力についてのソート順の指定は、呼び出しにおける本質的な部分になります。 また、これらの集約は直接引数をとることができます。 直接引数は、行毎に一度ではなく、集約に対して一度だけ評価されます。 仮想集約は、順序集約のサブクラスで、直接引数のいくつかが、集約される引数の列と、数とデータ型についてマッチする必要があります。 これにより、直接引数の値を、"仮想的な"行として、集約の入力行の集合に加えることができます。
MINやMAXのような振舞いをする集約では、すべての入力行を走査せずにインデックスを検索することで最適化できることがあります。
このように最適化される集約の場合、ソート演算子を指定することで明示してください。
その演算子で生成されるソート順で集約の最初の要素が生成されなければならないということが基本的な必要条件です。
言い換えると、
SELECT agg(col) FROM tab;
が
SELECT col FROM tab ORDER BY col USING sortop LIMIT 1;
と同じでなければならないということです。
更に、集約がNULL入力を無視すること、および、NULL以外の入力がまったくなかった時にのみNULLという結果を返すことも前提となります。
通常、データ型の<演算子はMINのソート演算子として、また、>演算子はMAXのソート演算子として適切です。
指定した演算子がB-treeインデックス演算子クラスの"より小さい"ストラテジか"より大きい"ストラテジのメンバでない限り、最適化が実際には効果がないことに注意してください。
集約関数を作成するためには、引数の型、状態の型、戻り値の型に対するUSAGE権限と遷移関数と最終関数に対するEXECUTE権限を持たなければなりません。
作成する集約関数の名前です（スキーマ修飾名も可）。
引数のモードで、INまたはVARIADICです。 (集約関数はOUTの引数をサポートしません。) 省略した場合のデフォルトはINです。 VARIADICを指定できるのは、最後の引数だけです。
引数の名前です。 現在は、文書化を目的とする場合にのみ有効です。 省略した場合、引数には名前がありません。
集約関数が演算する入力データ型です。
引数が存在しない集約関数を作成するには、引数指定のリストに*と記載してください
（例えば count(*)などの集約です）。
CREATE AGGREGATEの旧構文では、入力データ型は集約の名前の次に記載されたものではなくbasetypeパラメータにより指定されます。 この構文では入力パラメータを1つしかとれないことに注意してください。 この構文で引数を持たない集約を定義するためには、basetypeを"ANY" （*ではありません）と指定してください。 順序集約関数は旧構文では定義できません。
それぞれの入力行に対して呼び出される状態遷移関数の名前です。 通常のN引数を持つ集約関数では、sfuncはN+1個の引数を取らなければなりません。 最初の引数はstate_data_type型で、残りはその集約の入力データ型として宣言したものと一致していなければなりません。 この関数はstate_data_type型の値を返さなければなりません。 この関数は、現在の状態値と現在の入力データ値を受け取り、次の状態値を返します。
順序(仮想を含む)集約では、状態遷移関数は現在値と集約引数のみを受け取り、直接引数は受け取りません。 それ以外の点は全く同じです。
集約の状態値のデータ型です。
集約の状態値のおおよその平均サイズ(単位はバイト)です。 このパラメータを省略した場合、あるいはゼロを指定した場合、state_data_typeに基づいたデフォルトの推定が使われます。 プランナは、グループ化された集約のクエリに必要なメモリを推定するのに、この値を使います。 プランナは、ハッシュテーブルがwork_memに収まると見積もられる場合にのみ、ハッシュ集約の使用を検討します。 従って、このパラメータに大きな値を設定すると、ハッシュ集約が使われないようになります。
最終関数の名前です。最終関数は、全ての入力行に対する処理が終わった後、集約の結果を計算するために呼び出されます。 通常の集約では、この関数はstate_data_type型の引数を1つ取らなければなりません。 集約の出力データ型はこの関数の返り値として定義されます。 ffuncが指定されない場合には、集約の結果として終了時の状態値が使われます。出力型はstate_data_typeになります。
順序(仮想を含む)集約では、最終関数は終了時の状態値だけでなく、すべての直接引数の値も受け取ります。
FINALFUNC_EXTRAが指定された場合、最終関数は、終了時の状態値と直接引数に加えて、集約の通常の(集約された）引数に対応する追加のNULL値を受け取ります。 これは主に、多様型の集約が定義されているときに、集約の結果の型を正しく解決するのに役立ちます。
状態値の初期設定です。 データ型state_data_typeとして受け取り可能な文字列定数でなければいけません。 このパラメータが指定されない場合、状態値はNULLから始まります。
移動集約モードにおいて、それぞれの入力行に対して呼び出される前方状態遷移関数の名前です。 これは最初の引数と結果がmstate_data_type型で、state_data_typeとは異なるかもしれないことを除けば、通常の遷移関数と全く同じです。
移動集約モードで使われる逆状態遷移関数の名前です。 この関数はmsfuncと同じ引数および結果型を持ちますが、現在の集約状態に対して、値を追加するのではなく、取り除くために使われます。 逆遷移関数は前方状態遷移関数と同じstrictさの属性を持っていなければなりません。
移動集約モードを使うときの、集約状態値のデータ型です。
移動集約モードを使うときの、集約状態値のおおよその平均サイズ(バイト単位)です。 state_data_sizeと同じように作用します。
移動集約モードを使うときに、すべての入力行が走査された後で、集約結果を計算するために呼び出される最終関数の名前です。 これは、最初の引数の型がmstate_data_typeであり、MFINALFUNC_EXTRAにより追加のダミー引数を指定できることを除けば、ffuncと同じように作用します。 mffuncまたはmstate_data_typeによって決定される集約結果の型は、集約の通常の実装によって決定される型と適合しなければなりません。
移動集約モードを使うときの、状態値の初期設定です。 これは、initial_conditionと同じように作用します。
MINまたはMAXのような集約に対して関連付けされるソート演算子です。
これは単なる演算子の名前です（スキーマ修飾可能）。
この演算子は集約（これは単一引数の通常の集約でなければなりません）と同じ入力データ型を持つと前提されています。
順序集約についてのみ、このフラグは、仮想集約の要求に従って集約の引数が処理されることを指定します。 つまり、最後のいくつかの引数が、集約される(WITHIN GROUPの)引数と適合しなければなりません。 HYPOTHETICALフラグは実行時の動作には何の影響もなく、集約の引数のデータ型と照合についての解析時の解決にのみ影響します。
CREATE AGGREGATEのパラメータは、任意の順番で記述することができます。上記の順番で記述する必要はありません。
サポート関数名を指定するパラメータでは、必要なら、SFUNC = public.sumのようにスキーマ名を書くことができます。 しかし、引数の型をそこに書くことはできません。 サポート関数の引数の型は、他のパラメータにより決定されるからです。
集約が移動集約モードをサポートしていると、移動フレームの開始のあるウィンドウ（つまり、UNBOUNDED PRECEDING以外のフレーム開始モード)として集約が使われる場合に計算の効率が向上します。 概念的には、前方遷移関数はウィンドウフレームに下から入るときに集約の状態に入力値を追加し、逆遷移関数はフレームを上から抜けるときにそれを取り除きます。 従って、値が取り除かれるときは、必ず追加された時と同じ順番で取り除かれます。 従って、逆遷移関数が実行される時は、いつでも最も早く追加されたけれども、まだ取り除かれていない引き数値を受け取ります。 逆遷移関数は、最も古い行を取り除いた後、現在の状態に少なくとも1行が残ることを前提とできます。 (そうならない場合は、ウィンドウ関数の仕組みは、逆遷移関数を使うのでなく、単純に新しい集約を開始します。)
移動集約モードの前方遷移関数は、新しい集約値としてNULLを返すことは許されません。 逆遷移関数がNULLを返した場合、それは、逆関数がその入力値について状態計算を元に戻すことができなかったと見なされるため、集約の計算は現在のフレームの開始位置からやり直しとなります。 こうすることで、実行中の状態値を元に戻すのが現実的でないということが稀に起こるような場合でも、移動集約モードを使うことができます。
移動集約が実装されていない場合でも、集約は移動フレームを使うことができますが、フレームの開始が移動した場合、PostgreSQLは必ず集約全体を再計算します。 集約が移動集約モードをサポートするかどうかに関わらず、PostgreSQLは移動フレームの終了を再計算なしに処理することができます。 これは、集約の状態に新しい値を追加し続けることで処理できます。 最終関数は集約の状態値を破壊しないものとされるので、フレームの境界の集合に対して集約結果の値が得られた後でも、集約を続行することが可能です。
順序集約の構文では、VARIADICを最後の直接パラメータと、最後の集約(WITHIN GROUP)パラメータの両方について指定することができます。 しかし、現在の実装ではVARIADICの使用を2つの方法に制限しています。 1つ目は、順序集約では、VARIADIC "any"のみが利用でき、他のvariadicの配列型は利用できないことです。 2つ目は、最後の直接パラメータがVARIADIC "any"の場合、集約パラメータは1つだけしか使えず、かつそれもVARIADIC "any"でなければならない、ということです。 (システムカタログで使われる表現において、これらの2つのパラメータは、1つのVARIADIC "any"要素に統合されています。 なぜなら、pg_procは2つ以上のVARIADICパラメータがある関数を表現できないからです。) 仮想集約の場合、VARIADIC "any"パラメータに対応する直接引数は仮想的なパラメータで、それより前のパラメータは、集約引数に対応する制約のない、追加の直接引数となります。
現在は、順序集約は、ウィンドウ関数として使うことはできないので、移動集約モードをサポートする必要はありません。