postgres event trigger - ghdrako/doc_snipets GitHub Wiki

Rules and Trigers fire on DML statements. Event triggers fire on** DDL statements**.

Once fired, an event trigger receives an event and a command tag, both of which are useful for in- trospection and providing information about what fired the trigger. In particular, the command tag contains a description of the command (for example, CREATE or ALTER), while the event contains the category that fired the trigger – in particular, the following:

  • ddl_command_start and ddl_command_end indicate, respectively, the beginning and the completion of the DDL command.
  • sql_drop indicates that a DROP command is near completion.
  • table_rewrite indicates that a full table rewrite is about to begin.
CREATE EVENT TRIGGER name
ON event
[ WHEN filter_variable IN (filter_value [, ... ]) [ AND ... ] ]
EXECUTE { FUNCTION | PROCEDURE } function_name()

Event triggers must be created by the database administrator and have a database scope, meaning they live and act in the database they have been defined in.

Special functions to help developers perform introspection within an event trigger to understand the exact event that fired the trigger

  • pg_event_trigger_commands(), which returns a tuple for every command that was executed during the DDL statement.
  • pg_event_trigger_dropped_objects(), which reports a tuple for every dropped object within the same DDL statement.

Example

CREATE OR REPLACE FUNCTION
f_avoid_alter_table()
RETURNS EVENT_TRIGGER
AS
$code$
DECLARE
event_tuple record;
BEGIN
FOR event_tuple IN SELECT * FROM
pg_event_trigger_ddl_
commands() LOOP
IF event_tuple.command_tag = 'ALTER TABLE' AND event_tuple.object_
type = 'table' THEN
RAISE EXCEPTION 'Cannot execute an ALTER TABLE!';
END IF;
END LOOP;
END
$code$
LANGUAGE plpgsql;

CREATE EVENT TRIGGER tr_avoid_alter_table ON ddl_command_end
EXECUTE FUNCTION forum.f_avoid_alter_table();

ALTER TABLE tags ADD COLUMN thumbs_up int DEFAULT 0;
ERROR: Cannot execute an ALTER TABLE!