how to retrieve comment on triggers of postgresql?

Question

I create comment on trigger:

COMMENT ON TRIGGER my_trigger on my_table IS $$comment on trigger$$;

and then retrieve the comment like this:

select description 
from pg_description 
   join pg_trigger on pg_description.objoid = pg_trigger.tgfoid 
where tgname = 'my_trigger';

But the statement returned 0 row.

Here is the table and its trigger:

=# \d ps_dbupdates
Column |  Type   |                         Modifiers                         
--------+---------+-----------------------------------------------------------
 id     | integer | not null default nextval('ps_dbupdates_id_seq'::regclass)
 update | integer | not null
Indexes:
    "ps_dbupdates_pkey" PRIMARY KEY, btree (id)
Triggers:
    single_dbupdate_trigger BEFORE INSERT ON ps_dbupdates FOR EACH ROW EXECUTE PROCEDURE     single_dbupdate()

And my comment on statement:

=# COMMENT ON TRIGGER single_dbupdate_trigger ON ps_dbupdates IS $$ensure there is only one row in        table ps_dbupdates$$;

fetch the oid of trigger:

=# select tgrelid, tgname, tgfoid from pg_trigger where tgname='single_dbupdate_trigger';
 tgrelid |         tgname          | tgfoid 
---------+-------------------------+--------
   16689 | single_dbupdate_trigger |  16590
(1 row)

using obj_description(object_oid, catalog_name) to retrieve the comment:

=# select obj_description(16590,'pg_trigger');
 obj_description 
-----------------

(1 row)

and last thing, the PostgreSQL version:

 $ psql --version
 psql (PostgreSQL) 8.4.21

score 0 · Answer 1 · edited May 23 '17 at 11:50

0

per the documentation, there are prefab functions to get you the comments back.

http://www.postgresql.org/docs/9.3/static/functions-info.html#FUNCTIONS-INFO-COMMENT-TABLE

this info found thanks to an answer here: How to retrieve the comment of a PostgreSQL database?

edited May 23 '17 at 11:50

Community

1
1

answered Jul 22 '14 at 14:24

Andreas

4,937
2
25
35

thanks, I tried the function but still can not get the comment. I have edited the post. – Xinghua Guo Jul 23 '14 at 09:23

score 0 · Accepted Answer · answered Jul 24 '14 at 08:00

OK, now I found the wrong point. The SQL statement to retrieve comment on trigger should be:

select description 
from pg_description 
   join pg_trigger on pg_description.objoid = pg_trigger.oid 
where tgname = 'my_trigger';

The mistake I made is that I gave the wrong oid of trigger in join statement. oid column is not shown in "\d pg_trigger" or in postgresql document of pg_trigger. So it's a basic principle in postgresql that column 'oid' is not displayed.

select obj_description(oid, 'pg_trigger')

should also work as long as given the right oid;

how to retrieve comment on triggers of postgresql?

2 Answers2