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
|
<?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>47.6. Examples</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="spi-visibility.html" title="47.5. Visibility of Data Changes" /><link rel="next" href="bgworker.html" title="Chapter 48. Background Worker Processes" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">47.6. Examples</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="spi-visibility.html" title="47.5. Visibility of Data Changes">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="spi.html" title="Chapter 47. Server Programming Interface">Up</a></td><th width="60%" align="center">Chapter 47. Server Programming Interface</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 15.4 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="bgworker.html" title="Chapter 48. Background Worker Processes">Next</a></td></tr></table><hr /></div><div class="sect1" id="SPI-EXAMPLES"><div class="titlepage"><div><div><h2 class="title" style="clear: both">47.6. Examples</h2></div></div></div><p>
This section contains a very simple example of SPI usage. The
C function <code class="function">execq</code> takes an SQL command as its
first argument and a row count as its second, executes the command
using <code class="function">SPI_exec</code> and returns the number of rows
that were processed by the command. You can find more complex
examples for SPI in the source tree in
<code class="filename">src/test/regress/regress.c</code> and in the
<a class="xref" href="contrib-spi.html" title="F.41. spi">spi</a> module.
</p><pre class="programlisting">
#include "postgres.h"
#include "executor/spi.h"
#include "utils/builtins.h"
PG_MODULE_MAGIC;
PG_FUNCTION_INFO_V1(execq);
Datum
execq(PG_FUNCTION_ARGS)
{
char *command;
int cnt;
int ret;
uint64 proc;
/* Convert given text object to a C string */
command = text_to_cstring(PG_GETARG_TEXT_PP(0));
cnt = PG_GETARG_INT32(1);
SPI_connect();
ret = SPI_exec(command, cnt);
proc = SPI_processed;
/*
* If some rows were fetched, print them via elog(INFO).
*/
if (ret > 0 && SPI_tuptable != NULL)
{
SPITupleTable *tuptable = SPI_tuptable;
TupleDesc tupdesc = tuptable->tupdesc;
char buf[8192];
uint64 j;
for (j = 0; j < tuptable->numvals; j++)
{
HeapTuple tuple = tuptable->vals[j];
int i;
for (i = 1, buf[0] = 0; i <= tupdesc->natts; i++)
snprintf(buf + strlen(buf), sizeof(buf) - strlen(buf), " %s%s",
SPI_getvalue(tuple, tupdesc, i),
(i == tupdesc->natts) ? " " : " |");
elog(INFO, "EXECQ: %s", buf);
}
}
SPI_finish();
pfree(command);
PG_RETURN_INT64(proc);
}
</pre><p>
This is how you declare the function after having compiled it into
a shared library (details are in <a class="xref" href="xfunc-c.html#DFUNC" title="38.10.5. Compiling and Linking Dynamically-Loaded Functions">Section 38.10.5</a>.):
</p><pre class="programlisting">
CREATE FUNCTION execq(text, integer) RETURNS int8
AS '<em class="replaceable"><code>filename</code></em>'
LANGUAGE C STRICT;
</pre><p>
</p><p>
Here is a sample session:
</p><pre class="programlisting">
=> SELECT execq('CREATE TABLE a (x integer)', 0);
execq
-------
0
(1 row)
=> INSERT INTO a VALUES (execq('INSERT INTO a VALUES (0)', 0));
INSERT 0 1
=> SELECT execq('SELECT * FROM a', 0);
INFO: EXECQ: 0 <em class="lineannotation"><span class="lineannotation">-- inserted by execq</span></em>
INFO: EXECQ: 1 <em class="lineannotation"><span class="lineannotation">-- returned by execq and inserted by upper INSERT</span></em>
execq
-------
2
(1 row)
=> SELECT execq('INSERT INTO a SELECT x + 2 FROM a RETURNING *', 1);
INFO: EXECQ: 2 <em class="lineannotation"><span class="lineannotation">-- 0 + 2, then execution was stopped by count</span></em>
execq
-------
1
(1 row)
=> SELECT execq('SELECT * FROM a', 10);
INFO: EXECQ: 0
INFO: EXECQ: 1
INFO: EXECQ: 2
execq
-------
3 <em class="lineannotation"><span class="lineannotation">-- 10 is the max value only, 3 is the real number of rows</span></em>
(1 row)
=> SELECT execq('INSERT INTO a SELECT x + 10 FROM a', 1);
execq
-------
3 <em class="lineannotation"><span class="lineannotation">-- all rows processed; count does not stop it, because nothing is returned</span></em>
(1 row)
=> SELECT * FROM a;
x
----
0
1
2
10
11
12
(6 rows)
=> DELETE FROM a;
DELETE 6
=> INSERT INTO a VALUES (execq('SELECT * FROM a', 0) + 1);
INSERT 0 1
=> SELECT * FROM a;
x
---
1 <em class="lineannotation"><span class="lineannotation">-- 0 (no rows in a) + 1</span></em>
(1 row)
=> INSERT INTO a VALUES (execq('SELECT * FROM a', 0) + 1);
INFO: EXECQ: 1
INSERT 0 1
=> SELECT * FROM a;
x
---
1
2 <em class="lineannotation"><span class="lineannotation">-- 1 (there was one row in a) + 1</span></em>
(2 rows)
<em class="lineannotation"><span class="lineannotation">-- This demonstrates the data changes visibility rule.</span></em>
<em class="lineannotation"><span class="lineannotation">-- execq is called twice and sees different numbers of rows each time:</span></em>
=> INSERT INTO a SELECT execq('SELECT * FROM a', 0) * x FROM a;
INFO: EXECQ: 1 <em class="lineannotation"><span class="lineannotation">-- results from first execq</span></em>
INFO: EXECQ: 2
INFO: EXECQ: 1 <em class="lineannotation"><span class="lineannotation">-- results from second execq</span></em>
INFO: EXECQ: 2
INFO: EXECQ: 2
INSERT 0 2
=> SELECT * FROM a;
x
---
1
2
2 <em class="lineannotation"><span class="lineannotation">-- 2 rows * 1 (x in first row)</span></em>
6 <em class="lineannotation"><span class="lineannotation">-- 3 rows (2 + 1 just inserted) * 2 (x in second row)</span></em>
(4 rows)
</pre><p>
</p></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="spi-visibility.html" title="47.5. Visibility of Data Changes">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="spi.html" title="Chapter 47. Server Programming Interface">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="bgworker.html" title="Chapter 48. Background Worker Processes">Next</a></td></tr><tr><td width="40%" align="left" valign="top">47.5. Visibility of Data Changes </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 15.4 Documentation">Home</a></td><td width="40%" align="right" valign="top"> Chapter 48. Background Worker Processes</td></tr></table></div></body></html>
|
