PostgreSQL还提供了事件触发器。事件触发器是特定数据库的全局触发器,能够捕获DDL事件。
与常规触发器一样,事件触发器可以用任何包含事件触发器支持的过程语言编写,也可以用C语言编写,但不能用纯SQL编写。
1. Overview of Event Trigger Behavior
每当与事件相关联的事件发生在定义它的数据库中时,事件触发器就会激发。目前,支持的事件只有是 ddl_command_start、ddl_command_end、table_rewrite和sql_drop。对其他事件的支持可以在将来的版本中添加。
DDL命令启动事件发生在执行create、alter、drop、security label、comment、grant或revoke命令之前。在触发事件触发器之前,不检查受影响的对象是否存在。但是,作为例外,针对共享对象(数据库、角色和表空间)的DDL命令或针对事件触发器本身的命令不会发生此事件。事件触发器机制不支持这些对象类型。ddl_command_start 也发生在执行select into命令之前,因为这相当于CREATE TABLE AS。
DDL命令结束事件发生在同一组命令执行之后。要获得有关发生的DDL操作的更多详细信息,请使用DDL命令结束事件触发器代码中的set返回函数pg_event_trigger_ddl_commands()。注意,触发器在操作发生后(但在事务提交之前)触发,因此系统目录可以读取为已更改。
对于删除数据库对象的任何操作,SQL删除事件发生在DDL命令结束事件触发器之前。要列出已删除的对象,请使用SQL删除事件触发器代码中的set返回函数pg_event_trigger_dropped_objects()。注意,触发器是在对象从系统目录中删除后执行的,因此无法再查找它们。
表重写事件发生在通过命令alter table和alter type的某些操作重写表之前。虽然其他控制语句可用于重写表,如cluster和vacuum,但表重写事件不会被它们触发。
无法在中止的事务中执行事件触发器(与其他函数一样)。因此,如果DDL命令因错误而失败,则不会执行任何相关的 ddl_command_end 触发器。相反,如果ddl_command_start触发器因错误而失败,则不会再触发事件触发器,也不会尝试执行命令本身。类似地,如果ddl_command_end 触发器因错误而失败,DDL语句的效果将回滚,就像在包含事务中止的任何其他情况下一样。
事件触发器是使用命令create event trigger创建的。要创建事件触发器,必须首先创建具有特殊返回类型事件触发器的函数。此函数不需要(也可能不需要)返回值;返回类型仅充当将作为事件触发器调用函数的信号。
如果为特定事件定义了多个事件触发器,则它们将按触发器名称的字母顺序触发。
触发器定义还可以指定when条件,例如,只能为用户希望截获的特定命令触发DDL命令启动触发器。这种触发器的常见用途是限制用户可以执行的DDL操作的范围。
2. Event Trigger Firing Matrix
lists all commands for which event triggers are supported.
Command Tag | ddl_command_start | ddl_command_end | sql_drop | table_rewrite | Notes |
ALTER AGGREGATE | X | X | - | - |
|
ALTER COLLATION | X | X | - | - |
|
ALTER CONVERSION | X | X | - | - |
|
ALTER DOMAIN | X | X | - | - |
|
ALTER EXTENSION | X | X | - | - |
|
ALTER FOREIGN DATA WRAPPER | X | X | - | - |
|
ALTER FOREIGN TABLE | X | X | X | - |
|
ALTER FUNCTION | X | X | - | - |
|
ALTER LANGUAGE | X | X | - | - |
|
ALTER OPERATOR | X | X | - | - |
|
ALTER OPERATOR CLASS | X | X | - | - |
|
ALTER OPERATOR FAMILY | X | X | - | - |
|
ALTER POLICY | X | X | - | - |
|
ALTER SCHEMA | X | X | - | - |
|
ALTER SEQUENCE | X | X | - | - |
|
ALTER SERVER | X | X | - | - |
|
ALTER TABLE | X | X | X | X |
|
ALTER TEXT SEARCH CONFIGURATION | X | X | - | - |
|
ALTER TEXT SEARCH DICTIONARY | X | X | - | - |
|
ALTER TEXT SEARCH PARSER | X | X | - | - |
|
ALTER TEXT SEARCH TEMPLATE | X | X | - | - |
|
ALTER TRIGGER | X | X | - | - |
|
ALTER TYPE | X | X | - | X |
|
ALTER USER MAPPING | X | X | - | - |
|
ALTER VIEW | X | X | - | - |
|
CREATE AGGREGATE | X | X | - | - |
|
COMMENT | X | X | - | - | Only for local objects |
CREATE CAST | X | X | - | - |
|
CREATE COLLATION | X | X | - | - |
|
CREATE CONVERSION | X | X | - | - |
|
CREATE DOMAIN | X | X | - | - |
|
CREATE EXTENSION | X | X | - | - |
|
CREATE FOREIGN DATA WRAPPER | X | X | - | - |
|
CREATE FOREIGN TABLE | X | X | - | - |
|
CREATE FUNCTION | X | X | - | - |
|
CREATE INDEX | X | X | - | - |
|
CREATE LANGUAGE | X | X | - | - |
|
CREATE OPERATOR | X | X | - | - |
|
CREATE OPERATOR CLASS | X | X | - | - |
|
CREATE OPERATOR FAMILY | X | X | - | - |
|
CREATE POLICY | X | X | - | - |
|
CREATE RULE | X | X | - | - |
|
CREATE SCHEMA | X | X | - | - |
|
CREATE SEQUENCE | X | X | - | - |
|
CREATE SERVER | X | X | - | - |
|
CREATE STATISTICS | X | X | - | - |
|
CREATE TABLE | X | X | - | - |
|
CREATE TABLE AS | X | X | - | - |
|
CREATE TEXT SEARCH CONFIGURATION | X | X | - | - |
|
CREATE TEXT SEARCH DICTIONARY | X | X | - | - |
|
CREATE TEXT SEARCH PARSER | X | X | - | - |
|
CREATE TEXT SEARCH TEMPLATE | X | X | - | - |
|
CREATE TRIGGER | X | X | - | - |
|
CREATE TYPE | X | X | - | - |
|
CREATE USER MAPPING | X | X | - | - |
|
CREATE VIEW | X | X | - | - |
|
DROP AGGREGATE | X | X | X | - |
|
DROP CAST | X | X | X | - |
|
DROP COLLATION | X | X | X | - |
|
DROP CONVERSION | X | X | X | - |
|
DROP DOMAIN | X | X | X | - |
|
DROP EXTENSION | X | X | X | - |
|
DROP FOREIGN DATA WRAPPER | X | X | X | - |
|
DROP FOREIGN TABLE | X | X | X | - |
|
DROP FUNCTION | X | X | X | - |
|
DROP INDEX | X | X | X | - |
|
DROP LANGUAGE | X | X | X | - |
|
DROP OPERATOR | X | X | X | - |
|
DROP OPERATOR CLASS | X | X | X | - |
|
DROP OPERATOR FAMILY | X | X | X | - |
|
DROP OWNED | X | X | X | - |
|
DROP POLICY | X | X | X | - |
|
DROP RULE | X | X | X | - |
|
DROP SCHEMA | X | X | X | - |
|
DROP SEQUENCE | X | X | X | - |
|
DROP SERVER | X | X | X | - |
|
DROP STATISTICS | X | X | X | - |
|
DROP TABLE | X | X | X | - |
|
DROP TEXT SEARCH CONFIGURATION | X | X | X | - |
|
DROP TEXT SEARCH DICTIONARY | X | X | X | - |
|
DROP TEXT SEARCH PARSER | X | X | X | - |
|
DROP TEXT SEARCH TEMPLATE | X | X | X | - |
|
DROP TRIGGER | X | X | X | - |
|
DROP TYPE | X | X | X | - |
|
DROP USER MAPPING | X | X | X | - |
|
DROP VIEW | X | X | X | - |
|
GRANT | X | X | - | - | Only for local objects |
IMPORT FOREIGN SCHEMA | X | X | - | - |
|
REVOKE | X | X | - | - | Only for local objects |
SECURITY LABEL | X | X | - | - | Only for local objects |
SELECT INTO | X | X | - | - |
|
3. event trigger functions
pg_event_trigger_ddl_commands returns a list of DDL commands executed by each user action, when invoked in a function attached to a ddl_command_end event trigger. If called in any other context, an error is raised. pg_event_trigger_ddl_commands returns one row for each base command executed; some commands that are a single SQL sentence may return more than one row. This function returns the following columns:
Name | Type | Description |
classid | oid | OID of catalog the object belongs in |
objid | oid | OID of the object itself |
objsubid | integer | Sub-object ID (e.g. attribute number for a column) |
command_tag | text | Command tag |
object_type | text | Type of the object |
schema_name | text | Name of the schema the object belongs in, if any; otherwise NULL. No quoting is applied. |
object_identity | text | Text rendering of the object identity, schema-qualified. Each identifier included in the identity is quoted if necessary. |
in_extension | bool | True if the command is part of an extension script |
command | pg_ddl_command | A complete representation of the command, in internal format. This cannot be output directly, but it can be passed to other functions to obtain different pieces of information about the command. |
pg_event_trigger_dropped_objects returns a list of all objects dropped by the command in whose sql_drop event it is called. If called in any other context, pg_event_trigger_dropped_objects raises an error. pg_event_trigger_dropped_objects returns the following columns:
Name | Type | Description |
classid | oid | OID of catalog the object belonged in |
objid | oid | OID of the object itself |
objsubid | integer | Sub-object ID (e.g. attribute number for a column) |
original | bool | True if this was one of the root object(s) of the deletion |
normal | bool | True if there was a normal dependency relationship in the dependency graph leading to this object |
is_temporary | bool | True if this was a temporary object |
object_type | text | Type of the object |
schema_name | text | Name of the schema the object belonged in, if any; otherwise NULL. No quoting is applied. |
object_name | text | Name of the object, if the combination of schema and name can be used as a unique identifier for the object; otherwise NULL. No quoting is applied, and name is never schema-qualified. |
object_identity | text | Text rendering of the object identity, schema-qualified. Each identifier included in the identity is quoted if necessary. |
address_names | text[] | An array that, together with object_type and address_args, can be used by the pg_get_object_address() function to recreate the object address in a remote server containing an identically named object of the same kind |
address_args | text[] | Complement for address_names |
Table Rewrite information
Name | Return Type | Description |
pg_event_trigger_table_rewrite_oid() | Oid | The OID of the table about to be rewritten. |
pg_event_trigger_table_rewrite_reason() | int | The reason code(s) explaining the reason for rewriting. The exact meaning of the codes is release dependent. |