1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
|
<!-- doc/src/sgml/contrib-spi.sgml -->
<sect1 id="contrib-spi" xreflabel="spi">
<title>spi — Server Programming Interface features/examples</title>
<indexterm zone="contrib-spi">
<primary>SPI</primary>
<secondary>examples</secondary>
</indexterm>
<para>
The <application>spi</application> module provides several workable examples
of using the <link linkend="spi">Server Programming Interface</link>
(<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.
</para>
<para>
Each of the groups of functions described below is provided as a
separately-installable extension.
</para>
<sect2 id="contrib-spi-refint">
<title>refint — Functions for Implementing Referential Integrity</title>
<para>
<function>check_primary_key()</function> and
<function>check_foreign_key()</function> 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.)
</para>
<para>
<function>check_primary_key()</function> checks the referencing table.
To use, create an <literal>AFTER INSERT OR UPDATE</literal> 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.
</para>
<para>
<function>check_foreign_key()</function> checks the referenced table.
To use, create an <literal>AFTER DELETE OR UPDATE</literal> 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
(<literal>cascade</literal> — to delete the referencing row,
<literal>restrict</literal> — to abort transaction if referencing keys
exist, <literal>setnull</literal> — 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.
</para>
<para>
Note that if these triggers are executed from
another <literal>BEFORE</literal> trigger, they can fail unexpectedly. For
example, if a user inserts row1 and then the <literal>BEFORE</literal>
trigger inserts row2 and calls a trigger with the
<function>check_foreign_key()</function>,
the <function>check_foreign_key()</function>
function will not see row1 and will fail.
</para>
<para>
There are examples in <filename>refint.example</filename>.
</para>
</sect2>
<sect2 id="contrib-spi-autoinc">
<title>autoinc — Functions for Autoincrementing Fields</title>
<para>
<function>autoinc()</function> is a trigger that stores the next value of
a sequence into an integer field. This has some overlap with the
built-in <quote>serial column</quote> feature, but it is not the same.
The trigger will replace the field's value only if that value is
initially zero or null (after the action of the SQL statement that
inserted or updated the row). Also, if the sequence's next value is
zero, <function>nextval()</function> will be called a second time in
order to obtain a non-zero value.
</para>
<para>
To use, create a <literal>BEFORE INSERT</literal> (or optionally <literal>BEFORE
INSERT OR UPDATE</literal>) 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.)
</para>
<para>
There is an example in <filename>autoinc.example</filename>.
</para>
</sect2>
<sect2 id="contrib-spi-insert-username">
<title>insert_username — Functions for Tracking Who Changed a Table</title>
<para>
<function>insert_username()</function> 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.
</para>
<para>
To use, create a <literal>BEFORE INSERT</literal> and/or <literal>UPDATE</literal>
trigger using this function. Specify a single trigger
argument: the name of the text column to be modified.
</para>
<para>
There is an example in <filename>insert_username.example</filename>.
</para>
</sect2>
<sect2 id="contrib-spi-moddatetime">
<title>moddatetime — Functions for Tracking Last Modification Time</title>
<para>
<function>moddatetime()</function> is a trigger that stores the current
time into a <type>timestamp</type> field. This can be useful for tracking
the last modification time of a particular row within a table.
</para>
<para>
To use, create a <literal>BEFORE UPDATE</literal>
trigger using this function. Specify a single trigger
argument: the name of the column to be modified.
The column must be of type <type>timestamp</type> or <type>timestamp with
time zone</type>.
</para>
<para>
There is an example in <filename>moddatetime.example</filename>.
</para>
</sect2>
</sect1>
|
