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
|
<?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>SPI_execute</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-spi-finish.html" title="SPI_finish" /><link rel="next" href="spi-spi-exec.html" title="SPI_exec" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">SPI_execute</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="spi-spi-finish.html" title="SPI_finish">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="spi-interface.html" title="47.1. Interface Functions">Up</a></td><th width="60%" align="center">47.1. Interface Functions</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="spi-spi-exec.html" title="SPI_exec">Next</a></td></tr></table><hr /></div><div class="refentry" id="SPI-SPI-EXECUTE"><div class="titlepage"></div><a id="id-1.8.12.8.4.1" class="indexterm"></a><div class="refnamediv"><h2><span class="refentrytitle">SPI_execute</span></h2><p>SPI_execute — execute a command</p></div><div class="refsynopsisdiv"><h2>Synopsis</h2><pre class="synopsis">
int SPI_execute(const char * <em class="parameter"><code>command</code></em>, bool <em class="parameter"><code>read_only</code></em>, long <em class="parameter"><code>count</code></em>)
</pre></div><div class="refsect1" id="id-1.8.12.8.4.5"><h2>Description</h2><p>
<code class="function">SPI_execute</code> executes the specified SQL command
for <em class="parameter"><code>count</code></em> rows. If <em class="parameter"><code>read_only</code></em>
is <code class="literal">true</code>, the command must be read-only, and execution overhead
is somewhat reduced.
</p><p>
This function can only be called from a connected C function.
</p><p>
If <em class="parameter"><code>count</code></em> is zero then the command is executed
for all rows that it applies to. If <em class="parameter"><code>count</code></em>
is greater than zero, then no more than <em class="parameter"><code>count</code></em> rows
will be retrieved; execution stops when the count is reached, much like
adding a <code class="literal">LIMIT</code> clause to the query. For example,
</p><pre class="programlisting">
SPI_execute("SELECT * FROM foo", true, 5);
</pre><p>
will retrieve at most 5 rows from the table. Note that such a limit
is only effective when the command actually returns rows. For example,
</p><pre class="programlisting">
SPI_execute("INSERT INTO foo SELECT * FROM bar", false, 5);
</pre><p>
inserts all rows from <code class="structname">bar</code>, ignoring the
<em class="parameter"><code>count</code></em> parameter. However, with
</p><pre class="programlisting">
SPI_execute("INSERT INTO foo SELECT * FROM bar RETURNING *", false, 5);
</pre><p>
at most 5 rows would be inserted, since execution would stop after the
fifth <code class="literal">RETURNING</code> result row is retrieved.
</p><p>
You can pass multiple commands in one string;
<code class="function">SPI_execute</code> returns the
result for the command executed last. The <em class="parameter"><code>count</code></em>
limit applies to each command separately (even though only the last
result will actually be returned). The limit is not applied to any
hidden commands generated by rules.
</p><p>
When <em class="parameter"><code>read_only</code></em> is <code class="literal">false</code>,
<code class="function">SPI_execute</code> increments the command
counter and computes a new <em class="firstterm">snapshot</em> before executing each
command in the string. The snapshot does not actually change if the
current transaction isolation level is <code class="literal">SERIALIZABLE</code> or <code class="literal">REPEATABLE READ</code>, but in
<code class="literal">READ COMMITTED</code> mode the snapshot update allows each command to
see the results of newly committed transactions from other sessions.
This is essential for consistent behavior when the commands are modifying
the database.
</p><p>
When <em class="parameter"><code>read_only</code></em> is <code class="literal">true</code>,
<code class="function">SPI_execute</code> does not update either the snapshot
or the command counter, and it allows only plain <code class="command">SELECT</code>
commands to appear in the command string. The commands are executed
using the snapshot previously established for the surrounding query.
This execution mode is somewhat faster than the read/write mode due
to eliminating per-command overhead. It also allows genuinely
<em class="firstterm">stable</em> functions to be built: since successive executions
will all use the same snapshot, there will be no change in the results.
</p><p>
It is generally unwise to mix read-only and read-write commands within
a single function using SPI; that could result in very confusing behavior,
since the read-only queries would not see the results of any database
updates done by the read-write queries.
</p><p>
The actual number of rows for which the (last) command was executed
is returned in the global variable <code class="varname">SPI_processed</code>.
If the return value of the function is <code class="symbol">SPI_OK_SELECT</code>,
<code class="symbol">SPI_OK_INSERT_RETURNING</code>,
<code class="symbol">SPI_OK_DELETE_RETURNING</code>, or
<code class="symbol">SPI_OK_UPDATE_RETURNING</code>,
then you can use the
global pointer <code class="literal">SPITupleTable *SPI_tuptable</code> to
access the result rows. Some utility commands (such as
<code class="command">EXPLAIN</code>) also return row sets, and <code class="literal">SPI_tuptable</code>
will contain the result in these cases too. Some utility commands
(<code class="command">COPY</code>, <code class="command">CREATE TABLE AS</code>) don't return a row set, so
<code class="literal">SPI_tuptable</code> is NULL, but they still return the number of
rows processed in <code class="varname">SPI_processed</code>.
</p><p>
The structure <code class="structname">SPITupleTable</code> is defined
thus:
</p><pre class="programlisting">
typedef struct SPITupleTable
{
/* Public members */
TupleDesc tupdesc; /* tuple descriptor */
HeapTuple *vals; /* array of tuples */
uint64 numvals; /* number of valid tuples */
/* Private members, not intended for external callers */
uint64 alloced; /* allocated length of vals array */
MemoryContext tuptabcxt; /* memory context of result table */
slist_node next; /* link for internal bookkeeping */
SubTransactionId subid; /* subxact in which tuptable was created */
} SPITupleTable;
</pre><p>
The fields <code class="structfield">tupdesc</code>,
<code class="structfield">vals</code>, and
<code class="structfield">numvals</code>
can be used by SPI callers; the remaining fields are internal.
<code class="structfield">vals</code> is an array of pointers to rows.
The number of rows is given by <code class="structfield">numvals</code>
(for somewhat historical reasons, this count is also returned
in <code class="varname">SPI_processed</code>).
<code class="structfield">tupdesc</code> is a row descriptor which you can pass to
SPI functions dealing with rows.
</p><p>
<code class="function">SPI_finish</code> frees all
<code class="structname">SPITupleTable</code>s allocated during the current
C function. You can free a particular result table earlier, if you
are done with it, by calling <code class="function">SPI_freetuptable</code>.
</p></div><div class="refsect1" id="id-1.8.12.8.4.6"><h2>Arguments</h2><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="literal">const char * <em class="parameter"><code>command</code></em></code></span></dt><dd><p>
string containing command to execute
</p></dd><dt><span class="term"><code class="literal">bool <em class="parameter"><code>read_only</code></em></code></span></dt><dd><p><code class="literal">true</code> for read-only execution</p></dd><dt><span class="term"><code class="literal">long <em class="parameter"><code>count</code></em></code></span></dt><dd><p>
maximum number of rows to return,
or <code class="literal">0</code> for no limit
</p></dd></dl></div></div><div class="refsect1" id="id-1.8.12.8.4.7"><h2>Return Value</h2><p>
If the execution of the command was successful then one of the
following (nonnegative) values will be returned:
</p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="symbol">SPI_OK_SELECT</code></span></dt><dd><p>
if a <code class="command">SELECT</code> (but not <code class="command">SELECT
INTO</code>) was executed
</p></dd><dt><span class="term"><code class="symbol">SPI_OK_SELINTO</code></span></dt><dd><p>
if a <code class="command">SELECT INTO</code> was executed
</p></dd><dt><span class="term"><code class="symbol">SPI_OK_INSERT</code></span></dt><dd><p>
if an <code class="command">INSERT</code> was executed
</p></dd><dt><span class="term"><code class="symbol">SPI_OK_DELETE</code></span></dt><dd><p>
if a <code class="command">DELETE</code> was executed
</p></dd><dt><span class="term"><code class="symbol">SPI_OK_UPDATE</code></span></dt><dd><p>
if an <code class="command">UPDATE</code> was executed
</p></dd><dt><span class="term"><code class="symbol">SPI_OK_MERGE</code></span></dt><dd><p>
if a <code class="command">MERGE</code> was executed
</p></dd><dt><span class="term"><code class="symbol">SPI_OK_INSERT_RETURNING</code></span></dt><dd><p>
if an <code class="command">INSERT RETURNING</code> was executed
</p></dd><dt><span class="term"><code class="symbol">SPI_OK_DELETE_RETURNING</code></span></dt><dd><p>
if a <code class="command">DELETE RETURNING</code> was executed
</p></dd><dt><span class="term"><code class="symbol">SPI_OK_UPDATE_RETURNING</code></span></dt><dd><p>
if an <code class="command">UPDATE RETURNING</code> was executed
</p></dd><dt><span class="term"><code class="symbol">SPI_OK_UTILITY</code></span></dt><dd><p>
if a utility command (e.g., <code class="command">CREATE TABLE</code>)
was executed
</p></dd><dt><span class="term"><code class="symbol">SPI_OK_REWRITTEN</code></span></dt><dd><p>
if the command was rewritten into another kind of command (e.g.,
<code class="command">UPDATE</code> became an <code class="command">INSERT</code>) by a <a class="link" href="rules.html" title="Chapter 41. The Rule System">rule</a>.
</p></dd></dl></div><p>
</p><p>
On error, one of the following negative values is returned:
</p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="symbol">SPI_ERROR_ARGUMENT</code></span></dt><dd><p>
if <em class="parameter"><code>command</code></em> is <code class="symbol">NULL</code> or
<em class="parameter"><code>count</code></em> is less than 0
</p></dd><dt><span class="term"><code class="symbol">SPI_ERROR_COPY</code></span></dt><dd><p>
if <code class="command">COPY TO stdout</code> or <code class="command">COPY FROM stdin</code>
was attempted
</p></dd><dt><span class="term"><code class="symbol">SPI_ERROR_TRANSACTION</code></span></dt><dd><p>
if a transaction manipulation command was attempted
(<code class="command">BEGIN</code>,
<code class="command">COMMIT</code>,
<code class="command">ROLLBACK</code>,
<code class="command">SAVEPOINT</code>,
<code class="command">PREPARE TRANSACTION</code>,
<code class="command">COMMIT PREPARED</code>,
<code class="command">ROLLBACK PREPARED</code>,
or any variant thereof)
</p></dd><dt><span class="term"><code class="symbol">SPI_ERROR_OPUNKNOWN</code></span></dt><dd><p>
if the command type is unknown (shouldn't happen)
</p></dd><dt><span class="term"><code class="symbol">SPI_ERROR_UNCONNECTED</code></span></dt><dd><p>
if called from an unconnected C function
</p></dd></dl></div><p>
</p></div><div class="refsect1" id="id-1.8.12.8.4.8"><h2>Notes</h2><p>
All SPI query-execution functions set both
<code class="varname">SPI_processed</code> and
<code class="varname">SPI_tuptable</code> (just the pointer, not the contents
of the structure). Save these two global variables into local
C function variables if you need to access the result table of
<code class="function">SPI_execute</code> or another query-execution function
across later calls.
</p></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="spi-spi-finish.html" title="SPI_finish">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="spi-interface.html" title="47.1. Interface Functions">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="spi-spi-exec.html" title="SPI_exec">Next</a></td></tr><tr><td width="40%" align="left" valign="top">SPI_finish </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"> SPI_exec</td></tr></table></div></body></html>
|
