<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>F.36. spi</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="sepgsql.html" title="F.35. sepgsql" /><link rel="next" href="sslinfo.html" title="F.37. sslinfo" /></head><body><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">F.36. spi</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="sepgsql.html" title="F.35. sepgsql">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="contrib.html" title="Appendix F. Additional Supplied Modules">Up</a></td><th width="60%" align="center">Appendix F. Additional Supplied Modules</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 11.5 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="sslinfo.html" title="F.37. sslinfo">Next</a></td></tr></table><hr></hr></div><div class="sect1" id="CONTRIB-SPI"><div class="titlepage"><div><div><h2 class="title" style="clear: both">F.36. spi</h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="contrib-spi.html#id-1.11.7.45.5">F.36.1. refint — Functions for Implementing Referential Integrity</a></span></dt><dt><span class="sect2"><a href="contrib-spi.html#id-1.11.7.45.6">F.36.2. timetravel — Functions for Implementing Time Travel</a></span></dt><dt><span class="sect2"><a href="contrib-spi.html#id-1.11.7.45.7">F.36.3. autoinc — Functions for Autoincrementing Fields</a></span></dt><dt><span class="sect2"><a href="contrib-spi.html#id-1.11.7.45.8">F.36.4. insert_username — Functions for Tracking Who Changed a Table</a></span></dt><dt><span class="sect2"><a href="contrib-spi.html#id-1.11.7.45.9">F.36.5. moddatetime — Functions for Tracking Last Modification Time</a></span></dt></dl></div><a id="id-1.11.7.45.2" class="indexterm"></a><p>
The <span class="application">spi</span> module provides several workable examples
of using the <a class="link" href="spi.html" title="Chapter 47. Server Programming Interface">Server Programming Interface</a>
(<acronym class="acronym">SPI</acronym>) and triggers. While these functions are of
some value in
their own right, they are even more useful as examples to modify for
your own purposes. The functions are general enough to be used
with any table, but you have to specify table and field names (as described
below) while creating a trigger.
</p><p>
Each of the groups of functions described below is provided as a
separately-installable extension.
</p><div class="sect2" id="id-1.11.7.45.5"><div class="titlepage"><div><div><h3 class="title">F.36.1. refint — Functions for Implementing Referential Integrity</h3></div></div></div><p>
<code class="function">check_primary_key()</code> and
<code class="function">check_foreign_key()</code> are used to check foreign key constraints.
(This functionality is long since superseded by the built-in foreign
key mechanism, of course, but the module is still useful as an example.)
</p><p>
<code class="function">check_primary_key()</code> checks the referencing table.
To use, create a <code class="literal">BEFORE INSERT OR UPDATE</code> trigger using this
function on a table referencing another table. Specify as the trigger
arguments: the referencing table's column name(s) which form the foreign
key, the referenced table name, and the column names in the referenced table
which form the primary/unique key. To handle multiple foreign
keys, create a trigger for each reference.
</p><p>
<code class="function">check_foreign_key()</code> checks the referenced table.
To use, create a <code class="literal">BEFORE DELETE OR UPDATE</code> trigger using this
function on a table referenced by other table(s). Specify as the trigger
arguments: the number of referencing tables for which the function has to
perform checking, the action if a referencing key is found
(<code class="literal">cascade</code> — to delete the referencing row,
<code class="literal">restrict</code> — to abort transaction if referencing keys
exist, <code class="literal">setnull</code> — to set referencing key fields to null),
the triggered table's column names which form the primary/unique key, then
the referencing table name and column names (repeated for as many
referencing tables as were specified by first argument). Note that the
primary/unique key columns should be marked NOT NULL and should have a
unique index.
</p><p>
There are examples in <code class="filename">refint.example</code>.
</p></div><div class="sect2" id="id-1.11.7.45.6"><div class="titlepage"><div><div><h3 class="title">F.36.2. timetravel — Functions for Implementing Time Travel</h3></div></div></div><p>
Long ago, <span class="productname">PostgreSQL</span> had a built-in time travel feature
that kept the insert and delete times for each tuple. This can be
emulated using these functions. To use these functions,
you must add to a table two columns of <code class="type">abstime</code> type to store
the date when a tuple was inserted (start_date) and changed/deleted
(stop_date):
</p><pre class="programlisting">
CREATE TABLE mytab (
... ...
start_date abstime,
stop_date abstime
... ...
);
</pre><p>
The columns can be named whatever you like, but in this discussion
we'll call them start_date and stop_date.
</p><p>
When a new row is inserted, start_date should normally be set to
current time, and stop_date to <code class="literal">infinity</code>. The trigger
will automatically substitute these values if the inserted data
contains nulls in these columns. Generally, inserting explicit
non-null data in these columns should only be done when re-loading
dumped data.
</p><p>
Tuples with stop_date equal to <code class="literal">infinity</code> are <span class="quote">“<span class="quote">valid
now</span>”</span>, and can be modified. Tuples with a finite stop_date cannot
be modified anymore — the trigger will prevent it. (If you need
to do that, you can turn off time travel as shown below.)
</p><p>
For a modifiable row, on update only the stop_date in the tuple being
updated will be changed (to current time) and a new tuple with the modified
data will be inserted. Start_date in this new tuple will be set to current
time and stop_date to <code class="literal">infinity</code>.
</p><p>
A delete does not actually remove the tuple but only sets its stop_date
to current time.
</p><p>
To query for tuples <span class="quote">“<span class="quote">valid now</span>”</span>, include
<code class="literal">stop_date = 'infinity'</code> in the query's WHERE condition.
(You might wish to incorporate that in a view.) Similarly, you can
query for tuples valid at any past time with suitable conditions on
start_date and stop_date.
</p><p>
<code class="function">timetravel()</code> is the general trigger function that supports
this behavior. Create a <code class="literal">BEFORE INSERT OR UPDATE OR DELETE</code>
trigger using this function on each time-traveled table. Specify two
trigger arguments: the actual
names of the start_date and stop_date columns.
Optionally, you can specify one to three more arguments, which must refer
to columns of type <code class="type">text</code>. The trigger will store the name of
the current user into the first of these columns during INSERT, the
second column during UPDATE, and the third during DELETE.
</p><p>
<code class="function">set_timetravel()</code> allows you to turn time-travel on or off for
a table.
<code class="literal">set_timetravel('mytab', 1)</code> will turn TT ON for table <code class="literal">mytab</code>.
<code class="literal">set_timetravel('mytab', 0)</code> will turn TT OFF for table <code class="literal">mytab</code>.
In both cases the old status is reported. While TT is off, you can modify
the start_date and stop_date columns freely. Note that the on/off status
is local to the current database session — fresh sessions will
always start out with TT ON for all tables.
</p><p>
<code class="function">get_timetravel()</code> returns the TT state for a table without
changing it.
</p><p>
There is an example in <code class="filename">timetravel.example</code>.
</p></div><div class="sect2" id="id-1.11.7.45.7"><div class="titlepage"><div><div><h3 class="title">F.36.3. autoinc — Functions for Autoincrementing Fields</h3></div></div></div><p>
<code class="function">autoinc()</code> is a trigger that stores the next value of
a sequence into an integer field. This has some overlap with the
built-in <span class="quote">“<span class="quote">serial column</span>”</span> feature, but it is not the same:
<code class="function">autoinc()</code> will override attempts to substitute a
different field value during inserts, and optionally it can be
used to increment the field during updates, too.
</p><p>
To use, create a <code class="literal">BEFORE INSERT</code> (or optionally <code class="literal">BEFORE
INSERT OR UPDATE</code>) trigger using this function. Specify two
trigger arguments: the name of the integer column to be modified,
and the name of the sequence object that will supply values.
(Actually, you can specify any number of pairs of such names, if
you'd like to update more than one autoincrementing column.)
</p><p>
There is an example in <code class="filename">autoinc.example</code>.
</p></div><div class="sect2" id="id-1.11.7.45.8"><div class="titlepage"><div><div><h3 class="title">F.36.4. insert_username — Functions for Tracking Who Changed a Table</h3></div></div></div><p>
<code class="function">insert_username()</code> is a trigger that stores the current
user's name into a text field. This can be useful for tracking
who last modified a particular row within a table.
</p><p>
To use, create a <code class="literal">BEFORE INSERT</code> and/or <code class="literal">UPDATE</code>
trigger using this function. Specify a single trigger
argument: the name of the text column to be modified.
</p><p>
There is an example in <code class="filename">insert_username.example</code>.
</p></div><div class="sect2" id="id-1.11.7.45.9"><div class="titlepage"><div><div><h3 class="title">F.36.5. moddatetime — Functions for Tracking Last Modification Time</h3></div></div></div><p>
<code class="function">moddatetime()</code> is a trigger that stores the current
time into a <code class="type">timestamp</code> field. This can be useful for tracking
the last modification time of a particular row within a table.
</p><p>
To use, create a <code class="literal">BEFORE UPDATE</code>
trigger using this function. Specify a single trigger
argument: the name of the column to be modified.
The column must be of type <code class="type">timestamp</code> or <code class="type">timestamp with
time zone</code>.
</p><p>
There is an example in <code class="filename">moddatetime.example</code>.
</p></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="sepgsql.html">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="contrib.html">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="sslinfo.html">Next</a></td></tr><tr><td width="40%" align="left" valign="top">F.35. sepgsql </td><td width="20%" align="center"><a accesskey="h" href="index.html">Home</a></td><td width="40%" align="right" valign="top"> F.37. sslinfo</td></tr></table></div></body></html>
