An event trigger in PostgreSQL
Tags: postgres, eventtrigger, trigger, plpgsql
Published: 2023-12-26 09:27:24

[Buy Me a Coffee](ko-fi.com/superkai)☕

An [event trigger](https://www.postgresql.org/docs/current/event-triggers.html): 

- 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](https://www.postgresql.org/docs/current/event-triggers.html) explains an event trigger in detail.
*[My post](https://dev.to/hyperkai/a-trigger-in-postgresql-2p40) explains a [trigger](https://www.postgresql.org/docs/current/triggers.html).

For example, you create `test` table as shown below:

```sql
CREATE TABLE test (
  num INTEGER
);
```

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

```sql
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:

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

Or:

```sql
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](https://stackoverflow.com/questions/7726237/postgresql-trigger-function-with-parameters/77724267#77724267).
  - must be called with an event trigger otherwise there is error.
  - cannot be created with `LANGUAGE SQL` otherwise there is [the error](https://stackoverflow.com/questions/5565979/error-sql-functions-cannot-return-type-trigger-in-postgresql/77724247#77724247).
　- cannot have `NEW` and `OLD` records otherwise there is error while a [trigger function](https://www.postgresql.org/docs/current/plpgsql-trigger.html) 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](https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING-RETURN) to return nothing which is `RETURN;` but not to return a value otherwise there is [the error](https://stackoverflow.com/questions/17698503/plpgsql-error-return-cannot-have-a-parameter-in-function-returning-void#answer-77609986).

- A event trigger procedure doesn't exist.

- [The doc](https://www.postgresql.org/docs/current/plpgsql-trigger.html#PLPGSQL-EVENT-TRIGGER) explains an event trigger function.

- [My post](https://dev.to/hyperkai/a-plpgsql-function-in-postgresql-3j9f) explains how to create a [PL/pgSQL function](https://www.postgresql.org/docs/current/plpgsql.html):

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

```sql
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](https://www.postgresql.org/docs/current/event-trigger-definition.html) 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](https://www.postgresql.org/docs/current/sql-createeventtrigger.html) explains how to create an event trigger.

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

```sql
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)
```