他のバージョンの文書 16 | 15 | 14 | 13 | 12 | 11 | 10 | 9.6 | 9.5 | 9.4 | 9.3 | 9.2 | 9.1 | 9.0 | 8.4 | 8.3 | 8.2 | 8.1 | 8.0 | 7.4 | 7.3 | 7.2

41.9. トリガプロシージャ

PL/pgSQLはデータ変更やデータベースのイベントによるトリガプロシージャの定義に使用できます。 トリガプロシージャは、CREATE FUNCTIONコマンドを使って、(データ変更トリガには)trigger、(データベースイベントトリガには)event_triggerという戻り値の型を持った引数のない関数として作成されます。 その呼出しのトリガの原因となった条件を記述するため、PG_somethingという名前の特別な局所変数が自動的に定義されます。

41.9.1. データ変更によるトリガ

データ変更トリガtriggerという戻り値の型を持った引数のない関数として宣言されます。 その関数は、たとえ、CREATE TRIGGERにて引数を取るものとしていたとしても、引数を持たないものと宣言しなければならないことに注意してください。 トリガの引数は、後述する通り、TG_ARGV経由で渡されます。

PL/pgSQL関数がトリガとして呼び出された場合、いくつかの特殊な変数が自動的に最上位レベルのブロックで作成されます。 それらを以下に示します。

NEW

RECORDデータ型。 この変数は行レベルのトリガでのINSERT/UPDATE操作によって更新された、新しいデータベースの行を保持します。 この変数は、文レベルのトリガおよびDELETE操作では、割り当てられていません。

OLD

RECORDデータ型。 この変数は、行レベルのトリガでのUPDATE/DELETE操作によって更新される前のデータベースの行を保持します。 この変数は、文レベルのトリガおよびINSERT操作では、割り当てられていません。

TG_NAME

nameデータ型。 実際に発行されたトリガの名前を持つ変数。

TG_WHEN

textデータ型。 トリガの定義に依存したBEFOREAFTER、またはINSTEAD OFという文字列。

TG_LEVEL

textデータ型。 トリガの定義に依存したROW または STATEMENTという文字列。

TG_OP

textデータ型。 トリガを起動した操作を示す、INSERTUPDATEDELETE、または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 ToolkitGrocery 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;

41.9.2. イベントによるトリガ

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();