前提・実現したいこと
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件
あなたの回答
tips
プレビュー
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
2019/08/09 06:31
2019/08/09 07:46
2019/08/09 08:14
2019/08/09 08:55
2019/08/13 00:42
2019/08/13 01:24
2019/08/13 01:28
2019/08/21 08:39