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

Question

Where can I find documentation on bits of POSTGRES tgtype, ex.:

SELECT * FROM pg_trigger;
===>
 tgtype = 23; 

What does this mean?

Answer 1

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.

Answer 2

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