Home Explore Blog CI



postgresql

3rd chunk of `doc/src/sgml/spi.sgml`
5ce7dc37b1fb6b90ae69d8406249fb52a0cde9136444e8990000000100000fa7
 <refsynopsisdiv>
<synopsis>
int SPI_execute(const char * <parameter>command</parameter>, bool <parameter>read_only</parameter>, long <parameter>count</parameter>)
</synopsis>
 </refsynopsisdiv>

 <refsect1>
  <title>Description</title>

  <para>
   <function>SPI_execute</function> executes the specified SQL command
   for <parameter>count</parameter> rows.  If <parameter>read_only</parameter>
   is <literal>true</literal>, the command must be read-only, and execution overhead
   is somewhat reduced.
  </para>

  <para>
   This function can only be called from a connected C function.
  </para>

  <para>
   If <parameter>count</parameter> is zero then the command is executed
   for all rows that it applies to.  If <parameter>count</parameter>
   is greater than zero, then no more than <parameter>count</parameter> rows
   will be retrieved; execution stops when the count is reached, much like
   adding a <literal>LIMIT</literal> clause to the query. For example,
<programlisting>
SPI_execute("SELECT * FROM foo", true, 5);
</programlisting>
   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,
<programlisting>
SPI_execute("INSERT INTO foo SELECT * FROM bar", false, 5);
</programlisting>
   inserts all rows from <structname>bar</structname>, ignoring the
   <parameter>count</parameter> parameter.  However, with
<programlisting>
SPI_execute("INSERT INTO foo SELECT * FROM bar RETURNING *", false, 5);
</programlisting>
   at most 5 rows would be inserted, since execution would stop after the
   fifth <literal>RETURNING</literal> result row is retrieved.
  </para>

  <para>
   You can pass multiple commands in one string;
   <function>SPI_execute</function> returns the
   result for the command executed last.  The <parameter>count</parameter>
   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.
  </para>

  <para>
   When <parameter>read_only</parameter> is <literal>false</literal>,
   <function>SPI_execute</function> increments the command
   counter and computes a new <firstterm>snapshot</firstterm> before executing each
   command in the string.  The snapshot does not actually change if the
   current transaction isolation level is <literal>SERIALIZABLE</literal> or <literal>REPEATABLE READ</literal>, but in
   <literal>READ COMMITTED</literal> 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.
  </para>

  <para>
   When <parameter>read_only</parameter> is <literal>true</literal>,
   <function>SPI_execute</function> does not update either the snapshot
   or the command counter, and it allows only plain <command>SELECT</command>
   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
   <firstterm>stable</firstterm> functions to be built: since successive executions
   will all use the same snapshot, there will be no change in the results.
  </para>

  <para>
   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.
  </para>

  <para>
   The actual number of rows for which the (last) command was executed
   is returned in the global variable <varname>SPI_processed</varname>.
   If the return value of the function is <symbol>SPI_OK_SELECT</symbol>,
   <symbol>SPI_OK_INSERT_RETURNING</symbol>,
   <symbol>SPI_OK_DELETE_RETURNING</symbol>,

Title: SPI_execute: Executing SQL Commands with Row Limits and Read-Only Mode
Summary
This section describes the SPI_execute function, which executes SQL commands with options for limiting the number of rows processed and specifying read-only mode. It details how the count parameter affects row retrieval, how multiple commands in a string are handled, and how read-only mode impacts performance and snapshot management. It also cautions against mixing read-only and read-write commands within a single SPI function.