質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

87.36%

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

解決済

回答 1

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 721

score 15

前提・実現したいこと

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

CREATE VIEW view_item_result AS 
 SELECT lpad(mst_item.item_code::text, 8)::character varying(8) AS item_code,
    to_char(round(trn_item_lot_info.quantity), 'FM0000'::text)::character varying(4) AS quantity,
    lpad(trn_item_lot_info.lot_number::text, 50)::character varying(10) AS lot_number,
 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
;
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では下に来ています。

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 1

checkベストアンサー

0

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

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

日内で連番を保持する

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

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2019/08/13 10:24

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

    キャンセル

  • 2019/08/13 10:28

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

    キャンセル

  • 2019/08/21 17:39

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

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 87.36%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

同じタグがついた質問を見る