PostgreSQLトリガーの履歴番号採番に関して

■質問内容

PostgreSQLのトリガー機能を使用し、履歴テーブルへinsertする際にインサートした日付を見て日付が同様の場合、同じ履歴番号を採番することは可能でしょうか。

履歴テーブルにマスタテーブルから履歴を書込む際の処理がうまくいかず、
何回かトリガーが更新される操作(画面上からマスタテーブルを書込むような)を行い、確認すると採番が合わない箇所が出てきており、困っております。
当方トリガーを用いて開発を行うことが初めてのため、何か解決策をお持ちの方は教えていただけると幸いです。（別の方法などもありましたらご教示頂けると幸いです）

■期待値の履歴テーブルデータ例

rireki_noにrireki_dateが同じ日付のものを同じ番号で採番したい

履歴テーブル

rireki_id rireki_no rireki_date
1         1         2021-12-10 13:45:00.544928 UPDATE
2         1         2021-12-10 13:45:00.544928 INSERT
3         1         2021-12-10 13:45:00.544928 INSERT
4         2         2021-12-10 13:45:35.682152 UPDATE
5         2         2021-12-10 13:45:35.682152 UPDATE
6         3         2021-12-10 14:34:42.002834 INSERT
7         3         2021-12-10 14:34:42.002834 DELETE

■試したこと(履歴テーブルへinsertする際のトリガ関数)

①履歴テーブルから最新日付を取得
②履歴テーブルから最大履歴番号を取得
③インサート時に履歴テーブルの最新日とCURRENT_TIMESTAMPで日付を比較し、
一致していたら最大履歴番号から-1（同時刻のものを同じ履歴番号として扱いたいため）
一致していなかったらそのまま最大履歴番号を挿入

create function insert_test_rireki() returns trigger as $insert_test_rireki$
DECLARE
-- 最新日付・・・①
latestTimeStamp timestamp without time zone := (SELECT MAX(rireki_date) FROM test_rirkei where id = NEW.id or id = OLD.id);
-- 最大rieki_no+1 ・・・②
maxRirekiNo INTEGER := (SELECT CASE WHEN max(group_no) is null THEN 1 ELSE max(rireki_no) + 1 END FROM test_rirkei whereid = NEW.id or id = OLD.id);

BEGIN ・・・③
INSERT INTO test_rirki(rireki_no,rireki_date,dml_status,........)
VALUES ( CASE WHEN latestTimeStamp = CURRENT_TIMESTAMP THEN maxRirekiNo -1 ELSE maxRirekiNo END,CURRENT_TIMESTAMP,TG_OP,........);
RETRUN NEW;
END;
・・・省略

■試した際の履歴テーブル結果

rireki_id rireki_no rireki_date
1         1         2021-12-10 13:45:00.544928 UPDATE
2         1         2021-12-10 13:45:00.544928 INSERT
3         1         2021-12-10 13:45:00.544928 INSERT
4         2         2021-12-10 13:45:35.682152 UPDATE
5         2         2021-12-10 13:45:35.682152 UPDATE
6         3         2021-12-10 14:34:42.002834 INSERT
7         3         2021-12-10 14:34:42.002834 DELETE
8         4         2021-12-10 14:38:04.488303 DELETE
9         4         2021-12-10 14:38:04.488303 INSERT
10        4         2021-12-10 14:44:11.494948 INSERT   ←rireki_dateが異なるのに"5"で採番が行われない
11        4         2021-12-10 14:44:11.494948 INSERT   ←rireki_dateが異なるのに"5"で採番が行われない

■トリガの設定

今回のデータ更新処理は、元のマスタデータが更新された後に履歴テーブルに書く処理を行いため、以下の設定にしております。
create trigger insert_test_rireki_trigger after insert or update or delete on mst_test for each row execute function insert_test_rireki();

行動規範の内容に同意します

回答1件

ベストアンサー

TIMESTAMP型には、秒よりも小さい単位の時刻成分も含まれているので、
正確に日付成分だけで比較したい場合には、
date_trunc('day', MAX(rireki_date))
みたいに加工しないと出ないかと思います。

秒未満の成分が不要であれば、timestamp型で宣言する際に精度0指定で
そもそも持たせないやり方もできるでしょうね。

アルゴリズムとしては、
最新日付が未来の値を取らないという前提が成り立つのであれば、
最新日付＝今日の日付ということで、
WHERE date_trunc('day', rireki_date) = CURRENT_DATE
などとすれば、今日履歴テーブルに書き込みがあれば履歴番号ヒットするので、
簡素化できそうな気がします。

投稿2021/12/10 11:50

退会済みユーザー

総合スコア0

rei_rrrrr

2021/12/13 08:41

ご回答いただきありがとうございます。＞TIMESTAMP型には、秒よりも小さい単位の時刻成分も含まれているので、＞正確に日付成分だけで比較したい場合には、＞date_trunc('day', MAX(rireki_date)) ＞みたいに加工しないと出ないかと思います。今回の履歴テーブルの仕様上、秒単位で履歴番号を振らないといけないためdate_trunc('second', MAX(rireki_date))の指定で試してみたのですが、やはりずれるときと成功するときがあり秒で比較するのは難しそうだなと思いました。日付での比較でしたら、以下の取得方法で採番できそうだなと思いました。今回は別の方法で考えることにしようと思います。＞WHERE date_trunc('day', rireki_date) = CURRENT_DATE 丁寧なご回答いただきありがとうございました。

行動規範の内容に同意します