PL/pgSQLはデータ変更やデータベースのイベントによるトリガプロシージャの定義に使用できます。
トリガプロシージャは、CREATE FUNCTION
コマンドを使って、(データ変更トリガには)trigger
、(データベースイベントトリガには)event_trigger
という戻り値の型を持った引数のない関数として作成されます。
その呼出しのトリガの原因となった条件を記述するため、PG_
という名前の特別な局所変数が自動的に定義されます。
something
データ変更トリガはtrigger
という戻り値の型を持った引数のない関数として宣言されます。
その関数は、たとえ、CREATE TRIGGER
にて引数を取るものとしていたとしても、引数を持たないものと宣言しなければならないことに注意してください。
トリガの引数は、後述する通り、TG_ARGV
経由で渡されます。
PL/pgSQL関数がトリガとして呼び出された場合、いくつかの特殊な変数が自動的に最上位レベルのブロックで作成されます。 それらを以下に示します。
NEW
RECORD
データ型。
この変数は行レベルのトリガでのINSERT
/UPDATE
操作によって更新された、新しいデータベースの行を保持します。
この変数は、文レベルのトリガおよびDELETE
操作では、割り当てられていません。
OLD
RECORD
データ型。
この変数は、行レベルのトリガでのUPDATE
/DELETE
操作によって更新される前のデータベースの行を保持します。
この変数は、文レベルのトリガおよびINSERT
操作では、割り当てられていません。
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としても構いません。
しかし、これらの種類のトリガでも、エラーを発生させることで操作全体を中断させることが可能です。
例41.3「PL/pgSQLトリガプロシージャ」にPL/pgSQLのトリガプロシージャの例を示します。
例41.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 PROCEDURE emp_stamp();
テーブルにおける変更のログを取る他の方法は、挿入、更新または削除の各々に対する行を保有する新テーブルを作成することです。 この方法はテーブルにおける変更の監査と考えることができます。 例41.4「PL/pgSQLによる監査用のトリガプロシージャ」はPL/pgSQLによる監査用トリガプロシージャの一例を示します。
例41.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.*; RETURN OLD; ELSIF (TG_OP = 'UPDATE') THEN INSERT INTO emp_audit SELECT 'U', now(), user, NEW.*; RETURN NEW; ELSIF (TG_OP = 'INSERT') THEN INSERT INTO emp_audit SELECT 'I', now(), user, NEW.*; RETURN 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 PROCEDURE process_emp_audit();
前例の変形では、各エントリが最終修正された時を表示するため、主テーブルを監査テーブルに結合したビューを使用します。 この方法でもテーブルの変化の監査証跡を全て記録できますが、監査証跡から抽出した各エントリの最終修正のタイムスタンプ表示することにより、監査証跡の簡単なビューを表示することにもなります。 例41.5「監査のためのPL/pgSQLビュートリガプロシージャ」で示すものは、PL/pgSQLを用いたビューの監査トリガの例です。
例41.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 PROCEDURE update_emp_view();
トリガの使用目的の1つは、あるテーブルのサマリテーブルを維持することです。 結果のサマリテーブルは、元のテーブルに代わって、ある種の問い合わせに対して使用でき、しばしば実行時間を大幅に縮小します。 通常この手法は、計測または観測データ(ファクトテーブルと言います)が非常に大きくなるかもしれない、データウェアハウスに使用されます。 データウェアハウス内のファクトテーブルに対してサマリテーブルを維持するPL/pgSQLのトリガプロシージャの例を例41.6「サマリテーブルを維持するためのPL/pgSQLトリガプロシージャ」に示します。
例41.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 PROCEDURE 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;
PL/pgSQLはイベントトリガの定義に使用できます。
イベントトリガとして呼び出されるプロシージャは、引数のない関数として宣言され、戻り値の型はevent_trigger
となることがPostgreSQLでは必須です。
PL/pgSQL関数がイベントトリガとして呼び出された場合、数個の特別な変数が最高レベルのブロックで自動的に作成されます。 以下に示します。
TG_EVENT
text
データ型。
トリガが発行されたイベントを示す文字列。
TG_TAG
text
データ型。
トリガが発行されたコマンドタグを含む変数。
例41.7「PL/pgSQLイベントトリガプロシージャ」はPL/pgSQLにおけるイベントトリガプロシージャの一例を示します。
例41.7 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 PROCEDURE snitch();