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

『徹底攻略OSS-DB Silver問題集［Ver.2.0］対応（黒本）』10章総仕上げ問題問33
でトリガーについてつまずきました。
『OSS教科書 OSS-DB Silver Ver2.0対応』を参照しつつ、
実際にテーブル作成~トリガ実行まで試してみようとしましたが、挫折。
つまずいたポイントを記録しておきます。

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

つまずいたポイント

①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は予約語なので使えませんでした。
himokuに差し替えて解決！
（実際にはこのkakeiboテーブルは関数を組み込む方法がわからなくなり（後述②）使用しなかった）

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

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

④引数$nって何？
関数定義について、緑本p243で「引数を関数内部において$nで利用」と説明されていた。
また、p244の記述例で$1,$2が使われていた。
が、それぞれ何を指すのか、読むだけだと理解できなかった。実行してみて意味がわかった。
↓
CREATE FUNCTIONで定義する引数が複数ある場合、今回実行したSQLだと
ひとつめの「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）』も、関数については触れられていない。
↓
この記事では実行断念。
↓
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） SQL文の発行時に1回起動するか（STATEMENT）
トリガーの削除	DROP TRIGGER トリガー名 ON テーブル名;	-

関数とプロシージャの違い

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

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

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

PostgreSQLでは、データベースに関数を定義することができます。一般的にはストアドプロシージャと呼ばれます。
（中略）
PostgreSQLでは関数（FUNCTION）とプロシージャ（PROCEDURE）の2種類のストアドプロシージャを作成できます。役割や定義はほぼ同じですが、以下のような違いがあります。

・関数は戻り値を定義できるが、プロシージャはできない
・関数はSELECTで呼び出すが、プロシージャはCALLで呼び出す
・関数は定義内でトランザクションのCOMMITやROLLBACKができないが、プロシージャはできる

（引用元：『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 使用言語;

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	特定の列が更新されたかどうかを判別 OLDは更新前、NEWは更新後の値を参照するときに使う UPDATEのトリガは双方使える INSERTはNEWのみ、DELETEはOLDのみ使える
ROW / STATEMENT	行ごとの発動か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章 関数と演算子）

「非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と多少の違いはあっても、参考になるかもしれない。
次はサンプルコードを探して試してみる！

参考

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

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