查询触发器及其相关的触发器函数与表

• 直接看代码

  SELECT trg.tgname as tigger_name,
                CASE trg.tgtype::INTEGER & 66
                    WHEN 2 THEN 'BEFORE'
                    WHEN 64 THEN 'INSTEAD OF'
                    ELSE 'AFTER'
                END AS trigger_type,
               CASE trg.tgtype::INTEGER & cast(28 AS INT2)
                 WHEN 16 THEN 'UPDATE'
                 WHEN 8 THEN 'DELETE'
                 WHEN 4 THEN 'INSERT'
                 WHEN 20 THEN 'INSERT, UPDATE'
                 WHEN 28 THEN 'INSERT, UPDATE, DELETE'
                 WHEN 24 THEN 'UPDATE, DELETE'
                 WHEN 12 THEN 'INSERT, DELETE'
               END AS trigger_event,
               ns.nspname||'.'||tbl.relname AS trigger_table,
               obj_description(trg.oid) AS remarks,
                 CASE
                  WHEN trg.tgenabled='O' THEN 'ENABLED'
                    ELSE 'DISABLED'
                END AS status,
                CASE trg.tgtype::INTEGER & 1
                  WHEN 1 THEN 'ROW'::TEXT
                  ELSE 'STATEMENT'::TEXT
                END AS trigger_level,
                n.nspname || '.' || proc.proname AS function_name
        FROM pg_trigger trg
         JOIN pg_proc proc ON proc.oid = trg.tgfoid
         JOIN pg_catalog.pg_namespace n ON n.oid = proc.pronamespace
         JOIN pg_class tbl ON trg.tgrelid = tbl.oid
         JOIN pg_namespace ns ON ns.oid = tbl.relnamespace
        WHERE 
          trg.tgname not like 'RI_ConstraintTrigger%'
          AND trg.tgname not like 'pg_sync_pg%'

参考：https://dba.stackexchange.com/questions/121717/get-triggers-table-names-in-postgresql