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
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
|
<?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>39.4. A Complete Trigger Example</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="trigger-interface.html" title="39.3. Writing Trigger Functions in C" /><link rel="next" href="event-triggers.html" title="Chapter 40. Event Triggers" /></head><body id="docContent" class="container-fluid col-10"><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">39.4. A Complete Trigger Example</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="trigger-interface.html" title="39.3. Writing Trigger Functions in C">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="triggers.html" title="Chapter 39. Triggers">Up</a></td><th width="60%" align="center">Chapter 39. Triggers</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 14.5 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="event-triggers.html" title="Chapter 40. Event Triggers">Next</a></td></tr></table><hr></hr></div><div class="sect1" id="TRIGGER-EXAMPLE"><div class="titlepage"><div><div><h2 class="title" style="clear: both">39.4. A Complete Trigger Example</h2></div></div></div><p>
Here is a very simple example of a trigger function written in C.
(Examples of triggers written in procedural languages can be found
in the documentation of the procedural languages.)
</p><p>
The function <code class="function">trigf</code> reports the number of rows in the
table <code class="structname">ttest</code> and skips the actual operation if the
command attempts to insert a null value into the column
<code class="structfield">x</code>. (So the trigger acts as a not-null constraint but
doesn't abort the transaction.)
</p><p>
First, the table definition:
</p><pre class="programlisting">
CREATE TABLE ttest (
x integer
);
</pre><p>
</p><p>
This is the source code of the trigger function:
</p><pre class="programlisting">
#include "postgres.h"
#include "fmgr.h"
#include "executor/spi.h" /* this is what you need to work with SPI */
#include "commands/trigger.h" /* ... triggers ... */
#include "utils/rel.h" /* ... and relations */
PG_MODULE_MAGIC;
PG_FUNCTION_INFO_V1(trigf);
Datum
trigf(PG_FUNCTION_ARGS)
{
TriggerData *trigdata = (TriggerData *) fcinfo->context;
TupleDesc tupdesc;
HeapTuple rettuple;
char *when;
bool checknull = false;
bool isnull;
int ret, i;
/* make sure it's called as a trigger at all */
if (!CALLED_AS_TRIGGER(fcinfo))
elog(ERROR, "trigf: not called by trigger manager");
/* tuple to return to executor */
if (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event))
rettuple = trigdata->tg_newtuple;
else
rettuple = trigdata->tg_trigtuple;
/* check for null values */
if (!TRIGGER_FIRED_BY_DELETE(trigdata->tg_event)
&& TRIGGER_FIRED_BEFORE(trigdata->tg_event))
checknull = true;
if (TRIGGER_FIRED_BEFORE(trigdata->tg_event))
when = "before";
else
when = "after ";
tupdesc = trigdata->tg_relation->rd_att;
/* connect to SPI manager */
if ((ret = SPI_connect()) < 0)
elog(ERROR, "trigf (fired %s): SPI_connect returned %d", when, ret);
/* get number of rows in table */
ret = SPI_exec("SELECT count(*) FROM ttest", 0);
if (ret < 0)
elog(ERROR, "trigf (fired %s): SPI_exec returned %d", when, ret);
/* count(*) returns int8, so be careful to convert */
i = DatumGetInt64(SPI_getbinval(SPI_tuptable->vals[0],
SPI_tuptable->tupdesc,
1,
&isnull));
elog (INFO, "trigf (fired %s): there are %d rows in ttest", when, i);
SPI_finish();
if (checknull)
{
SPI_getbinval(rettuple, tupdesc, 1, &isnull);
if (isnull)
rettuple = NULL;
}
return PointerGetDatum(rettuple);
}
</pre><p>
</p><p>
After you have compiled the source code (see <a class="xref" href="xfunc-c.html#DFUNC" title="38.10.5. Compiling and Linking Dynamically-Loaded Functions">Section 38.10.5</a>), declare the function and the triggers:
</p><pre class="programlisting">
CREATE FUNCTION trigf() RETURNS trigger
AS '<em class="replaceable"><code>filename</code></em>'
LANGUAGE C;
CREATE TRIGGER tbefore BEFORE INSERT OR UPDATE OR DELETE ON ttest
FOR EACH ROW EXECUTE FUNCTION trigf();
CREATE TRIGGER tafter AFTER INSERT OR UPDATE OR DELETE ON ttest
FOR EACH ROW EXECUTE FUNCTION trigf();
</pre><p>
</p><p>
Now you can test the operation of the trigger:
</p><pre class="screen">
=> INSERT INTO ttest VALUES (NULL);
INFO: trigf (fired before): there are 0 rows in ttest
INSERT 0 0
-- Insertion skipped and AFTER trigger is not fired
=> SELECT * FROM ttest;
x
---
(0 rows)
=> INSERT INTO ttest VALUES (1);
INFO: trigf (fired before): there are 0 rows in ttest
INFO: trigf (fired after ): there are 1 rows in ttest
^^^^^^^^
remember what we said about visibility.
INSERT 167793 1
vac=> SELECT * FROM ttest;
x
---
1
(1 row)
=> INSERT INTO ttest SELECT x * 2 FROM ttest;
INFO: trigf (fired before): there are 1 rows in ttest
INFO: trigf (fired after ): there are 2 rows in ttest
^^^^^^
remember what we said about visibility.
INSERT 167794 1
=> SELECT * FROM ttest;
x
---
1
2
(2 rows)
=> UPDATE ttest SET x = NULL WHERE x = 2;
INFO: trigf (fired before): there are 2 rows in ttest
UPDATE 0
=> UPDATE ttest SET x = 4 WHERE x = 2;
INFO: trigf (fired before): there are 2 rows in ttest
INFO: trigf (fired after ): there are 2 rows in ttest
UPDATE 1
vac=> SELECT * FROM ttest;
x
---
1
4
(2 rows)
=> DELETE FROM ttest;
INFO: trigf (fired before): there are 2 rows in ttest
INFO: trigf (fired before): there are 1 rows in ttest
INFO: trigf (fired after ): there are 0 rows in ttest
INFO: trigf (fired after ): there are 0 rows in ttest
^^^^^^
remember what we said about visibility.
DELETE 2
=> SELECT * FROM ttest;
x
---
(0 rows)
</pre><p>
</p><p>
There are more complex examples in
<code class="filename">src/test/regress/regress.c</code> and
in <a class="xref" href="contrib-spi.html" title="F.38. spi">spi</a>.
</p></div><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navfooter"><hr></hr><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="trigger-interface.html" title="39.3. Writing Trigger Functions in C">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="triggers.html" title="Chapter 39. Triggers">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="event-triggers.html" title="Chapter 40. Event Triggers">Next</a></td></tr><tr><td width="40%" align="left" valign="top">39.3. Writing Trigger Functions in C </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 14.5 Documentation">Home</a></td><td width="40%" align="right" valign="top"> Chapter 40. Event Triggers</td></tr></table></div></body></html>
|
