Can an event trigger capture DDL events in functions?

Answer

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 text.

Example

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 function, in the function body, it inserts a record into the table t_table and changes the current value of the sequence t_table_id_seq.

CREATE TABLE t_table (id serial, name text);

CREATE OR REPLACE FUNCTION func_with_ddl(num integer)
  RETURNS void AS $$
BEGIN
  INSERT INTO t_table (id, name) VALUES (num + 1, 'dummy');
  EXECUTE format('ALTER SEQUENCE t_table_id_seq RESTART %s', num + 2);
END $$ LANGUAGE plpgsql;

SELECT func_with_ddl(coalesce(max(id), 0)) FROM t_table;

In the example above, we will find that even if there is a DDL command in the function body, the event trigger can capture the DDL event that occurs during the function execution.