diff options
Diffstat (limited to 'doc/src/sgml/html/spi-spi-execute.html')
| -rw-r--r-- | doc/src/sgml/html/spi-spi-execute.html | 177 |
1 files changed, 177 insertions, 0 deletions
diff --git a/doc/src/sgml/html/spi-spi-execute.html b/doc/src/sgml/html/spi-spi-execute.html new file mode 100644 index 0000000..27e39f5 --- /dev/null +++ b/doc/src/sgml/html/spi-spi-execute.html @@ -0,0 +1,177 @@ +<?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 xmlns="http://www.w3.org/TR/xhtml1/transitional" 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 14.5 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></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_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 xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navfooter"><hr></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 14.5 Documentation">Home</a></td><td width="40%" align="right" valign="top"> SPI_exec</td></tr></table></div></body></html>
\ No newline at end of file |