Home • Explore • Blog • CI
☰



postgresql
23th chunk of `doc/src/sgml/spi.sgml`
ac9566ce6d992c70966c731d5de08fe0c1c346fe796dd7ec0000000100000fa1
 <parameter>count</parameter></literal></term>
    <listitem>
     <para>
      maximum number of rows to return,
      or <literal>0</literal> for no limit
     </para>
    </listitem>
   </varlistentry>
  </variablelist>
 </refsect1>

 <refsect1>
  <title>Return Value</title>

  <para>
   See <function>SPI_execute_plan</function>.
  </para>

  <para>
   <varname>SPI_processed</varname> and
   <varname>SPI_tuptable</varname> are set as in
   <function>SPI_execute</function> if successful.
  </para>
 </refsect1>
</refentry>

<!-- *********************************************** -->

<refentry id="spi-spi-cursor-open">
 <indexterm><primary>SPI_cursor_open</primary></indexterm>

 <refmeta>
  <refentrytitle>SPI_cursor_open</refentrytitle>
  <manvolnum>3</manvolnum>
 </refmeta>

 <refnamediv>
  <refname>SPI_cursor_open</refname>
  <refpurpose>set up a cursor using a statement created with <function>SPI_prepare</function></refpurpose>
 </refnamediv>

 <refsynopsisdiv>
<synopsis>
Portal SPI_cursor_open(const char * <parameter>name</parameter>, SPIPlanPtr <parameter>plan</parameter>,
                       Datum * <parameter>values</parameter>, const char * <parameter>nulls</parameter>,
                       bool <parameter>read_only</parameter>)
</synopsis>
 </refsynopsisdiv>

 <refsect1>
  <title>Description</title>

  <para>
   <function>SPI_cursor_open</function> sets up a cursor (internally,
   a portal) that will execute a statement prepared by
   <function>SPI_prepare</function>.  The parameters have the same
   meanings as the corresponding parameters to
   <function>SPI_execute_plan</function>.
  </para>

  <para>
   Using a cursor instead of executing the statement directly has two
   benefits.  First, the result rows can be retrieved a few at a time,
   avoiding memory overrun for queries that return many rows.  Second,
   a portal can outlive the current C function (it can, in fact, live
   to the end of the current transaction).  Returning the portal name
   to the C function's caller provides a way of returning a row set as
   result.
  </para>

  <para>
   The passed-in parameter data will be copied into the cursor's portal, so it
   can be freed while the cursor still exists.
  </para>
 </refsect1>

 <refsect1>
  <title>Arguments</title>

  <variablelist>
   <varlistentry>
    <term><literal>const char * <parameter>name</parameter></literal></term>
    <listitem>
     <para>
      name for portal, or <symbol>NULL</symbol> to let the system
      select a name
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><literal>SPIPlanPtr <parameter>plan</parameter></literal></term>
    <listitem>
     <para>
      prepared statement (returned by <function>SPI_prepare</function>)
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><literal>Datum * <parameter>values</parameter></literal></term>
    <listitem>
     <para>
      An array of actual parameter values.  Must have same length as the
      statement's number of arguments.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><literal>const char * <parameter>nulls</parameter></literal></term>
    <listitem>
     <para>
      An array describing which parameters are null.  Must have same length as
      the statement's number of arguments.
     </para>

     <para>
      If <parameter>nulls</parameter> is <symbol>NULL</symbol> then
      <function>SPI_cursor_open</function> assumes that no parameters
      are null.  Otherwise, each entry of the <parameter>nulls</parameter>
      array should be <literal>'&nbsp;'</literal> if the corresponding parameter
      value is non-null, or <literal>'n'</literal> if the corresponding parameter
      value is null.  (In the latter case, the actual value in the
      corresponding <parameter>values</parameter> entry doesn't matter.)  Note
      that <parameter>nulls</parameter> is not a text string, just an array:
      it does not need a <literal>'\0'</literal>
Title: SPI_cursor_open: Setting up a Cursor for Prepared Statements
Summary
This section focuses on the `SPI_cursor_open` function, which sets up a cursor (portal) to execute a prepared statement created by `SPI_prepare`. The parameters of `SPI_cursor_open` mirror those of `SPI_execute_plan`. Using a cursor allows for retrieving result rows in batches to prevent memory overruns and enables the portal to persist beyond the current C function, potentially until the end of the transaction. The passed-in parameter data will be copied into the cursor's portal. The arguments for `SPI_cursor_open` are then detailed, including the portal name (`name`), the prepared statement (`plan`), the parameter values (`values`), an array indicating null parameters (`nulls`), and whether the cursor is read-only (`read_only`).