doc/src/sgml/contrib-spi.sgml

   1 <!-- $PostgreSQL$ -->
   2
   3 <sect1 id="contrib-spi">
   4  <title>spi</title>
   5
   6  <indexterm zone="contrib-spi">
   7   <primary>SPI</primary>
   8   <secondary>examples</secondary>
   9  </indexterm>
  10
  11  <para>
  12   The <filename>contrib/spi</> module provides several workable examples
  13   of using SPI and triggers.  While these functions are of some value in
  14   their own right, they are even more useful as examples to modify for
  15   your own purposes.  The functions are general enough to be used
  16   with any table, but you have to specify table and field names (as described
  17   below) while creating a trigger.
  18  </para>
  19
  20  <sect2>
  21   <title>refint.c &mdash; functions for implementing referential integrity</title>
  22
  23   <para>
  24    <function>check_primary_key()</> and
  25    <function>check_foreign_key()</> are used to check foreign key constraints.
  26    (This functionality is long since superseded by the built-in foreign
  27    key mechanism, of course, but the module is still useful as an example.)
  28   </para>
  29
  30   <para>
  31    <function>check_primary_key()</> checks the referencing table.
  32    To use, create a <literal>BEFORE INSERT OR UPDATE</> trigger using this
  33    function on a table referencing another table. Specify as the trigger
  34    arguments: the referencing table's column name(s) which form the foreign
  35    key, the referenced table name, and the column names in the referenced table
  36    which form the primary/unique key.  To handle multiple foreign
  37    keys, create a trigger for each reference.
  38   </para>
  39
  40   <para>
  41    <function>check_foreign_key()</> checks the referenced table.
  42    To use, create a <literal>BEFORE DELETE OR UPDATE</> trigger using this
  43    function on a table referenced by other table(s).  Specify as the trigger
  44    arguments: the number of referencing tables for which the function has to
  45    perform checking, the action if a referencing key is found
  46    (<literal>cascade</> &mdash; to delete the referencing row,
  47    <literal>restrict</> &mdash; to abort transaction if referencing keys
  48    exist, <literal>setnull</> &mdash; to set referencing key fields to null),
  49    the triggered table's column names which form the primary/unique key, then
  50    the referencing table name and column names (repeated for as many
  51    referencing tables as were specified by first argument).  Note that the
  52    primary/unique key columns should be marked NOT NULL and should have a
  53    unique index.
  54   </para>
  55
  56   <para>
  57    There are examples in <filename>refint.example</>.
  58   </para>
  59  </sect2>
  60
  61  <sect2>
  62   <title>timetravel.c &mdash; functions for implementing time travel</title>
  63
  64   <para>
  65    Long ago, <productname>PostgreSQL</> had a built-in time travel feature
  66    that kept the insert and delete times for each tuple.  This can be
  67    emulated using these functions.  To use these functions,
  68    you must add to a table two columns of <type>abstime</> type to store
  69    the date when a tuple was inserted (start_date) and changed/deleted
  70    (stop_date):
  71
  72 <programlisting>
  73 CREATE TABLE mytab (
  74         ...             ...
  75         start_date      abstime,
  76         stop_date       abstime
  77         ...             ...
  78 );
  79 </programlisting>
  80
  81    The columns can be named whatever you like, but in this discussion
  82    we'll call them start_date and stop_date.
  83   </para>
  84
  85   <para>
  86    When a new row is inserted, start_date should normally be set to
  87    current time, and stop_date to <literal>infinity</>.  The trigger
  88    will automatically substitute these values if the inserted data
  89    contains nulls in these columns.  Generally, inserting explicit
  90    non-null data in these columns should only be done when re-loading
  91    dumped data.
  92   </para>
  93
  94   <para>
  95    Tuples with stop_date equal to <literal>infinity</> are <quote>valid
  96    now</quote>, and can be modified.  Tuples with a finite stop_date cannot
  97    be modified anymore &mdash; the trigger will prevent it.  (If you need
  98    to do that, you can turn off time travel as shown below.)
  99   </para>
 100
 101   <para>
 102    For a modifiable row, on update only the stop_date in the tuple being
 103    updated will be changed (to current time) and a new tuple with the modified
 104    data will be inserted.  Start_date in this new tuple will be set to current
 105    time and stop_date to <literal>infinity</>.
 106   </para>
 107
 108   <para>
 109    A delete does not actually remove the tuple but only sets its stop_date
 110    to current time.
 111   </para>
 112
 113   <para>
 114    To query for tuples <quote>valid now</quote>, include
 115    <literal>stop_date = 'infinity'</> in the query's WHERE condition.
 116    (You might wish to incorporate that in a view.)  Similarly, you can
 117    query for tuples valid at any past time with suitable conditions on
 118    start_date and stop_date.
 119   </para>
 120
 121   <para>
 122    <function>timetravel()</> is the general trigger function that supports
 123    this behavior.  Create a <literal>BEFORE INSERT OR UPDATE OR DELETE</>
 124    trigger using this function on each time-traveled table. Specify two
 125    trigger arguments: the actual
 126    names of the start_date and stop_date columns.
 127    Optionally, you can specify one to three more arguments, which must refer
 128    to columns of type <type>text</>.  The trigger will store the name of
 129    the current user into the first of these columns during INSERT, the
 130    second column during UPDATE, and the third during DELETE.
 131   </para>
 132
 133   <para>
 134    <function>set_timetravel()</> allows you to turn time-travel on or off for
 135    a table.
 136    <literal>set_timetravel('mytab', 1)</> will turn TT ON for table mytab.
 137    <literal>set_timetravel('mytab', 0)</> will turn TT OFF for table mytab.
 138    In both cases the old status is reported.  While TT is off, you can modify
 139    the start_date and stop_date columns freely.  Note that the on/off status
 140    is local to the current database session &mdash; fresh sessions will
 141    always start out with TT ON for all tables.
 142   </para>
 143
 144   <para>
 145    <function>get_timetravel()</> returns the TT state for a table without
 146    changing it.
 147   </para>
 148
 149   <para>
 150    There is an example in <filename>timetravel.example</>.
 151   </para>
 152  </sect2>
 153
 154  <sect2>
 155   <title>autoinc.c &mdash; functions for autoincrementing fields</title>
 156
 157   <para>
 158    <function>autoinc()</> is a trigger that stores the next value of
 159    a sequence into an integer field.  This has some overlap with the
 160    built-in <quote>serial column</> feature, but it is not the same:
 161    <function>autoinc()</> will override attempts to substitute a
 162    different field value during inserts, and optionally it can be
 163    used to increment the field during updates, too.
 164   </para>
 165
 166   <para>
 167    To use, create a <literal>BEFORE INSERT</> (or optionally <literal>BEFORE
 168    INSERT OR UPDATE</>) trigger using this function.  Specify two
 169    trigger arguments: the name of the integer column to be modified,
 170    and the name of the sequence object that will supply values.
 171    (Actually, you can specify any number of pairs of such names, if
 172    you'd like to update more than one autoincrementing column.)
 173   </para>
 174
 175   <para>
 176    There is an example in <filename>autoinc.example</>.
 177   </para>
 178
 179  </sect2>
 180
 181  <sect2>
 182   <title>insert_username.c &mdash; functions for tracking who changed a table</title>
 183
 184   <para>
 185    <function>insert_username()</> is a trigger that stores the current
 186    user's name into a text field.  This can be useful for tracking
 187    who last modified a particular row within a table.
 188   </para>
 189
 190   <para>
 191    To use, create a <literal>BEFORE INSERT</> and/or <literal>UPDATE</>
 192    trigger using this function.  Specify a single trigger
 193    argument: the name of the text column to be modified.
 194   </para>
 195
 196   <para>
 197    There is an example in <filename>insert_username.example</>.
 198   </para>
 199
 200  </sect2>
 201
 202  <sect2>
 203   <title>moddatetime.c &mdash; functions for tracking last modification time</title>
 204
 205   <para>
 206    <function>moddatetime()</> is a trigger that stores the current
 207    time into a <type>timestamp</> field.  This can be useful for tracking
 208    the last modification time of a particular row within a table.
 209   </para>
 210
 211   <para>
 212    To use, create a <literal>BEFORE UPDATE</>
 213    trigger using this function.  Specify a single trigger
 214    argument: the name of the <type>timestamp</> column to be modified.
 215   </para>
 216
 217   <para>
 218    There is an example in <filename>moddatetime.example</>.
 219   </para>
 220
 221  </sect2>
 222
 223 </sect1>