pg_event_trigger_ddl_commands() with DROP TABLE command

Question

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.

Kouber Saparev · Accepted Answer · 2025-01-14 14:00:20Z

4

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();

edited Jan 14 at 14:00

Kouber Saparev

8,2652 gold badges32 silver badges28 bronze badges

answered Aug 13, 2021 at 12:43

TomasOAK

563 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Andrey Over a year ago

but if I need drop table and drop function how to specify it in the trigger WHEN TAG IN ('DROP TABLE')

user2995801 Over a year ago

@Andrey www.postgresql.org/docs/9.3/event-trigger-matrix.html

Collectives™ on Stack Overflow

pg_event_trigger_ddl_commands() with DROP TABLE command

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related