SPI_execute
执行指定的 SQL 命令以获得count行。如果read_only为真,该命令必须是只读的,并且执行开销也会有所降低。
只能从一个已连接的过程中调用这个函数。
如果count为零,那么该命令会为其所适用的所有行执行。如果count大于零,那么会检索不超过count行,当到达该计数时执行会停止,这很像为查询增加一个LIMIT子句。例如:
SPI_execute("SELECT * FROM foo", true, 5);
会从表中检索至多 5 行。注意这样一个限制只有当命令真正返回行时才有效。例如:
SPI_execute("INSERT INTO foo SELECT * FROM bar", false, 5);
插入所有来自于bar的行,而忽略count参数。不过,通过
SPI_execute("INSERT INTO foo SELECT * FROM bar RETURNING *", false, 5);
将插入至多 5 行,因为在第五个RETURNING结果行被检索到后执行就会停止。
You can pass multiple commands in one string;
SPI_execute
returns the
result for the command executed last. The count
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.
When read_only is false,
SPI_execute
increments the command
counter and computes a new snapshot before executing each
command in the string. The snapshot does not actually change if the
current transaction isolation level is SERIALIZABLE or REPEATABLE READ, but in
READ COMMITTED 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.
When read_only is true,
SPI_execute
does not update either the snapshot
or the command counter, and it allows only plain SELECT
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
stable functions to be built: since successive executions
will all use the same snapshot, there will be no change in the results.
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.
The actual number of rows for which the (last) command was executed is returned in the global variable SPI_processed. If the return value of the function is SPI_OK_SELECT, SPI_OK_INSERT_RETURNING, SPI_OK_DELETE_RETURNING, or SPI_OK_UPDATE_RETURNING, then you can use the global pointer SPITupleTable *SPI_tuptable to access the result rows. Some utility commands (such as EXPLAIN) also return row sets, and SPI_tuptable will contain the result in these cases too. Some utility commands (COPY, CREATE TABLE AS) don't return a row set, so SPI_tuptable is NULL, but they still return the number of rows processed in SPI_processed.
The structure SPITupleTable is defined thus:
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;
vals is an array of pointers to rows. (The number of valid entries is given by SPI_processed.) tupdesc is a row descriptor which you can pass to SPI functions dealing with rows. tuptabcxt, alloced, and free are internal fields not intended for use by SPI callers.
SPI_finish
frees all
SPITupleTables allocated during the current
procedure. You can free a particular result table earlier, if you
are done with it, by calling SPI_freetuptable
.
string containing command to execute
true for read-only execution
maximum number of rows to return, or 0 for no limit
If the execution of the command was successful then one of the following (nonnegative) values will be returned:
if a SELECT (but not SELECT INTO) was executed
if a SELECT INTO was executed
if an INSERT was executed
if a DELETE was executed
if an UPDATE was executed
if an INSERT RETURNING was executed
if a DELETE RETURNING was executed
if an UPDATE RETURNING was executed
if a utility command (e.g., CREATE TABLE) was executed
if the command was rewritten into another kind of command (e.g., UPDATE became an INSERT) by a rule.
On error, one of the following negative values is returned:
if command is NULL or count is less than 0
if COPY TO stdout or COPY FROM stdin was attempted
if a transaction manipulation command was attempted (BEGIN, COMMIT, ROLLBACK, SAVEPOINT, PREPARE TRANSACTION, COMMIT PREPARED, ROLLBACK PREPARED, or any variant thereof)
if the command type is unknown (shouldn't happen)
if called from an unconnected procedure
All SPI query-execution functions set both
SPI_processed and
SPI_tuptable (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
SPI_execute
or another query-execution function
across later calls.