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
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
|
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>SPI_execute</title>
<link rel="stylesheet" href="stylesheet.css" type="text/css">
<link rev="made" href="pgsql-docs@postgresql.org">
<meta name="generator" content="DocBook XSL Stylesheets V1.70.0">
<link rel="start" href="index.html" title="PostgreSQL 8.1.4 Documentation">
<link rel="up" href="spi.html#spi-interface" title="40.1.Interface Functions">
<link rel="prev" href="spi-spi-pop.html" title="SPI_pop">
<link rel="next" href="spi-spi-exec.html" title="SPI_exec">
<link rel="copyright" href="ln-legalnotice.html" title="Legal Notice">
</head>
<body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF"><div class="refentry" lang="en">
<a name="spi-spi-execute"></a><div class="titlepage"></div>
<div class="refnamediv">
<h2>Name</h2>
<p>SPI_execute — execute a command</p>
</div>
<a name="id734383"></a><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" lang="en">
<a name="id734415"></a><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 may only be called from a connected procedure.
</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 0, then the number of rows for which the command
will be executed is restricted (much like a
<code class="literal">LIMIT</code> clause). For example,
</p>
<pre class="programlisting">SPI_execute("INSERT INTO foo SELECT * FROM bar", false, 5);</pre>
<p>
will allow at most 5 rows to be inserted into the table.
</p>
<p> You may 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, but it is not applied to
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>, 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>
(unless the return value of the function is
<code class="symbol">SPI_OK_UTILITY</code>). If the return value of the
function is <code class="symbol">SPI_OK_SELECT</code> then you may use the
global pointer <code class="literal">SPITupleTable *SPI_tuptable</code> to
access the result rows.
</p>
<p> The structure <code class="structname">SPITupleTable</code> is defined
thus:
</p>
<pre class="programlisting">typedef struct
{
MemoryContext tuptabcxt; /* memory context of result table */
uint32 alloced; /* number of alloced vals */
uint32 free; /* number of free vals */
TupleDesc tupdesc; /* row descriptor */
HeapTuple *vals; /* rows */
} SPITupleTable;</pre>
<p>
<code class="structfield">vals</code> is an array of pointers to rows. (The number
of valid entries is given by <code class="varname">SPI_processed</code>.)
<code class="structfield">tupdesc</code> is a row descriptor which you may pass to
SPI functions dealing with rows. <code class="structfield">tuptabcxt</code>,
<code class="structfield">alloced</code>, and <code class="structfield">free</code> are internal
fields not intended for use by SPI callers.
</p>
<p> <code class="function">SPI_finish</code> frees all
<code class="structname">SPITupleTable</code>s allocated during the current
procedure. 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" lang="en">
<a name="id734700"></a><h2>Arguments</h2>
<div class="variablelist"><dl>
<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 process or return
</p></dd>
</dl></div>
</div>
<div class="refsect1" lang="en">
<a name="id734763"></a><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>
<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_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_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_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_UTILITY</code></span></dt>
<dd><p> if a utility command (e.g., <code class="command">CREATE TABLE</code>)
was executed
</p></dd>
</dl></div>
<p>
</p>
<p> On error, one of the following negative values is returned:
</p>
<div class="variablelist"><dl>
<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_CURSOR</code></span></dt>
<dd><p> if <code class="command">DECLARE</code>, <code class="command">CLOSE</code>, or <code class="command">FETCH</code>
was attempted
</p></dd>
<dt><span class="term"><code class="symbol">SPI_ERROR_TRANSACTION</code></span></dt>
<dd><p> if any command involving transaction manipulation 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 procedure
</p></dd>
</dl></div>
<p>
</p>
</div>
<div class="refsect1" lang="en">
<a name="id735041"></a><h2>Notes</h2>
<p> The functions <code class="function">SPI_execute</code>,
<code class="function">SPI_exec</code>,
<code class="function">SPI_execute_plan</code>, and
<code class="function">SPI_execp</code> change 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
procedure variables if you need to access the result table of
<code class="function">SPI_execute</code> or a related function
across later calls.
</p>
</div>
</div></body>
</html>
|
