1
by Martin Pitt
Import upstream version 9.3~beta1 |
1 |
'\" t
|
2 |
.\" Title: SPI_execute |
|
3 |
.\" Author: The PostgreSQL Global Development Group |
|
1.2.4
by Martin Pitt
Import upstream version 9.3.11 |
4 |
.\" Generator: DocBook XSL Stylesheets v1.78.1 <http://docbook.sf.net/> |
5 |
.\" Date: 2016 |
|
1.1.11
by Martin Pitt
Import upstream version 9.3.12 |
6 |
.\" Manual: PostgreSQL 9.3.12 Documentation |
7 |
.\" Source: PostgreSQL 9.3.12 |
|
1
by Martin Pitt
Import upstream version 9.3~beta1 |
8 |
.\" Language: English |
9 |
.\" |
|
1.1.11
by Martin Pitt
Import upstream version 9.3.12 |
10 |
.TH "SPI_EXECUTE" "3" "2016" "PostgreSQL 9.3.12" "PostgreSQL 9.3.12 Documentation" |
1
by Martin Pitt
Import upstream version 9.3~beta1 |
11 |
.\" ----------------------------------------------------------------- |
12 |
.\" * Define some portability stuff |
|
13 |
.\" ----------------------------------------------------------------- |
|
14 |
.\" ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ |
|
15 |
.\" http://bugs.debian.org/507673 |
|
16 |
.\" http://lists.gnu.org/archive/html/groff/2009-02/msg00013.html |
|
17 |
.\" ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ |
|
18 |
.ie \n(.g .ds Aq \(aq |
|
19 |
.el .ds Aq ' |
|
20 |
.\" ----------------------------------------------------------------- |
|
21 |
.\" * set default formatting |
|
22 |
.\" ----------------------------------------------------------------- |
|
23 |
.\" disable hyphenation |
|
24 |
.nh
|
|
25 |
.\" disable justification (adjust text to left margin only) |
|
26 |
.ad l |
|
27 |
.\" ----------------------------------------------------------------- |
|
28 |
.\" * MAIN CONTENT STARTS HERE * |
|
29 |
.\" ----------------------------------------------------------------- |
|
30 |
.SH "NAME" |
|
31 |
SPI_execute \- execute a command
|
|
32 |
.\" SPI_execute |
|
33 |
.SH "SYNOPSIS" |
|
34 |
.sp
|
|
35 |
.nf
|
|
36 |
int SPI_execute(const char * \fIcommand\fR, bool \fIread_only\fR, long \fIcount\fR) |
|
37 |
.fi
|
|
38 |
.SH "DESCRIPTION" |
|
39 |
.PP
|
|
40 |
\fBSPI_execute\fR |
|
41 |
executes the specified SQL command for |
|
42 |
\fIcount\fR |
|
43 |
rows\&. If
|
|
44 |
\fIread_only\fR |
|
45 |
is |
|
46 |
true, the command must be read\-only, and execution overhead is somewhat reduced\&. |
|
47 |
.PP
|
|
48 |
This function can only be called from a connected procedure\&.
|
|
49 |
.PP
|
|
50 |
If |
|
51 |
\fIcount\fR |
|
52 |
is zero then the command is executed for all rows that it applies to\&. If
|
|
53 |
\fIcount\fR |
|
54 |
is greater than zero, then no more than |
|
55 |
\fIcount\fR |
|
56 |
rows will be retrieved; execution stops when the count is reached, much like adding a |
|
57 |
LIMIT |
|
58 |
clause to the query\&. For example,
|
|
59 |
.sp
|
|
60 |
.if n \{\ |
|
61 |
.RS 4 |
|
62 |
.\}
|
|
63 |
.nf
|
|
64 |
SPI_execute("SELECT * FROM foo", true, 5); |
|
65 |
.fi
|
|
66 |
.if n \{\ |
|
67 |
.RE
|
|
68 |
.\}
|
|
69 |
.sp
|
|
70 |
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, |
|
71 |
.sp
|
|
72 |
.if n \{\ |
|
73 |
.RS 4 |
|
74 |
.\}
|
|
75 |
.nf
|
|
76 |
SPI_execute("INSERT INTO foo SELECT * FROM bar", false, 5); |
|
77 |
.fi
|
|
78 |
.if n \{\ |
|
79 |
.RE
|
|
80 |
.\}
|
|
81 |
.sp
|
|
82 |
inserts all rows from |
|
83 |
bar, ignoring the |
|
84 |
\fIcount\fR |
|
85 |
parameter\&. However, with
|
|
86 |
.sp
|
|
87 |
.if n \{\ |
|
88 |
.RS 4 |
|
89 |
.\}
|
|
90 |
.nf
|
|
91 |
SPI_execute("INSERT INTO foo SELECT * FROM bar RETURNING *", false, 5); |
|
92 |
.fi
|
|
93 |
.if n \{\ |
|
94 |
.RE
|
|
95 |
.\}
|
|
96 |
.sp
|
|
97 |
at most 5 rows would be inserted, since execution would stop after the fifth |
|
98 |
RETURNING |
|
99 |
result row is retrieved\&.
|
|
100 |
.PP
|
|
101 |
You can pass multiple commands in one string; |
|
102 |
\fBSPI_execute\fR |
|
103 |
returns the result for the command executed last\&. The
|
|
104 |
\fIcount\fR |
|
105 |
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\&. |
|
106 |
.PP
|
|
107 |
When |
|
108 |
\fIread_only\fR |
|
109 |
is |
|
110 |
false, |
|
111 |
\fBSPI_execute\fR |
|
112 |
increments the command counter and computes a new |
|
113 |
snapshot |
|
114 |
before executing each command in the string\&. The snapshot does not actually change if the current transaction isolation level is
|
|
115 |
SERIALIZABLE |
|
116 |
or |
|
117 |
REPEATABLE READ, but in |
|
118 |
READ COMMITTED |
|
119 |
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\&. |
|
120 |
.PP
|
|
121 |
When |
|
122 |
\fIread_only\fR |
|
123 |
is |
|
124 |
true, |
|
125 |
\fBSPI_execute\fR |
|
126 |
does not update either the snapshot or the command counter, and it allows only plain |
|
127 |
\fBSELECT\fR |
|
128 |
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 |
|
129 |
stable |
|
130 |
functions to be built: since successive executions will all use the same snapshot, there will be no change in the results\&.
|
|
131 |
.PP
|
|
132 |
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\&. |
|
133 |
.PP
|
|
134 |
The actual number of rows for which the (last) command was executed is returned in the global variable |
|
135 |
\fISPI_processed\fR\&. If the return value of the function is |
|
136 |
SPI_OK_SELECT, |
|
137 |
SPI_OK_INSERT_RETURNING, |
|
138 |
SPI_OK_DELETE_RETURNING, or |
|
139 |
SPI_OK_UPDATE_RETURNING, then you can use the global pointer |
|
140 |
SPITupleTable *SPI_tuptable |
|
141 |
to access the result rows\&. Some utility commands (such as
|
|
142 |
\fBEXPLAIN\fR) also return row sets, and |
|
143 |
SPI_tuptable |
|
144 |
will contain the result in these cases too\&. Some utility commands (\fBCOPY\fR, |
|
145 |
\fBCREATE TABLE AS\fR) don\*(Aqt return a row set, so |
|
146 |
SPI_tuptable |
|
147 |
is NULL, but they still return the number of rows processed in |
|
148 |
\fISPI_processed\fR\&. |
|
149 |
.PP
|
|
150 |
The structure |
|
151 |
SPITupleTable |
|
152 |
is defined thus: |
|
153 |
.sp
|
|
154 |
.if n \{\ |
|
155 |
.RS 4 |
|
156 |
.\}
|
|
157 |
.nf
|
|
158 |
typedef struct |
|
159 |
{ |
|
160 |
MemoryContext tuptabcxt; /* memory context of result table */ |
|
161 |
uint32 alloced; /* number of alloced vals */ |
|
162 |
uint32 free; /* number of free vals */ |
|
163 |
TupleDesc tupdesc; /* row descriptor */ |
|
164 |
HeapTuple *vals; /* rows */ |
|
165 |
} SPITupleTable; |
|
166 |
.fi
|
|
167 |
.if n \{\ |
|
168 |
.RE
|
|
169 |
.\}
|
|
170 |
.sp
|
|
171 |
vals |
|
172 |
is an array of pointers to rows\&. (The number of valid entries is given by
|
|
173 |
\fISPI_processed\fR\&.) |
|
174 |
tupdesc |
|
175 |
is a row descriptor which you can pass to SPI functions dealing with rows\&.
|
|
176 |
tuptabcxt, |
|
177 |
alloced, and |
|
178 |
free |
|
179 |
are internal fields not intended for use by SPI callers\&.
|
|
180 |
.PP
|
|
181 |
\fBSPI_finish\fR |
|
182 |
frees all |
|
183 |
SPITupleTables allocated during the current procedure\&. You can free a particular result table earlier, if you are done with it, by calling
|
|
184 |
\fBSPI_freetuptable\fR\&. |
|
185 |
.SH "ARGUMENTS" |
|
186 |
.PP
|
|
187 |
const char * \fIcommand\fR |
|
188 |
.RS 4 |
|
189 |
string containing command to execute |
|
190 |
.RE
|
|
191 |
.PP
|
|
192 |
bool \fIread_only\fR |
|
193 |
.RS 4 |
|
194 |
true |
|
195 |
for read\-only execution
|
|
196 |
.RE
|
|
197 |
.PP
|
|
198 |
long \fIcount\fR |
|
199 |
.RS 4 |
|
200 |
maximum number of rows to return, or |
|
201 |
0 |
|
202 |
for no limit |
|
203 |
.RE
|
|
204 |
.SH "RETURN VALUE" |
|
205 |
.PP
|
|
206 |
If the execution of the command was successful then one of the following (nonnegative) values will be returned: |
|
207 |
.PP
|
|
208 |
SPI_OK_SELECT |
|
209 |
.RS 4 |
|
210 |
if a |
|
211 |
\fBSELECT\fR |
|
212 |
(but not |
|
213 |
\fBSELECT INTO\fR) was executed |
|
214 |
.RE
|
|
215 |
.PP
|
|
216 |
SPI_OK_SELINTO |
|
217 |
.RS 4 |
|
218 |
if a |
|
219 |
\fBSELECT INTO\fR |
|
220 |
was executed |
|
221 |
.RE
|
|
222 |
.PP
|
|
223 |
SPI_OK_INSERT |
|
224 |
.RS 4 |
|
225 |
if an |
|
226 |
\fBINSERT\fR |
|
227 |
was executed |
|
228 |
.RE
|
|
229 |
.PP
|
|
230 |
SPI_OK_DELETE |
|
231 |
.RS 4 |
|
232 |
if a |
|
233 |
\fBDELETE\fR |
|
234 |
was executed |
|
235 |
.RE
|
|
236 |
.PP
|
|
237 |
SPI_OK_UPDATE |
|
238 |
.RS 4 |
|
239 |
if an |
|
240 |
\fBUPDATE\fR |
|
241 |
was executed |
|
242 |
.RE
|
|
243 |
.PP
|
|
244 |
SPI_OK_INSERT_RETURNING |
|
245 |
.RS 4 |
|
246 |
if an |
|
247 |
\fBINSERT RETURNING\fR |
|
248 |
was executed |
|
249 |
.RE
|
|
250 |
.PP
|
|
251 |
SPI_OK_DELETE_RETURNING |
|
252 |
.RS 4 |
|
253 |
if a |
|
254 |
\fBDELETE RETURNING\fR |
|
255 |
was executed |
|
256 |
.RE
|
|
257 |
.PP
|
|
258 |
SPI_OK_UPDATE_RETURNING |
|
259 |
.RS 4 |
|
260 |
if an |
|
261 |
\fBUPDATE RETURNING\fR |
|
262 |
was executed |
|
263 |
.RE
|
|
264 |
.PP
|
|
265 |
SPI_OK_UTILITY |
|
266 |
.RS 4 |
|
267 |
if a utility command (e\&.g\&., |
|
268 |
\fBCREATE TABLE\fR) was executed |
|
269 |
.RE
|
|
270 |
.PP
|
|
271 |
SPI_OK_REWRITTEN |
|
272 |
.RS 4 |
|
273 |
if the command was rewritten into another kind of command (e\&.g\&., |
|
274 |
\fBUPDATE\fR |
|
275 |
became an |
|
276 |
\fBINSERT\fR) by a |
|
277 |
rule\&.
|
|
278 |
.RE
|
|
279 |
.PP
|
|
280 |
On error, one of the following negative values is returned: |
|
281 |
.PP
|
|
282 |
SPI_ERROR_ARGUMENT |
|
283 |
.RS 4 |
|
284 |
if |
|
285 |
\fIcommand\fR |
|
286 |
is |
|
287 |
NULL |
|
288 |
or |
|
289 |
\fIcount\fR |
|
290 |
is less than 0 |
|
291 |
.RE
|
|
292 |
.PP
|
|
293 |
SPI_ERROR_COPY |
|
294 |
.RS 4 |
|
295 |
if |
|
296 |
\fBCOPY TO stdout\fR |
|
297 |
or |
|
298 |
\fBCOPY FROM stdin\fR |
|
299 |
was attempted |
|
300 |
.RE
|
|
301 |
.PP
|
|
302 |
SPI_ERROR_TRANSACTION |
|
303 |
.RS 4 |
|
304 |
if a transaction manipulation command was attempted (\fBBEGIN\fR, |
|
305 |
\fBCOMMIT\fR, |
|
306 |
\fBROLLBACK\fR, |
|
307 |
\fBSAVEPOINT\fR, |
|
308 |
\fBPREPARE TRANSACTION\fR, |
|
309 |
\fBCOMMIT PREPARED\fR, |
|
310 |
\fBROLLBACK PREPARED\fR, or any variant thereof) |
|
311 |
.RE
|
|
312 |
.PP
|
|
313 |
SPI_ERROR_OPUNKNOWN |
|
314 |
.RS 4 |
|
315 |
if the command type is unknown (shouldn\*(Aqt happen)
|
|
316 |
.RE
|
|
317 |
.PP
|
|
318 |
SPI_ERROR_UNCONNECTED |
|
319 |
.RS 4 |
|
320 |
if called from an unconnected procedure |
|
321 |
.RE
|
|
322 |
.SH "NOTES" |
|
323 |
.PP
|
|
324 |
All SPI query\-execution functions set both
|
|
325 |
\fISPI_processed\fR |
|
326 |
and |
|
327 |
\fISPI_tuptable\fR |
|
328 |
(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
|
|
329 |
\fBSPI_execute\fR |
|
330 |
or another query\-execution function across later calls\&. |