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
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>
.
-
2
If you don't have access to psql commands, you can still use :
select pg_get_functiondef('functionname'::regproc);
-
Sometimes:
select pg_get_functiondef ('schema.functionname'::regproc);
(means, schema name prefix may be needed)– taniusFeb 15, 2023 at 16:49
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
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)