| 1 |
<!-- doc/src/sgml/contrib-spi.sgml --> |
| 2 |
|
| 3 |
<sect1 id="contrib-spi" xreflabel="spi"> |
| 4 |
<title>spi — 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 — 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 |
This module will be removed in <productname>PostgreSQL</productname> 20.) |
| 36 |
</para> |
| 37 |
|
| 38 |
<note> |
| 39 |
<para> |
| 40 |
<filename>refint</filename> requires a |
| 41 |
<link linkend="ddl-schemas-patterns">secure schema usage pattern</link> and |
| 42 |
data types where the equality operator is named <literal>=</literal>. |
| 43 |
</para> |
| 44 |
</note> |
| 45 |
|
| 46 |
<para> |
| 47 |
<function>check_primary_key()</function> checks the referencing table. |
| 48 |
To use, create an <literal>AFTER INSERT OR UPDATE</literal> trigger using this |
| 49 |
function on a table referencing another table. Specify as the trigger |
| 50 |
arguments: the referencing table's column name(s) which form the foreign |
| 51 |
key, the referenced table name, and the column names in the referenced table |
| 52 |
which form the primary/unique key. To handle multiple foreign |
| 53 |
keys, create a trigger for each reference. |
| 54 |
</para> |
| 55 |
|
| 56 |
<note> |
| 57 |
<para> |
| 58 |
The <emphasis>referenced</emphasis> table name and column name arguments to |
| 59 |
<function>check_primary_key()</function> are copied as-is into internally |
| 60 |
generated SQL statements and therefore must be double-quoted by the user as |
| 61 |
necessary in the <command>CREATE TRIGGER</command> command. See |
| 62 |
<xref linkend="sql-syntax-identifiers"/> for more information about quoting |
| 63 |
SQL identifiers. Conversely, the <emphasis>referencing</emphasis> table |
| 64 |
column name arguments should not be double quoted. See the following mock |
| 65 |
example of proper use of <function>check_primary_key()</function>: |
| 66 |
<programlisting> |
| 67 |
CREATE TRIGGER mytrigger |
| 68 |
AFTER INSERT OR UPDATE ON referencing_table |
| 69 |
FOR EACH ROW EXECUTE PROCEDURE |
| 70 |
check_primary_key ( |
| 71 |
'column A', 'column B', -- referencing table columns |
| 72 |
'myschema."referenced table"', -- referenced table |
| 73 |
'"column A"', '"column B"' -- referenced table columns |
| 74 |
); |
| 75 |
</programlisting> |
| 76 |
</para> |
| 77 |
</note> |
| 78 |
|
| 79 |
<para> |
| 80 |
<function>check_foreign_key()</function> checks the referenced table. |
| 81 |
To use, create an <literal>AFTER DELETE OR UPDATE</literal> trigger using this |
| 82 |
function on a table referenced by other table(s). Specify as the trigger |
| 83 |
arguments: the number of referencing tables for which the function has to |
| 84 |
perform checking, the action if a referencing key is found |
| 85 |
(<literal>cascade</literal> — to delete the referencing row, |
| 86 |
<literal>restrict</literal> — to abort transaction if referencing keys |
| 87 |
exist, <literal>setnull</literal> — to set referencing key fields to null), |
| 88 |
the referenced table's column names which form the primary/unique key, then |
| 89 |
the referencing table name and column names (repeated for as many |
| 90 |
referencing tables as were specified by first argument). Note that the |
| 91 |
primary/unique key columns should be marked NOT NULL and should have a |
| 92 |
unique index. |
| 93 |
</para> |
| 94 |
|
| 95 |
<note> |
| 96 |
<para> |
| 97 |
The <emphasis>referencing</emphasis> table name and column name arguments |
| 98 |
to <function>check_foreign_key()</function> are copied as-is into |
| 99 |
internally generated SQL statements and therefore must be double-quoted by |
| 100 |
the user as necessary in the <command>CREATE TRIGGER</command> command. |
| 101 |
See <xref linkend="sql-syntax-identifiers"/> for more information about |
| 102 |
quoting SQL identifiers. Conversely, the <emphasis>referenced</emphasis> |
| 103 |
table column name arguments should not be double quoted. See the following |
| 104 |
mock example of proper use of <function>check_foreign_key()</function>: |
| 105 |
<programlisting> |
| 106 |
CREATE TRIGGER mytrigger |
| 107 |
AFTER DELETE OR UPDATE ON referenced_table |
| 108 |
FOR EACH ROW EXECUTE PROCEDURE |
| 109 |
check_foreign_key ( |
| 110 |
1, -- number of referencing tables |
| 111 |
'cascade', -- action |
| 112 |
'column A', 'column B', -- referenced table columns |
| 113 |
'myschema."referencing table"', -- referencing table |
| 114 |
'"column A"', '"column B"' -- referencing table columns |
| 115 |
); |
| 116 |
</programlisting> |
| 117 |
</para> |
| 118 |
</note> |
| 119 |
|
| 120 |
<para> |
| 121 |
Note that if these triggers are executed from |
| 122 |
another <literal>BEFORE</literal> trigger, they can fail unexpectedly. For |
| 123 |
example, if a user inserts row1 and then the <literal>BEFORE</literal> |
| 124 |
trigger inserts row2 and calls a trigger with the |
| 125 |
<function>check_foreign_key()</function>, |
| 126 |
the <function>check_foreign_key()</function> |
| 127 |
function will not see row1 and will fail. |
| 128 |
</para> |
| 129 |
|
| 130 |
<para> |
| 131 |
There are examples in <filename>refint.example</filename>. |
| 132 |
</para> |
| 133 |
</sect2> |
| 134 |
|
| 135 |
<sect2 id="contrib-spi-autoinc"> |
| 136 |
<title>autoinc — Functions for Autoincrementing Fields</title> |
| 137 |
|
| 138 |
<para> |
| 139 |
<function>autoinc()</function> is a trigger that stores the next value of |
| 140 |
a sequence into an integer field. This has some overlap with the |
| 141 |
built-in <quote>serial column</quote> feature, but it is not the same. |
| 142 |
The trigger will replace the field's value only if that value is |
| 143 |
initially zero or null (after the action of the SQL statement that |
| 144 |
inserted or updated the row). Also, if the sequence's next value is |
| 145 |
zero, <function>nextval()</function> will be called a second time in |
| 146 |
order to obtain a non-zero value. |
| 147 |
</para> |
| 148 |
|
| 149 |
<para> |
| 150 |
To use, create a <literal>BEFORE INSERT</literal> (or optionally <literal>BEFORE |
| 151 |
INSERT OR UPDATE</literal>) trigger using this function. Specify two |
| 152 |
trigger arguments: the name of the integer column to be modified, |
| 153 |
and the name of the sequence object that will supply values. |
| 154 |
(Actually, you can specify any number of pairs of such names, if |
| 155 |
you'd like to update more than one autoincrementing column.) |
| 156 |
</para> |
| 157 |
|
| 158 |
<para> |
| 159 |
There is an example in <filename>autoinc.example</filename>. |
| 160 |
</para> |
| 161 |
|
| 162 |
</sect2> |
| 163 |
|
| 164 |
<sect2 id="contrib-spi-insert-username"> |
| 165 |
<title>insert_username — Functions for Tracking Who Changed a Table</title> |
| 166 |
|
| 167 |
<para> |
| 168 |
<function>insert_username()</function> is a trigger that stores the current |
| 169 |
user's name into a text field. This can be useful for tracking |
| 170 |
who last modified a particular row within a table. |
| 171 |
</para> |
| 172 |
|
| 173 |
<para> |
| 174 |
To use, create a <literal>BEFORE INSERT</literal> and/or <literal>UPDATE</literal> |
| 175 |
trigger using this function. Specify a single trigger |
| 176 |
argument: the name of the text column to be modified. |
| 177 |
</para> |
| 178 |
|
| 179 |
<para> |
| 180 |
There is an example in <filename>insert_username.example</filename>. |
| 181 |
</para> |
| 182 |
|
| 183 |
</sect2> |
| 184 |
|
| 185 |
<sect2 id="contrib-spi-moddatetime"> |
| 186 |
<title>moddatetime — Functions for Tracking Last Modification Time</title> |
| 187 |
|
| 188 |
<para> |
| 189 |
<function>moddatetime()</function> is a trigger that stores the current |
| 190 |
time into a <type>timestamp</type> field. This can be useful for tracking |
| 191 |
the last modification time of a particular row within a table. |
| 192 |
</para> |
| 193 |
|
| 194 |
<para> |
| 195 |
To use, create a <literal>BEFORE UPDATE</literal> |
| 196 |
trigger using this function. Specify a single trigger |
| 197 |
argument: the name of the column to be modified. |
| 198 |
The column must be of type <type>timestamp</type> or <type>timestamp with |
| 199 |
time zone</type>. |
| 200 |
</para> |
| 201 |
|
| 202 |
<para> |
| 203 |
There is an example in <filename>moddatetime.example</filename>. |
| 204 |
</para> |
| 205 |
|
| 206 |
</sect2> |
| 207 |
|
| 208 |
</sect1> |