Viewのみで使用するデータを保持する方法

前提・実現したいこと

PostgreSQLのテーブルをSelectしてViewを作成しました。
このViewは他プログラムと連携を行うためのものです。

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

Viewに「シーケンス番号」(日内で連番を振る)と、
「そのデータが連携されたかどうか」のデータが必要になりました。
どのような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-08-08
1	2019080901	10.000000	2020-08-09

view_item_result

SQL
1CREATE VIEW view_item_result AS 
2 SELECT lpad(mst_item.item_code::text, 8)::character varying(8) AS item_code,
3    to_char(round(trn_item_lot_info.quantity), 'FM0000'::text)::character varying(4) AS quantity,
4    lpad(trn_item_lot_info.lot_number::text, 50)::character varying(10) AS lot_number,
5 FROM trn_item_lot_info
6   LEFT JOIN mst_item USING (item_id)
7  ORDER BY trn_item_lot_info.item_id, trn_item_lot_info.shelf_life
8;

item_code	quantity	lot_number
ITEM001	0001	2019080801
ITEM002	0002	2019080801
ITEM001	0010	2019080901

この「view_item_result」に
「is_coordinated」というカラムと、
「date_seq」というカラムを増やしたいです。

要件は以下の通りです。

is_coordinated

・boolean型
・viewの1レコードごとに保持
・Viewにデータが増えた時(＝trn_item_lot_infoにデータが書き込まれた時)はfalse
・他プログラムにデータを取得してもらったらtrueにする(この部分は他プログラムがViewに対しUPDATEを投げてもらう。RULEで実装予定)

date_seq

・integer型
・viewの1レコードごとに保持
・Viewにデータが増えた時(＝trn_item_lot_infoにデータが書き込まれた時)、日内で連番を保持する

以上を踏まえ、希望のViewは以下の通りです。

item_code	quantity	lot_number	is_coordinated	date_seq
ITEM001	0001	2019080801	true	1
ITEM002	0002	2019080801	true	2
ITEM001	0010	2019080901	false	1

データを取得済みですので1、2レコード目はis_coordinatedがtrueになっています。
また、1、2レコード目は同じ日にデータをinsertしたため、1、2と連番が振られています。
3レコード目は次の日にデータをinsertしたため、連番が1に戻っています。

試したこと

元々は「is_coordinated」のみだったため、
trn_item_lot_infoにカラムを追加し、保持していました。
(Viewでしか使わないデータをテーブルに持たせるのは変だとは思っていましたが、これしか方法が思いつきませんでした。)
しかし「date_seq」が増えたため「もっと別のやり方があるのではないか」と思い、質問させていただいた次第です。

いっそtrn_item_lot_infoがinsertされた時、triggerを用いて
「is_coordinated」と「date_seq」を追加したテーブルに書き込ませて、
それをViewでselectさせようかとも思いましたが回りくどいなと思っております。

情報が不足しておりましたらコメントください。
皆様のお知恵をお貸しいただければ幸いです。
どうぞよろしくお願いいたします。

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

PostgreSQL 9.3

追記①

下記の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-08-08
1	2019080901	10.000000	2020-08-09

新しく

[PK]item_id(integer)	[PK]lot_number(varchar(20))	quantity(numeric(16,6))	shelf_life(date)
1	2019010101	1.000000	2020-01-01

というデータが出来た場合(3行目と新しく出来る4行目のinsert日は別)、trn_item_lot_infoは

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

こうなりますが、Viewは

item_code	quantity	lot_number	is_coordinated	date_seq
ITEM001	0001	2019080801	true	1
ITEM002	0002	2019080801	true	2
ITEM001	0010	2019080901	false	1
ITEM001	0001	2019010101	false	1

このようになってほしいです。
trn_item_lot_infoはOrderByで一番上に来ていますが、insertした日付は後なので、Viewでは下に来ています。

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

回答1件

ベストアンサー

分かっておられるようですけど、Viewにデータを保存する事はできません。

date_seqはtrn_item_lot_infoに項目追加すれば良さそうですけど、

日内で連番を保持する

という制約があるので無理ですね。
データに応じて、変動があっても構わないなら、**row_number()**などのウィンドウ関数を使用すればViewだけで済みます。

投稿2019/08/09 06:01

編集2019/08/09 06:02

sazi

総合スコア25173

heart_crimson

2019/08/09 06:31

回答ありがとうございます。ただ連番が欲しいだけであれば、trn_item_lot_infoはデータが消えることは無いのでrow_number()はアリだと思いましたが、今回はそうではないので断念した次第です。邪道ではありますが新しくテーブルを作るしかありませんかね・・・？

sazi

2019/08/09 07:46

連番の仕様はtrn_item_lot_info.item_id, trn_item_lot_info.shelf_life順なのでしょうから、trn_item_lot_infoに追加や削除があれば変動しますので、参照整合性を保つつもりが無いならキー用途では使えません。ですので、新たにテーブルを作ろうとも使えない事は同じです。

heart_crimson

2019/08/09 08:14

「trn_item_lot_infoにデータが書き込まれた時」とありますように、trn_item_lot_infoにinsertされた順で連番を振りたいです。質問の例がまずかったです。申し訳ないです。

sazi

2019/08/09 08:55

削除はありませんか？追加のみだとしてもshelf_lifeに対しての挿入は発生しませんか？上記があると、連番という仕様上、番号の振り直しが必要になりませんか？

heart_crimson

2019/08/13 00:42

プログラムの仕様上、trn_tem_lot_infoのレコードが削除されることは無いです。「shelf_lifeに対しての挿入は発生しませんか？」が上手く理解出来ていないのですが、質問の追記内容の理解で問題ないでしょうか。

sazi

2019/08/13 01:24

shelf_lifeが連番の要素だと思いましたので、2020-08-09の後に2020-08-08が追加されるような場合がないかどうかを気にしていました。 lot_numberが日付の体系になっているなら、shelf_lifeは連番を決める要素にしなくてよいので、気にしなくても良いみたいですね。

sazi

2019/08/13 01:28

data_seqの振り直しを考えなくて良さそうですので、追加時に採番するようにすれば良いかと思います。保持するなら、インデックスや一意制約などの効率を考えると、trn_item_lot_infoに項目追加ですね。

heart_crimson

2019/08/21 08:39

色々考慮した結果、view専用のテーブルを作成し、trn_item_lot_infoがupdateされた際にその専用テーブルに書き込むruleを作成、その専用テーブルをviewに参照させるという形を取りましたが、連番が振れずに悩んでいますので再度質問を挙げさせていただきます。この度はありがとうございました。

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