RULEで日内連番を振りたい

Question

前提・実現したいこと

こちらの質問の続きです。
前回の質問の経緯を簡単にご説明しますと、
DBにアイテムテーブルmst_itemと、アイテムが入庫された時に書き込まれるトランザクションtrn_item_lot_infoがあります。
上記二つのテーブル・トランザクションをjoinし、ビューview_item_resultを作成しました。
そのviewに日内連番・連携したかどうかのbooleanを持たせたいという質問です。

結局は
view専用のテーブルtmp_item_lot_infoを作成、tmp_item_lot_infoにinsertするルールrule_trn_item_lot_info_insertをtrn_item_lot_infoに作成する
↓
アイテムが入庫される(＝trn_item_lot_infoにinsertされる)
↓
insertでruleに基づき、tmp_item_lot_infoにデータがinsertされる
↓
tmp_item_lot_infoをselectしているviewに反映される
という流れに落ち着きました。

発生している問題・エラーメッセージ

プログラムからtrn_item_lot_infoにデータを複数insert(トランザクションでまとめている)する場合があります。
その際、tmp_item_lot_infoに複数データがinsertされてしまうと日内連番が同じになってしまうようです。
1つだけinsertされると特に問題ありません。
この連携は専用の機能なので、できればプログラム(VB.NET)を触らずにSQLだけで解決したいです。

該当のソースコード

mst_item

[PK]item_id(serial)	item_code(varchar(20))	item_name(varchar(128))
1	ITEM001	アイテム①
2	ITEM002	アイテム②
3	ITEM003	アイテム③

trn_item_lot_info

[PK]item_id(integer)	[PK]lot_number(varchar(20))	quantity(numeric(16,6))	shelf_life(date)
1	2019080801	1.000000	2020-08-08
2	2019080801	2.000000	2020-01-01
1	2019080901	10.000000	2020-08-09

trn_item_lot_info.rule_trn_item_lot_info_insert

CREATE OR REPLACE RULE rule_trn_item_lot_info_insert AS
    ON INSERT TO trn_item_lot_info DO INSERT INTO tmp_item_lot_info (item_code, quantity, lot_number, shelf_life, seq_in_date, is_coordinated, insert_timestamp)
  VALUES (( SELECT mst_item.item_code
            FROM mst_item
            WHERE mst_preparation.preparation_id = new.preparation_id), 
          new.quantity,
          new.lot_number,
          new.shelf_life,
          CASE
            WHEN (( SELECT max(tmp_item_lot_info.seq_in_date) AS max
                     FROM tmp_item_lot_info
                    WHERE tmp_item_lot_info.insert_timestamp::date = CURRENT_DATE
                    GROUP BY tmp_item_lot_info.insert_timestamp::date)) IS NULL THEN 1
            ELSE (( SELECT max(tmp_item_lot_info.seq_in_date) AS max
                     FROM tmp_item_lot_info
                    WHERE tmp_item_lot_info.insert_timestamp::date = CURRENT_DATE
                    GROUP BY tmp_item_lot_info.insert_timestamp::date)) + 1
          END, -- ここの書き方が悪いのだと思います。
          FALSE,
          CURRENT_TIMESTAMP
  );

tmp_item_lot_info
|item_code(varchar(20))|quantity(numeric(16,6))|lot_number(varchar(20))|shelf_life(date)|seq_in_date(integer)|is_coordinated(boolean)|[PK]insert_timestamp|
|:--|:--|:--|:--|:--|:--|
|ITEM001|1.000000|2019080801|2020-08-08|1|false|2019-08-08 08:00:00.000000|
|ITEM002|2.000000|2019080801|2020-01-01|2|false|2019-08-08 12:00:00.000000|
|ITEM001|10.000000|2019080901|2019-08-09|1|false|2019-08-09 08:00:00.000000|

試したこと

MAXではなくCOUNTを使用してみたのですが同様にダメでした。
ruleでinsertされるデータだけトランザクション無しに出来ればいいのかなと思うのですが。

補足情報（FW/ツールのバージョンなど）

PostgreSQL 9.3

Answer 1

テーブルtrn_item_lot_infoに項目seq_in_dateを追加しているので☓ですかね。
外部から日内連番をRULEに渡すためです。

CREATE OR REPLACE RULE rule_trn_item_lot_info_insert AS
  ON INSERT TO trn_item_lot_info
  DO INSTEAD INSERT INTO tmp_item_lot_info (item_code, quantity, lot_number, shelf_life, seq_in_date, is_coordinated, insert_timestamp)
  VALUES ((SELECT item_code FROM mst_item WHERE item_id=new.item_id),
  new.quantity,
  new.lot_number,
  new.shelf_life,
  COALESCE((SELECT max(seq_in_date) from tmp_item_lot_info WHERE shelf_life=new.shelf_life),0) + new.seq_in_date,
  FALSE,
  CURRENT_TIMESTAMP)
  ;

INSERT INTO trn_item_lot_info (item_id, quantity,lot_number,shelf_life,seq_in_date)
SELECT
 mst_item.item_id AS item_id,
 trn_item_lot_info.quantity AS quantity,
 trn_item_lot_info.lot_number AS lot_number,
 shelf_life,
 ROW_NUMBER() OVER (PARTITION BY shelf_life)
 FROM trn_item_lot_info
 LEFT JOIN mst_item USING (item_id)
 ORDER BY trn_item_lot_info.item_id, trn_item_lot_info.shelf_life
;

Answer 2

同一のトランザクション内なので、max()やcount()では無理でしょうね。

シーケンスならトランザクションと関係なく処理されるので使えそうですけど、連番にするためには、insert前にそのシーケンスをリセットする必要があるでしょうね。

Comments

[heart_crimson]

回答ありがとうございます。
おっしゃる通り、シーケンスですと日内の連番にならないため採用しておりません。
「1つのrule内に複数SQLを含めること」と「tmp_item_lot_info.insert_timestampの最大値＜本日だった場合シーケンスをリセット(1スタート)にすること」が出来れば採用できそうです。少し調べてみます。