Debian Codegraph @

doc/src/sgml/contrib-spi.sgml

raw
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 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> &mdash; to delete the referencing row,
86 <literal>restrict</literal> &mdash; to abort transaction if referencing keys
87 exist, <literal>setnull</literal> &mdash; 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 &mdash; 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 &mdash; 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 &mdash; 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>