Meanings of bits in trigger type field (tgtype) of Postgres pg_trigger

tgtype is a (per documentation):

Bit mask identifying trigger firing conditions

But individual bits are not identified there.
The source code says:

41  int16  tgtype;  /* BEFORE/AFTER/INSTEAD, UPDATE/DELETE/INSERT,
42                                   * ROW/STATEMENT; see below */

...

93  /* Bits within tgtype */
94  #define TRIGGER_TYPE_ROW         (1 << 0)
95  #define TRIGGER_TYPE_BEFORE      (1 << 1)
96  #define TRIGGER_TYPE_INSERT      (1 << 2)
97  #define TRIGGER_TYPE_DELETE      (1 << 3)
98  #define TRIGGER_TYPE_UPDATE      (1 << 4)
99  #define TRIGGER_TYPE_TRUNCATE    (1 << 5)
100 #define TRIGGER_TYPE_INSTEAD     (1 << 6)
101
102 #define TRIGGER_TYPE_LEVEL_MASK  (TRIGGER_TYPE_ROW)
103 #define TRIGGER_TYPE_STATEMENT   0

23 translates to 00010111 in the least significant 8 bits, which should stand for:

CREATE TRIGGER trigger_name
BEFORE INSERT OR UPDATE ON table_name
FOR EACH ROW
EXECUTE PROCEDURE func_name();

A couple of quick tests in existing databases of mine seem to confirm it.

Thanks everyone, these answers saved me some time. Regarding the last answer, it is not overkill, if you have TRUNCATE triggers. I just noticed the other day that such triggers are not included in information_schema.triggers. As I either remember, or speculate..., this is a conscious choice. TRUNCATE is non-standard, and I think the core Postgres folks are strict about not making information_schema non-standard at all.

I did a tiny revision to the posted code for myself, posted here for anyone else who wants such a thing.

SELECT 
  c.relnamespace::regnamespace::text as schema_name, 
  tgrelid::regclass                  as table_name,
  t.tgname                           as trigger_name, 
  proname                            as function_name,
  tgtype::int::bit(7)                as condition_bits,
  CASE WHEN (tgtype::int::bit(7) & b'0000001')::int = 0 THEN 'STATEMENT' ELSE 'EACH ROW' END
                                     as trigger_scope,
  COALESCE(
    CASE WHEN (tgtype::int::bit(7) & b'0000010')::int = 0 THEN NULL ELSE 'BEFORE' END,
    CASE WHEN (tgtype::int::bit(7) & b'0000010')::int = 0 THEN 'AFTER' ELSE NULL END,
    CASE WHEN (tgtype::int::bit(7) & b'1000000')::int = 0 THEN NULL ELSE 'INSTEAD' END,
    ''
  )::text                           as trigger_timing, 
    (CASE WHEN (tgtype::int::bit(7) & b'0000100')::int = 0 THEN '' ELSE ' INSERT' END) ||
    (CASE WHEN (tgtype::int::bit(7) & b'0001000')::int = 0 THEN '' ELSE ' DELETE' END) ||
    (CASE WHEN (tgtype::int::bit(7) & b'0010000')::int = 0 THEN '' ELSE ' UPDATE' END) ||
    (CASE WHEN (tgtype::int::bit(7) & b'0100000')::int = 0 THEN '' ELSE ' TRUNCATE' END)
                                     as trigger_action,
pg_get_triggerdef(t.oid)             as trigger_definition
  
FROM pg_trigger t
JOIN pg_proc p      ON t.tgfoid = p.oid
JOIN pg_class c     ON c.oid    = t.tgrelid

Thanks rlib and Erwin. This was exactly what I was looking for.

I put together a query for this. I'll leave it here if anyone else needs it.

SELECT 
  n.nspname as schema_name, 
  c.relname as table_name, 
  t.tgname as trigger_name, 
  proname as function_name,
  tgtype::int::bit(7) as condition_bits,
  CASE WHEN (tgtype::int::bit(7) & b'0000001')::int = 0 THEN 'STATEMENT' ELSE 'EACH ROW' END as cond_row,
  COALESCE(
    CASE WHEN (tgtype::int::bit(7) & b'0000010')::int = 0 THEN NULL ELSE 'BEFORE' END,
    CASE WHEN (tgtype::int::bit(7) & b'0000010')::int = 0 THEN 'AFTER' ELSE NULL END,
    CASE WHEN (tgtype::int::bit(7) & b'1000000')::int = 0 THEN NULL ELSE 'INSTEAD' END,
    ''
  )::text as cond_timing, 
    (CASE WHEN (tgtype::int::bit(7) & b'0000100')::int = 0 THEN '' ELSE ' INSERT' END) ||
    (CASE WHEN (tgtype::int::bit(7) & b'0001000')::int = 0 THEN '' ELSE ' DELETE' END) ||
    (CASE WHEN (tgtype::int::bit(7) & b'0010000')::int = 0 THEN '' ELSE ' UPDATE' END) ||
    (CASE WHEN (tgtype::int::bit(7) & b'0100000')::int = 0 THEN '' ELSE ' TRUNCATE' END)
  as cond_event
FROM pg_trigger t
JOIN pg_proc p ON t.tgfoid = p.oid
JOIN pg_class c ON c.oid = t.tgrelid
JOIN pg_namespace n ON n.oid = c.relnamespace;

Edit:

Hmm, that was overkill. It turns out that the above bit-work is already done in the triggers view in the information_schema schema.

Easier: SELECT * FROM information_schema.triggers