doc/src/sgml/contrib-spi.sgml

   1 <!-- doc/src/sgml/contrib-spi.sgml -->
   2
   3 <sect1 id="contrib-spi" xreflabel="spi">
   4  <title>spi &mdash; Server Programming Interface features/examples</title>
   5
   6  <indexterm zone="contrib-spi">
   7   <primary>SPI</primary>
   8   <secondary>examples</secondary>
   9  </indexterm>
  10
  11  <para>
  12   The <application>spi</application> module provides several workable examples
  13   of using the <link linkend="spi">Server Programming Interface</link>
  14   (<acronym>SPI</acronym>) and triggers.  While these functions are of
  15   some value in
  16   their own right, they are even more useful as examples to modify for
  17   your own purposes.  The functions are general enough to be used
  18   with any table, but you have to specify table and field names (as described
  19   below) while creating a trigger.
  20  </para>
  21
  22  <para>
  23   Each of the groups of functions described below is provided as a
  24   separately-installable extension.
  25  </para>
  26
  27  <sect2 id="contrib-spi-refint">
  28   <title>refint &mdash; Functions for Implementing Referential Integrity</title>
  29
  30   <para>
  31    <function>check_primary_key()</function> and
  32    <function>check_foreign_key()</function> are used to check foreign key constraints.
  33    (This functionality is long since superseded by the built-in foreign
  34    key mechanism, of course, but the module is still useful as an example.)
  35   </para>
  36
  37   <para>
  38    <function>check_primary_key()</function> checks the referencing table.
  39    To use, create a <literal>BEFORE INSERT OR UPDATE</literal> trigger using this
  40    function on a table referencing another table. Specify as the trigger
  41    arguments: the referencing table's column name(s) which form the foreign
  42    key, the referenced table name, and the column names in the referenced table
  43    which form the primary/unique key.  To handle multiple foreign
  44    keys, create a trigger for each reference.
  45   </para>
  46
  47   <para>
  48    <function>check_foreign_key()</function> checks the referenced table.
  49    To use, create a <literal>BEFORE DELETE OR UPDATE</literal> trigger using this
  50    function on a table referenced by other table(s).  Specify as the trigger
  51    arguments: the number of referencing tables for which the function has to
  52    perform checking, the action if a referencing key is found
  53    (<literal>cascade</literal> &mdash; to delete the referencing row,
  54    <literal>restrict</literal> &mdash; to abort transaction if referencing keys
  55    exist, <literal>setnull</literal> &mdash; to set referencing key fields to null),
  56    the triggered table's column names which form the primary/unique key, then
  57    the referencing table name and column names (repeated for as many
  58    referencing tables as were specified by first argument).  Note that the
  59    primary/unique key columns should be marked NOT NULL and should have a
  60    unique index.
  61   </para>
  62
  63   <para>
  64    There are examples in <filename>refint.example</filename>.
  65   </para>
  66  </sect2>
  67
  68  <sect2 id="contrib-spi-autoinc">
  69   <title>autoinc &mdash; Functions for Autoincrementing Fields</title>
  70
  71   <para>
  72    <function>autoinc()</function> is a trigger that stores the next value of
  73    a sequence into an integer field.  This has some overlap with the
  74    built-in <quote>serial column</quote> feature, but it is not the same:
  75    <function>autoinc()</function> will override attempts to substitute a
  76    different field value during inserts, and optionally it can be
  77    used to increment the field during updates, too.
  78   </para>
  79
  80   <para>
  81    To use, create a <literal>BEFORE INSERT</literal> (or optionally <literal>BEFORE
  82    INSERT OR UPDATE</literal>) trigger using this function.  Specify two
  83    trigger arguments: the name of the integer column to be modified,
  84    and the name of the sequence object that will supply values.
  85    (Actually, you can specify any number of pairs of such names, if
  86    you'd like to update more than one autoincrementing column.)
  87   </para>
  88
  89   <para>
  90    There is an example in <filename>autoinc.example</filename>.
  91   </para>
  92
  93  </sect2>
  94
  95  <sect2 id="contrib-spi-insert-username">
  96   <title>insert_username &mdash; Functions for Tracking Who Changed a Table</title>
  97
  98   <para>
  99    <function>insert_username()</function> is a trigger that stores the current
 100    user's name into a text field.  This can be useful for tracking
 101    who last modified a particular row within a table.
 102   </para>
 103
 104   <para>
 105    To use, create a <literal>BEFORE INSERT</literal> and/or <literal>UPDATE</literal>
 106    trigger using this function.  Specify a single trigger
 107    argument: the name of the text column to be modified.
 108   </para>
 109
 110   <para>
 111    There is an example in <filename>insert_username.example</filename>.
 112   </para>
 113
 114  </sect2>
 115
 116  <sect2 id="contrib-spi-moddatetime">
 117   <title>moddatetime &mdash; Functions for Tracking Last Modification Time</title>
 118
 119   <para>
 120    <function>moddatetime()</function> is a trigger that stores the current
 121    time into a <type>timestamp</type> field.  This can be useful for tracking
 122    the last modification time of a particular row within a table.
 123   </para>
 124
 125   <para>
 126    To use, create a <literal>BEFORE UPDATE</literal>
 127    trigger using this function.  Specify a single trigger
 128    argument: the name of the column to be modified.
 129    The column must be of type <type>timestamp</type> or <type>timestamp with
 130    time zone</type>.
 131   </para>
 132
 133   <para>
 134    There is an example in <filename>moddatetime.example</filename>.
 135   </para>
 136
 137  </sect2>
 138
 139 </sect1>