This section describes the low-level details of the interface to an event trigger function. This information is only needed when writing event trigger functions in C. If you are using a higher-level language then these details are handled for you. In most cases you should consider using a procedural language before writing your event triggers in C. The documentation of each procedural language explains how to write an event trigger in that language.
Event trigger functions must use the “version 1” function manager interface.
When a function is called by the event trigger manager, it is not passed any normal arguments, but it is passed a “context” pointer pointing to a EventTriggerData structure. C functions can check whether they were called from the event trigger manager or not by executing the macro:
which expands to:
If this returns true, then it is safe to cast fcinfo->context to type EventTriggerData * and make use of the pointed-to EventTriggerData structure. The function must not alter the EventTriggerData structure or any of the data it points to.
struct EventTriggerData is defined in commands/event_trigger.h:
where the members are defined as follows:type
Always T_EventTriggerData.event
Describes the event for which the function is called, one of "ddl_command_start", "ddl_command_end", "sql_drop", "table_rewrite". See Section 39.1 for the meaning of these events.parsetree
A pointer to the parse tree of the command. Check the PostgreSQL source code for details. The parse tree structure is subject to change without notice.tag
The command tag associated with the event for which the event trigger is run, for example "CREATE FUNCTION".
An event trigger function must return a NULL pointer (not an SQL null value, that is, do not set isNull true).
An event trigger fires whenever the event with which it is associated occurs in the database in which it is defined. Currently, the only supported events are ddl_command_start, ddl_command_end, table_rewrite and sql_drop. Support for additional events may be added in future releases.
The ddl_command_start event occurs just before the execution of a CREATE, ALTER, DROP, SECURITY LABEL, COMMENT, GRANT or REVOKE command. No check whether the affected object exists or doesn't exist is performed before the event trigger fires. As an exception, however, this event does not occur for DDL commands targeting shared objects — databases, roles, and tablespaces — or for commands targeting event triggers themselves. The event trigger mechanism does not support these object types. ddl_command_start also occurs just before the execution of a SELECT INTO command, since this is equivalent to CREATE TABLE AS.
The ddl_command_end event occurs just after the execution of this same set of commands. To obtain more details on the DDL operations that took place, use the set-returning function pg_event_trigger_ddl_commands() from the ddl_command_end event trigger code (see ). Note that the trigger fires after the actions have taken place (but before the transaction commits), and thus the system catalogs can be read as already changed.
The sql_drop event occurs just before the ddl_command_end event trigger for any operation that drops database objects. To list the objects that have been dropped, use the set-returning function pg_event_trigger_dropped_objects() from the sql_drop event trigger code (see ). Note that the trigger is executed after the objects have been deleted from the system catalogs, so it's not possible to look them up anymore.
The table_rewrite event occurs just before a table is rewritten by some actions of the commands ALTER TABLE and ALTER TYPE. While other control statements are available to rewrite a table, like CLUSTER and VACUUM, the table_rewrite event is not triggered by them.
Event triggers (like other functions) cannot be executed in an aborted transaction. Thus, if a DDL command fails with an error, any associated ddl_command_end triggers will not be executed. Conversely, if a ddl_command_start trigger fails with an error, no further event triggers will fire, and no attempt will be made to execute the command itself. Similarly, if a ddl_command_end trigger fails with an error, the effects of the DDL statement will be rolled back, just as they would be in any other case where the containing transaction aborts.
For a complete list of commands supported by the event trigger mechanism, see .
Event triggers are created using the command . In order to create an event trigger, you must first create a function with the special return type event_trigger. This function need not (and may not) return a value; the return type serves merely as a signal that the function is to be invoked as an event trigger.
If more than one event trigger is defined for a particular event, they will fire in alphabetical order by trigger name.
A trigger definition can also specify a WHEN condition so that, for example, a ddl_command_start trigger can be fired only for particular commands which the user wishes to intercept. A common use of such triggers is to restrict the range of DDL operations which users may perform.
為了補充中討論的觸發(Trigger)機制，PostgreSQL 還提供了事件觸發。與附加到單個資料表只能用於 DML 事件的一般觸發器不同，事件觸發器是特定資料庫的全域觸發器，能夠在 DDL 事件發生時觸發。
與一般觸發器一樣，事件觸發器可以用任何包含事件觸發器所支援的程序語言撰寫，也可以用 C 語言撰寫，但不能使用普通的 SQL。
Here is a very simple example of an event trigger function written in C. (Examples of triggers written in procedural languages can be found in the documentation of the procedural languages.)
The function noddl raises an exception each time it is called. The event trigger definition associated the function with the ddl_command_start event. The effect is that all DDL commands (with the exceptions mentioned in ) are prevented from running.
This is the source code of the trigger function:
Now you can test the operation of the trigger:
In this situation, in order to be able to run some DDL commands when you need to do so, you have to either drop the event trigger or disable it. It can be convenient to disable the trigger for only the duration of a transaction:
(Recall that DDL commands on event triggers themselves are not affected by event triggers.)
After you have compiled the source code (see ), declare the function and the triggers:
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 DEFAULT PRIVILEGES
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 LARGE OBJECT
X
X
-
-
ALTER MATERIALIZED VIEW
X
X
-
-
ALTER OPERATOR
X
X
-
-
ALTER OPERATOR CLASS
X
X
-
-
ALTER OPERATOR FAMILY
X
X
-
-
ALTER POLICY
X
X
-
-
ALTER PROCEDURE
X
X
-
-
ALTER PUBLICATION
X
X
-
-
ALTER SCHEMA
X
X
-
-
ALTER SEQUENCE
X
X
-
-
ALTER SERVER
X
X
-
-
ALTER STATISTICS
X
X
-
-
ALTER SUBSCRIPTION
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
-
-
COMMENT
X
X
-
-
Only for local objects
CREATE ACCESS METHOD
X
X
-
-
CREATE AGGREGATE
X
X
-
-
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 MATERIALIZED VIEW
X
X
-
-
CREATE OPERATOR
X
X
-
-
CREATE OPERATOR CLASS
X
X
-
-
CREATE OPERATOR FAMILY
X
X
-
-
CREATE POLICY
X
X
-
-
CREATE PROCEDURE
X
X
-
-
CREATE PUBLICATION
X
X
-
-
CREATE RULE
X
X
-
-
CREATE SCHEMA
X
X
-
-
CREATE SEQUENCE
X
X
-
-
CREATE SERVER
X
X
-
-
CREATE STATISTICS
X
X
-
-
CREATE SUBSCRIPTION
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 ACCESS METHOD
X
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 MATERIALIZED VIEW
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 PROCEDURE
X
X
X
-
DROP PUBLICATION
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 SUBSCRIPTION
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
-
-
REFRESH MATERIALIZED VIEW
X
X
-
-
REVOKE
X
X
-
-
Only for local objects
SECURITY LABEL
X
X
-
-
Only for local objects
SELECT INTO
X
X
-
-