PostgreSQLでトリガーに関する情報を取得するための各種操作についてのメモ
トリガーの一覧取得
トリガーの一覧は以下のSQLで取得できます
SELECT * FROM pg_trigger;
■実行例
SELECT * FROM pg_trigger; tgrelid | tgname | tgfoid | tgtype | tgenabled | tgisinternal | tgconstrrelid | tgconstrindid | tgconstraint | tgdeferrable | tginitdeferred | tgnargs | tgattr | tgargs | tgqual ---------+----------------+--------+--------+-----------+--------------+---------------+---------------+--------------+--------------+----------------+---------+--------+--------+-------- 20646 | sample_trigger | 159097 | 23 | O | f | 0 | 0 | 0 | f | f | 0 | | \x |
トリガーに紐づく関数名の取得
トリガーに紐づく関数の関数名(proname)は以下のSQLで取得できます
SELECT tgname, proname FROM pg_trigger t, pg_proc f where t.tgfoid = f.oid and tgname = '{トリガ名}';
■実行例
SELECT tgname, proname FROM pg_trigger t, pg_proc f where t.tgfoid = f.oid and tgname = 'sample_trigger'; tgname | proname ----------------+----------------- sample_trigger | user_id_padding
関数の中身の確認
関数の中身は以下のSQLで取得できます
SELECT prosrc FROM pg_proc WHERE proname = '{関数名}';
■実行例
SELECT prosrc FROM pg_proc WHERE proname = 'user_id_padding'; prosrc -------------------------------------------------------------------------- + BEGIN + IF NEW.user_id_padding IS NOT NULL THEN + IF length(NEW.user_id) < 5 and length(NEW.user_id) > 0 THEN + NEW.user_id := lpad(NEW.user_id, 5, '0'); + END IF; + END IF; + + RETURN NEW; + END; +