読者です 読者をやめる 読者になる 読者になる

set setting reset

インフラ関連の小ネタと備忘録

postgresql で trigger とその trigger が動作するテーブルの一覧を取得する

SELECT event_object_schema,
       event_object_table,
       trigger_schema,
       trigger_name
FROM information_schema.triggers

結果

 event_object_schema |     event_object_table      | trigger_schema |               trigger_name
---------------------+-----------------------------+----------------+-------------------------------------------
 schema              | user_table                  | schema         | user_trigger

* だと action_timingevent_manipulation も取得できます。information_schema 便利です。

\d information_schema.triggers
                     View "information_schema.triggers"
           Column           |                Type                | Modifiers
----------------------------+------------------------------------+-----------
 trigger_catalog            | information_schema.sql_identifier  |
 trigger_schema             | information_schema.sql_identifier  |
 trigger_name               | information_schema.sql_identifier  |
 event_manipulation         | information_schema.character_data  |
 event_object_catalog       | information_schema.sql_identifier  |
 event_object_schema        | information_schema.sql_identifier  |
 event_object_table         | information_schema.sql_identifier  |
 action_order               | information_schema.cardinal_number |
 action_condition           | information_schema.character_data  |
 action_statement           | information_schema.character_data  |
 action_orientation         | information_schema.character_data  |
 action_timing              | information_schema.character_data  |
 action_reference_old_table | information_schema.sql_identifier  |
 action_reference_new_table | information_schema.sql_identifier  |
 action_reference_old_row   | information_schema.sql_identifier  |
 action_reference_new_row   | information_schema.sql_identifier  |
 created                    | information_schema.time_stamp      |