I want to run a trigger when an ALTER event happens on my base table. I know we can do trigger on INSERT, UPDATE, DELETE, TRUNCATE but not sure if triggers in postgres support ALTER.
Any help would be appreciated
Add a comment
|
1 Answer
Yes, you are looking for event triggers.
To create an event trigger that would do something for a specific table, you could do something like this:
CREATE TABLE public.test (a int);
CREATE FUNCTION trg_alter_test()
RETURNS event_trigger LANGUAGE plpgsql AS $$
DECLARE
obj record;
BEGIN
FOR obj in SELECT * FROM pg_event_trigger_ddl_commands()
LOOP
-- Use the name of your table instead of public.test here
IF obj.objid = 'public.test'::regclass THEN
RAISE NOTICE '% altered table: %', tg_tag, obj.objid::regclass::text;
END IF;
END LOOP;
END
$$
;
CREATE EVENT TRIGGER test_trigger ON ddl_command_end WHEN TAG IN ('ALTER TABLE')
EXECUTE FUNCTION trg_alter_test();
If I try to alter public.test, I get this output:
NOTICE: ALTER TABLE altered table: test
ALTER TABLE
You could obviously change this to whatever you want.
6 Comments
abhi
got it, thanks, I want the event trigger to go only when a alter table is executed on a specific table, can you help me how to do that please ?
Jeremy
@abhi I added an example.
abhi
thanks, but my database will have lot fo ddl events through out the day.... pg_event_trigger_ddl_commands() will have all of those events? if yes, it might take time to loop through them
Jeremy
It would have to loop through any ALTER TABLE statements.
abhi
to be more specific, my alter statement would be
ALTER TABLE public.emp1 INHERIT public.emp....and the emp1 table name(basically a child table) keeps changing, I want the trigger to go when such alter statement is executed on public.emp table(parent/base table)..can I have a regex like obj.objid = 'public.emp*'::regclass ? @Jeremy |