2

How can I get a listing of all triggers for a database along w/ the table that it belongs to?

I assume that I need pg_trigger and pg_tables but I'm not quite sure how to join them together.

4

This works for me:

select trg.tgname,
        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
FROM pg_trigger trg
 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%'

It doesn't include DDL triggers though.

2

here is improved one, which gets trigger function with its namespace:

         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%';
1

Start up psql with -E. That will show you the commands it issues. Then run \dft to show the triggers.

The SQL it generates is,

SELECT n.nspname as "Schema",
  p.proname as "Name",
  pg_catalog.pg_get_function_result(p.oid) as "Result data type",
  pg_catalog.pg_get_function_arguments(p.oid) as "Argument data types",
 CASE
  WHEN p.proisagg THEN 'agg'
  WHEN p.proiswindow THEN 'window'
  WHEN p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype THEN 'trigger'
  ELSE 'normal'
 END as "Type"
FROM pg_catalog.pg_proc p
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE (
       p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype
      )
  AND pg_catalog.pg_function_is_visible(p.oid)
      AND n.nspname <> 'pg_catalog'
      AND n.nspname <> 'information_schema'
ORDER BY 1, 2, 4;

That will return a table like this,

 Schema |        Name        | Result data type | Argument data types |  Type   
--------+--------------------+------------------+---------------------+---------
 public | checkauthtrigger   | trigger          |                     | trigger
 public | do_stupid_stuff    | trigger          |                     | trigger
 public | postgis_cache_bbox | trigger          |                     | trigger
(3 rows)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.