I came across the need to list all triggers in PostgreSQL, it returns painful and verbose results. Something like this.

It doesn’t show you the table the trigger belongs to and you would need to map through an oid.

ebarges=> select * from pg_trigger;
 tgrelid |                 tgname                  | tgfoid | tgtype | tgenabled | tgisconstraint | tgconstrname | tgconstrrelid | tgconstraint | tgdeferrable |
 tginitdeferred | tgnargs | tgattr | tgargs
---------+-----------------------------------------+--------+--------+-----------+----------------+--------------+---------------+--------------+--------------+
----------------+---------+--------+--------
    1262 | pg_sync_pg_database                     |   1689 |     28 | O         | f              |              |             0 |            0 | f            |
 f              |       0 |        |
    1260 | pg_sync_pg_authid                       |   1689 |     28 | O         | f              |              |             0 |            0 | f            |
 f              |       0 |        |
    1261 | pg_sync_pg_auth_members                 |   1689 |     28 | O         | f              |              |             0 |            0 | f            |
 f              |       0 |        |
   50288 | map_iso_uom_trigger                     |  50126 |     31 | O         | f              |              |             0 |            0 | f            |
 f              |       0 |        |
   50293 | map_location_trigger                    |  50127 |     31 | O         | f              |              |             0 |            0 | f            |
 f              |       0 |        |
   50298 | map_product_trigger                     |  50128 |     31 | O         | f              |              |             0 |            0 | f            |
 f              |       0 |        |
   50303 | map_storage_location_route_code_trigger |  50129 |     31 | O         | f              |              |             0 |            0 | f            |
 f              |       0 |        |
   50326 | master_route_code_trigger               |  50130 |     31 | O         | f              |              |             0 |            0 | f            |
 f              |       0 |        |
   50331 | master_storage_location_trigger         |  50131 |     31 | O         | f              |              |             0 |            0 | f            |
 f              |       0 |        |
   50358 | user_info_trigger                       |  50132 |     31 | O         | f              |              |             0 |            0 | f            |
 f              |       0 |        |
(10 rows)

In order to narrow down the search to show only columns i need, i issued the describe table command. This official reference shows the PostgreSQL pg_trigger table description otherwise.

ebarges=> \d pg_trigger
      Table "pg_catalog.pg_trigger"
     Column     |    Type    | Modifiers
----------------+------------+-----------
 tgrelid        | oid        | not null
 tgname         | name       | not null
 tgfoid         | oid        | not null
 tgtype         | smallint   | not null
 tgenabled      | "char"     | not null
 tgisconstraint | boolean    | not null
 tgconstrname   | name       | not null
 tgconstrrelid  | oid        | not null
 tgconstraint   | oid        | not null
 tgdeferrable   | boolean    | not null
 tginitdeferred | boolean    | not null
 tgnargs        | smallint   | not null
 tgattr         | int2vector | not null
 tgargs         | bytea      |
Indexes:
    "pg_trigger_oid_index" UNIQUE, btree (oid)
    "pg_trigger_tgrelid_tgname_index" UNIQUE, btree (tgrelid, tgname)
    "pg_trigger_tgconstrname_index" btree (tgconstrname)

I went on to show only the columns i needed, which are tgrelid and tgname.

ebarges=> select tgrelid, tgname from pg_trigger;
 tgrelid |                 tgname
---------+-----------------------------------------
    1262 | pg_sync_pg_database
    1260 | pg_sync_pg_authid
    1261 | pg_sync_pg_auth_members
   50288 | map_iso_uom_trigger
   50293 | map_location_trigger
   50298 | map_product_trigger
   50303 | map_storage_location_route_code_trigger
   50326 | master_route_code_trigger
   50331 | master_storage_location_trigger
   50358 | user_info_trigger
(10 rows)

tgrelid appears to be a table oid of the table the trigger belongs to, it works equally the same as the foreign key concept. Now to find out the mapping between the oid and its table, one of the ways is to use the oid2name utility program. oid2name reference documentation


From your shell, call oid2name the same way as you would for psql when logging in to the PostgreSQL server. You may be prompted for a password if it exists for your account.

Admin@Admin-THINK ~
$ oid2name -U ebarges_user -d ebarges

You will see these results similar to your schema.

Admin@Admin-THINK ~
$ oid2name -U ebarges_user -d ebarges
Password:
From database "ebarges":
  Filenode                             Table Name
-------------------------------------------------
     50288                            map_iso_uom
     50293                           map_location
     50298                            map_product
     50303        map_storage_location_route_code
     50326                      master_route_code
     50331                master_storage_location

From this point you should be able to map the oid from the trigger to the appropriate table. Cheers.

* Do note that oid may be (it most certainly will be) different from server to server, i.e. your PostgreSQL database in production server from your PostgreSQL database in staging server regardless of version.

Share