PostgreSQLのトリガーは、特定のデータベースイベントが発生した際に自動的に実行されるコールバック関数です。

以下はPostgreSQLトリガーに関する重要なポイントです：

• トリガーは以下のタイミングで動作します：
• 操作前（制約チェックおよび挿入、更新、削除試行前）。
• 操作後（制約チェックおよび挿入、更新、削除完了後）。
• 更新操作（ビューに対する挿入、更新、削除時）。
• 「FOR EACH ROW」属性はオプションであり、選択すると各行の変更時に一度呼び出されます。「FOR EACH STATEMENT」を選択すると、何行変更されても1回だけ実行されます。
• WHEN句とトリガーアクションは、NEW.col_nameおよびOLD.col_name形式を使用して各要素にアクセスできます。ここでcol_nameはトリガーに関連付けられたテーブルの列名です。
• WHEN句がある場合、条件が満たされた行に対してのみSQL文が実行されます。条件がない場合、すべての行に対して実行されます。
• BEFOREまたはAFTERキーワードは、トリガーのアクションがいつ実行されるかを決定します。

構文

トリガーを作成する基本的な構文は次の通りです：

CREATE TRIGGER trg_name [BEFORE|AFTER] event_name ON tbl_name
[ -- トリガー論理...
];

ここではevent_nameはtbl_name上でINSERT、DELETE、UPDATEなどのデータベース操作です。必要に応じて「FOR EACH ROW」を指定できます。

次はUPDATE操作に基づいて特定の列にトリガーを作成する構文です：

CREATE TRIGGER trg_name [BEFORE|AFTER] UPDATE OF col_name ON tbl_name
[ -- トリガー論理...
];

実例

COMPANYテーブルに挿入される各レコードの監査ログを保持する例を示します：

db=# CREATE TABLE COMPANY(
   ID SERIAL PRIMARY KEY,
   NAME TEXT NOT NULL,
   AGE INT NOT NULL,
   ADDRESS VARCHAR(50),
   SALARY REAL
);

AUDITテーブルを新規作成し、COMPANYテーブルへの挿入ごとにログメッセージを記録します：

db=# CREATE TABLE AUDIT_LOG(
   EMP_ID INT NOT NULL,
   LOG_DATE TIMESTAMP NOT NULL
);

COMPANYテーブル上にトリガーを作成します：

db=# CREATE TRIGGER ins_audit AFTER INSERT ON COMPANY FOR EACH ROW EXECUTE FUNCTION log_func();

log_func()は次のように定義されています：

CREATE OR REPLACE FUNCTION log_func() RETURNS TRIGGER AS $$
BEGIN
   INSERT INTO AUDIT_LOG(EMP_ID, LOG_DATE) VALUES (NEW.ID, CURRENT_TIMESTAMP);
   RETURN NEW;
END;
$$ LANGUAGE plpgsql;

COMPANYテーブルにデータを挿入します：

db=# INSERT INTO COMPANY (NAME, AGE, ADDRESS, SALARY) VALUES ('John', 28, 'NYC', 30000.00);

同様に、更新や削除時のトリガーも作成可能です。

トリガーの一覧表示

pg_triggerテーブルから現在のデータベースのすべてのトリガーをリストアップできます：

db=# SELECT tgname FROM pg_trigger JOIN pg_class ON tgrelid=pg_class.oid WHERE relname='company';

結果例：

tgname
---------
ins_audit
(1 row)

トリガーの削除

トリガーを削除する基本的な構文は次の通りです：

DROP TRIGGER trg_name ON tbl_name;

例えば、companyテーブル上のins_auditトリガーを削除するには：

DROP TRIGGER ins_audit ON company;

PostgreSQLのインデックス

インデックスはデータ検索を高速化するための特殊なテーブルクエリです。簡単に言えば、インデックスはテーブル内のデータを指すポインタです。

インデックスはSELECTクエリやWHERE句を高速化しますが、UPDATEやINSERT操作のパフォーマンスを低下させる可能性があります。

CREATE INDEX コマンド

CREATE INDEXの基本構文は次の通りです：

CREATE INDEX idx_name ON tbl_name(col_name);

インデックスタイプ

単一カラムインデックス

単一カラムインデックスは1つのカラムに基づいて作成されます：

CREATE INDEX idx_name ON tbl_name(col_name);

組み合わせインデックス

複数のカラムに基づくインデックスです：

CREATE INDEX idx_name ON tbl_name(col1_name, col2_name);

ユニークインデックス

ユニークインデックスは重複を防ぐために使用されます：

CREATE UNIQUE INDEX idx_name ON tbl_name(col_name);

DROP INDEX

インデックスを削除するには次のようにします：

DROP INDEX idx_name;

インデックスを使用すべきでない状況：

• 小規模なテーブルでは不要です。
• 大量の更新や挿入操作のあるテーブルでは非効率です。
• 多くのNULL値を持つカラムでは不適切です。
• 頻繁に操作されるカラムでは避けます。