CODEWITHSUNDEEP
postgresqltriggers
abhi
abhi

Reputation: 2148

run a `TRIGGER` in postgres on `ALTER EVENT`

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

Upvotes: 1

Views: 879

Answers (1)

Jeremy
Jeremy

Reputation: 6723

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.

Upvotes: 5

Related Questions