summaryrefslogtreecommitdiffstats
path: root/doc/src/sgml/html/spi-spi-execute.html
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2024-04-13 13:44:03 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2024-04-13 13:44:03 +0000
commit293913568e6a7a86fd1479e1cff8e2ecb58d6568 (patch)
treefc3b469a3ec5ab71b36ea97cc7aaddb838423a0c /doc/src/sgml/html/spi-spi-execute.html
parentInitial commit. (diff)
downloadpostgresql-16-293913568e6a7a86fd1479e1cff8e2ecb58d6568.tar.xz
postgresql-16-293913568e6a7a86fd1479e1cff8e2ecb58d6568.zip
Adding upstream version 16.2.upstream/16.2
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'doc/src/sgml/html/spi-spi-execute.html')
-rw-r--r--doc/src/sgml/html/spi-spi-execute.html179
1 files changed, 179 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..bff6db1
--- /dev/null
+++ b/doc/src/sgml/html/spi-spi-execute.html
@@ -0,0 +1,179 @@
+<?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 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 16.2 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 /></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_MERGE</code></span></dt><dd><p>
+ if a <code class="command">MERGE</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 class="navfooter"><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 16.2 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
generated by cgit v1.2.3 (git 2.39.1) at 2024-11-29 12:45:42 +0000