An event trigger in PostgreSQL

Buy Me a Coffee☕

An event trigger:

• can run the PL/pgSQL event trigger function in the database. *A SQL event trigger function and event trigger procedure don't exist.

• is fired when CREATE, ALTER, DROP, SECURITY LABEL, COMMENT, GRANT, REVOKE or SELECT INTO happens in the database.

*The doc explains an event trigger in detail.
*My post explains a trigger.

For example, you create test table as shown below:

CREATE TABLE test (
  num INTEGER
);

Then, you insert the row whose num is 0 into test table as shown below:

INSERT INTO test (num) VALUES (0);

Now, you can create my_func() event trigger function with RETURNS event_trigger and LANGUAGE plpgsql which increments num by 1 as shown below:

CREATE FUNCTION my_func()
RETURNS event_trigger
LANGUAGE plpgsql
AS $$
BEGIN
  UPDATE test SET num = num + 1;
END;
$$;

Or:

CREATE FUNCTION my_func()
RETURNS event_trigger
LANGUAGE plpgsql
AS $$
BEGIN
  UPDATE test SET num = num + 1;
  RETURN;
END;
$$;

*Memos:

• An event trigger function:

  • must have no parameters otherwise there is the error.
  • must be called with an event trigger otherwise there is error.
  • cannot be created with LANGUAGE SQL otherwise there is the error. 　- cannot have NEW and OLD records otherwise there is error while a trigger function can have them.
  • must have event_trigger in RETURNS <type> clause to be used with an event trigger otherwise there is error.
  • can have RETURN statement to return nothing which is RETURN; but not to return a value otherwise there is the error.

• A event trigger procedure doesn't exist.

• The doc explains an event trigger function.

• My post explains how to create a PL/pgSQL function:

And now, you can create my_et event trigger with my_func() as shown below:

CREATE EVENT TRIGGER my_et ON ddl_command_start
EXECUTE FUNCTION my_func();

*Memos:

• The user(role) must be a superuser to create an event trigger otherwise there is error.

• There are 4 events ddl_command_start, ddl_command_end, sql_drop and table_rewrite. *The doc explains 4 events in detail:

  • ddl_command_start event occurs just before the execution of CREATE, ALTER, DROP, SECURITY LABEL, COMMENT, GRANT, REVOKE and SELECT INTO.
  • ddl_command_end event occurs just after the execution of CREATE, ALTER, DROP, SECURITY LABEL, COMMENT, GRANT or REVOKE and SELECT INTO.
  • sql_drop event occurs just before ddl_command_end event trigger for any operation that drops database objects.
  • table_rewrite event occurs just before a table is rewritten by ALTER TABLE and ALTER TYPE but not by CLUSTER and VACUUM.

• The doc explains how to create an event trigger.

Then, creating and dropping my_t table run my_et trigger as shown below:

postgres=# CREATE TABLE my_t ();
CREATE TABLE
postgres=# SELECT num FROM test;
 num
-----
   1
(1 row)

postgres=# INSERT INTO my_t DEFAULT VALUES;
INSERT 0 1
postgres=# SELECT num FROM test;
 num
-----
   2
(1 row)

postgres=# DROP TABLE my_t;
DROP TABLE
postgres=# SELECT num FROM test;
 num
-----
   3
(1 row)