CODEWITHSUNDEEP
postgresqltriggers
Oto Shavadze
Oto Shavadze

Reputation: 42863

pg_event_trigger_ddl_commands() with DROP TABLE command

CREATE OR REPLACE FUNCTION ddl_command_test()
RETURNS event_trigger
AS $$
DECLARE
    obj record;
BEGIN
    FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands()
        LOOP
        RAISE NOTICE '% commnad on oid: %',
                 tg_tag,
                 obj.objid;

            RAISE NOTICE 'triggered';
        END LOOP;
END; $$ LANGUAGE plpgsql;


CREATE EVENT TRIGGER test_ddl ON ddl_command_end
   EXECUTE FUNCTION ddl_command_test();

While pg_event_trigger_ddl_commands() function returns info for table creation:

CREATE TABLE test_table(
    col int
);

It not prints notification message when table is dropped:

DROP TABLE test_table;

Don't get why, because event-trigger-matrix shows that ddl_​command_​end includes DROP TABLE command also.

Upvotes: 1

Views: 2653

Answers (1)

TomasOAK
TomasOAK

Reputation: 56

Although the documentation event-trigger-matrix says that ddl_command_end can be used for DROP statements, I also struggled with that issue in the past.

Thereby, I found this workaround, which involves creating a specific function that fetches FROM pg_event_trigger_dropped_objects(), to notify when the DROP statement is used.

CREATE OR REPLACE FUNCTION ddl_drop_command_test()
RETURNS event_trigger
AS $$
DECLARE
    obj record;
BEGIN
    FOR obj IN SELECT * FROM pg_event_trigger_dropped_objects()
        LOOP
        RAISE NOTICE '% command on oid: %',
                 tg_tag,
                 obj.objid;

            RAISE NOTICE 'triggered';
        END LOOP;
END; $$ LANGUAGE plpgsql;

Further, you need to use ON sql_drop to create your event trigger. The WHEN TAG can be incremented with DROP SCHEMA and other Postgres objects.

CREATE EVENT TRIGGER drop_test ON sql_drop 
WHEN TAG IN ('DROP TABLE')
EXECUTE PROCEDURE ddl_drop_command_test();

Upvotes: 4

Related Questions