1SPI_EXECUTE(3) PostgreSQL 12.2 Documentation SPI_EXECUTE(3)
2
6 SPI_execute - execute a command
7
9 int SPI_execute(const char * command, bool read_only, long count)
10
12 SPI_execute executes the specified SQL command for count rows. If
13 read_only is true, the command must be read-only, and execution
14 overhead is somewhat reduced.
15 This function can only be called from a connected C function.
17 If count is zero then the command is executed for all rows that it
19 applies to. If count is greater than zero, then no more than count rows
20 will be retrieved; execution stops when the count is reached, much like
21 adding a LIMIT clause to the query. For example,
22 SPI_execute("SELECT * FROM foo", true, 5);
24 will retrieve at most 5 rows from the table. Note that such a limit is
26 only effective when the command actually returns rows. For example,
27 SPI_execute("INSERT INTO foo SELECT * FROM bar", false, 5);
29 inserts all rows from bar, ignoring the count parameter. However, with
31 SPI_execute("INSERT INTO foo SELECT * FROM bar RETURNING *", false, 5);
33 at most 5 rows would be inserted, since execution would stop after the
35 fifth RETURNING result row is retrieved.
36 You can pass multiple commands in one string; SPI_execute returns the
38 result for the command executed last. The count limit applies to each
39 command separately (even though only the last result will actually be
40 returned). The limit is not applied to any hidden commands generated by
41 rules.
42 When read_only is false, SPI_execute increments the command counter and
44 computes a new snapshot before executing each command in the string.
45 The snapshot does not actually change if the current transaction
46 isolation level is SERIALIZABLE or REPEATABLE READ, but in READ
47 COMMITTED mode the snapshot update allows each command to see the
48 results of newly committed transactions from other sessions. This is
49 essential for consistent behavior when the commands are modifying the
50 database.
51 When read_only is true, SPI_execute does not update either the snapshot
53 or the command counter, and it allows only plain SELECT commands to
54 appear in the command string. The commands are executed using the
55 snapshot previously established for the surrounding query. This
56 execution mode is somewhat faster than the read/write mode due to
57 eliminating per-command overhead. It also allows genuinely stable
58 functions to be built: since successive executions will all use the
59 same snapshot, there will be no change in the results.
60 It is generally unwise to mix read-only and read-write commands within
62 a single function using SPI; that could result in very confusing
63 behavior, since the read-only queries would not see the results of any
64 database updates done by the read-write queries.
65 The actual number of rows for which the (last) command was executed is
67 returned in the global variable SPI_processed. If the return value of
68 the function is SPI_OK_SELECT, SPI_OK_INSERT_RETURNING,
69 SPI_OK_DELETE_RETURNING, or SPI_OK_UPDATE_RETURNING, then you can use
70 the global pointer SPITupleTable *SPI_tuptable to access the result
71 rows. Some utility commands (such as EXPLAIN) also return row sets, and
72 SPI_tuptable will contain the result in these cases too. Some utility
73 commands (COPY, CREATE TABLE AS) don't return a row set, so
74 SPI_tuptable is NULL, but they still return the number of rows
75 processed in SPI_processed.
76 The structure SPITupleTable is defined thus:
78 typedef struct
80 {
81 MemoryContext tuptabcxt; /* memory context of result table */
82 uint64 alloced; /* number of alloced vals */
83 uint64 free; /* number of free vals */
84 TupleDesc tupdesc; /* row descriptor */
85 HeapTuple *vals; /* rows */
86 } SPITupleTable;
87 vals is an array of pointers to rows. (The number of valid entries is
90 given by SPI_processed.) tupdesc is a row descriptor which you can
91 pass to SPI functions dealing with rows. tuptabcxt, alloced, and free
92 are internal fields not intended for use by SPI callers.
93 SPI_finish frees all SPITupleTables allocated during the current C
95 function. You can free a particular result table earlier, if you are
96 done with it, by calling SPI_freetuptable.
97
99 const char * command
100 string containing command to execute
101 bool read_only
103 true for read-only execution
104 long count
106 maximum number of rows to return, or 0 for no limit
107
109 If the execution of the command was successful then one of the
110 following (nonnegative) values will be returned:
111 SPI_OK_SELECT
113 if a SELECT (but not SELECT INTO) was executed
114 SPI_OK_SELINTO
116 if a SELECT INTO was executed
117 SPI_OK_INSERT
119 if an INSERT was executed
120 SPI_OK_DELETE
122 if a DELETE was executed
123 SPI_OK_UPDATE
125 if an UPDATE was executed
126 SPI_OK_INSERT_RETURNING
128 if an INSERT RETURNING was executed
129 SPI_OK_DELETE_RETURNING
131 if a DELETE RETURNING was executed
132 SPI_OK_UPDATE_RETURNING
134 if an UPDATE RETURNING was executed
135 SPI_OK_UTILITY
137 if a utility command (e.g., CREATE TABLE) was executed
138 SPI_OK_REWRITTEN
140 if the command was rewritten into another kind of command (e.g.,
141 UPDATE became an INSERT) by a rule.
142 On error, one of the following negative values is returned:
144 SPI_ERROR_ARGUMENT
146 if command is NULL or count is less than 0
147 SPI_ERROR_COPY
149 if COPY TO stdout or COPY FROM stdin was attempted
150 SPI_ERROR_TRANSACTION
152 if a transaction manipulation command was attempted (BEGIN, COMMIT,
153 ROLLBACK, SAVEPOINT, PREPARE TRANSACTION, COMMIT PREPARED, ROLLBACK
154 PREPARED, or any variant thereof)
155 SPI_ERROR_OPUNKNOWN
157 if the command type is unknown (shouldn't happen)
158 SPI_ERROR_UNCONNECTED
160 if called from an unconnected C function
161
163 All SPI query-execution functions set both SPI_processed and
164 SPI_tuptable (just the pointer, not the contents of the structure).
165 Save these two global variables into local C function variables if you
166 need to access the result table of SPI_execute or another
167 query-execution function across later calls.
168PostgreSQL 12.2 2020 SPI_EXECUTE(3)