Postgresql 中文操作指南

40.4. A Complete Event Trigger Example #

以下是使用 C 编写的事件触发器函数的一个非常简单的示例。（有关使用过程语言编写的触发器的示例，请参阅过程语言的文档。）

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.)

该函数 noddl 每次调用时都会引发异常。事件触发器定义将该函数关联到 ddl_command_start 事件。其效果是，所有 DDL 命令（对于 Section 40.1 中提到的例外情况除外）都无法运行。

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 Section 40.1) are prevented from running.

这是触发器函数的源代码：

This is the source code of the trigger function:

#include "postgres.h"
#include "commands/event_trigger.h"


PG_MODULE_MAGIC;

PG_FUNCTION_INFO_V1(noddl);

Datum
noddl(PG_FUNCTION_ARGS)
{
    EventTriggerData *trigdata;

    if (!CALLED_AS_EVENT_TRIGGER(fcinfo))  /* internal error */
        elog(ERROR, "not fired by event trigger manager");

    trigdata = (EventTriggerData *) fcinfo->context;

    ereport(ERROR,
            (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
             errmsg("command \"%s\" denied",
                    GetCommandTagName(trigdata->tag))));

    PG_RETURN_NULL();
}

在编译完源代码后（请参阅 Section 38.10.5），声明函数和触发器：

After you have compiled the source code (see Section 38.10.5), declare the function and the triggers:

CREATE FUNCTION noddl() RETURNS event_trigger
    AS 'noddl' LANGUAGE C;

CREATE EVENT TRIGGER noddl ON ddl_command_start
    EXECUTE FUNCTION noddl();

现在你可以测试触发器的操作：

Now you can test the operation of the trigger:

=# \dy
                     List of event triggers
 Name  |       Event       | Owner | Enabled | Function | Tags
-------+-------------------+-------+---------+----------+------
 noddl | ddl_command_start | dim   | enabled | noddl    |
(1 row)

=# CREATE TABLE foo(id serial);
ERROR:  command "CREATE TABLE" denied

在这种情况下，为了能够在需要时运行一些 DDL 命令，你必须删除事件触发器或禁用它。在交易的持续时间内禁用触发器会很方便：

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:

BEGIN;
ALTER EVENT TRIGGER noddl DISABLE;
CREATE TABLE foo (id serial);
ALTER EVENT TRIGGER noddl ENABLE;
COMMIT;

（回想一下，对事件触发器本身的 DDL 命令不受事件触发器影响。）

(Recall that DDL commands on event triggers themselves are not affected by event triggers.)