codekingpro/portable-devtools
114k
1<?xml version="1.0" encoding="UTF-8" standalone="no"?>2<!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.3 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">3int 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>)4</pre></div><div class="refsect1" id="id-1.8.12.8.4.5"><h2>Description</h2><p>5 <code class="function">SPI_execute</code> executes the specified SQL command6 for <em class="parameter"><code>count</code></em> rows. If <em class="parameter"><code>read_only</code></em>7 is <code class="literal">true</code>, the command must be read-only, and execution overhead8 is somewhat reduced.9 </p><p>10 This function can only be called from a connected C function.11 </p><p>12 If <em class="parameter"><code>count</code></em> is zero then the command is executed13 for all rows that it applies to. If <em class="parameter"><code>count</code></em>14 is greater than zero, then no more than <em class="parameter"><code>count</code></em> rows15 will be retrieved; execution stops when the count is reached, much like16 adding a <code class="literal">LIMIT</code> clause to the query. For example,17</p><pre class="programlisting">18SPI_execute("SELECT * FROM foo", true, 5);19</pre><p>20 will retrieve at most 5 rows from the table. Note that such a limit21 is only effective when the command actually returns rows. For example,22</p><pre class="programlisting">23SPI_execute("INSERT INTO foo SELECT * FROM bar", false, 5);24</pre><p>25 inserts all rows from <code class="structname">bar</code>, ignoring the26 <em class="parameter"><code>count</code></em> parameter. However, with27</p><pre class="programlisting">28SPI_execute("INSERT INTO foo SELECT * FROM bar RETURNING *", false, 5);29</pre><p>30 at most 5 rows would be inserted, since execution would stop after the31 fifth <code class="literal">RETURNING</code> result row is retrieved.32 </p><p>33 You can pass multiple commands in one string;34 <code class="function">SPI_execute</code> returns the35 result for the command executed last. The <em class="parameter"><code>count</code></em>36 limit applies to each command separately (even though only the last37 result will actually be returned). The limit is not applied to any38 hidden commands generated by rules.39 </p><p>40 When <em class="parameter"><code>read_only</code></em> is <code class="literal">false</code>,41 <code class="function">SPI_execute</code> increments the command42 counter and computes a new <em class="firstterm">snapshot</em> before executing each43 command in the string. The snapshot does not actually change if the44 current transaction isolation level is <code class="literal">SERIALIZABLE</code> or <code class="literal">REPEATABLE READ</code>, but in45 <code class="literal">READ COMMITTED</code> mode the snapshot update allows each command to46 see the results of newly committed transactions from other sessions.47 This is essential for consistent behavior when the commands are modifying48 the database.49 </p><p>50 When <em class="parameter"><code>read_only</code></em> is <code class="literal">true</code>,51 <code class="function">SPI_execute</code> does not update either the snapshot52 or the command counter, and it allows only plain <code class="command">SELECT</code>53 commands to appear in the command string. The commands are executed54 using the snapshot previously established for the surrounding query.55 This execution mode is somewhat faster than the read/write mode due56 to eliminating per-command overhead. It also allows genuinely57 <em class="firstterm">stable</em> functions to be built: since successive executions58 will all use the same snapshot, there will be no change in the results.59 </p><p>60 It is generally unwise to mix read-only and read-write commands within61 a single function using SPI; that could result in very confusing behavior,62 since the read-only queries would not see the results of any database63 updates done by the read-write queries.64 </p><p>65 The actual number of rows for which the (last) command was executed66 is returned in the global variable <code class="varname">SPI_processed</code>.67 If the return value of the function is <code class="symbol">SPI_OK_SELECT</code>,68 <code class="symbol">SPI_OK_INSERT_RETURNING</code>,69 <code class="symbol">SPI_OK_DELETE_RETURNING</code>, or70 <code class="symbol">SPI_OK_UPDATE_RETURNING</code>,71 then you can use the72 global pointer <code class="literal">SPITupleTable *SPI_tuptable</code> to73 access the result rows. Some utility commands (such as74 <code class="command">EXPLAIN</code>) also return row sets, and <code class="literal">SPI_tuptable</code>75 will contain the result in these cases too. Some utility commands76 (<code class="command">COPY</code>, <code class="command">CREATE TABLE AS</code>) don't return a row set, so77 <code class="literal">SPI_tuptable</code> is NULL, but they still return the number of78 rows processed in <code class="varname">SPI_processed</code>.79 </p><p>80 The structure <code class="structname">SPITupleTable</code> is defined81 thus:82</p><pre class="programlisting">83typedef struct SPITupleTable84{85 /* Public members */86 TupleDesc tupdesc; /* tuple descriptor */87 HeapTuple *vals; /* array of tuples */88 uint64 numvals; /* number of valid tuples */89 90 /* Private members, not intended for external callers */91 uint64 alloced; /* allocated length of vals array */92 MemoryContext tuptabcxt; /* memory context of result table */93 slist_node next; /* link for internal bookkeeping */94 SubTransactionId subid; /* subxact in which tuptable was created */95} SPITupleTable;96</pre><p>97 The fields <code class="structfield">tupdesc</code>,98 <code class="structfield">vals</code>, and99 <code class="structfield">numvals</code>100 can be used by SPI callers; the remaining fields are internal.101 <code class="structfield">vals</code> is an array of pointers to rows.102 The number of rows is given by <code class="structfield">numvals</code>103 (for somewhat historical reasons, this count is also returned104 in <code class="varname">SPI_processed</code>).105 <code class="structfield">tupdesc</code> is a row descriptor which you can pass to106 SPI functions dealing with rows.107 </p><p>108 <code class="function">SPI_finish</code> frees all109 <code class="structname">SPITupleTable</code>s allocated during the current110 C function. You can free a particular result table earlier, if you111 are done with it, by calling <code class="function">SPI_freetuptable</code>.112 </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>113 string containing command to execute114 </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>115 maximum number of rows to return,116 or <code class="literal">0</code> for no limit117 </p></dd></dl></div></div><div class="refsect1" id="id-1.8.12.8.4.7"><h2>Return Value</h2><p>118 If the execution of the command was successful then one of the119 following (nonnegative) values will be returned:120 121 </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="symbol">SPI_OK_SELECT</code></span></dt><dd><p>122 if a <code class="command">SELECT</code> (but not <code class="command">SELECT123 INTO</code>) was executed124 </p></dd><dt><span class="term"><code class="symbol">SPI_OK_SELINTO</code></span></dt><dd><p>125 if a <code class="command">SELECT INTO</code> was executed126 </p></dd><dt><span class="term"><code class="symbol">SPI_OK_INSERT</code></span></dt><dd><p>127 if an <code class="command">INSERT</code> was executed128 </p></dd><dt><span class="term"><code class="symbol">SPI_OK_DELETE</code></span></dt><dd><p>129 if a <code class="command">DELETE</code> was executed130 </p></dd><dt><span class="term"><code class="symbol">SPI_OK_UPDATE</code></span></dt><dd><p>131 if an <code class="command">UPDATE</code> was executed132 </p></dd><dt><span class="term"><code class="symbol">SPI_OK_MERGE</code></span></dt><dd><p>133 if a <code class="command">MERGE</code> was executed134 </p></dd><dt><span class="term"><code class="symbol">SPI_OK_INSERT_RETURNING</code></span></dt><dd><p>135 if an <code class="command">INSERT RETURNING</code> was executed136 </p></dd><dt><span class="term"><code class="symbol">SPI_OK_DELETE_RETURNING</code></span></dt><dd><p>137 if a <code class="command">DELETE RETURNING</code> was executed138 </p></dd><dt><span class="term"><code class="symbol">SPI_OK_UPDATE_RETURNING</code></span></dt><dd><p>139 if an <code class="command">UPDATE RETURNING</code> was executed140 </p></dd><dt><span class="term"><code class="symbol">SPI_OK_UTILITY</code></span></dt><dd><p>141 if a utility command (e.g., <code class="command">CREATE TABLE</code>)142 was executed143 </p></dd><dt><span class="term"><code class="symbol">SPI_OK_REWRITTEN</code></span></dt><dd><p>144 if the command was rewritten into another kind of command (e.g.,145 <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>.146 </p></dd></dl></div><p>147 </p><p>148 On error, one of the following negative values is returned:149 150 </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="symbol">SPI_ERROR_ARGUMENT</code></span></dt><dd><p>151 if <em class="parameter"><code>command</code></em> is <code class="symbol">NULL</code> or152 <em class="parameter"><code>count</code></em> is less than 0153 </p></dd><dt><span class="term"><code class="symbol">SPI_ERROR_COPY</code></span></dt><dd><p>154 if <code class="command">COPY TO stdout</code> or <code class="command">COPY FROM stdin</code>155 was attempted156 </p></dd><dt><span class="term"><code class="symbol">SPI_ERROR_TRANSACTION</code></span></dt><dd><p>157 if a transaction manipulation command was attempted158 (<code class="command">BEGIN</code>,159 <code class="command">COMMIT</code>,160 <code class="command">ROLLBACK</code>,161 <code class="command">SAVEPOINT</code>,162 <code class="command">PREPARE TRANSACTION</code>,163 <code class="command">COMMIT PREPARED</code>,164 <code class="command">ROLLBACK PREPARED</code>,165 or any variant thereof)166 </p></dd><dt><span class="term"><code class="symbol">SPI_ERROR_OPUNKNOWN</code></span></dt><dd><p>167 if the command type is unknown (shouldn't happen)168 </p></dd><dt><span class="term"><code class="symbol">SPI_ERROR_UNCONNECTED</code></span></dt><dd><p>169 if called from an unconnected C function170 </p></dd></dl></div><p>171 </p></div><div class="refsect1" id="id-1.8.12.8.4.8"><h2>Notes</h2><p>172 All SPI query-execution functions set both173 <code class="varname">SPI_processed</code> and174 <code class="varname">SPI_tuptable</code> (just the pointer, not the contents175 of the structure). Save these two global variables into local176 C function variables if you need to access the result table of177 <code class="function">SPI_execute</code> or another query-execution function178 across later calls.179 </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.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> SPI_exec</td></tr></table></div></body></html>