Are DDL events captured by event triggers aware of changes in schema search path?
Event triggers are global to a particular database and are capable of capturing DDL events occured in the database. Redrock Postgres provides a function pg_ddl_command_deparse to deparse a command from internal format of type
pg_ddl_command, and returns a SQL statement in textual representation of type
In the example below, we create an event trigger that captures DDL events that occur in the database at the end of a command.
CREATE OR REPLACE FUNCTION ddl_trigger_func() RETURNS event_trigger AS $$ DECLARE ddl record; BEGIN FOR ddl IN SELECT * FROM pg_event_trigger_ddl_commands() LOOP RAISE NOTICE 'ddl command: %', pg_ddl_command_deparse(ddl.command); END LOOP; END $$ LANGUAGE plpgsql; CREATE EVENT TRIGGER ddl_event_trigger ON ddl_command_end EXECUTE PROCEDURE ddl_trigger_func();
Let’s create a schema
myschema, change schema search path to create and delete a table
CREATE SCHEMA myschema; SET search_path TO myschema; CREATE TABLE t_table (id integer, name text); DROP TABLE t_table;
In the example above, we see that the DDL events captured by the event trigger contain the schema information of the object when the table
t_table is created and dropped.