PostgreSQLでトリガによる値の自動算出を行ったのですが、
あまりウェブに情報が無く苦労したのでここにまとめておきます。
UPDATEをトリガとしてUPDATEをかけ、それをトリガとして…と無限ループに陥っている人向けです。
ただ、全てを理解しているわけではないため、ところどころ曖昧な点もあると思います。
はじめに
今回説明する内容について説明します。
今回の内容
この記事の概要
勤怠情報を格納するデータベースに、終了時刻と開始時刻、休憩時間をもとに合計勤務時間を算出するプログラムを作りました。
なお、使用したPostgreSQLのバージョンは以下です（クエリで確認した値）。
PostgreSQL 9.3.4, compiled by Visual C++ build 1600, 32-bit
具体的な説明
まず、対象となる勤務実績テーブルには
・開始時刻
・終了時刻
・休憩時間
・勤務時間
の４つの値があります。よって勤務時間は、
[終了時刻] - [開始時刻] - [休憩時間]
となります。
ただ、今回僕が作ろうとしたものは、
深夜0時を超えて業務を終了したとき（例：終了時刻 = 02:00）
の場合も考える必要がありますが、これについては後述します。
前提知識
自分はPL/pgSQLやトリガについての知識は全くありませんでしたが、
とりあえず公式リファレンスを読んでおきました。
PL/pgSQL - SQL手続き言語：https://www.postgresql.jp/document/9.3/html/plpgsql.html
ストアドプログラム・トリガとは
今回使ったこれらについて、簡単に説明します。
ストアドプログラム
ストアド（Stored）というのは、"格納された"という意味です。
簡単に言うと、データベースに格納されたプログラムを指します。
ストアドプロシージャやストアドファンクションなどの分類があるのですが、ここでは省略します。
トリガ
何かの動作、例えばINSERT文が実行されたことなどを引き金（Trigger）にトリガ関数を呼び出します。
今回はこのトリガを使って開始時間などが入力（INSERT）もしくは更新（UPDATE）されると
業務時間を計算するというプログラムを組みました。
手順
それでは、実際に作ったものを簡単に説明していきます。
参考：https://www.postgresql.jp/document/9.3/html/plpgsql-trigger.html
トリガ関数の作成
トリガ関数のテンプレート
まずはトリガ関数を作成します。
先に関数を作成しないと、呼び出す関数が存在しないといわれトリガを作成することができません。
トリガ関数は以下のような構造になっています。
CREATE OR REPLACE FUNCTION culc_totalworktime() RETURNS trigger AS $BODY$
BEGIN
/* 処理内容 */
RETURN [戻り値];
END;
$BODY$ LANGUAGE plpgsql VOLATILE COST 100; -- 言語を指定、後半はよくわかんない
ALTER FUNCTION [関数名]() OWNER TO [オーナー名]; -- この一行はよくわかんない
今回はストアドプログラムについての解説記事ではないので、
このテンプレートについての解説はしません。
というか、自分もよく理解していないのでできません。
トリガ関数
では、これをベースに関数を作成していきます。
通常勤務の場合
以下の通りです。
CREATE OR REPLACE FUNCTION culc_totalworktime() RETURNS trigger AS $BODY$
BEGIN
NEW.勤務時間 := NEW.終了時刻-NEW.開始時刻-NEW.休憩時間; -- 解説
RETURN NEW;
END;
$BODY$ LANGUAGE plpgsql VOLATILE COST 100;
ALTER FUNCTION culc_totalworktime() OWNER TO postgres;
コメントのある部分だけ解説していきます。
PL/pgSQLでは、「:=」を使うことで値の代入ができます。
NEW演算子は、処理後の値を表します。
よって、NEW.勤務時間 := [計算式]とすることで勤務時間に計算結果を代入することができます。
あまりよくわかっていませんが、RETURN NEW;とすることでその値に更新できるのだと思います。
はじめ、ここにUPDATE文を使って勤務時間を更新しようとしたのですが、それをすると
勤務時間を"UPDATE"したことをトリガにまた勤務時間を"UPDATE"して…
と無限ループに陥ります。
そのため、今回のように同一テーブルの値を更新したい場合は、上のように行うみたいです。
勤務時間が日をまたいだ場合
次に、勤務時間が日をまたぎ、終了時刻 = 02:00などになる場合です。
そんなケースなんか考えなくていいぜといいたいところですが、念のため組んでおきます。
この場合は、終了時刻が開始時刻より早かった場合に別の計算を行います。
CREATE OR REPLACE FUNCTION culc_totalworktime() RETURNS trigger AS $BODY$
BEGIN
IF NEW.終了時刻 > NEW.開始時刻 THEN
NEW.勤務時間 := NEW.終了時刻-NEW.開始時刻-NEW.休憩時間;
ELSE
NEW.勤務時間 := '24:00:00' -(NEW.開始時刻-NEW.終了時刻) -NEW.休憩時間;
END IF;
RETURN NEW;
END;
$BODY$ LANGUAGE plpgsql VOLATILE COST 100;
ALTER FUNCTION culc_totalworktime() OWNER TO postgres;
IF文を使い、日をまたがった時の計算式を追加しました。
参考 IF分：https://www.postgresql.jp/document/9.3/html/plpgsql-control-structures.html
参考 比較演算子：https://www.postgresql.jp/document/9.4/html/functions-comparison.html
一日は24時間なので、そこから働いていない分である「開始時刻と終了時刻の差」、「休憩時間」を引きます。
ただ、これだと次の日の17時に帰った場合などには対応できませんが、
それ以前にヒューマンエラーが返ってきます。
まあ真面目に書くと、もしそうなってしまった場合は23:59終了とし、次の日に00:00開始とすることで一応対処はできます。
トリガの作成
つぎにトリガを作成します。
勤怠情報テーブルにINSERTかUPDATEがかかると、勤怠時間を計算する関数＝ストアドファンクションが呼び出されます。
トリガの宣言は単純なので、結論だけ書いておきます。
CREATE TRIGGER totalworktime_trg
BEFORE INSERT OR UPDATE ON 勤務実績
FOR EACH ROW EXECUTE PROCEDURE culc_totalworktime();
おわりに
Oracle DatabaseのPL/SQLについての情報は割と多かったのですが、
PostgreSQLのPL/pgSQLについての情報は少なく、ここまでたどり着くのに苦労しました。
同じようにトリガを使う際の無限ループ対処法に悩んでいる人の役に立てたら幸いです。