I would like to know which table stores the create statement of a trigger eg
CREATE TRIGGER t_update_sal
AFTER INSERT OR UPDATE
ON employees
FOR EACH ROW
EXECUTE PROCEDURE t_update_sal_v1();
Add a comment
|
3 Answers
You can dig that information on information_schema.triggers.
Comments
information_schema.triggers only stores part of the trigger definition.
You can get the complete SQL for the trigger using pg_get_triggerdef():
select trg.tgname,
pg_catalog.pg_get_triggerdef(trg.oid, true)
from pg_trigger trg
join pg_class tbl on trg.tgrelid = tbl.oid
join pg_namespace sch on tbl.relnamespace = sch.oid
where tbl.relname = 'employees'
and sch.nspname = 'public'
order by 1;
Comments
There is no table that stores the "create statement" for a trigger as such. The SQL is parsed, executed and discarded.
pg_dump re-constructs the statement from the system catalogs: primarily pg_catalog.pg_trigger, with additional information fetched from pg_class, pg_attribute, pg_namespace, etc.
As Soni points out, there's a user-friendly interface for this in the information_schema.triggers view.
Comments
