【PostgreSQL】緑本の関数定義＆トリガーを試したい→挫折。つまずいたポイントをメモ

『[徹底攻略OSS-DB Silver問題集［Ver.2.0］対応（黒本）](https://www.amazon.co.jp/dp/B086YWFFPR/ref=sspa_dk_detail_0?psc=1p13NParams&spLa=ZW5jcnlwdGVkUXVhbGlmaWVyPUEzU1FBREpDSEMyUUlaJmVuY3J5cHRlZElkPUEwNDcwNTg1MzNLUUtTVjJZMVQ0MSZlbmNyeXB0ZWRBZElkPUFWSE1UMjRWQzBKMjEmd2lkZ2V0TmFtZT1zcF9kZXRhaWwmYWN0aW9uPWNsaWNrUmVkaXJlY3QmZG9Ob3RMb2dDbGljaz10cnVl)』10章総仕上げ問題問33
でトリガーについてつまずきました。
『[OSS教科書 OSS-DB Silver Ver2.0対応](https://www.amazon.co.jp/OSS%E6%95%99%E7%A7%91%E6%9B%B8-OSS-DB-Silver-Ver2-0%E5%AF%BE%E5%BF%9C-%E7%A6%8F%E5%B2%A1/dp/4798162558/ref=sr_1_1?__mk_ja_JP=%E3%82%AB%E3%82%BF%E3%82%AB%E3%83%8A&crid=1FFH6POCJ18L4&keywords=OSS%E6%95%99%E7%A7%91%E6%9B%B8+OSS-DB+Silver&qid=1646351212&sprefix=oss%E6%95%99%E7%A7%91%E6%9B%B8+oss-db+silver+%2Caps%2C155&sr=8-1)』を参照しつつ、
実際にテーブル作成~トリガ実行まで試してみようとしましたが、挫折。
つまずいたポイントを記録しておきます。

（次はシンプルなサンプルコードを見つけてそれを真似するところから始めようと思います。）

# つまずいたポイント
**①CREATE TABLEで指定した列名に「group」が入っているとエラー発生（解決済み）**
↓エラー文

```
ossdb=# CREATE TABLE kakeibo (store text, group text);
ERROR:  syntax error at or near "group"
LINE 1: CREATE TABLE kakeibo (store text, group text);
```

groupは[予約語](https://www.postgresql.jp/document/11/html/sql-keywords-appendix.html)なので使えませんでした。
himokuに差し替えて解決！
（実際にはこのkakeiboテーブルは関数を組み込む方法がわからなくなり（後述②）使用しなかった）

**②自分でゼロから関数定義&トリガ作成→断念した**
・家計簿テーブルで店名カラムに店名を入力すると→費目カラムに自動で費目が入力される
・売上テーブルで売上金額カラムに売上金額を入力すると→合計カラムに 入力済みのそれまでの売上金額の合計が自動で入力される
という関数&トリガを実行しようと試みたが、
入力済みのレコードに新たに値を足して合計金額を出す（後述③）、という方法がわからず断念。

**③前のレコードの値に対して値を足す方法がわからない。（これはOSSDB試験の範囲外？）**

**④引数$nって何？**
関数定義について、緑本p243で「引数を関数内部において`$n`で利用」と説明されていた。
また、p244の記述例で`$1`,`$2`が使われていた。
が、それぞれ何を指すのか、読むだけだと理解できなかった。実行してみて意味がわかった。
↓
CREATE FUNCTIONで定義する引数が複数ある場合、[今回実行したSQL](https://qiita.com/ume-san/items/6ac14b32380af90c5f74#%E5%AE%9F%E8%A1%8C)だと
ひとつめの「int」が第1引数`$1`、
ふたつめの「timestamp」が第2引数`$2`
解決。

**⑤トリガと関数の違いをわかっていなかった**
→解決（記事中で整理）

**⑥ループ処理と条件分岐→断念**
緑本には関数処理記述の中にループ処理と条件分岐を入れる記述例（後述①関数定義・変数宣言・関数の処理を記述）が記載されていたけれど、それを真似するのは現段階の自分の理解では難しい。

**⑦後述（①関数定義・変数宣言・関数の処理を記述）のコードを試したいが断念**
緑本p244に記載されていたコードを一文一文読み解くところまではできたが、
その一文一文をつなげるとどう動くのか（実行したらどんな動きになるのか）、わからない。
この関数に適したテーブルを作って実行して確かめたいが、これに適したテーブルをどう作って良いか、
また、そのテーブルに何をINSERTしたらよいのか、わからない。
↓
実行断念。

**⑧後述（②テーブルとトリガを作成）のコードを試したいが断念**
緑本p238に記載されていたコードを一文一文読み解こうとしたが、
`WHEN (OLD.c1 IS DISTINCT FROM NEW.c1 )`の意味がわからない。（緑本にはこのIS DISTINCTの使い方についての解説は無い。SELECT DISTINCTについての解説はあるが、今回のコードとは違うと思う。）
↓
コードを実行してどんな動きをするのか確かめたいが、
何をINSERTしたらよいのか（trg_f()をどう組み込んだら実行できるのか）わからない。

**⑨簡単なコードサンプルが欲しい→見つからない**
「関数定義(CREATE FUNCTION)・変数宣言(DECLARE)・関数の処理記述(BEGIN-END;)」
↓
「作った関数を使ったトリガの作成」
↓
「トリガ実行」

という一連の流れを実行したい。
が、自分でコードを作ってみようとしても（家計簿テーブル、売上テーブル）、値の合計方法がわからない等
知識を補う必要があった&その知識をどう補って良いかわからなかった。
そこで、現段階の自分がわかる内容のシンプルなサンプルコードを見つけようとしたが、見つけられず。
緑本も黒本も、部分部分のサンプルコードは掲載されているけれど、一連の流れを実行できるコードは掲載されていない。
以前使った『[オープンソースデータベース標準教科書 -PostgreSQL-（Ver.2.0.0）](https://www.amazon.co.jp/%E3%82%AA%E3%83%BC%E3%83%97%E3%83%B3%E3%82%BD%E3%83%BC%E3%82%B9%E3%83%87%E3%83%BC%E3%82%BF%E3%83%99%E3%83%BC%E3%82%B9%E6%A8%99%E6%BA%96%E6%95%99%E7%A7%91%E6%9B%B8-PostgreSQL-%E5%AE%AE%E5%8E%9F%E5%BE%B9-ebook/dp/B00BW2KFSW)』も、関数については触れられていない。
↓
この記事では実行断念。
↓
PL/pgSQL(PostgreSQL)ではないSQLでも、ストアドプロシージャ、CREATE FUNCTIONというコードはあるようだった。
PL/SQL(Oracle Database),MySQLでのサンプルコードなら見つかるかもしれない。
PL/pgSQLと多少の違いはあっても、参考になるかもしれない。
次はサンプルコードを探して試してみる！


以下、試行錯誤記録。

# トリガとは
テーブルの行に対する更新のイベント（INSERT 挿入/ DELETE 削除 / UPDATE 更新）が行われたときに、
指定の関数を呼び出す（規定のユーザー定義関数（プロシージャ）を発動させる）機能。
（参考：『OSS教科書 OSS-DB Silver Ver2.0対応』（緑本）p239、
『徹底攻略OSS-DB Silver問題集［Ver.2.0］対応』（黒本）p148）

□□□□□□こういうことだと理解□□□□□□
（事前にユーザー定義関数を指定しておく）
（テーブルに対してイベントが発動（例：INSERT）されたら発動するトリガを作成しておく）
↓
テーブルに対してイベント発動（例：INSERT）
↓
トリガ発動！
□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□

◆構文◆（[]内は省略可能。{}は選択。）
**CREATE TRIGGER トリガー名 {BEFORE | AFTER} イベントの種類 ON テーブル名 [FOR [EACH] {ROW | STATENENT}]
EXECUTE PROCEDURE 関数名 (引数) ;**

|ポイントは4つ↓|||
|--|--|--|
|実行タイミング①|BEFORE / AFTER|イベントの前後どちらで起動するか|
|起動単位|DELETE / INSERT / UPDATE / TRUNCATE|どのイベントが行われるとトリガーが起動するか|
|実行タイミング②|ROW / STATENENT|行の更新毎にトリガーを起動するか（ROW）<br>SQL文の発行時に1回起動するか（STATEMENT）|
|トリガーの削除|DROP TRIGGER トリガー名 ON テーブル名; |-|

# 関数とプロシージャの違い
トリガが何なのかざっくりわかったので、試しに使ってみようと思いました。
で、まず「ユーザー定義関数（プロシージャ）を指定」しなきゃな、と思い手が止まりました。

**関数とプロシージャって違うものなんじゃない？どっちを使えば良いの？
２つの違いは何？**

これは緑本に答えが書いてありました。

>PostgreSQLでは、データベースに関数を定義することができます。一般的には**ストアドプロシージャ**と呼ばれます。
（中略）
PostgreSQLでは関数（FUNCTION）とプロシージャ（PROCEDURE）の2種類のストアドプロシージャを作成できます。役割や定義はほぼ同じですが、以下のような違いがあります。
<br>・関数は戻り値を定義できるが、プロシージャはできない
・関数はSELECTで呼び出すが、プロシージャはCALLで呼び出す
・関数は定義内でトランザクションのCOMMITやROLLBACKができないが、プロシージャはできる
<br>（引用元：『OSS教科書 OSS-DB Silver Ver2.0対応』（緑本）p242（8章SQLとオブジェクト））

# 関数定義＆トリガーを試す
トリガー、使ってみます。

## 環境
|||バージョン|
|:--|:--|:--|
|Host OS|Windows 10 Home|21H1(OSビルド:19043.1165)|
|Virtual Machine|WSL2|-|
|Remote OS|Ubuntu| 20.04.2 LTS (GNU/Linux 5.4.72-microsoft-standard-WSL2 x86_64)|
|Database|PostgreSQL|12.7|

## 何をするか
「関数定義(CREATE FUNCTION)・変数宣言(DECLARE)・関数の処理記述(BEGIN-END;)」については、緑本p244のコードを
「作った関数を使ったトリガの作成」については、緑本p238 のコードを
実行したい。


## 実行
**①関数定義・変数宣言・関数の処理を記述**

◆関数定義の書式◆（戻り値が1つの場合）
`CREATE [OR REPLACE] FUNCTION 関数名(引数) RETURNS 戻り値 AS $$` 
`関数の記述`
`$$ LANGUAGE 使用言語;`

```sql:PL/pgSQL (←PostgreSQLではなくコレだという認識で合っているのか？）
CREATE OR REPLACE FUNCTION test_func(int, timestamp) RETURNS integer AS $$　
--関数定義(関数名は test_func。第1引数のデータ型はint、第2引数のデータ型はtimestamp。戻り値のデータ型はinteger。)
DECLARE 
　r timestamp; --LOOPで使用する変数の宣言（変数名はr。データ型はtimestamp。）
　result int := 0; --戻り値に使用する変数の宣言（変数へ代入する値は0。）
BEGIN
　FOR r IN SELECT c2 FROM tbl WHERE c1 = $1 -- FOR文でc2の値を逐次rへ代入
　LOOP 
　　IF r < $2 --第2引数のtimestampの情報より古い日付かチェック
　　THEN
　　　result := result + 1 ; --IFの結果が真ならresultをインクリメント(変数の値を1増する演算)
　　END IF ; --IFの終了
　END LOOP; --LOOPの終了
  RETURN result;  --戻り値をRETURN(返却)する
END;
$$ LANGUAGE plpgsql;　--使用言語

（コード参照：緑本p244）
```

↓
これを実行したらどんな動きになるのか、予想できない。
この関数に適したテーブルを作って実行して確かめたいが、これに適したテーブルをどう作って良いか、
また、そのテーブルに何をINSERTしたらよいのか、わからない。
↓
断念。

**②テーブルとトリガを作成**
①とは関係のないテーブル&トリガになってしまうが、緑本p244にあったサンプルコードを読み解いてみる。

◆書式◆（参照：緑本p238）
`CREATE TRIGGER トリガ名 {BEFORE | AFTER} {UPDATE | INSERT | DELETE | TRUNCATE}`
`ON テーブル名 [FOR [EACH] {ROW | STATENENT}]`
`EXECUTE PROCEDURE 関数名;`

|書式|内容|
|--|--|
|BEFORE / AFTER|更新前後どちらで発動するかを指定|
|UPDATE　など|更新種別を指定|
|-|更新種別は「OR」でつなげて複数指定することも可能|
|-|UPDATEを指定した場合に限り、「UPDATE OF 列名」で、特定の列が更新されたときにトリガを発動させることが可能|
|NEW / OLD|特定の列が更新されたかどうかを判別<br>OLDは更新前、NEWは更新後の値を参照するときに使う<br>UPDATEのトリガは双方使える<br>INSERTはNEWのみ、DELETEはOLDのみ使える|
|ROW / STATEMENT|行ごとの発動かSQL文ごとの発動かを指定|
|-|トリガの実際の処理となる関数は、最後の「関数名」に指定|

```sql:PL/pgSQL (←PostgreSQLではなくコレだという認識で合っているのか？）
CREATE TABLE tbl (c1 int, c2 text, c3 text);
--tblテーブルを作成（c1列はint型、c2・c3列はtext型）
CREATE TRIGGER trg_1 AFTER UPDATE OR INSERT ON tbl FOR EACH ROW
--trg_1トリガを作成（tblテーブルへの更新 or 挿入処理の実行後に、行ごとにトリガを発動）
  EXECUTE PROCEDURE trg_f();
--実際に発動させる関数名はtrg_f()

CREATE TRIGGER trg_2 AFTER UPDATE OF c1 ON tbl FOR EACH ROW
--trg_2トリガを作成（tblテーブルc1列の更新処理の実行後に、行ごとにトリガを発動）
  WHEN (OLD.c1 IS DISTINCT FROM NEW.c1 )
--☆★解読できない。更新前のc1列が更新後のc1列（重複している値を除去済みのc1列）？どういう意味？？★☆
--(DISTINCTについては下記へ)
  EXECUTE PROCEDURE trg_f();
--実際に発動させる関数名はtrg_f()

（コード参照：緑本p238）
```

◆DISTINCTについて◆
上記中の`WHEN (OLD.c1 IS DISTINCT FROM NEW.c1 )`でつまずいたのでメモ。
DISTINCTを緑本で引くと、p195に重複を除去したい場合に使用するという解説があった。
【書式】
`SELECT DISTINCT [ ON (重複除去対象の列名) ] 列名 FROM テーブル名 WHERE 検索条件;`
・「ON (重複除去対象の列名)」を省略した場合は、「列名」に列挙したすべての列を重複除去の対象として、列挙した「列名」を出力する。
・「ON」で重複除去対象の列名を指定した場合は、指定した列だけで重複除去を行う。

しかし、例文には「IS DISTINCT FROM」とある。この緑本の解説を無理やりあてはめて解釈しようとしたが、解読できず…。

改めて調べると、ドキュメントにこうあった。

>入力のどちらかがNULLの場合、通常の比較演算子は真や偽ではなく（「不明」を意味する）nullを生成します。 
例えば7 = NULLはnullになります。7 <> NULLも同様です。
この動作が適切でない場合は、IS [ NOT ] DISTINCT FROM述語を使用してください。
a IS DISTINCT FROM b　　　--等しくない（NULLは通常の値と同様に扱う）
a IS NOT DISTINCT FROM b　--等しい（NULLは通常の値と同様に扱う）
非NULLの入力では、IS DISTINCT FROMは<>演算子と同じです。 しかし、入力がどちらもNULLの場合、これは偽を返し、片方の入力のみがNULLの場合は真を返します。 同様に、IS NOT DISTINCT FROMは非NULL入力では=と同じですが、両方の入力がNULLであれば真を、片方のみがNULLの場合は偽を返します。 このように、これらの述語はNULLを「不明な値」ではなく、通常の値かのように動作します。
（引用元：[PostgreSQL 11.5文書　第9章 関数と演算子](https://www.postgresql.jp/document/11/html/functions-comparison.html)）

「非NULLの入力では、IS DISTINCT FROMは<>演算子と同じ」とのこと。今回のコードは、「非NULLの入力」なのか？わからない。

↓
これも上記コードを実行してどんな動きをするのか確かめたいが、
何をINSERTしたらよいのか（trg_f()をどう組み込んだら実行できるのか）わからない。


## 断念
「関数定義(CREATE FUNCTION)・変数宣言(DECLARE)・関数の処理記述(BEGIN-END;)」
↓
「作った関数を使ったトリガの作成」
↓
「トリガ実行」

という一連の流れを実行したい。
が、自分でコードを作ってみようとしても（家計簿テーブル、売上テーブル）、値の合計方法がわからない等
知識を補う必要があった&その知識をどう補って良いかわからなかった。
そこで、現段階の自分がわかる内容のシンプルなサンプルコードを見つけようとしたが、見つけられず。
↓
この記事では断念。
↓
PL/pgSQL(PostgreSQL)ではないSQLでも、ストアドプロシージャ、CREATE FUNCTIONというコードはあるようだった。
PL/SQL(Oracle Database),MySQLでのサンプルコードなら見つかるかもしれない。
PL/pgSQLと多少の違いはあっても、参考になるかもしれない。
次はサンプルコードを探して試してみる！

# 参考

https://blog.t5o.me/post/20140412/postgresql-plpgsql-beginner.html

https://qiita.com/jiyu58546526/items/7a9b0dafce961466ba7d

→トリガの作成方法、実行方法、参考にさせていただきました。

https://resanaplaza.com/2021/11/07/%E3%80%90%E3%82%B5%E3%83%B3%E3%83%97%E3%83%AB%E6%BA%80%E8%BC%89%E3%80%91postgresql-%E3%82%B9%E3%83%88%E3%82%A2%E3%83%89-%E3%83%97%E3%83%AD%E3%82%B7%E3%83%BC%E3%82%B8%E3%83%A3%E8%87%AA%E7%94%B1/

→をぶろぐさんの記事では、「トリガを仕込んだテーブルからさらに引数を取って動かす」こと、「FUNCTION , TRIGGER の削除の仕方」まで記載されていました。トリガを使って実際にどんなことができるのかがコードとコメントで示されていたので大変わかりやすかったです。ありがたいです…。