granicus.if.org Git - postgresql/blob - doc/src/sgml/event-trigger.sgml

   1 <!-- doc/src/sgml/event-trigger.sgml -->
   2
   3  <chapter id="event-triggers">
   4   <title>Event Triggers</title>
   5
   6   <indexterm zone="event-triggers">
   7    <primary>event trigger</primary>
   8   </indexterm>
   9
  10   <para>
  11    To supplement the trigger mechanism discussed in <xref linkend="triggers">,
  12    <productname>PostgreSQL</> also provides event triggers.  Unlike regular
  13    triggers, which are attached to a single table and capture only DML events,
  14    event triggers are global to a particular database and are capable of
  15    capturing DDL events.
  16   </para>
  17
  18   <para>
  19    Like regular triggers, event triggers can be written in any procedural
  20    language that includes event trigger support, or in C, but not in plain
  21    SQL.
  22   </para>
  23
  24   <sect1 id="event-trigger-definition">
  25    <title>Overview of Event Trigger Behavior</title>
  26
  27    <para>
  28      An event trigger fires whenever the event with which it is associated
  29      occurs in the database in which it is defined. Currently, the only
  30      supported events are
  31      <literal>ddl_command_start</>,
  32      <literal>ddl_command_end</>,
  33      <literal>table_rewrite</>
  34      and <literal>sql_drop</>.
  35      Support for additional events may be added in future releases.
  36    </para>
  37
  38    <para>
  39      The <literal>ddl_command_start</> event occurs just before the
  40      execution of a <literal>CREATE</>, <literal>ALTER</>, <literal>DROP</>,
  41      <literal>SECURITY LABEL</>,
  42      <literal>COMMENT</>, <literal>GRANT</> or <literal>REVOKE</>
  43      command.  No check whether the affected object exists or doesn't exist is
  44      performed before the event trigger fires.
  45      As an exception, however, this event does not occur for
  46      DDL commands targeting shared objects &mdash; databases, roles, and tablespaces
  47      &mdash; or for commands targeting event triggers themselves.  The event trigger
  48      mechanism does not support these object types.
  49      <literal>ddl_command_start</> also occurs just before the execution of a
  50      <literal>SELECT INTO</literal> command, since this is equivalent to
  51      <literal>CREATE TABLE AS</literal>.
  52    </para>
  53
  54    <para>
  55     The <literal>ddl_command_end</> event occurs just after the execution of
  56     this same set of commands.  To obtain more details on the <acronym>DDL</>
  57     operations that took place, use the set-returning function
  58     <literal>pg_event_trigger_ddl_commands()</> from the
  59     <literal>ddl_command_end</> event trigger code (see
  60     <xref linkend="functions-event-triggers">).  Note that the trigger fires
  61     after the actions have taken place (but before the transaction commits),
  62     and thus the system catalogs can be read as already changed.
  63    </para>
  64
  65    <para>
  66     The <literal>sql_drop</> event occurs just before the
  67     <literal>ddl_command_end</> event trigger for any operation that drops
  68     database objects.  To list the objects that have been dropped, use the
  69     set-returning function <literal>pg_event_trigger_dropped_objects()</> from the
  70     <literal>sql_drop</> event trigger code (see
  71     <xref linkend="functions-event-triggers">). Note that
  72     the trigger is executed after the objects have been deleted from the
  73     system catalogs, so it's not possible to look them up anymore.
  74    </para>
  75
  76    <para>
  77     The <literal>table_rewrite</> event occurs just before a table is
  78     rewritten by some actions of the commands <literal>ALTER TABLE</> and
  79     <literal>ALTER TYPE</>.  While other
  80     control statements are available to rewrite a table,
  81     like <literal>CLUSTER</literal> and <literal>VACUUM</literal>,
  82     the <literal>table_rewrite</> event is not triggered by them.
  83    </para>
  84
  85    <para>
  86      Event triggers (like other functions) cannot be executed in an aborted
  87      transaction.  Thus, if a DDL command fails with an error, any associated
  88      <literal>ddl_command_end</> triggers will not be executed.  Conversely,
  89      if a <literal>ddl_command_start</> trigger fails with an error, no
  90      further event triggers will fire, and no attempt will be made to execute
  91      the command itself.  Similarly, if a <literal>ddl_command_end</> trigger
  92      fails with an error, the effects of the DDL statement will be rolled
  93      back, just as they would be in any other case where the containing
  94      transaction aborts.
  95    </para>
  96
  97    <para>
  98      For a complete list of commands supported by the event trigger mechanism,
  99      see <xref linkend="event-trigger-matrix">.
 100    </para>
 101
 102    <para>
 103      Event triggers are created using the command <xref linkend="sql-createeventtrigger">.
 104      In order to create an event trigger, you must first create a function with
 105      the special return type <literal>event_trigger</literal>.  This function
 106      need not (and may not) return a value; the return type serves merely as
 107      a signal that the function is to be invoked as an event trigger.
 108    </para>
 109
 110    <para>
 111      If more than one event trigger is defined for a particular event, they will
 112      fire in alphabetical order by trigger name.
 113    </para>
 114
 115    <para>
 116      A trigger definition can also specify a <literal>WHEN</literal>
 117      condition so that, for example, a <literal>ddl_command_start</literal>
 118      trigger can be fired only for particular commands which the user wishes
 119      to intercept. A common use of such triggers is to restrict the range of
 120      DDL operations which users may perform.
 121    </para>
 122   </sect1>
 123
 124   <sect1 id="event-trigger-matrix">
 125    <title>Event Trigger Firing Matrix</title>
 126
 127    <para>
 128      <xref linkend="event-trigger-by-command-tag"> lists all commands
 129      for which event triggers are supported.
 130    </para>
 131
 132    <table id="event-trigger-by-command-tag">
 133      <title>Event Trigger Support by Command Tag</title>
 134      <tgroup cols="6">
 135       <thead>
 136        <row>
 137         <entry>Command Tag</entry>
 138         <entry><literal>ddl_command_start</literal></entry>
 139         <entry><literal>ddl_command_end</literal></entry>
 140         <entry><literal>sql_drop</literal></entry>
 141         <entry><literal>table_rewrite</literal></entry>
 142         <entry>Notes</entry>
 143        </row>
 144       </thead>
 145       <tbody>
 146        <row>
 147         <entry align="left"><literal>ALTER AGGREGATE</literal></entry>
 148         <entry align="center"><literal>X</literal></entry>
 149         <entry align="center"><literal>X</literal></entry>
 150         <entry align="center"><literal>-</literal></entry>
 151         <entry align="center"><literal>-</literal></entry>
 152         <entry align="center"></entry>
 153        </row>
 154        <row>
 155         <entry align="left"><literal>ALTER COLLATION</literal></entry>
 156         <entry align="center"><literal>X</literal></entry>
 157         <entry align="center"><literal>X</literal></entry>
 158         <entry align="center"><literal>-</literal></entry>
 159         <entry align="center"><literal>-</literal></entry>
 160         <entry align="center"></entry>
 161        </row>
 162        <row>
 163         <entry align="left"><literal>ALTER CONVERSION</literal></entry>
 164         <entry align="center"><literal>X</literal></entry>
 165         <entry align="center"><literal>X</literal></entry>
 166         <entry align="center"><literal>-</literal></entry>
 167         <entry align="center"><literal>-</literal></entry>
 168         <entry align="center"></entry>
 169        </row>
 170        <row>
 171         <entry align="left"><literal>ALTER DOMAIN</literal></entry>
 172         <entry align="center"><literal>X</literal></entry>
 173         <entry align="center"><literal>X</literal></entry>
 174         <entry align="center"><literal>-</literal></entry>
 175         <entry align="center"><literal>-</literal></entry>
 176         <entry align="center"></entry>
 177        </row>
 178        <row>
 179         <entry align="left"><literal>ALTER EXTENSION</literal></entry>
 180         <entry align="center"><literal>X</literal></entry>
 181         <entry align="center"><literal>X</literal></entry>
 182         <entry align="center"><literal>-</literal></entry>
 183         <entry align="center"><literal>-</literal></entry>
 184         <entry align="center"></entry>
 185        </row>
 186        <row>
 187         <entry align="left"><literal>ALTER FOREIGN DATA WRAPPER</literal></entry>
 188         <entry align="center"><literal>X</literal></entry>
 189         <entry align="center"><literal>X</literal></entry>
 190         <entry align="center"><literal>-</literal></entry>
 191         <entry align="center"><literal>-</literal></entry>
 192         <entry align="center"></entry>
 193        </row>
 194        <row>
 195         <entry align="left"><literal>ALTER FOREIGN TABLE</literal></entry>
 196         <entry align="center"><literal>X</literal></entry>
 197         <entry align="center"><literal>X</literal></entry>
 198         <entry align="center"><literal>X</literal></entry>
 199         <entry align="center"><literal>-</literal></entry>
 200         <entry align="center"></entry>
 201        </row>
 202        <row>
 203         <entry align="left"><literal>ALTER FUNCTION</literal></entry>
 204         <entry align="center"><literal>X</literal></entry>
 205         <entry align="center"><literal>X</literal></entry>
 206         <entry align="center"><literal>-</literal></entry>
 207         <entry align="center"><literal>-</literal></entry>
 208         <entry align="center"></entry>
 209        </row>
 210        <row>
 211         <entry align="left"><literal>ALTER LANGUAGE</literal></entry>
 212         <entry align="center"><literal>X</literal></entry>
 213         <entry align="center"><literal>X</literal></entry>
 214         <entry align="center"><literal>-</literal></entry>
 215         <entry align="center"><literal>-</literal></entry>
 216         <entry align="center"></entry>
 217        </row>
 218        <row>
 219         <entry align="left"><literal>ALTER OPERATOR</literal></entry>
 220         <entry align="center"><literal>X</literal></entry>
 221         <entry align="center"><literal>X</literal></entry>
 222         <entry align="center"><literal>-</literal></entry>
 223         <entry align="center"><literal>-</literal></entry>
 224         <entry align="center"></entry>
 225        </row>
 226        <row>
 227         <entry align="left"><literal>ALTER OPERATOR CLASS</literal></entry>
 228         <entry align="center"><literal>X</literal></entry>
 229         <entry align="center"><literal>X</literal></entry>
 230         <entry align="center"><literal>-</literal></entry>
 231         <entry align="center"><literal>-</literal></entry>
 232         <entry align="center"></entry>
 233        </row>
 234        <row>
 235         <entry align="left"><literal>ALTER OPERATOR FAMILY</literal></entry>
 236         <entry align="center"><literal>X</literal></entry>
 237         <entry align="center"><literal>X</literal></entry>
 238         <entry align="center"><literal>-</literal></entry>
 239         <entry align="center"><literal>-</literal></entry>
 240         <entry align="center"></entry>
 241        </row>
 242        <row>
 243         <entry align="left"><literal>ALTER POLICY</literal></entry>
 244         <entry align="center"><literal>X</literal></entry>
 245         <entry align="center"><literal>X</literal></entry>
 246         <entry align="center"><literal>-</literal></entry>
 247         <entry align="center"><literal>-</literal></entry>
 248         <entry align="center"></entry>
 249        </row>
 250        <row>
 251         <entry align="left"><literal>ALTER SCHEMA</literal></entry>
 252         <entry align="center"><literal>X</literal></entry>
 253         <entry align="center"><literal>X</literal></entry>
 254         <entry align="center"><literal>-</literal></entry>
 255         <entry align="center"><literal>-</literal></entry>
 256         <entry align="center"></entry>
 257        </row>
 258        <row>
 259         <entry align="left"><literal>ALTER SEQUENCE</literal></entry>
 260         <entry align="center"><literal>X</literal></entry>
 261         <entry align="center"><literal>X</literal></entry>
 262         <entry align="center"><literal>-</literal></entry>
 263         <entry align="center"><literal>-</literal></entry>
 264         <entry align="center"></entry>
 265        </row>
 266        <row>
 267         <entry align="left"><literal>ALTER SERVER</literal></entry>
 268         <entry align="center"><literal>X</literal></entry>
 269         <entry align="center"><literal>X</literal></entry>
 270         <entry align="center"><literal>-</literal></entry>
 271         <entry align="center"><literal>-</literal></entry>
 272         <entry align="center"></entry>
 273        </row>
 274        <row>
 275         <entry align="left"><literal>ALTER TABLE</literal></entry>
 276         <entry align="center"><literal>X</literal></entry>
 277         <entry align="center"><literal>X</literal></entry>
 278         <entry align="center"><literal>X</literal></entry>
 279         <entry align="center"><literal>X</literal></entry>
 280         <entry align="center"></entry>
 281        </row>
 282        <row>
 283         <entry align="left"><literal>ALTER TEXT SEARCH CONFIGURATION</literal></entry>
 284         <entry align="center"><literal>X</literal></entry>
 285         <entry align="center"><literal>X</literal></entry>
 286         <entry align="center"><literal>-</literal></entry>
 287         <entry align="center"><literal>-</literal></entry>
 288         <entry align="center"></entry>
 289        </row>
 290        <row>
 291         <entry align="left"><literal>ALTER TEXT SEARCH DICTIONARY</literal></entry>
 292         <entry align="center"><literal>X</literal></entry>
 293         <entry align="center"><literal>X</literal></entry>
 294         <entry align="center"><literal>-</literal></entry>
 295         <entry align="center"><literal>-</literal></entry>
 296         <entry align="center"></entry>
 297        </row>
 298        <row>
 299         <entry align="left"><literal>ALTER TEXT SEARCH PARSER</literal></entry>
 300         <entry align="center"><literal>X</literal></entry>
 301         <entry align="center"><literal>X</literal></entry>
 302         <entry align="center"><literal>-</literal></entry>
 303         <entry align="center"><literal>-</literal></entry>
 304         <entry align="center"></entry>
 305        </row>
 306        <row>
 307         <entry align="left"><literal>ALTER TEXT SEARCH TEMPLATE</literal></entry>
 308         <entry align="center"><literal>X</literal></entry>
 309         <entry align="center"><literal>X</literal></entry>
 310         <entry align="center"><literal>-</literal></entry>
 311         <entry align="center"><literal>-</literal></entry>
 312         <entry align="center"></entry>
 313        </row>
 314        <row>
 315         <entry align="left"><literal>ALTER TRIGGER</literal></entry>
 316         <entry align="center"><literal>X</literal></entry>
 317         <entry align="center"><literal>X</literal></entry>
 318         <entry align="center"><literal>-</literal></entry>
 319         <entry align="center"><literal>-</literal></entry>
 320         <entry align="center"></entry>
 321        </row>
 322        <row>
 323         <entry align="left"><literal>ALTER TYPE</literal></entry>
 324         <entry align="center"><literal>X</literal></entry>
 325         <entry align="center"><literal>X</literal></entry>
 326         <entry align="center"><literal>-</literal></entry>
 327         <entry align="center"><literal>X</literal></entry>
 328         <entry align="center"></entry>
 329        </row>
 330        <row>
 331         <entry align="left"><literal>ALTER USER MAPPING</literal></entry>
 332         <entry align="center"><literal>X</literal></entry>
 333         <entry align="center"><literal>X</literal></entry>
 334         <entry align="center"><literal>-</literal></entry>
 335         <entry align="center"><literal>-</literal></entry>
 336         <entry align="center"></entry>
 337        </row>
 338        <row>
 339         <entry align="left"><literal>ALTER VIEW</literal></entry>
 340         <entry align="center"><literal>X</literal></entry>
 341         <entry align="center"><literal>X</literal></entry>
 342         <entry align="center"><literal>-</literal></entry>
 343         <entry align="center"><literal>-</literal></entry>
 344         <entry align="center"></entry>
 345        </row>
 346        <row>
 347         <entry align="left"><literal>CREATE AGGREGATE</literal></entry>
 348         <entry align="center"><literal>X</literal></entry>
 349         <entry align="center"><literal>X</literal></entry>
 350         <entry align="center"><literal>-</literal></entry>
 351         <entry align="center"><literal>-</literal></entry>
 352         <entry align="center"></entry>
 353        </row>
 354        <row>
 355         <entry align="left"><literal>COMMENT</literal></entry>
 356         <entry align="center"><literal>X</literal></entry>
 357         <entry align="center"><literal>X</literal></entry>
 358         <entry align="center"><literal>-</literal></entry>
 359         <entry align="center"><literal>-</literal></entry>
 360         <entry align="center">Only for local objects</entry>
 361        </row>
 362        <row>
 363         <entry align="left"><literal>CREATE CAST</literal></entry>
 364         <entry align="center"><literal>X</literal></entry>
 365         <entry align="center"><literal>X</literal></entry>
 366         <entry align="center"><literal>-</literal></entry>
 367         <entry align="center"><literal>-</literal></entry>
 368         <entry align="center"></entry>
 369        </row>
 370        <row>
 371         <entry align="left"><literal>CREATE COLLATION</literal></entry>
 372         <entry align="center"><literal>X</literal></entry>
 373         <entry align="center"><literal>X</literal></entry>
 374         <entry align="center"><literal>-</literal></entry>
 375         <entry align="center"><literal>-</literal></entry>
 376         <entry align="center"></entry>
 377        </row>
 378        <row>
 379         <entry align="left"><literal>CREATE CONVERSION</literal></entry>
 380         <entry align="center"><literal>X</literal></entry>
 381         <entry align="center"><literal>X</literal></entry>
 382         <entry align="center"><literal>-</literal></entry>
 383         <entry align="center"><literal>-</literal></entry>
 384         <entry align="center"></entry>
 385        </row>
 386        <row>
 387         <entry align="left"><literal>CREATE DOMAIN</literal></entry>
 388         <entry align="center"><literal>X</literal></entry>
 389         <entry align="center"><literal>X</literal></entry>
 390         <entry align="center"><literal>-</literal></entry>
 391         <entry align="center"><literal>-</literal></entry>
 392         <entry align="center"></entry>
 393        </row>
 394        <row>
 395         <entry align="left"><literal>CREATE EXTENSION</literal></entry>
 396         <entry align="center"><literal>X</literal></entry>
 397         <entry align="center"><literal>X</literal></entry>
 398         <entry align="center"><literal>-</literal></entry>
 399         <entry align="center"><literal>-</literal></entry>
 400         <entry align="center"></entry>
 401        </row>
 402        <row>
 403         <entry align="left"><literal>CREATE FOREIGN DATA WRAPPER</literal></entry>
 404         <entry align="center"><literal>X</literal></entry>
 405         <entry align="center"><literal>X</literal></entry>
 406         <entry align="center"><literal>-</literal></entry>
 407         <entry align="center"><literal>-</literal></entry>
 408         <entry align="center"></entry>
 409        </row>
 410        <row>
 411         <entry align="left"><literal>CREATE FOREIGN TABLE</literal></entry>
 412         <entry align="center"><literal>X</literal></entry>
 413         <entry align="center"><literal>X</literal></entry>
 414         <entry align="center"><literal>-</literal></entry>
 415         <entry align="center"><literal>-</literal></entry>
 416         <entry align="center"></entry>
 417        </row>
 418        <row>
 419         <entry align="left"><literal>CREATE FUNCTION</literal></entry>
 420         <entry align="center"><literal>X</literal></entry>
 421         <entry align="center"><literal>X</literal></entry>
 422         <entry align="center"><literal>-</literal></entry>
 423         <entry align="center"><literal>-</literal></entry>
 424         <entry align="center"></entry>
 425        </row>
 426        <row>
 427         <entry align="left"><literal>CREATE INDEX</literal></entry>
 428         <entry align="center"><literal>X</literal></entry>
 429         <entry align="center"><literal>X</literal></entry>
 430         <entry align="center"><literal>-</literal></entry>
 431         <entry align="center"><literal>-</literal></entry>
 432         <entry align="center"></entry>
 433        </row>
 434        <row>
 435         <entry align="left"><literal>CREATE LANGUAGE</literal></entry>
 436         <entry align="center"><literal>X</literal></entry>
 437         <entry align="center"><literal>X</literal></entry>
 438         <entry align="center"><literal>-</literal></entry>
 439         <entry align="center"><literal>-</literal></entry>
 440         <entry align="center"></entry>
 441        </row>
 442        <row>
 443         <entry align="left"><literal>CREATE OPERATOR</literal></entry>
 444         <entry align="center"><literal>X</literal></entry>
 445         <entry align="center"><literal>X</literal></entry>
 446         <entry align="center"><literal>-</literal></entry>
 447         <entry align="center"><literal>-</literal></entry>
 448         <entry align="center"></entry>
 449        </row>
 450        <row>
 451         <entry align="left"><literal>CREATE OPERATOR CLASS</literal></entry>
 452         <entry align="center"><literal>X</literal></entry>
 453         <entry align="center"><literal>X</literal></entry>
 454         <entry align="center"><literal>-</literal></entry>
 455         <entry align="center"><literal>-</literal></entry>
 456         <entry align="center"></entry>
 457        </row>
 458        <row>
 459         <entry align="left"><literal>CREATE OPERATOR FAMILY</literal></entry>
 460         <entry align="center"><literal>X</literal></entry>
 461         <entry align="center"><literal>X</literal></entry>
 462         <entry align="center"><literal>-</literal></entry>
 463         <entry align="center"><literal>-</literal></entry>
 464         <entry align="center"></entry>
 465        </row>
 466        <row>
 467         <entry align="left"><literal>CREATE POLICY</literal></entry>
 468         <entry align="center"><literal>X</literal></entry>
 469         <entry align="center"><literal>X</literal></entry>
 470         <entry align="center"><literal>-</literal></entry>
 471         <entry align="center"><literal>-</literal></entry>
 472         <entry align="center"></entry>
 473        </row>
 474        <row>
 475         <entry align="left"><literal>CREATE RULE</literal></entry>
 476         <entry align="center"><literal>X</literal></entry>
 477         <entry align="center"><literal>X</literal></entry>
 478         <entry align="center"><literal>-</literal></entry>
 479         <entry align="center"><literal>-</literal></entry>
 480         <entry align="center"></entry>
 481        </row>
 482        <row>
 483         <entry align="left"><literal>CREATE SCHEMA</literal></entry>
 484         <entry align="center"><literal>X</literal></entry>
 485         <entry align="center"><literal>X</literal></entry>
 486         <entry align="center"><literal>-</literal></entry>
 487         <entry align="center"><literal>-</literal></entry>
 488         <entry align="center"></entry>
 489        </row>
 490        <row>
 491         <entry align="left"><literal>CREATE SEQUENCE</literal></entry>
 492         <entry align="center"><literal>X</literal></entry>
 493         <entry align="center"><literal>X</literal></entry>
 494         <entry align="center"><literal>-</literal></entry>
 495         <entry align="center"><literal>-</literal></entry>
 496         <entry align="center"></entry>
 497        </row>
 498        <row>
 499         <entry align="left"><literal>CREATE SERVER</literal></entry>
 500         <entry align="center"><literal>X</literal></entry>
 501         <entry align="center"><literal>X</literal></entry>
 502         <entry align="center"><literal>-</literal></entry>
 503         <entry align="center"><literal>-</literal></entry>
 504         <entry align="center"></entry>
 505        </row>
 506        <row>
 507         <entry align="left"><literal>CREATE TABLE</literal></entry>
 508         <entry align="center"><literal>X</literal></entry>
 509         <entry align="center"><literal>X</literal></entry>
 510         <entry align="center"><literal>-</literal></entry>
 511         <entry align="center"><literal>-</literal></entry>
 512         <entry align="center"></entry>
 513        </row>
 514        <row>
 515         <entry align="left"><literal>CREATE TABLE AS</literal></entry>
 516         <entry align="center"><literal>X</literal></entry>
 517         <entry align="center"><literal>X</literal></entry>
 518         <entry align="center"><literal>-</literal></entry>
 519         <entry align="center"><literal>-</literal></entry>
 520         <entry align="center"></entry>
 521        </row>
 522        <row>
 523         <entry align="left"><literal>CREATE TEXT SEARCH CONFIGURATION</literal></entry>
 524         <entry align="center"><literal>X</literal></entry>
 525         <entry align="center"><literal>X</literal></entry>
 526         <entry align="center"><literal>-</literal></entry>
 527         <entry align="center"><literal>-</literal></entry>
 528         <entry align="center"></entry>
 529        </row>
 530        <row>
 531         <entry align="left"><literal>CREATE TEXT SEARCH DICTIONARY</literal></entry>
 532         <entry align="center"><literal>X</literal></entry>
 533         <entry align="center"><literal>X</literal></entry>
 534         <entry align="center"><literal>-</literal></entry>
 535         <entry align="center"><literal>-</literal></entry>
 536         <entry align="center"></entry>
 537        </row>
 538        <row>
 539         <entry align="left"><literal>CREATE TEXT SEARCH PARSER</literal></entry>
 540         <entry align="center"><literal>X</literal></entry>
 541         <entry align="center"><literal>X</literal></entry>
 542         <entry align="center"><literal>-</literal></entry>
 543         <entry align="center"><literal>-</literal></entry>
 544         <entry align="center"></entry>
 545        </row>
 546        <row>
 547         <entry align="left"><literal>CREATE TEXT SEARCH TEMPLATE</literal></entry>
 548         <entry align="center"><literal>X</literal></entry>
 549         <entry align="center"><literal>X</literal></entry>
 550         <entry align="center"><literal>-</literal></entry>
 551         <entry align="center"><literal>-</literal></entry>
 552         <entry align="center"></entry>
 553        </row>
 554        <row>
 555         <entry align="left"><literal>CREATE TRIGGER</literal></entry>
 556         <entry align="center"><literal>X</literal></entry>
 557         <entry align="center"><literal>X</literal></entry>
 558         <entry align="center"><literal>-</literal></entry>
 559         <entry align="center"><literal>-</literal></entry>
 560         <entry align="center"></entry>
 561        </row>
 562        <row>
 563         <entry align="left"><literal>CREATE TYPE</literal></entry>
 564         <entry align="center"><literal>X</literal></entry>
 565         <entry align="center"><literal>X</literal></entry>
 566         <entry align="center"><literal>-</literal></entry>
 567         <entry align="center"><literal>-</literal></entry>
 568        </row>
 569        <row>
 570         <entry align="left"><literal>CREATE USER MAPPING</literal></entry>
 571         <entry align="center"><literal>X</literal></entry>
 572         <entry align="center"><literal>X</literal></entry>
 573         <entry align="center"><literal>-</literal></entry>
 574         <entry align="center"><literal>-</literal></entry>
 575         <entry align="center"></entry>
 576        </row>
 577        <row>
 578         <entry align="left"><literal>CREATE VIEW</literal></entry>
 579         <entry align="center"><literal>X</literal></entry>
 580         <entry align="center"><literal>X</literal></entry>
 581         <entry align="center"><literal>-</literal></entry>
 582         <entry align="center"><literal>-</literal></entry>
 583         <entry align="center"></entry>
 584        </row>
 585        <row>
 586         <entry align="left"><literal>DROP AGGREGATE</literal></entry>
 587         <entry align="center"><literal>X</literal></entry>
 588         <entry align="center"><literal>X</literal></entry>
 589         <entry align="center"><literal>X</literal></entry>
 590         <entry align="center"><literal>-</literal></entry>
 591         <entry align="center"></entry>
 592        </row>
 593        <row>
 594         <entry align="left"><literal>DROP CAST</literal></entry>
 595         <entry align="center"><literal>X</literal></entry>
 596         <entry align="center"><literal>X</literal></entry>
 597         <entry align="center"><literal>X</literal></entry>
 598         <entry align="center"><literal>-</literal></entry>
 599         <entry align="center"></entry>
 600        </row>
 601        <row>
 602         <entry align="left"><literal>DROP COLLATION</literal></entry>
 603         <entry align="center"><literal>X</literal></entry>
 604         <entry align="center"><literal>X</literal></entry>
 605         <entry align="center"><literal>X</literal></entry>
 606         <entry align="center"><literal>-</literal></entry>
 607         <entry align="center"></entry>
 608        </row>
 609        <row>
 610         <entry align="left"><literal>DROP CONVERSION</literal></entry>
 611         <entry align="center"><literal>X</literal></entry>
 612         <entry align="center"><literal>X</literal></entry>
 613         <entry align="center"><literal>X</literal></entry>
 614         <entry align="center"><literal>-</literal></entry>
 615         <entry align="center"></entry>
 616        </row>
 617        <row>
 618         <entry align="left"><literal>DROP DOMAIN</literal></entry>
 619         <entry align="center"><literal>X</literal></entry>
 620         <entry align="center"><literal>X</literal></entry>
 621         <entry align="center"><literal>X</literal></entry>
 622         <entry align="center"><literal>-</literal></entry>
 623         <entry align="center"></entry>
 624        </row>
 625        <row>
 626         <entry align="left"><literal>DROP EXTENSION</literal></entry>
 627         <entry align="center"><literal>X</literal></entry>
 628         <entry align="center"><literal>X</literal></entry>
 629         <entry align="center"><literal>X</literal></entry>
 630         <entry align="center"><literal>-</literal></entry>
 631         <entry align="center"></entry>
 632        </row>
 633        <row>
 634         <entry align="left"><literal>DROP FOREIGN DATA WRAPPER</literal></entry>
 635         <entry align="center"><literal>X</literal></entry>
 636         <entry align="center"><literal>X</literal></entry>
 637         <entry align="center"><literal>X</literal></entry>
 638         <entry align="center"><literal>-</literal></entry>
 639         <entry align="center"></entry>
 640        </row>
 641        <row>
 642         <entry align="left"><literal>DROP FOREIGN TABLE</literal></entry>
 643         <entry align="center"><literal>X</literal></entry>
 644         <entry align="center"><literal>X</literal></entry>
 645         <entry align="center"><literal>X</literal></entry>
 646         <entry align="center"><literal>-</literal></entry>
 647         <entry align="center"></entry>
 648        </row>
 649        <row>
 650         <entry align="left"><literal>DROP FUNCTION</literal></entry>
 651         <entry align="center"><literal>X</literal></entry>
 652         <entry align="center"><literal>X</literal></entry>
 653         <entry align="center"><literal>X</literal></entry>
 654         <entry align="center"><literal>-</literal></entry>
 655         <entry align="center"></entry>
 656        </row>
 657        <row>
 658         <entry align="left"><literal>DROP INDEX</literal></entry>
 659         <entry align="center"><literal>X</literal></entry>
 660         <entry align="center"><literal>X</literal></entry>
 661         <entry align="center"><literal>X</literal></entry>
 662         <entry align="center"><literal>-</literal></entry>
 663         <entry align="center"></entry>
 664        </row>
 665        <row>
 666         <entry align="left"><literal>DROP LANGUAGE</literal></entry>
 667         <entry align="center"><literal>X</literal></entry>
 668         <entry align="center"><literal>X</literal></entry>
 669         <entry align="center"><literal>X</literal></entry>
 670         <entry align="center"><literal>-</literal></entry>
 671         <entry align="center"></entry>
 672        </row>
 673        <row>
 674         <entry align="left"><literal>DROP OPERATOR</literal></entry>
 675         <entry align="center"><literal>X</literal></entry>
 676         <entry align="center"><literal>X</literal></entry>
 677         <entry align="center"><literal>X</literal></entry>
 678         <entry align="center"><literal>-</literal></entry>
 679         <entry align="center"></entry>
 680        </row>
 681        <row>
 682         <entry align="left"><literal>DROP OPERATOR CLASS</literal></entry>
 683         <entry align="center"><literal>X</literal></entry>
 684         <entry align="center"><literal>X</literal></entry>
 685         <entry align="center"><literal>X</literal></entry>
 686         <entry align="center"><literal>-</literal></entry>
 687         <entry align="center"></entry>
 688        </row>
 689        <row>
 690         <entry align="left"><literal>DROP OPERATOR FAMILY</literal></entry>
 691         <entry align="center"><literal>X</literal></entry>
 692         <entry align="center"><literal>X</literal></entry>
 693         <entry align="center"><literal>X</literal></entry>
 694         <entry align="center"><literal>-</literal></entry>
 695         <entry align="center"></entry>
 696        </row>
 697        <row>
 698         <entry align="left"><literal>DROP OWNED</literal></entry>
 699         <entry align="center"><literal>X</literal></entry>
 700         <entry align="center"><literal>X</literal></entry>
 701         <entry align="center"><literal>X</literal></entry>
 702         <entry align="center"><literal>-</literal></entry>
 703         <entry align="center"></entry>
 704        </row>
 705        <row>
 706         <entry align="left"><literal>DROP POLICY</literal></entry>
 707         <entry align="center"><literal>X</literal></entry>
 708         <entry align="center"><literal>X</literal></entry>
 709         <entry align="center"><literal>X</literal></entry>
 710         <entry align="center"><literal>-</literal></entry>
 711         <entry align="center"></entry>
 712        </row>
 713        <row>
 714         <entry align="left"><literal>DROP RULE</literal></entry>
 715         <entry align="center"><literal>X</literal></entry>
 716         <entry align="center"><literal>X</literal></entry>
 717         <entry align="center"><literal>X</literal></entry>
 718         <entry align="center"><literal>-</literal></entry>
 719         <entry align="center"></entry>
 720        </row>
 721        <row>
 722         <entry align="left"><literal>DROP SCHEMA</literal></entry>
 723         <entry align="center"><literal>X</literal></entry>
 724         <entry align="center"><literal>X</literal></entry>
 725         <entry align="center"><literal>X</literal></entry>
 726         <entry align="center"><literal>-</literal></entry>
 727         <entry align="center"></entry>
 728        </row>
 729        <row>
 730         <entry align="left"><literal>DROP SEQUENCE</literal></entry>
 731         <entry align="center"><literal>X</literal></entry>
 732         <entry align="center"><literal>X</literal></entry>
 733         <entry align="center"><literal>X</literal></entry>
 734         <entry align="center"><literal>-</literal></entry>
 735         <entry align="center"></entry>
 736        </row>
 737        <row>
 738         <entry align="left"><literal>DROP SERVER</literal></entry>
 739         <entry align="center"><literal>X</literal></entry>
 740         <entry align="center"><literal>X</literal></entry>
 741         <entry align="center"><literal>X</literal></entry>
 742         <entry align="center"><literal>-</literal></entry>
 743         <entry align="center"></entry>
 744        </row>
 745        <row>
 746         <entry align="left"><literal>DROP TABLE</literal></entry>
 747         <entry align="center"><literal>X</literal></entry>
 748         <entry align="center"><literal>X</literal></entry>
 749         <entry align="center"><literal>X</literal></entry>
 750         <entry align="center"><literal>-</literal></entry>
 751         <entry align="center"></entry>
 752        </row>
 753        <row>
 754         <entry align="left"><literal>DROP TEXT SEARCH CONFIGURATION</literal></entry>
 755         <entry align="center"><literal>X</literal></entry>
 756         <entry align="center"><literal>X</literal></entry>
 757         <entry align="center"><literal>X</literal></entry>
 758         <entry align="center"><literal>-</literal></entry>
 759         <entry align="center"></entry>
 760        </row>
 761        <row>
 762         <entry align="left"><literal>DROP TEXT SEARCH DICTIONARY</literal></entry>
 763         <entry align="center"><literal>X</literal></entry>
 764         <entry align="center"><literal>X</literal></entry>
 765         <entry align="center"><literal>X</literal></entry>
 766         <entry align="center"><literal>-</literal></entry>
 767         <entry align="center"></entry>
 768        </row>
 769        <row>
 770         <entry align="left"><literal>DROP TEXT SEARCH PARSER</literal></entry>
 771         <entry align="center"><literal>X</literal></entry>
 772         <entry align="center"><literal>X</literal></entry>
 773         <entry align="center"><literal>X</literal></entry>
 774         <entry align="center"><literal>-</literal></entry>
 775         <entry align="center"></entry>
 776        </row>
 777        <row>
 778         <entry align="left"><literal>DROP TEXT SEARCH TEMPLATE</literal></entry>
 779         <entry align="center"><literal>X</literal></entry>
 780         <entry align="center"><literal>X</literal></entry>
 781         <entry align="center"><literal>X</literal></entry>
 782         <entry align="center"><literal>-</literal></entry>
 783         <entry align="center"></entry>
 784        </row>
 785        <row>
 786         <entry align="left"><literal>DROP TRIGGER</literal></entry>
 787         <entry align="center"><literal>X</literal></entry>
 788         <entry align="center"><literal>X</literal></entry>
 789         <entry align="center"><literal>X</literal></entry>
 790         <entry align="center"><literal>-</literal></entry>
 791         <entry align="center"></entry>
 792        </row>
 793        <row>
 794         <entry align="left"><literal>DROP TYPE</literal></entry>
 795         <entry align="center"><literal>X</literal></entry>
 796         <entry align="center"><literal>X</literal></entry>
 797         <entry align="center"><literal>X</literal></entry>
 798         <entry align="center"><literal>-</literal></entry>
 799         <entry align="center"></entry>
 800        </row>
 801        <row>
 802         <entry align="left"><literal>DROP USER MAPPING</literal></entry>
 803         <entry align="center"><literal>X</literal></entry>
 804         <entry align="center"><literal>X</literal></entry>
 805         <entry align="center"><literal>X</literal></entry>
 806         <entry align="center"><literal>-</literal></entry>
 807         <entry align="center"></entry>
 808        </row>
 809        <row>
 810         <entry align="left"><literal>DROP VIEW</literal></entry>
 811         <entry align="center"><literal>X</literal></entry>
 812         <entry align="center"><literal>X</literal></entry>
 813         <entry align="center"><literal>X</literal></entry>
 814         <entry align="center"><literal>-</literal></entry>
 815         <entry align="center"></entry>
 816        </row>
 817        <row>
 818         <entry align="left"><literal>GRANT</literal></entry>
 819         <entry align="center"><literal>X</literal></entry>
 820         <entry align="center"><literal>X</literal></entry>
 821         <entry align="center"><literal>-</literal></entry>
 822         <entry align="center"><literal>-</literal></entry>
 823         <entry align="center">Only for local objects</entry>
 824        </row>
 825        <row>
 826         <entry align="left"><literal>IMPORT FOREIGN SCHEMA</literal></entry>
 827         <entry align="center"><literal>X</literal></entry>
 828         <entry align="center"><literal>X</literal></entry>
 829         <entry align="center"><literal>-</literal></entry>
 830         <entry align="center"><literal>-</literal></entry>
 831         <entry align="center"></entry>
 832        </row>
 833        <row>
 834         <entry align="left"><literal>REVOKE</literal></entry>
 835         <entry align="center"><literal>X</literal></entry>
 836         <entry align="center"><literal>X</literal></entry>
 837         <entry align="center"><literal>-</literal></entry>
 838         <entry align="center"><literal>-</literal></entry>
 839         <entry align="center">Only for local objects</entry>
 840        </row>
 841        <row>
 842         <entry align="left"><literal>SECURITY LABEL</literal></entry>
 843         <entry align="center"><literal>X</literal></entry>
 844         <entry align="center"><literal>X</literal></entry>
 845         <entry align="center"><literal>-</literal></entry>
 846         <entry align="center"><literal>-</literal></entry>
 847         <entry align="center">Only for local objects</entry>
 848        </row>
 849        <row>
 850         <entry align="left"><literal>SELECT INTO</literal></entry>
 851         <entry align="center"><literal>X</literal></entry>
 852         <entry align="center"><literal>X</literal></entry>
 853         <entry align="center"><literal>-</literal></entry>
 854         <entry align="center"><literal>-</literal></entry>
 855         <entry align="center"></entry>
 856        </row>
 857       </tbody>
 858      </tgroup>
 859    </table>
 860   </sect1>
 861
 862   <sect1 id="event-trigger-interface">
 863    <title>Writing Event Trigger Functions in C</title>
 864
 865    <indexterm zone="event-trigger-interface">
 866     <primary>event trigger</primary>
 867     <secondary>in C</secondary>
 868    </indexterm>
 869
 870    <para>
 871     This section describes the low-level details of the interface to an
 872     event trigger function. This information is only needed when writing
 873     event trigger functions in C. If you are using a higher-level language
 874     then these details are handled for you. In most cases you should
 875     consider using a procedural language before writing your event triggers
 876     in C. The documentation of each procedural language explains how to
 877     write an event trigger in that language.
 878    </para>
 879
 880    <para>
 881     Event trigger functions must use the <quote>version 1</> function
 882     manager interface.
 883    </para>
 884
 885    <para>
 886     When a function is called by the event trigger manager, it is not passed
 887     any normal arguments, but it is passed a <quote>context</> pointer
 888     pointing to a <structname>EventTriggerData</> structure. C functions can
 889     check whether they were called from the event trigger manager or not by
 890     executing the macro:
 891 <programlisting>
 892 CALLED_AS_EVENT_TRIGGER(fcinfo)
 893 </programlisting>
 894     which expands to:
 895 <programlisting>
 896 ((fcinfo)-&gt;context != NULL &amp;&amp; IsA((fcinfo)-&gt;context, EventTriggerData))
 897 </programlisting>
 898     If this returns true, then it is safe to cast
 899     <literal>fcinfo-&gt;context</> to type <literal>EventTriggerData
 900     *</literal> and make use of the pointed-to
 901     <structname>EventTriggerData</> structure.  The function must
 902     <emphasis>not</emphasis> alter the <structname>EventTriggerData</>
 903     structure or any of the data it points to.
 904    </para>
 905
 906    <para>
 907     <structname>struct EventTriggerData</structname> is defined in
 908     <filename>commands/event_trigger.h</filename>:
 909
 910 <programlisting>
 911 typedef struct EventTriggerData
 912 {
 913     NodeTag     type;
 914     const char *event;      /* event name */
 915     Node       *parsetree;  /* parse tree */
 916     const char *tag;        /* command tag */
 917 } EventTriggerData;
 918 </programlisting>
 919
 920     where the members are defined as follows:
 921
 922     <variablelist>
 923      <varlistentry>
 924       <term><structfield>type</></term>
 925       <listitem>
 926        <para>
 927         Always <literal>T_EventTriggerData</literal>.
 928        </para>
 929       </listitem>
 930      </varlistentry>
 931
 932      <varlistentry>
 933       <term><structfield>event</></term>
 934       <listitem>
 935        <para>
 936         Describes the event for which the function is called, one of
 937         <literal>"ddl_command_start"</literal>, <literal>"ddl_command_end"</literal>,
 938         <literal>"sql_drop"</literal>, <literal>"table_rewrite"</literal>.
 939         See <xref linkend="event-trigger-definition"> for the meaning of these
 940         events.
 941        </para>
 942       </listitem>
 943      </varlistentry>
 944
 945      <varlistentry>
 946       <term><structfield>parsetree</></term>
 947       <listitem>
 948        <para>
 949         A pointer to the parse tree of the command.  Check the PostgreSQL
 950         source code for details.  The parse tree structure is subject to change
 951         without notice.
 952        </para>
 953       </listitem>
 954      </varlistentry>
 955
 956      <varlistentry>
 957       <term><structfield>tag</></term>
 958       <listitem>
 959        <para>
 960         The command tag associated with the event for which the event trigger
 961         is run, for example <literal>"CREATE FUNCTION"</literal>.
 962        </para>
 963       </listitem>
 964      </varlistentry>
 965     </variablelist>
 966    </para>
 967
 968    <para>
 969     An event trigger function must return a <symbol>NULL</> pointer
 970     (<emphasis>not</> an SQL null value, that is, do not
 971     set <parameter>isNull</parameter> true).
 972    </para>
 973   </sect1>
 974
 975   <sect1 id="event-trigger-example">
 976    <title>A Complete Event Trigger Example</title>
 977
 978    <para>
 979     Here is a very simple example of an event trigger function written in C.
 980     (Examples of triggers written in procedural languages can be found in
 981     the documentation of the procedural languages.)
 982    </para>
 983
 984    <para>
 985     The function <function>noddl</> raises an exception each time it is called.
 986     The event trigger definition associated the function with
 987     the <literal>ddl_command_start</literal> event.  The effect is that all DDL
 988     commands (with the exceptions mentioned
 989     in <xref linkend="event-trigger-definition">) are prevented from running.
 990    </para>
 991
 992    <para>
 993     This is the source code of the trigger function:
 994 <programlisting><![CDATA[
 995 #include "postgres.h"
 996 #include "commands/event_trigger.h"
 997
 998
 999 PG_MODULE_MAGIC;
1000
1001 PG_FUNCTION_INFO_V1(noddl);
1002
1003 Datum
1004 noddl(PG_FUNCTION_ARGS)
1005 {
1006     EventTriggerData *trigdata;
1007
1008     if (!CALLED_AS_EVENT_TRIGGER(fcinfo))  /* internal error */
1009         elog(ERROR, "not fired by event trigger manager");
1010
1011     trigdata = (EventTriggerData *) fcinfo->context;
1012
1013     ereport(ERROR,
1014         (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
1015                  errmsg("command \"%s\" denied", trigdata->tag)));
1016
1017     PG_RETURN_NULL();
1018 }
1019 ]]></programlisting>
1020    </para>
1021
1022    <para>
1023     After you have compiled the source code (see <xref linkend="dfunc">),
1024     declare the function and the triggers:
1025 <programlisting>
1026 CREATE FUNCTION noddl() RETURNS event_trigger
1027     AS 'noddl' LANGUAGE C;
1028
1029 CREATE EVENT TRIGGER noddl ON ddl_command_start
1030     EXECUTE PROCEDURE noddl();
1031 </programlisting>
1032    </para>
1033
1034    <para>
1035     Now you can test the operation of the trigger:
1036 <screen>
1037 =# \dy
1038                      List of event triggers
1039  Name  |       Event       | Owner | Enabled | Procedure | Tags
1040 -------+-------------------+-------+---------+-----------+------
1041  noddl | ddl_command_start | dim   | enabled | noddl     |
1042 (1 row)
1043
1044 =# CREATE TABLE foo(id serial);
1045 ERROR:  command "CREATE TABLE" denied
1046 </screen>
1047    </para>
1048
1049    <para>
1050     In this situation, in order to be able to run some DDL commands when you
1051     need to do so, you have to either drop the event trigger or disable it.  It
1052     can be convenient to disable the trigger for only the duration of a
1053     transaction:
1054 <programlisting>
1055 BEGIN;
1056 ALTER EVENT TRIGGER noddl DISABLE;
1057 CREATE TABLE foo (id serial);
1058 ALTER EVENT TRIGGER noddl ENABLE;
1059 COMMIT;
1060 </programlisting>
1061     (Recall that DDL commands on event triggers themselves are not affected by
1062     event triggers.)
1063    </para>
1064   </sect1>
1065
1066   <sect1 id="event-trigger-table-rewrite-example">
1067    <title>A Table Rewrite Event Trigger Example</title>
1068
1069    <para>
1070     Thanks to the <literal>table_rewrite</> event, it is possible to implement
1071     a table rewriting policy only allowing the rewrite in maintenance windows.
1072    </para>
1073
1074    <para>
1075     Here's an example implementing such a policy.
1076 <programlisting>
1077 CREATE OR REPLACE FUNCTION no_rewrite()
1078  RETURNS event_trigger
1079  LANGUAGE plpgsql AS
1080 $$
1081 ---
1082 --- Implement local Table Rewriting policy:
1083 ---   public.foo is not allowed rewriting, ever
1084 ---   other tables are only allowed rewriting between 1am and 6am
1085 ---   unless they have more than 100 blocks
1086 ---
1087 DECLARE
1088   table_oid oid := pg_event_trigger_table_rewrite_oid();
1089   current_hour integer := extract('hour' from current_time);
1090   pages integer;
1091   max_pages integer := 100;
1092 BEGIN
1093   IF pg_event_trigger_table_rewrite_oid() = 'public.foo'::regclass
1094   THEN
1095         RAISE EXCEPTION 'you''re not allowed to rewrite the table %',
1096                         table_oid::regclass;
1097   END IF;
1098
1099   SELECT INTO pages relpages FROM pg_class WHERE oid = table_oid;
1100   IF pages > max_pages
1101   THEN
1102         RAISE EXCEPTION 'rewrites only allowed for table with less than % pages',
1103                         max_pages;
1104   END IF;
1105
1106   IF current_hour NOT BETWEEN 1 AND 6
1107   THEN
1108         RAISE EXCEPTION 'rewrites only allowed between 1am and 6am';
1109   END IF;
1110 END;
1111 $$;
1112
1113 CREATE EVENT TRIGGER no_rewrite_allowed
1114                   ON table_rewrite
1115    EXECUTE PROCEDURE no_rewrite();
1116 </programlisting>
1117    </para>
1118  </sect1>
1119 </chapter>