PL/pgSQLはデータ変更やデータベースのイベントによるトリガ関数の定義に使用できます。
トリガ関数は、CREATE FUNCTIONコマンドを使って、(データ変更トリガには)trigger、(データベースイベントトリガには)event_triggerという戻り値の型を持った引数のない関数として作成されます。
その呼出しのトリガの原因となった条件を記述するため、TG_という名前の特別な局所変数が自動的に定義されます。
something
データ変更トリガはtriggerという戻り値の型を持った引数のない関数として宣言されます。
その関数は、たとえ、CREATE TRIGGERにて引数を取るものとしていたとしても、引数を持たないものと宣言しなければならないことに注意してください。
トリガの引数は、後述する通り、TG_ARGV経由で渡されます。
PL/pgSQL関数がトリガとして呼び出された場合、いくつかの特殊な変数が自動的に最上位レベルのブロックで作成されます。 それらを以下に示します。
NEW
RECORDデータ型。
この変数は行レベルのトリガでのINSERT/UPDATE操作によって更新された、新しいデータベースの行を保持します。
文レベルのトリガおよびDELETE操作では、この変数はnullです。
OLD
RECORDデータ型。
この変数は、行レベルのトリガでのUPDATE/DELETE操作によって更新される前のデータベースの行を保持します。
文レベルのトリガおよびINSERT操作では、この変数はnullです。
TG_NAME
nameデータ型。
実際に発行されたトリガの名前を持つ変数。
TG_WHEN
textデータ型。
トリガの定義に依存したBEFORE、AFTER、またはINSTEAD OFという文字列。
TG_LEVEL
textデータ型。
トリガの定義に依存したROW または STATEMENTという文字列。
TG_OP
textデータ型。
トリガを起動した操作を示す、INSERT、UPDATE、DELETE、またはTRUNCATEという文字列。
TG_RELID
oidデータ型。
このトリガの呼び出し元になるテーブルのオブジェクトID。
TG_RELNAME
nameデータ型。
このトリガの呼び出し元になるテーブルの名前。
将来これは廃止されそうです。
代わりにTG_TABLE_NAMEを使用してください。
TG_TABLE_NAME
nameデータ型。
このトリガの呼び出し元になるテーブルの名前。
TG_TABLE_SCHEMA
nameデータ型。
このトリガの呼び出し元になるテーブルのスキーマ名。
TG_NARGS
integer型。
CREATE TRIGGER文におけるトリガ関数に与えられる引数の数。
TG_ARGV[]
text型の配列型。
CREATE TRIGGER文での引数。
このインデックスは0から始まります。
無効なインデックス（0未満やtg_nargs以上）は、NULL値という結果になります。
トリガ関数はNULLまたは、トリガの発行元になったテーブルの構造を正確に持ったレコード/行を返さなければなりません。
BEFOREとして発行された行レベルトリガがNULLを返す場合には、トリガマネージャに実際の行への操作を取りやめるように通知します
（つまり、その後にトリガが発行されず、そのINSERT/UPDATE/DELETEはその行に対して実行されません）。
非NULL値を返す場合には、その操作はその行値で処理されます。
元のNEWの値と異なる行値を返すことは、挿入、更新される値を変更します。
したがってトリガ関数が行値を変更せずにトリガ処理を普通に成功させたい場合は、NEW(またはその等価な値)を返さなければなりません。
格納する行を変更するために、NEWの個々の値を直接置き換え、変更したNEWを返すことも、新しいレコード/行を完全に作成して返すことも可能です。
DELETEに対するBEFOREトリガの場合、返される値は直接的な影響を与えませんが、トリガ動作を継続させるためには非NULLを返さなければなりません。
DELETEトリガではNEWがNULLであり、これを返すことは通常無意味であることに注意して下さい。
DELETEトリガにおける通常の慣例はOLDを返すことです。
INSTEAD OFトリガ（これは常に行レベルトリガであり、ビューに対してのみ使用可能です）は、まったく更新を行わなかったためにこの行に対する残りの操作を飛ばさなければならない（つまり後続のトリガは発行されず、 トリガの発生元のINSERT/UPDATE/DELETEにおいて影響を受けた行数として数えられない）ことを通知するNULLを返すことができます。
この他の場合は、トリガが要求された操作を実行したことを通知するために、非NULLの値を返さなければなりません。
INSERTおよびUPDATE操作では、戻り値は、トリガ関数がINSERT RETURNINGおよびUPDATE RETURNINGをサポートするために変更しているかもしれない、NEWとなるはずです
（これは後続のトリガ、または、ON CONFLICT DO UPDATE句を伴うINSERT文の中で特別なEXCLUDED別名参照に渡される行値にも影響します）。
DELETE操作では、戻り値はOLDとなるはずです。
行レベルのAFTERトリガ、文レベルのBEFOREまたはAFTERトリガの戻り値は常に無視されます。
NULLとしても構いません。
しかし、これらの種類のトリガでも、エラーを発生させることで操作全体を中断させることが可能です。
例 43.3にPL/pgSQLのトリガ関数の例を示します。
例43.3 PL/pgSQLトリガ関数
このトリガの例では、テーブルの行が挿入または更新された時には必ず、現在のユーザ名と時刻がその行に入っていることを確実にします。 そして、従業員名が与えられていることとその給料が正の値であることを確認します。
CREATE TABLE emp (
empname text,
salary integer,
last_date timestamp,
last_user text
);
CREATE FUNCTION emp_stamp() RETURNS trigger AS $emp_stamp$
BEGIN
-- empnameとsalaryが与えられていることをチェック
IF NEW.empname IS NULL THEN
RAISE EXCEPTION 'empname cannot be null';
END IF;
IF NEW.salary IS NULL THEN
RAISE EXCEPTION '% cannot have null salary', NEW.empname;
END IF;
-- 支払時に問題が起こらないように
IF NEW.salary < 0 THEN
RAISE EXCEPTION '% cannot have a negative salary', NEW.empname;
END IF;
-- 誰がいつ変更したかを記録
NEW.last_date := current_timestamp;
NEW.last_user := current_user;
RETURN NEW;
END;
$emp_stamp$ LANGUAGE plpgsql;
CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp
FOR EACH ROW EXECUTE FUNCTION emp_stamp();
テーブルにおける変更のログを取る他の方法は、挿入、更新または削除の各々に対する行を保有する新テーブルを作成することです。 この方法はテーブルにおける変更の監査と考えることができます。 例 43.4はPL/pgSQLによる監査用トリガ関数の一例を示します。
例43.4 PL/pgSQLによる監査用のトリガ関数
このトリガの例では、empテーブルにおける行の挿入、更新または削除のどれもがemp_auditテーブルの中へ確実に記録（すなわち監査）されます。
現在時刻とユーザ名は、行った操作の種類とともにemp_auditの行の中に記録されます。
CREATE TABLE emp (
empname text NOT NULL,
salary integer
);
CREATE TABLE emp_audit(
operation char(1) NOT NULL,
stamp timestamp NOT NULL,
userid text NOT NULL,
empname text NOT NULL,
salary integer
);
CREATE OR REPLACE FUNCTION process_emp_audit() RETURNS TRIGGER AS $emp_audit$
BEGIN
--
-- empで行った操作を反映する行をemp_auditに作成
-- 操作の種類を決定するために、特殊な変数TG_OPを活用
--
IF (TG_OP = 'DELETE') THEN
INSERT INTO emp_audit SELECT 'D', now(), user, OLD.*;
ELSIF (TG_OP = 'UPDATE') THEN
INSERT INTO emp_audit SELECT 'U', now(), user, NEW.*;
ELSIF (TG_OP = 'INSERT') THEN
INSERT INTO emp_audit SELECT 'I', now(), user, NEW.*;
END IF;
RETURN NULL; -- AFTERトリガですので、結果は無視されます
END;
$emp_audit$ LANGUAGE plpgsql;
CREATE TRIGGER emp_audit
AFTER INSERT OR UPDATE OR DELETE ON emp
FOR EACH ROW EXECUTE FUNCTION process_emp_audit();
前例の変形では、各エントリが最終修正された時を表示するため、主テーブルを監査テーブルに結合したビューを使用します。 この方法でもテーブルの変化の監査証跡を全て記録できますが、監査証跡から抽出した各エントリの最終修正のタイムスタンプ表示することにより、監査証跡の簡単なビューを表示することにもなります。 例 43.5で示すものは、PL/pgSQLを用いたビューの監査トリガの例です。
例43.5 監査のためのPL/pgSQLビュートリガ関数
この例では、ビューを更新可能とし、その行の挿入、更新、削除をemp_auditテーブルに確実に記録（つまり監査）するためにビューに対するトリガを使用します。
現在時刻とユーザ名が実行された操作種類と一緒に記録されます。
ビューは各行の最終更新時間を表示します。
CREATE TABLE emp (
empname text PRIMARY KEY,
salary integer
);
CREATE TABLE emp_audit(
operation char(1) NOT NULL,
userid text NOT NULL,
empname text NOT NULL,
salary integer,
stamp timestamp NOT NULL
);
CREATE VIEW emp_view AS
SELECT e.empname,
e.salary,
max(ea.stamp) AS last_updated
FROM emp e
LEFT JOIN emp_audit ea ON ea.empname = e.empname
GROUP BY 1, 2;
CREATE OR REPLACE FUNCTION update_emp_view() RETURNS TRIGGER AS $$
BEGIN
--
-- 要求された操作を emp に実行し
-- emp_audit に行を作成し
-- emp の変化を反映する
--
IF (TG_OP = 'DELETE') THEN
DELETE FROM emp WHERE empname = OLD.empname;
IF NOT FOUND THEN RETURN NULL; END IF;
OLD.last_updated = now();
INSERT INTO emp_audit VALUES('D', user, OLD.*);
RETURN OLD;
ELSIF (TG_OP = 'UPDATE') THEN
UPDATE emp SET salary = NEW.salary WHERE empname = OLD.empname;
IF NOT FOUND THEN RETURN NULL; END IF;
NEW.last_updated = now();
INSERT INTO emp_audit VALUES('U', user, NEW.*);
RETURN NEW;
ELSIF (TG_OP = 'INSERT') THEN
INSERT INTO emp VALUES(NEW.empname, NEW.salary);
NEW.last_updated = now();
INSERT INTO emp_audit VALUES('I', user, NEW.*);
RETURN NEW;
END IF;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER emp_audit
INSTEAD OF INSERT OR UPDATE OR DELETE ON emp_view
FOR EACH ROW EXECUTE FUNCTION update_emp_view();
トリガの使用目的の1つは、あるテーブルのサマリテーブルを維持することです。 結果のサマリテーブルは、元のテーブルに代わって、ある種の問い合わせに対して使用でき、しばしば実行時間を大幅に縮小します。 通常この手法は、計測または観測データ（ファクトテーブルと言います）が非常に大きくなるかもしれない、データウェアハウスに使用されます。 データウェアハウス内のファクトテーブルに対してサマリテーブルを維持するPL/pgSQLのトリガ関数の例を例 43.6に示します。
例43.6 サマリテーブルを維持するためのPL/pgSQLトリガ関数
ここに述べるスキーマの一部はRalph KimballによるThe Data Warehouse ToolkitのGrocery Storeの例に基づいています。
--
-- time dimensionとsales factの主テーブル
--
CREATE TABLE time_dimension (
time_key integer NOT NULL,
day_of_week integer NOT NULL,
day_of_month integer NOT NULL,
month integer NOT NULL,
quarter integer NOT NULL,
year integer NOT NULL
);
CREATE UNIQUE INDEX time_dimension_key ON time_dimension(time_key);
CREATE TABLE sales_fact (
time_key integer NOT NULL,
product_key integer NOT NULL,
store_key integer NOT NULL,
amount_sold numeric(12,2) NOT NULL,
units_sold integer NOT NULL,
amount_cost numeric(12,2) NOT NULL
);
CREATE INDEX sales_fact_time ON sales_fact(time_key);
--
-- sales by timeのサマリテーブル
--
CREATE TABLE sales_summary_bytime (
time_key integer NOT NULL,
amount_sold numeric(15,2) NOT NULL,
units_sold numeric(12) NOT NULL,
amount_cost numeric(15,2) NOT NULL
);
CREATE UNIQUE INDEX sales_summary_bytime_key ON sales_summary_bytime(time_key);
--
-- 更新、挿入および削除によりサマリテーブルの列を修正する関数とトリガ
--
CREATE OR REPLACE FUNCTION maint_sales_summary_bytime() RETURNS TRIGGER
AS $maint_sales_summary_bytime$
DECLARE
delta_time_key integer;
delta_amount_sold numeric(15,2);
delta_units_sold numeric(12);
delta_amount_cost numeric(15,2);
BEGIN
-- 増加または減少量を算出
IF (TG_OP = 'DELETE') THEN
delta_time_key = OLD.time_key;
delta_amount_sold = -1 * OLD.amount_sold;
delta_units_sold = -1 * OLD.units_sold;
delta_amount_cost = -1 * OLD.amount_cost;
ELSIF (TG_OP = 'UPDATE') THEN
-- time_keyを変更する更新を禁止します
-- （削除 + 挿入の方法により大部分の変更を行うため
-- それほど厄介ではありません）。
IF ( OLD.time_key != NEW.time_key) THEN
RAISE EXCEPTION 'Update of time_key : % -> % not allowed',
OLD.time_key, NEW.time_key;
END IF;
delta_time_key = OLD.time_key;
delta_amount_sold = NEW.amount_sold - OLD.amount_sold;
delta_units_sold = NEW.units_sold - OLD.units_sold;
delta_amount_cost = NEW.amount_cost - OLD.amount_cost;
ELSIF (TG_OP = 'INSERT') THEN
delta_time_key = NEW.time_key;
delta_amount_sold = NEW.amount_sold;
delta_units_sold = NEW.units_sold;
delta_amount_cost = NEW.amount_cost;
END IF;
-- サマリテーブルの行を挿入または新しい値で更新します。
<<insert_update>>
LOOP
UPDATE sales_summary_bytime
SET amount_sold = amount_sold + delta_amount_sold,
units_sold = units_sold + delta_units_sold,
amount_cost = amount_cost + delta_amount_cost
WHERE time_key = delta_time_key;
EXIT insert_update WHEN found;
BEGIN
INSERT INTO sales_summary_bytime (
time_key,
amount_sold,
units_sold,
amount_cost)
VALUES (
delta_time_key,
delta_amount_sold,
delta_units_sold,
delta_amount_cost
);
EXIT insert_update;
EXCEPTION
WHEN UNIQUE_VIOLATION THEN
-- 何もしません
END;
END LOOP insert_update;
RETURN NULL;
END;
$maint_sales_summary_bytime$ LANGUAGE plpgsql;
CREATE TRIGGER maint_sales_summary_bytime
AFTER INSERT OR UPDATE OR DELETE ON sales_fact
FOR EACH ROW EXECUTE FUNCTION maint_sales_summary_bytime();
INSERT INTO sales_fact VALUES(1,1,1,10,3,15);
INSERT INTO sales_fact VALUES(1,2,1,20,5,35);
INSERT INTO sales_fact VALUES(2,2,1,40,15,135);
INSERT INTO sales_fact VALUES(2,3,1,10,1,13);
SELECT * FROM sales_summary_bytime;
DELETE FROM sales_fact WHERE product_key = 1;
SELECT * FROM sales_summary_bytime;
UPDATE sales_fact SET units_sold = units_sold * 2;
SELECT * FROM sales_summary_bytime;
AFTERトリガは、トリガ文により変更された行の集合全体を調べるために遷移テーブルを使うこともできます。
CREATE TRIGGERコマンドで名前を1つまたは2つの遷移テーブルに割り当てると、関数はその名前を読み込み専用の一時テーブルであるかのように参照できます。
例 43.7に例を示します。
例43.7 遷移テーブルでの監査
この例は例 43.4と同じ結果になりますが、行毎に起動するトリガを使う代わりに、関係のある情報を遷移テーブルに集めた後に文毎に1回起動するトリガを使っています。
これは、呼び出された文が多くの行を変更する場合には行トリガの方法よりとても速くなる場合があります。
REFERENCING句はそれぞれの場合で異ならなければなりませんので、それぞれの種類のイベントに対して別々のトリガ宣言をしなければならないことに注意してください。
ですが、もし選ぶのなら、このために単一のトリガ関数が使えなくなることはありません。
(実際には、3つに別れた関数を使い、実行時のTG_OPの確認を避ける方が良いでしょう。)
CREATE TABLE emp (
empname text NOT NULL,
salary integer
);
CREATE TABLE emp_audit(
operation char(1) NOT NULL,
stamp timestamp NOT NULL,
userid text NOT NULL,
empname text NOT NULL,
salary integer
);
CREATE OR REPLACE FUNCTION process_emp_audit() RETURNS TRIGGER AS $emp_audit$
BEGIN
--
-- empで実行された操作を反映するためにemp_auditに行を作り、
-- 操作を完了するために特殊な変数TG_OPを使う。
--
IF (TG_OP = 'DELETE') THEN
INSERT INTO emp_audit
SELECT 'D', now(), user, o.* FROM old_table o;
ELSIF (TG_OP = 'UPDATE') THEN
INSERT INTO emp_audit
SELECT 'U', now(), user, n.* FROM new_table n;
ELSIF (TG_OP = 'INSERT') THEN
INSERT INTO emp_audit
SELECT 'I', now(), user, n.* FROM new_table n;
END IF;
RETURN NULL; -- これはAFTERトリガなので結果は無視される
END;
$emp_audit$ LANGUAGE plpgsql;
CREATE TRIGGER emp_audit_ins
AFTER INSERT ON emp
REFERENCING NEW TABLE AS new_table
FOR EACH STATEMENT EXECUTE FUNCTION process_emp_audit();
CREATE TRIGGER emp_audit_upd
AFTER UPDATE ON emp
REFERENCING OLD TABLE AS old_table NEW TABLE AS new_table
FOR EACH STATEMENT EXECUTE FUNCTION process_emp_audit();
CREATE TRIGGER emp_audit_del
AFTER DELETE ON emp
REFERENCING OLD TABLE AS old_table
FOR EACH STATEMENT EXECUTE FUNCTION process_emp_audit();
PL/pgSQLはイベントトリガの定義に使用できます。
イベントトリガとして呼び出される関数は、引数のない関数として宣言され、戻り値の型はevent_triggerとなることがPostgreSQLでは必須です。
PL/pgSQL関数がイベントトリガとして呼び出された場合、数個の特別な変数が最高レベルのブロックで自動的に作成されます。 以下に示します。
TG_EVENT
textデータ型。
トリガが発行されたイベントを示す文字列。
TG_TAG
textデータ型。
トリガが発行されたコマンドタグを含む変数。
例 43.8はPL/pgSQLにおけるイベントトリガ関数の一例を示します。
例43.8 PL/pgSQLイベントトリガ関数
以下の例では、サポートされたコマンドが実行されたとき、トリガはNOTICEを発生させるだけです。
CREATE OR REPLACE FUNCTION snitch() RETURNS event_trigger AS $$
BEGIN
RAISE NOTICE 'snitch: % %', tg_event, tg_tag;
END;
$$ LANGUAGE plpgsql;
CREATE EVENT TRIGGER snitch ON ddl_command_start EXECUTE FUNCTION snitch();