30

I know I can list the triggers with \dft. But how can I see one concrete trigger? I want to know details like on which events the trigger is executed, which function is executed and so on.

4 Answers 4

42

OK, I found out about it myself.

The command \dft doesn't show the triggers itself (as I thought), it shows all trigger-functions (return-type trigger).

To see the trigger you can make \dS <tablename>, it shows not only columns of this table, but also all triggers defined on this table.

To show the source of the trigger-function (or any function) use \df+ <functionname>.

1
  • 2
    Use \ef <function_name> for more convenient reads.
    – Brain90
    Jun 23, 2016 at 3:59
11

If you don't have access to psql commands, you can still use :

select pg_get_functiondef('functionname'::regproc);
1
  • Sometimes: select pg_get_functiondef ('schema.functionname'::regproc); (means, schema name prefix may be needed)
    – tanius
    Feb 15, 2023 at 16:49
4

You could try the following:

SELECT event_object_table,trigger_name,event_manipulation,action_statement,action_timing FROM information_schema.triggers ORDER BY event_object_table,event_manipulation

or you can show triggers of a table named 'testtable' like this:

SELECT event_object_table,trigger_name,event_manipulation,action_statement,action_timing FROM information_schema.triggers WHERE event_object_table='testtable' ORDER BY event_object_table,event_manipulation
0

For example, you create my_t trigger which runs my_func() after UPDATE operation happens on person table for each row as shown below. *My post explains how to create a trigger:

CREATE TRIGGER my_t AFTER UPDATE ON person
FOR EACH ROW EXECUTE FUNCTION my_func();

Now first, you need to get the OID(Object identifier) from pg_trigger as shown below:

postgres=# SELECT oid FROM pg_trigger WHERE tgname = 'my_t';
  oid
-------
 16429
(1 row)

Then, you can get the code of my_t trigger with pg_get_triggerdef() and the OID as shown below. *My post explains an OID with a function:

postgres=# SELECT pg_get_triggerdef(16429);
                                     pg_get_triggerdef
-------------------------------------------------------------------------------------------
 CREATE TRIGGER my_t AFTER UPDATE ON public.person FOR EACH ROW EXECUTE FUNCTION my_func()
(1 row)

Or, you can get the OID from pg_trigger and get the code of my_t trigger with pg_get_triggerdef() and the OID at once as shown below:

postgres=# SELECT pg_get_triggerdef((SELECT oid FROM pg_trigger WHERE tgname = 'my_t'));
                                     pg_get_triggerdef
-------------------------------------------------------------------------------------------
 CREATE TRIGGER my_t AFTER UPDATE ON public.person FOR EACH ROW EXECUTE FUNCTION my_func()
(1 row)

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .