記事の位置づけ、実行環境、手順、参考リンク

（1）今まで集約関数を作ったことがなく、その練習（2）複数列を返す集約関数が何か役に立つかも（3）事前に複合型を定義することが必要で、その解説がウェブ上に見当らなかったので。実行環境はWindows7 32bit + PostgreSQL Portable 9.4.1。

集約関数の作り方は、普通のストアド関数と文法や手順が違います。あらかじめ「状態遷移関数」SFUNCを定義し、遷移状態のデータ型STYPEを決めておく必要があり、必要に応じて「最終計算関数」FINALFUNCなども事前に定義しておきます。

今回、複数列を返す集約関数を作るにあたり、事前に複合型を定義しなくとも関数自体は作れてしまい、「いざ使おうとするとエラーが出て嵌まる」状態でした^^;後々のため、その経過も再現します。まとめると

状態遷移関数の作成
最終計算関数の作成
とりあえず複数列を返す集約関数の作成
実際には使えない（複合型から個々の列を取り出せない）
複合型を定義
最終計算関数を作り直し
集約関数を作り直し
テスト

以下ドキュメント関連部分、集約関数を作る例、あと複合型が未定義の時の同じようなエラー例です。

実際のクエリ

最初に状態遷移関数を作ります。1行ごとに実行され、結果を次行に渡すもの。今回は単純に文字列を連結する内容にしました。第1引数が前行までの結果、第2引数が当該行の値です。第1引数のデータ型がすなわち遷移状態のデータ型STYPEになり、集約関数を作る時に使います。

CREATE OR REPLACE FUNCTION sfunc1(text, text)
RETURNS text LANGUAGE sql IMMUTABLE AS $_$
SELECT concat($1, $2);
$_$;

次に、クエリ自体は通るけど結局使えなかった「最終計算関数」と集約関数の定義。前者が複数列を返すようにし、1列目が連結された文字列、2列目が文字数。

CREATE OR REPLACE FUNCTION ffunc1(
    IN text, OUT result text, OUT count int)
LANGUAGE sql IMMUTABLE AS $_$
SELECT $1, length($1);
$_$;

CREATE AGGREGATE agg1(text) (
    sfunc = sfunc1,
    stype = text,
    finalfunc = ffunc1);

試しに使うと、結果自体は複合型できちんと返ってきます。

-- ダミーデータ
SELECT *
FROM generate_series(1, 80) AS gs,
    chr(12353 + gs);

-- 使ってみる
SELECT agg1(chr)
FROM generate_series(1, 80) AS gs,
    chr(12353 + gs)
GROUP BY (random() * 5) :: int;

                         agg1
-------------------------------------------------------
 (きごぞとなねぶぽもゐ,10)
 (ぃえぉぎくすずぜたぢどのばぴほめやゆよわゑ,21)
 (ぇしちつゎ,5)
 (ぅかがこさじそづてでにぬはぱふへべみゅょらりるれ,24)
 (おげせだっひびぺぼまむゃ,12)
 (あいうぐけざぷろ,8)
(6 行)

ところが複合型から個々の列を取り出そうとすると、ERROR: record type has not been registeredとなって駄目です。せっかく集約関数を作ったのに事実上使えない…。

SELECT (agg1(chr)).*
FROM generate_series(1, 80) AS gs,
    chr(12353 + gs)
GROUP BY (random() * 5) :: int;

ERROR:  record type has not been registered

最終計算関数を単独で使うと、複合型から個々の列を取り出せます。これが複数列を返すユーザ定義関数の普通の動作。残念ながら、そのままでは集約関数に使えない仕様のようです。

SELECT (ffunc1('abcde')).*;

 result | count                      
--------+-------                     
 abcde  |     5                      
(1 行)

そこで、最終計算関数が出力する複合型を定義し、改めて最終計算関数と集約関数を作り直します。状態遷移関数はそのままで大丈夫。

-- 複合型を定義
CREATE TYPE agg1_rev AS (result text, count int);

-- 最終計算関数を再作成
CREATE OR REPLACE FUNCTION ffunc1_rev(text)
RETURNS agg1_rev LANGUAGE sql IMMUTABLE AS $_$
SELECT $1, length($1);
$_$;

-- 集約関数も再作成
CREATE AGGREGATE agg1_rev(text) (
    sfunc = sfunc1,
    stype = text,
    finalfunc = ffunc1_rev
);

これで試すと、複合型からデータをきちんと取り出せました。

-- 複合型のまま
SELECT agg1_rev(chr)
FROM generate_series(1, 80) AS gs,
    chr(12352 + gs)
GROUP BY (random() * 5) :: int;

-- 複合型を個々の列に展開
SELECT (agg1_rev(chr)).*
FROM generate_series(1, 80) AS gs,
    chr(12352 + gs)
GROUP BY (random() * 5) :: int;

今後

複合型でなくJSON型でも試してみたい。遷移状態そのものをJSON型で保持して、最終計算関数は不要になり、結果の取り出しが簡単になるかな？状態遷移関数の中での処理が少し増えるけど。今回のように単純に文字を足したり計算するだけでなく、配列にまとめる場合はJSONの方が柔軟にできそうだし。

もう少し模索して自分なりの方法が固まったら、昨日の移動平均みたいに実用的なことを考える予定。