doc/src/sgml/spi.sgml

   1 <!-- doc/src/sgml/spi.sgml -->
   2
   3 <chapter id="spi">
   4  <title>Server Programming Interface</title>
   5
   6  <indexterm zone="spi">
   7   <primary>SPI</primary>
   8  </indexterm>
   9
  10  <para>
  11   The <firstterm>Server Programming Interface</firstterm>
  12   (<acronym>SPI</acronym>) gives writers of user-defined
  13   <acronym>C</acronym> functions the ability to run
  14   <acronym>SQL</acronym> commands inside their functions or procedures.
  15   <acronym>SPI</acronym> is a set of
  16   interface functions to simplify access to the parser, planner,
  17   and executor. <acronym>SPI</acronym> also does some
  18   memory management.
  19  </para>
  20
  21  <note>
  22   <para>
  23    The available procedural languages provide various means to
  24    execute SQL commands from functions.  Most of these facilities are
  25    based on SPI, so this documentation might be of use for users
  26    of those languages as well.
  27   </para>
  28  </note>
  29
  30  <para>
  31   Note that if a command invoked via SPI fails, then control will not be
  32   returned to your C function.  Rather, the
  33   transaction or subtransaction in which your C function executes will be
  34   rolled back.  (This might seem surprising given that the SPI functions mostly
  35   have documented error-return conventions.  Those conventions only apply
  36   for errors detected within the SPI functions themselves, however.)
  37   It is possible to recover control after an error by establishing your own
  38   subtransaction surrounding SPI calls that might fail.
  39  </para>
  40
  41  <para>
  42   <acronym>SPI</acronym> functions return a nonnegative result on
  43   success (either via a returned integer value or in the global
  44   variable <varname>SPI_result</varname>, as described below).  On
  45   error, a negative result or <symbol>NULL</symbol> will be returned.
  46  </para>
  47
  48  <para>
  49   Source code files that use SPI must include the header file
  50   <filename>executor/spi.h</filename>.
  51  </para>
  52
  53
  54 <sect1 id="spi-interface">
  55  <title>Interface Functions</title>
  56
  57  <refentry id="spi-spi-connect">
  58   <indexterm><primary>SPI_connect</primary></indexterm>
  59   <indexterm><primary>SPI_connect_ext</primary></indexterm>
  60
  61   <refmeta>
  62    <refentrytitle>SPI_connect</refentrytitle>
  63    <manvolnum>3</manvolnum>
  64   </refmeta>
  65
  66   <refnamediv>
  67    <refname>SPI_connect</refname>
  68    <refname>SPI_connect_ext</refname>
  69    <refpurpose>connect a C function to the SPI manager</refpurpose>
  70  </refnamediv>
  71
  72  <refsynopsisdiv>
  73 <synopsis>
  74 int SPI_connect(void)
  75 </synopsis>
  76
  77  <synopsis>
  78 int SPI_connect_ext(int <parameter>options</parameter>)
  79 </synopsis>
  80  </refsynopsisdiv>
  81
  82  <refsect1>
  83   <title>Description</title>
  84
  85   <para>
  86    <function>SPI_connect</function> opens a connection from a
  87    C function invocation to the SPI manager.  You must call this
  88    function if you want to execute commands through SPI.  Some utility
  89    SPI functions can be called from unconnected C functions.
  90   </para>
  91
  92   <para>
  93    <function>SPI_connect_ext</function> does the same but has an argument that
  94    allows passing option flags.  Currently, the following option values are
  95    available:
  96    <variablelist>
  97     <varlistentry>
  98      <term><symbol>SPI_OPT_NONATOMIC</symbol></term>
  99      <listitem>
 100       <para>
 101        Sets the SPI connection to be <firstterm>nonatomic</firstterm>, which
 102        means that transaction control calls (<function>SPI_commit</function>,
 103        <function>SPI_rollback</function>) are allowed.  Otherwise,
 104        calling those functions will result in an immediate error.
 105       </para>
 106      </listitem>
 107     </varlistentry>
 108    </variablelist>
 109   </para>
 110
 111   <para>
 112    <literal>SPI_connect()</literal> is equivalent to
 113    <literal>SPI_connect_ext(0)</literal>.
 114   </para>
 115  </refsect1>
 116
 117  <refsect1>
 118   <title>Return Value</title>
 119
 120   <variablelist>
 121    <varlistentry>
 122     <term><symbol>SPI_OK_CONNECT</symbol></term>
 123     <listitem>
 124      <para>
 125       on success
 126      </para>
 127     </listitem>
 128    </varlistentry>
 129   </variablelist>
 130
 131   <para>
 132    The fact that these functions return <type>int</type>
 133    not <type>void</type> is historical.  All failure cases are reported
 134    via <function>ereport</function> or <function>elog</function>.
 135    (In versions before <productname>PostgreSQL</productname> v10,
 136    some but not all failures would be reported with a result value
 137    of <symbol>SPI_ERROR_CONNECT</symbol>.)
 138   </para>
 139  </refsect1>
 140 </refentry>
 141
 142 <!-- *********************************************** -->
 143
 144 <refentry id="spi-spi-finish">
 145  <indexterm><primary>SPI_finish</primary></indexterm>
 146
 147  <refmeta>
 148   <refentrytitle>SPI_finish</refentrytitle>
 149   <manvolnum>3</manvolnum>
 150  </refmeta>
 151
 152  <refnamediv>
 153   <refname>SPI_finish</refname>
 154   <refpurpose>disconnect a C function from the SPI manager</refpurpose>
 155  </refnamediv>
 156
 157  <refsynopsisdiv>
 158 <synopsis>
 159 int SPI_finish(void)
 160 </synopsis>
 161  </refsynopsisdiv>
 162
 163  <refsect1>
 164   <title>Description</title>
 165
 166   <para>
 167    <function>SPI_finish</function> closes an existing connection to
 168    the SPI manager.  You must call this function after completing the
 169    SPI operations needed during your C function's current invocation.
 170    You do not need to worry about making this happen, however, if you
 171    abort the transaction via <literal>elog(ERROR)</literal>.  In that
 172    case SPI will clean itself up automatically.
 173   </para>
 174  </refsect1>
 175
 176  <refsect1>
 177   <title>Return Value</title>
 178
 179   <variablelist>
 180    <varlistentry>
 181     <term><symbol>SPI_OK_FINISH</symbol></term>
 182     <listitem>
 183      <para>
 184       if properly disconnected
 185      </para>
 186     </listitem>
 187    </varlistentry>
 188
 189    <varlistentry>
 190     <term><symbol>SPI_ERROR_UNCONNECTED</symbol></term>
 191     <listitem>
 192      <para>
 193       if called from an unconnected C function
 194      </para>
 195     </listitem>
 196    </varlistentry>
 197   </variablelist>
 198  </refsect1>
 199 </refentry>
 200
 201 <!-- *********************************************** -->
 202
 203 <refentry id="spi-spi-execute">
 204  <indexterm><primary>SPI_execute</primary></indexterm>
 205
 206  <refmeta>
 207   <refentrytitle>SPI_execute</refentrytitle>
 208   <manvolnum>3</manvolnum>
 209  </refmeta>
 210
 211  <refnamediv>
 212   <refname>SPI_execute</refname>
 213   <refpurpose>execute a command</refpurpose>
 214  </refnamediv>
 215
 216  <refsynopsisdiv>
 217 <synopsis>
 218 int SPI_execute(const char * <parameter>command</parameter>, bool <parameter>read_only</parameter>, long <parameter>count</parameter>)
 219 </synopsis>
 220  </refsynopsisdiv>
 221
 222  <refsect1>
 223   <title>Description</title>
 224
 225   <para>
 226    <function>SPI_execute</function> executes the specified SQL command
 227    for <parameter>count</parameter> rows.  If <parameter>read_only</parameter>
 228    is <literal>true</literal>, the command must be read-only, and execution overhead
 229    is somewhat reduced.
 230   </para>
 231
 232   <para>
 233    This function can only be called from a connected C function.
 234   </para>
 235
 236   <para>
 237    If <parameter>count</parameter> is zero then the command is executed
 238    for all rows that it applies to.  If <parameter>count</parameter>
 239    is greater than zero, then no more than <parameter>count</parameter> rows
 240    will be retrieved; execution stops when the count is reached, much like
 241    adding a <literal>LIMIT</literal> clause to the query. For example,
 242 <programlisting>
 243 SPI_execute("SELECT * FROM foo", true, 5);
 244 </programlisting>
 245    will retrieve at most 5 rows from the table.  Note that such a limit
 246    is only effective when the command actually returns rows.  For example,
 247 <programlisting>
 248 SPI_execute("INSERT INTO foo SELECT * FROM bar", false, 5);
 249 </programlisting>
 250    inserts all rows from <structname>bar</structname>, ignoring the
 251    <parameter>count</parameter> parameter.  However, with
 252 <programlisting>
 253 SPI_execute("INSERT INTO foo SELECT * FROM bar RETURNING *", false, 5);
 254 </programlisting>
 255    at most 5 rows would be inserted, since execution would stop after the
 256    fifth <literal>RETURNING</literal> result row is retrieved.
 257   </para>
 258
 259   <para>
 260    You can pass multiple commands in one string;
 261    <function>SPI_execute</function> returns the
 262    result for the command executed last.  The <parameter>count</parameter>
 263    limit applies to each command separately (even though only the last
 264    result will actually be returned).  The limit is not applied to any
 265    hidden commands generated by rules.
 266   </para>
 267
 268   <para>
 269    When <parameter>read_only</parameter> is <literal>false</literal>,
 270    <function>SPI_execute</function> increments the command
 271    counter and computes a new <firstterm>snapshot</firstterm> before executing each
 272    command in the string.  The snapshot does not actually change if the
 273    current transaction isolation level is <literal>SERIALIZABLE</literal> or <literal>REPEATABLE READ</literal>, but in
 274    <literal>READ COMMITTED</literal> mode the snapshot update allows each command to
 275    see the results of newly committed transactions from other sessions.
 276    This is essential for consistent behavior when the commands are modifying
 277    the database.
 278   </para>
 279
 280   <para>
 281    When <parameter>read_only</parameter> is <literal>true</literal>,
 282    <function>SPI_execute</function> does not update either the snapshot
 283    or the command counter, and it allows only plain <command>SELECT</command>
 284    commands to appear in the command string.  The commands are executed
 285    using the snapshot previously established for the surrounding query.
 286    This execution mode is somewhat faster than the read/write mode due
 287    to eliminating per-command overhead.  It also allows genuinely
 288    <firstterm>stable</firstterm> functions to be built: since successive executions
 289    will all use the same snapshot, there will be no change in the results.
 290   </para>
 291
 292   <para>
 293    It is generally unwise to mix read-only and read-write commands within
 294    a single function using SPI; that could result in very confusing behavior,
 295    since the read-only queries would not see the results of any database
 296    updates done by the read-write queries.
 297   </para>
 298
 299   <para>
 300    The actual number of rows for which the (last) command was executed
 301    is returned in the global variable <varname>SPI_processed</varname>.
 302    If the return value of the function is <symbol>SPI_OK_SELECT</symbol>,
 303    <symbol>SPI_OK_INSERT_RETURNING</symbol>,
 304    <symbol>SPI_OK_DELETE_RETURNING</symbol>,
 305    <symbol>SPI_OK_UPDATE_RETURNING</symbol>, or
 306    <symbol>SPI_OK_MERGE_RETURNING</symbol>,
 307    then you can use the
 308    global pointer <literal>SPITupleTable *SPI_tuptable</literal> to
 309    access the result rows.  Some utility commands (such as
 310    <command>EXPLAIN</command>) also return row sets, and <literal>SPI_tuptable</literal>
 311    will contain the result in these cases too. Some utility commands
 312    (<command>COPY</command>, <command>CREATE TABLE AS</command>) don't return a row set, so
 313    <literal>SPI_tuptable</literal> is NULL, but they still return the number of
 314    rows processed in <varname>SPI_processed</varname>.
 315   </para>
 316
 317   <para>
 318    The structure <structname>SPITupleTable</structname> is defined
 319    thus:
 320 <programlisting>
 321 typedef struct SPITupleTable
 322 {
 323     /* Public members */
 324     TupleDesc   tupdesc;        /* tuple descriptor */
 325     HeapTuple  *vals;           /* array of tuples */
 326     uint64      numvals;        /* number of valid tuples */
 327
 328     /* Private members, not intended for external callers */
 329     uint64      alloced;        /* allocated length of vals array */
 330     MemoryContext tuptabcxt;    /* memory context of result table */
 331     slist_node  next;           /* link for internal bookkeeping */
 332     SubTransactionId subid;     /* subxact in which tuptable was created */
 333 } SPITupleTable;
 334 </programlisting>
 335    The fields <structfield>tupdesc</structfield>,
 336    <structfield>vals</structfield>, and
 337    <structfield>numvals</structfield>
 338    can be used by SPI callers; the remaining fields are internal.
 339    <structfield>vals</structfield> is an array of pointers to rows.
 340    The number of rows is given by <structfield>numvals</structfield>
 341    (for somewhat historical reasons, this count is also returned
 342    in <varname>SPI_processed</varname>).
 343    <structfield>tupdesc</structfield> is a row descriptor which you can pass to
 344    SPI functions dealing with rows.
 345   </para>
 346
 347   <para>
 348    <function>SPI_finish</function> frees all
 349    <structname>SPITupleTable</structname>s allocated during the current
 350    C function.  You can free a particular result table earlier, if you
 351    are done with it, by calling <function>SPI_freetuptable</function>.
 352   </para>
 353  </refsect1>
 354
 355  <refsect1>
 356   <title>Arguments</title>
 357
 358   <variablelist>
 359    <varlistentry>
 360     <term><literal>const char * <parameter>command</parameter></literal></term>
 361     <listitem>
 362      <para>
 363       string containing command to execute
 364      </para>
 365     </listitem>
 366    </varlistentry>
 367
 368    <varlistentry>
 369     <term><literal>bool <parameter>read_only</parameter></literal></term>
 370     <listitem>
 371      <para><literal>true</literal> for read-only execution</para>
 372     </listitem>
 373    </varlistentry>
 374
 375    <varlistentry>
 376     <term><literal>long <parameter>count</parameter></literal></term>
 377     <listitem>
 378      <para>
 379       maximum number of rows to return,
 380       or <literal>0</literal> for no limit
 381      </para>
 382     </listitem>
 383    </varlistentry>
 384   </variablelist>
 385  </refsect1>
 386
 387  <refsect1>
 388   <title>Return Value</title>
 389
 390   <para>
 391    If the execution of the command was successful then one of the
 392    following (nonnegative) values will be returned:
 393
 394    <variablelist>
 395     <varlistentry>
 396      <term><symbol>SPI_OK_SELECT</symbol></term>
 397      <listitem>
 398       <para>
 399        if a <command>SELECT</command> (but not <command>SELECT
 400        INTO</command>) was executed
 401       </para>
 402      </listitem>
 403     </varlistentry>
 404
 405     <varlistentry>
 406      <term><symbol>SPI_OK_SELINTO</symbol></term>
 407      <listitem>
 408       <para>
 409        if a <command>SELECT INTO</command> was executed
 410       </para>
 411      </listitem>
 412     </varlistentry>
 413
 414     <varlistentry>
 415      <term><symbol>SPI_OK_INSERT</symbol></term>
 416      <listitem>
 417       <para>
 418        if an <command>INSERT</command> was executed
 419       </para>
 420      </listitem>
 421     </varlistentry>
 422
 423     <varlistentry>
 424      <term><symbol>SPI_OK_DELETE</symbol></term>
 425      <listitem>
 426       <para>
 427        if a <command>DELETE</command> was executed
 428       </para>
 429      </listitem>
 430     </varlistentry>
 431
 432     <varlistentry>
 433      <term><symbol>SPI_OK_UPDATE</symbol></term>
 434      <listitem>
 435       <para>
 436        if an <command>UPDATE</command> was executed
 437       </para>
 438      </listitem>
 439     </varlistentry>
 440
 441     <varlistentry>
 442      <term><symbol>SPI_OK_MERGE</symbol></term>
 443      <listitem>
 444       <para>
 445        if a <command>MERGE</command> was executed
 446       </para>
 447      </listitem>
 448     </varlistentry>
 449
 450     <varlistentry>
 451      <term><symbol>SPI_OK_INSERT_RETURNING</symbol></term>
 452      <listitem>
 453       <para>
 454        if an <command>INSERT RETURNING</command> was executed
 455       </para>
 456      </listitem>
 457     </varlistentry>
 458
 459     <varlistentry>
 460      <term><symbol>SPI_OK_DELETE_RETURNING</symbol></term>
 461      <listitem>
 462       <para>
 463        if a <command>DELETE RETURNING</command> was executed
 464       </para>
 465      </listitem>
 466     </varlistentry>
 467
 468     <varlistentry>
 469      <term><symbol>SPI_OK_UPDATE_RETURNING</symbol></term>
 470      <listitem>
 471       <para>
 472        if an <command>UPDATE RETURNING</command> was executed
 473       </para>
 474      </listitem>
 475     </varlistentry>
 476
 477     <varlistentry>
 478      <term><symbol>SPI_OK_MERGE_RETURNING</symbol></term>
 479      <listitem>
 480       <para>
 481        if a <command>MERGE RETURNING</command> was executed
 482       </para>
 483      </listitem>
 484     </varlistentry>
 485
 486     <varlistentry>
 487      <term><symbol>SPI_OK_UTILITY</symbol></term>
 488      <listitem>
 489       <para>
 490        if a utility command (e.g., <command>CREATE TABLE</command>)
 491        was executed
 492       </para>
 493      </listitem>
 494     </varlistentry>
 495
 496     <varlistentry>
 497      <term><symbol>SPI_OK_REWRITTEN</symbol></term>
 498      <listitem>
 499       <para>
 500        if the command was rewritten into another kind of command (e.g.,
 501        <command>UPDATE</command> became an <command>INSERT</command>) by a <link linkend="rules">rule</link>.
 502       </para>
 503      </listitem>
 504     </varlistentry>
 505    </variablelist>
 506   </para>
 507
 508   <para>
 509    On error, one of the following negative values is returned:
 510
 511    <variablelist>
 512     <varlistentry>
 513      <term><symbol>SPI_ERROR_ARGUMENT</symbol></term>
 514      <listitem>
 515       <para>
 516        if <parameter>command</parameter> is <symbol>NULL</symbol> or
 517        <parameter>count</parameter> is less than 0
 518       </para>
 519      </listitem>
 520     </varlistentry>
 521
 522     <varlistentry>
 523      <term><symbol>SPI_ERROR_COPY</symbol></term>
 524      <listitem>
 525       <para>
 526        if <command>COPY TO stdout</command> or <command>COPY FROM stdin</command>
 527        was attempted
 528       </para>
 529      </listitem>
 530     </varlistentry>
 531
 532     <varlistentry>
 533      <term><symbol>SPI_ERROR_TRANSACTION</symbol></term>
 534      <listitem>
 535       <para>
 536        if a transaction manipulation command was attempted
 537        (<command>BEGIN</command>,
 538        <command>COMMIT</command>,
 539        <command>ROLLBACK</command>,
 540        <command>SAVEPOINT</command>,
 541        <command>PREPARE TRANSACTION</command>,
 542        <command>COMMIT PREPARED</command>,
 543        <command>ROLLBACK PREPARED</command>,
 544        or any variant thereof)
 545       </para>
 546      </listitem>
 547     </varlistentry>
 548
 549     <varlistentry>
 550      <term><symbol>SPI_ERROR_OPUNKNOWN</symbol></term>
 551      <listitem>
 552       <para>
 553        if the command type is unknown (shouldn't happen)
 554       </para>
 555      </listitem>
 556     </varlistentry>
 557
 558     <varlistentry>
 559      <term><symbol>SPI_ERROR_UNCONNECTED</symbol></term>
 560      <listitem>
 561       <para>
 562        if called from an unconnected C function
 563       </para>
 564      </listitem>
 565     </varlistentry>
 566    </variablelist>
 567   </para>
 568  </refsect1>
 569
 570  <refsect1>
 571   <title>Notes</title>
 572
 573   <para>
 574    All SPI query-execution functions set both
 575    <varname>SPI_processed</varname> and
 576    <varname>SPI_tuptable</varname> (just the pointer, not the contents
 577    of the structure).  Save these two global variables into local
 578    C function variables if you need to access the result table of
 579    <function>SPI_execute</function> or another query-execution function
 580    across later calls.
 581   </para>
 582  </refsect1>
 583 </refentry>
 584
 585 <!-- *********************************************** -->
 586
 587 <refentry id="spi-spi-exec">
 588  <indexterm><primary>SPI_exec</primary></indexterm>
 589
 590  <refmeta>
 591   <refentrytitle>SPI_exec</refentrytitle>
 592   <manvolnum>3</manvolnum>
 593  </refmeta>
 594
 595  <refnamediv>
 596   <refname>SPI_exec</refname>
 597   <refpurpose>execute a read/write command</refpurpose>
 598  </refnamediv>
 599
 600  <refsynopsisdiv>
 601 <synopsis>
 602 int SPI_exec(const char * <parameter>command</parameter>, long <parameter>count</parameter>)
 603 </synopsis>
 604  </refsynopsisdiv>
 605
 606  <refsect1>
 607   <title>Description</title>
 608
 609   <para>
 610    <function>SPI_exec</function> is the same as
 611    <function>SPI_execute</function>, with the latter's
 612    <parameter>read_only</parameter> parameter always taken as
 613    <literal>false</literal>.
 614   </para>
 615  </refsect1>
 616
 617  <refsect1>
 618   <title>Arguments</title>
 619
 620   <variablelist>
 621    <varlistentry>
 622     <term><literal>const char * <parameter>command</parameter></literal></term>
 623     <listitem>
 624      <para>
 625       string containing command to execute
 626      </para>
 627     </listitem>
 628    </varlistentry>
 629
 630    <varlistentry>
 631     <term><literal>long <parameter>count</parameter></literal></term>
 632     <listitem>
 633      <para>
 634       maximum number of rows to return,
 635       or <literal>0</literal> for no limit
 636      </para>
 637     </listitem>
 638    </varlistentry>
 639   </variablelist>
 640  </refsect1>
 641
 642  <refsect1>
 643   <title>Return Value</title>
 644
 645   <para>
 646    See <function>SPI_execute</function>.
 647   </para>
 648  </refsect1>
 649 </refentry>
 650
 651 <!-- *********************************************** -->
 652
 653 <refentry id="spi-spi-execute-extended">
 654  <indexterm><primary>SPI_execute_extended</primary></indexterm>
 655
 656  <refmeta>
 657   <refentrytitle>SPI_execute_extended</refentrytitle>
 658   <manvolnum>3</manvolnum>
 659  </refmeta>
 660
 661  <refnamediv>
 662   <refname>SPI_execute_extended</refname>
 663   <refpurpose>execute a command with out-of-line parameters</refpurpose>
 664  </refnamediv>
 665
 666  <refsynopsisdiv>
 667 <synopsis>
 668 int SPI_execute_extended(const char *<parameter>command</parameter>,
 669                          const SPIExecuteOptions * <parameter>options</parameter>)
 670 </synopsis>
 671  </refsynopsisdiv>
 672
 673  <refsect1>
 674   <title>Description</title>
 675
 676   <para>
 677    <function>SPI_execute_extended</function> executes a command that might
 678    include references to externally supplied parameters.  The command text
 679    refers to a parameter as <literal>$<replaceable>n</replaceable></literal>,
 680    and the <parameter>options-&gt;params</parameter> object (if supplied)
 681    provides values and type information for each such symbol.
 682    Various execution options can be specified
 683    in the <parameter>options</parameter> struct, too.
 684   </para>
 685
 686   <para>
 687    The <parameter>options-&gt;params</parameter> object should normally
 688    mark each parameter with the <literal>PARAM_FLAG_CONST</literal> flag,
 689    since a one-shot plan is always used for the query.
 690   </para>
 691
 692   <para>
 693    If <parameter>options-&gt;dest</parameter> is not NULL, then result
 694    tuples are passed to that object as they are generated by the executor,
 695    instead of being accumulated in <varname>SPI_tuptable</varname>.  Using
 696    a caller-supplied <literal>DestReceiver</literal> object is particularly
 697    helpful for queries that might generate many tuples, since the data can
 698    be processed on-the-fly instead of being accumulated in memory.
 699   </para>
 700  </refsect1>
 701
 702  <refsect1>
 703   <title>Arguments</title>
 704
 705   <variablelist>
 706    <varlistentry>
 707     <term><literal>const char * <parameter>command</parameter></literal></term>
 708     <listitem>
 709      <para>
 710       command string
 711      </para>
 712     </listitem>
 713    </varlistentry>
 714
 715    <varlistentry>
 716     <term><literal>const SPIExecuteOptions * <parameter>options</parameter></literal></term>
 717     <listitem>
 718      <para>
 719       struct containing optional arguments
 720      </para>
 721     </listitem>
 722    </varlistentry>
 723   </variablelist>
 724
 725   <para>
 726    Callers should always zero out the entire <parameter>options</parameter>
 727    struct, then fill whichever fields they want to set.  This ensures forward
 728    compatibility of code, since any fields that are added to the struct in
 729    future will be defined to behave backwards-compatibly if they are zero.
 730    The currently available <parameter>options</parameter> fields are:
 731   </para>
 732
 733   <variablelist>
 734    <varlistentry>
 735     <term><literal>ParamListInfo <parameter>params</parameter></literal></term>
 736     <listitem>
 737      <para>
 738       data structure containing query parameter types and values; NULL if none
 739      </para>
 740     </listitem>
 741    </varlistentry>
 742
 743    <varlistentry>
 744     <term><literal>bool <parameter>read_only</parameter></literal></term>
 745     <listitem>
 746      <para><literal>true</literal> for read-only execution</para>
 747     </listitem>
 748    </varlistentry>
 749
 750    <varlistentry>
 751     <term><literal>bool <parameter>allow_nonatomic</parameter></literal></term>
 752     <listitem>
 753      <para>
 754       <literal>true</literal> allows non-atomic execution of CALL and DO
 755       statements (but this field is ignored unless
 756       the <symbol>SPI_OPT_NONATOMIC</symbol> flag was passed
 757       to <function>SPI_connect_ext</function>)
 758      </para>
 759     </listitem>
 760    </varlistentry>
 761
 762    <varlistentry>
 763     <term><literal>bool <parameter>must_return_tuples</parameter></literal></term>
 764     <listitem>
 765      <para>
 766       if <literal>true</literal>, raise error if the query is not of a kind
 767       that returns tuples (this does not forbid the case where it happens to
 768       return zero tuples)
 769      </para>
 770     </listitem>
 771    </varlistentry>
 772
 773    <varlistentry>
 774     <term><literal>uint64 <parameter>tcount</parameter></literal></term>
 775     <listitem>
 776      <para>
 777       maximum number of rows to return,
 778       or <literal>0</literal> for no limit
 779      </para>
 780     </listitem>
 781    </varlistentry>
 782
 783    <varlistentry>
 784     <term><literal>DestReceiver * <parameter>dest</parameter></literal></term>
 785     <listitem>
 786      <para>
 787       <literal>DestReceiver</literal> object that will receive any tuples
 788       emitted by the query; if NULL, result tuples are accumulated into
 789       a <varname>SPI_tuptable</varname> structure, as
 790       in <function>SPI_execute</function>
 791      </para>
 792     </listitem>
 793    </varlistentry>
 794
 795    <varlistentry>
 796     <term><literal>ResourceOwner <parameter>owner</parameter></literal></term>
 797     <listitem>
 798      <para>
 799       This field is present for consistency
 800       with <function>SPI_execute_plan_extended</function>, but it is
 801       ignored, since the plan used
 802       by <function>SPI_execute_extended</function> is never saved.
 803      </para>
 804     </listitem>
 805    </varlistentry>
 806   </variablelist>
 807  </refsect1>
 808
 809  <refsect1>
 810   <title>Return Value</title>
 811
 812   <para>
 813    The return value is the same as for <function>SPI_execute</function>.
 814   </para>
 815
 816   <para>
 817    When <parameter>options-&gt;dest</parameter> is NULL,
 818    <varname>SPI_processed</varname> and
 819    <varname>SPI_tuptable</varname> are set as in
 820    <function>SPI_execute</function>.
 821    When <parameter>options-&gt;dest</parameter> is not NULL,
 822    <varname>SPI_processed</varname> is set to zero and
 823    <varname>SPI_tuptable</varname> is set to NULL.  If a tuple count
 824    is required, the caller's <literal>DestReceiver</literal> object must
 825    calculate it.
 826   </para>
 827  </refsect1>
 828 </refentry>
 829
 830 <!-- *********************************************** -->
 831
 832 <refentry id="spi-spi-execute-with-args">
 833  <indexterm><primary>SPI_execute_with_args</primary></indexterm>
 834
 835  <refmeta>
 836   <refentrytitle>SPI_execute_with_args</refentrytitle>
 837   <manvolnum>3</manvolnum>
 838  </refmeta>
 839
 840  <refnamediv>
 841   <refname>SPI_execute_with_args</refname>
 842   <refpurpose>execute a command with out-of-line parameters</refpurpose>
 843  </refnamediv>
 844
 845  <refsynopsisdiv>
 846 <synopsis>
 847 int SPI_execute_with_args(const char *<parameter>command</parameter>,
 848                           int <parameter>nargs</parameter>, Oid *<parameter>argtypes</parameter>,
 849                           Datum *<parameter>values</parameter>, const char *<parameter>nulls</parameter>,
 850                           bool <parameter>read_only</parameter>, long <parameter>count</parameter>)
 851 </synopsis>
 852  </refsynopsisdiv>
 853
 854  <refsect1>
 855   <title>Description</title>
 856
 857   <para>
 858    <function>SPI_execute_with_args</function> executes a command that might
 859    include references to externally supplied parameters.  The command text
 860    refers to a parameter as <literal>$<replaceable>n</replaceable></literal>, and
 861    the call specifies data types and values for each such symbol.
 862    <parameter>read_only</parameter> and <parameter>count</parameter> have
 863    the same interpretation as in <function>SPI_execute</function>.
 864   </para>
 865
 866   <para>
 867    The main advantage of this routine compared to
 868    <function>SPI_execute</function> is that data values can be inserted
 869    into the command without tedious quoting/escaping, and thus with much
 870    less risk of SQL-injection attacks.
 871   </para>
 872
 873   <para>
 874    Similar results can be achieved with <function>SPI_prepare</function> followed by
 875    <function>SPI_execute_plan</function>; however, when using this function
 876    the query plan is always customized to the specific parameter values
 877    provided.
 878    For one-time query execution, this function should be preferred.
 879    If the same command is to be executed with many different parameters,
 880    either method might be faster, depending on the cost of re-planning
 881    versus the benefit of custom plans.
 882   </para>
 883  </refsect1>
 884
 885  <refsect1>
 886   <title>Arguments</title>
 887
 888   <variablelist>
 889    <varlistentry>
 890     <term><literal>const char * <parameter>command</parameter></literal></term>
 891     <listitem>
 892      <para>
 893       command string
 894      </para>
 895     </listitem>
 896    </varlistentry>
 897
 898    <varlistentry>
 899     <term><literal>int <parameter>nargs</parameter></literal></term>
 900     <listitem>
 901      <para>
 902       number of input parameters (<literal>$1</literal>, <literal>$2</literal>, etc.)
 903      </para>
 904     </listitem>
 905    </varlistentry>
 906
 907    <varlistentry>
 908     <term><literal>Oid * <parameter>argtypes</parameter></literal></term>
 909     <listitem>
 910      <para>
 911       an array of length <parameter>nargs</parameter>, containing the
 912       <acronym>OID</acronym>s of the data types of the parameters
 913      </para>
 914     </listitem>
 915    </varlistentry>
 916
 917    <varlistentry>
 918     <term><literal>Datum * <parameter>values</parameter></literal></term>
 919     <listitem>
 920      <para>
 921       an array of length <parameter>nargs</parameter>, containing the actual
 922       parameter values
 923      </para>
 924     </listitem>
 925    </varlistentry>
 926
 927    <varlistentry>
 928     <term><literal>const char * <parameter>nulls</parameter></literal></term>
 929     <listitem>
 930      <para>
 931       an array of length <parameter>nargs</parameter>, describing which
 932       parameters are null
 933      </para>
 934
 935      <para>
 936       If <parameter>nulls</parameter> is <symbol>NULL</symbol> then
 937       <function>SPI_execute_with_args</function> assumes that no parameters
 938       are null.  Otherwise, each entry of the <parameter>nulls</parameter>
 939       array should be <literal>'&nbsp;'</literal> if the corresponding parameter
 940       value is non-null, or <literal>'n'</literal> if the corresponding parameter
 941       value is null.  (In the latter case, the actual value in the
 942       corresponding <parameter>values</parameter> entry doesn't matter.)  Note
 943       that <parameter>nulls</parameter> is not a text string, just an array:
 944       it does not need a <literal>'\0'</literal> terminator.
 945      </para>
 946     </listitem>
 947    </varlistentry>
 948
 949    <varlistentry>
 950     <term><literal>bool <parameter>read_only</parameter></literal></term>
 951     <listitem>
 952      <para><literal>true</literal> for read-only execution</para>
 953     </listitem>
 954    </varlistentry>
 955
 956    <varlistentry>
 957     <term><literal>long <parameter>count</parameter></literal></term>
 958     <listitem>
 959      <para>
 960       maximum number of rows to return,
 961       or <literal>0</literal> for no limit
 962      </para>
 963     </listitem>
 964    </varlistentry>
 965   </variablelist>
 966  </refsect1>
 967
 968  <refsect1>
 969   <title>Return Value</title>
 970
 971   <para>
 972    The return value is the same as for <function>SPI_execute</function>.
 973   </para>
 974
 975   <para>
 976    <varname>SPI_processed</varname> and
 977    <varname>SPI_tuptable</varname> are set as in
 978    <function>SPI_execute</function> if successful.
 979   </para>
 980  </refsect1>
 981 </refentry>
 982
 983 <!-- *********************************************** -->
 984
 985 <refentry id="spi-spi-prepare">
 986  <indexterm><primary>SPI_prepare</primary></indexterm>
 987
 988  <refmeta>
 989   <refentrytitle>SPI_prepare</refentrytitle>
 990   <manvolnum>3</manvolnum>
 991  </refmeta>
 992
 993  <refnamediv>
 994   <refname>SPI_prepare</refname>
 995   <refpurpose>prepare a statement, without executing it yet</refpurpose>
 996  </refnamediv>
 997
 998  <refsynopsisdiv>
 999 <synopsis>
1000 SPIPlanPtr SPI_prepare(const char * <parameter>command</parameter>, int <parameter>nargs</parameter>, Oid * <parameter>argtypes</parameter>)
1001 </synopsis>
1002  </refsynopsisdiv>
1003
1004  <refsect1>
1005   <title>Description</title>
1006
1007   <para>
1008    <function>SPI_prepare</function> creates and returns a prepared
1009    statement for the specified command, but doesn't execute the command.
1010    The prepared statement can later be executed repeatedly using
1011    <function>SPI_execute_plan</function>.
1012   </para>
1013
1014   <para>
1015    When the same or a similar command is to be executed repeatedly, it
1016    is generally advantageous to perform parse analysis only once, and
1017    might furthermore be advantageous to re-use an execution plan for the
1018    command.
1019    <function>SPI_prepare</function> converts a command string into a
1020    prepared statement that encapsulates the results of parse analysis.
1021    The prepared statement also provides a place for caching an execution plan
1022    if it is found that generating a custom plan for each execution is not
1023    helpful.
1024   </para>
1025
1026   <para>
1027    A prepared command can be generalized by writing parameters
1028    (<literal>$1</literal>, <literal>$2</literal>, etc.) in place of what would be
1029    constants in a normal command.  The actual values of the parameters
1030    are then specified when <function>SPI_execute_plan</function> is called.
1031    This allows the prepared command to be used over a wider range of
1032    situations than would be possible without parameters.
1033   </para>
1034
1035   <para>
1036    The statement returned by <function>SPI_prepare</function> can be used
1037    only in the current invocation of the C function, since
1038    <function>SPI_finish</function> frees memory allocated for such a
1039    statement.  But the statement can be saved for longer using the functions
1040    <function>SPI_keepplan</function> or <function>SPI_saveplan</function>.
1041   </para>
1042  </refsect1>
1043
1044  <refsect1>
1045   <title>Arguments</title>
1046
1047   <variablelist>
1048    <varlistentry>
1049     <term><literal>const char * <parameter>command</parameter></literal></term>
1050     <listitem>
1051      <para>
1052       command string
1053      </para>
1054     </listitem>
1055    </varlistentry>
1056
1057    <varlistentry>
1058     <term><literal>int <parameter>nargs</parameter></literal></term>
1059     <listitem>
1060      <para>
1061       number of input parameters (<literal>$1</literal>, <literal>$2</literal>, etc.)
1062      </para>
1063     </listitem>
1064    </varlistentry>
1065
1066    <varlistentry>
1067     <term><literal>Oid * <parameter>argtypes</parameter></literal></term>
1068     <listitem>
1069      <para>
1070       pointer to an array containing the <acronym>OID</acronym>s of
1071       the data types of the parameters
1072      </para>
1073     </listitem>
1074    </varlistentry>
1075   </variablelist>
1076  </refsect1>
1077
1078  <refsect1>
1079   <title>Return Value</title>
1080
1081   <para>
1082    <function>SPI_prepare</function> returns a non-null pointer to an
1083    <type>SPIPlan</type>, which is an opaque struct representing a prepared
1084    statement.  On error, <symbol>NULL</symbol> will be returned,
1085    and <varname>SPI_result</varname> will be set to one of the same
1086    error codes used by <function>SPI_execute</function>, except that
1087    it is set to <symbol>SPI_ERROR_ARGUMENT</symbol> if
1088    <parameter>command</parameter> is <symbol>NULL</symbol>, or if
1089    <parameter>nargs</parameter> is less than 0, or if <parameter>nargs</parameter> is
1090    greater than 0 and <parameter>argtypes</parameter> is <symbol>NULL</symbol>.
1091   </para>
1092  </refsect1>
1093
1094  <refsect1>
1095   <title>Notes</title>
1096
1097   <para>
1098    If no parameters are defined, a generic plan will be created at the
1099    first use of <function>SPI_execute_plan</function>, and used for all
1100    subsequent executions as well.  If there are parameters, the first few uses
1101    of <function>SPI_execute_plan</function> will generate custom plans
1102    that are specific to the supplied parameter values.  After enough uses
1103    of the same prepared statement, <function>SPI_execute_plan</function> will
1104    build a generic plan, and if that is not too much more expensive than the
1105    custom plans, it will start using the generic plan instead of re-planning
1106    each time.  If this default behavior is unsuitable, you can alter it by
1107    passing the <literal>CURSOR_OPT_GENERIC_PLAN</literal> or
1108    <literal>CURSOR_OPT_CUSTOM_PLAN</literal> flag to
1109    <function>SPI_prepare_cursor</function>, to force use of generic or custom
1110    plans respectively.
1111   </para>
1112
1113   <para>
1114    Although the main point of a prepared statement is to avoid repeated parse
1115    analysis and planning of the statement, <productname>PostgreSQL</productname> will
1116    force re-analysis and re-planning of the statement before using it
1117    whenever database objects used in the statement have undergone
1118    definitional (DDL) changes since the previous use of the prepared
1119    statement.  Also, if the value of <xref linkend="guc-search-path"/> changes
1120    from one use to the next, the statement will be re-parsed using the new
1121    <varname>search_path</varname>.  (This latter behavior is new as of
1122    <productname>PostgreSQL</productname> 9.3.)  See <xref
1123    linkend="sql-prepare"/> for more information about the behavior of prepared
1124    statements.
1125   </para>
1126
1127   <para>
1128    This function should only be called from a connected C function.
1129   </para>
1130
1131   <para>
1132    <type>SPIPlanPtr</type> is declared as a pointer to an opaque struct type in
1133    <filename>spi.h</filename>.  It is unwise to try to access its contents
1134    directly, as that makes your code much more likely to break in
1135    future revisions of <productname>PostgreSQL</productname>.
1136   </para>
1137
1138   <para>
1139    The name <type>SPIPlanPtr</type> is somewhat historical, since the data
1140    structure no longer necessarily contains an execution plan.
1141   </para>
1142  </refsect1>
1143 </refentry>
1144
1145 <!-- *********************************************** -->
1146
1147 <refentry id="spi-spi-prepare-cursor">
1148  <indexterm><primary>SPI_prepare_cursor</primary></indexterm>
1149
1150  <refmeta>
1151   <refentrytitle>SPI_prepare_cursor</refentrytitle>
1152   <manvolnum>3</manvolnum>
1153  </refmeta>
1154
1155  <refnamediv>
1156   <refname>SPI_prepare_cursor</refname>
1157   <refpurpose>prepare a statement, without executing it yet</refpurpose>
1158  </refnamediv>
1159
1160  <refsynopsisdiv>
1161 <synopsis>
1162 SPIPlanPtr SPI_prepare_cursor(const char * <parameter>command</parameter>, int <parameter>nargs</parameter>,
1163                               Oid * <parameter>argtypes</parameter>, int <parameter>cursorOptions</parameter>)
1164 </synopsis>
1165  </refsynopsisdiv>
1166
1167  <refsect1>
1168   <title>Description</title>
1169
1170   <para>
1171    <function>SPI_prepare_cursor</function> is identical to
1172    <function>SPI_prepare</function>, except that it also allows specification
1173    of the planner's <quote>cursor options</quote> parameter.  This is a bit mask
1174    having the values shown in <filename>nodes/parsenodes.h</filename>
1175    for the <structfield>options</structfield> field of <structname>DeclareCursorStmt</structname>.
1176    <function>SPI_prepare</function> always takes the cursor options as zero.
1177   </para>
1178
1179   <para>
1180    This function is now deprecated in favor
1181    of <function>SPI_prepare_extended</function>.
1182   </para>
1183  </refsect1>
1184
1185  <refsect1>
1186   <title>Arguments</title>
1187
1188   <variablelist>
1189    <varlistentry>
1190     <term><literal>const char * <parameter>command</parameter></literal></term>
1191     <listitem>
1192      <para>
1193       command string
1194      </para>
1195     </listitem>
1196    </varlistentry>
1197
1198    <varlistentry>
1199     <term><literal>int <parameter>nargs</parameter></literal></term>
1200     <listitem>
1201      <para>
1202       number of input parameters (<literal>$1</literal>, <literal>$2</literal>, etc.)
1203      </para>
1204     </listitem>
1205    </varlistentry>
1206
1207    <varlistentry>
1208     <term><literal>Oid * <parameter>argtypes</parameter></literal></term>
1209     <listitem>
1210      <para>
1211       pointer to an array containing the <acronym>OID</acronym>s of
1212       the data types of the parameters
1213      </para>
1214     </listitem>
1215    </varlistentry>
1216
1217    <varlistentry>
1218     <term><literal>int <parameter>cursorOptions</parameter></literal></term>
1219     <listitem>
1220      <para>
1221       integer bit mask of cursor options; zero produces default behavior
1222      </para>
1223     </listitem>
1224    </varlistentry>
1225   </variablelist>
1226  </refsect1>
1227
1228  <refsect1>
1229   <title>Return Value</title>
1230
1231   <para>
1232    <function>SPI_prepare_cursor</function> has the same return conventions as
1233    <function>SPI_prepare</function>.
1234   </para>
1235  </refsect1>
1236
1237  <refsect1>
1238   <title>Notes</title>
1239
1240   <para>
1241    Useful bits to set in <parameter>cursorOptions</parameter> include
1242    <symbol>CURSOR_OPT_SCROLL</symbol>,
1243    <symbol>CURSOR_OPT_NO_SCROLL</symbol>,
1244    <symbol>CURSOR_OPT_FAST_PLAN</symbol>,
1245    <symbol>CURSOR_OPT_GENERIC_PLAN</symbol>, and
1246    <symbol>CURSOR_OPT_CUSTOM_PLAN</symbol>.  Note in particular that
1247    <symbol>CURSOR_OPT_HOLD</symbol> is ignored.
1248   </para>
1249  </refsect1>
1250 </refentry>
1251
1252 <!-- *********************************************** -->
1253
1254 <refentry id="spi-spi-prepare-extended">
1255  <indexterm><primary>SPI_prepare_extended</primary></indexterm>
1256
1257  <refmeta>
1258   <refentrytitle>SPI_prepare_extended</refentrytitle>
1259   <manvolnum>3</manvolnum>
1260  </refmeta>
1261
1262  <refnamediv>
1263   <refname>SPI_prepare_extended</refname>
1264   <refpurpose>prepare a statement, without executing it yet</refpurpose>
1265  </refnamediv>
1266
1267  <refsynopsisdiv>
1268 <synopsis>
1269 SPIPlanPtr SPI_prepare_extended(const char * <parameter>command</parameter>,
1270                                 const SPIPrepareOptions * <parameter>options</parameter>)
1271 </synopsis>
1272  </refsynopsisdiv>
1273
1274  <refsect1>
1275   <title>Description</title>
1276
1277   <para>
1278    <function>SPI_prepare_extended</function> creates and returns a prepared
1279    statement for the specified command, but doesn't execute the command.
1280    This function is equivalent to <function>SPI_prepare</function>,
1281    with the addition that the caller can specify options to control
1282    the parsing of external parameter references, as well as other facets
1283    of query parsing and planning.
1284   </para>
1285  </refsect1>
1286
1287  <refsect1>
1288   <title>Arguments</title>
1289
1290   <variablelist>
1291    <varlistentry>
1292     <term><literal>const char * <parameter>command</parameter></literal></term>
1293     <listitem>
1294      <para>
1295       command string
1296      </para>
1297     </listitem>
1298    </varlistentry>
1299
1300    <varlistentry>
1301     <term><literal>const SPIPrepareOptions * <parameter>options</parameter></literal></term>
1302     <listitem>
1303      <para>
1304       struct containing optional arguments
1305      </para>
1306     </listitem>
1307    </varlistentry>
1308   </variablelist>
1309
1310   <para>
1311    Callers should always zero out the entire <parameter>options</parameter>
1312    struct, then fill whichever fields they want to set.  This ensures forward
1313    compatibility of code, since any fields that are added to the struct in
1314    future will be defined to behave backwards-compatibly if they are zero.
1315    The currently available <parameter>options</parameter> fields are:
1316   </para>
1317
1318   <variablelist>
1319    <varlistentry>
1320     <term><literal>ParserSetupHook <parameter>parserSetup</parameter></literal></term>
1321     <listitem>
1322      <para>
1323       Parser hook setup function
1324      </para>
1325     </listitem>
1326    </varlistentry>
1327
1328    <varlistentry>
1329     <term><literal>void * <parameter>parserSetupArg</parameter></literal></term>
1330     <listitem>
1331      <para>
1332       pass-through argument for <parameter>parserSetup</parameter>
1333      </para>
1334     </listitem>
1335    </varlistentry>
1336
1337    <varlistentry>
1338     <term><literal>RawParseMode <parameter>parseMode</parameter></literal></term>
1339     <listitem>
1340      <para>
1341       mode for raw parsing; <literal>RAW_PARSE_DEFAULT</literal> (zero)
1342       produces default behavior
1343      </para>
1344     </listitem>
1345    </varlistentry>
1346
1347    <varlistentry>
1348     <term><literal>int <parameter>cursorOptions</parameter></literal></term>
1349     <listitem>
1350      <para>
1351       integer bit mask of cursor options; zero produces default behavior
1352      </para>
1353     </listitem>
1354    </varlistentry>
1355   </variablelist>
1356  </refsect1>
1357
1358  <refsect1>
1359   <title>Return Value</title>
1360
1361   <para>
1362    <function>SPI_prepare_extended</function> has the same return conventions as
1363    <function>SPI_prepare</function>.
1364   </para>
1365  </refsect1>
1366 </refentry>
1367
1368 <!-- *********************************************** -->
1369
1370 <refentry id="spi-spi-prepare-params">
1371  <indexterm><primary>SPI_prepare_params</primary></indexterm>
1372
1373  <refmeta>
1374   <refentrytitle>SPI_prepare_params</refentrytitle>
1375   <manvolnum>3</manvolnum>
1376  </refmeta>
1377
1378  <refnamediv>
1379   <refname>SPI_prepare_params</refname>
1380   <refpurpose>prepare a statement, without executing it yet</refpurpose>
1381  </refnamediv>
1382
1383  <refsynopsisdiv>
1384 <synopsis>
1385 SPIPlanPtr SPI_prepare_params(const char * <parameter>command</parameter>,
1386                               ParserSetupHook <parameter>parserSetup</parameter>,
1387                               void * <parameter>parserSetupArg</parameter>,
1388                               int <parameter>cursorOptions</parameter>)
1389 </synopsis>
1390  </refsynopsisdiv>
1391
1392  <refsect1>
1393   <title>Description</title>
1394
1395   <para>
1396    <function>SPI_prepare_params</function> creates and returns a prepared
1397    statement for the specified command, but doesn't execute the command.
1398    This function is equivalent to <function>SPI_prepare_cursor</function>,
1399    with the addition that the caller can specify parser hook functions
1400    to control the parsing of external parameter references.
1401   </para>
1402
1403   <para>
1404    This function is now deprecated in favor
1405    of <function>SPI_prepare_extended</function>.
1406   </para>
1407  </refsect1>
1408
1409  <refsect1>
1410   <title>Arguments</title>
1411
1412   <variablelist>
1413    <varlistentry>
1414     <term><literal>const char * <parameter>command</parameter></literal></term>
1415     <listitem>
1416      <para>
1417       command string
1418      </para>
1419     </listitem>
1420    </varlistentry>
1421
1422    <varlistentry>
1423     <term><literal>ParserSetupHook <parameter>parserSetup</parameter></literal></term>
1424     <listitem>
1425      <para>
1426       Parser hook setup function
1427      </para>
1428     </listitem>
1429    </varlistentry>
1430
1431    <varlistentry>
1432     <term><literal>void * <parameter>parserSetupArg</parameter></literal></term>
1433     <listitem>
1434      <para>
1435       pass-through argument for <parameter>parserSetup</parameter>
1436      </para>
1437     </listitem>
1438    </varlistentry>
1439
1440    <varlistentry>
1441     <term><literal>int <parameter>cursorOptions</parameter></literal></term>
1442     <listitem>
1443      <para>
1444       integer bit mask of cursor options; zero produces default behavior
1445      </para>
1446     </listitem>
1447    </varlistentry>
1448   </variablelist>
1449  </refsect1>
1450
1451  <refsect1>
1452   <title>Return Value</title>
1453
1454   <para>
1455    <function>SPI_prepare_params</function> has the same return conventions as
1456    <function>SPI_prepare</function>.
1457   </para>
1458  </refsect1>
1459 </refentry>
1460
1461 <!-- *********************************************** -->
1462
1463 <refentry id="spi-spi-getargcount">
1464  <indexterm><primary>SPI_getargcount</primary></indexterm>
1465
1466  <refmeta>
1467   <refentrytitle>SPI_getargcount</refentrytitle>
1468   <manvolnum>3</manvolnum>
1469  </refmeta>
1470
1471  <refnamediv>
1472   <refname>SPI_getargcount</refname>
1473   <refpurpose>return the number of arguments needed by a statement
1474   prepared by <function>SPI_prepare</function></refpurpose>
1475  </refnamediv>
1476
1477  <refsynopsisdiv>
1478 <synopsis>
1479 int SPI_getargcount(SPIPlanPtr <parameter>plan</parameter>)
1480 </synopsis>
1481  </refsynopsisdiv>
1482
1483  <refsect1>
1484   <title>Description</title>
1485
1486   <para>
1487    <function>SPI_getargcount</function> returns the number of arguments needed
1488    to execute a statement prepared by <function>SPI_prepare</function>.
1489   </para>
1490  </refsect1>
1491
1492  <refsect1>
1493   <title>Arguments</title>
1494
1495   <variablelist>
1496    <varlistentry>
1497     <term><literal>SPIPlanPtr <parameter>plan</parameter></literal></term>
1498     <listitem>
1499      <para>
1500       prepared statement (returned by <function>SPI_prepare</function>)
1501      </para>
1502     </listitem>
1503    </varlistentry>
1504   </variablelist>
1505  </refsect1>
1506
1507  <refsect1>
1508   <title>Return Value</title>
1509   <para>
1510     The count of expected arguments for the <parameter>plan</parameter>.
1511     If the <parameter>plan</parameter> is <symbol>NULL</symbol> or invalid,
1512     <varname>SPI_result</varname> is set to <symbol>SPI_ERROR_ARGUMENT</symbol>
1513     and -1 is returned.
1514   </para>
1515  </refsect1>
1516 </refentry>
1517
1518 <!-- *********************************************** -->
1519
1520 <refentry id="spi-spi-getargtypeid">
1521  <indexterm><primary>SPI_getargtypeid</primary></indexterm>
1522
1523  <refmeta>
1524   <refentrytitle>SPI_getargtypeid</refentrytitle>
1525   <manvolnum>3</manvolnum>
1526  </refmeta>
1527
1528  <refnamediv>
1529   <refname>SPI_getargtypeid</refname>
1530   <refpurpose>return the data type OID for an argument of
1531   a statement prepared by <function>SPI_prepare</function></refpurpose>
1532  </refnamediv>
1533
1534  <refsynopsisdiv>
1535 <synopsis>
1536 Oid SPI_getargtypeid(SPIPlanPtr <parameter>plan</parameter>, int <parameter>argIndex</parameter>)
1537 </synopsis>
1538  </refsynopsisdiv>
1539
1540  <refsect1>
1541   <title>Description</title>
1542
1543   <para>
1544    <function>SPI_getargtypeid</function> returns the OID representing the type
1545    for the <parameter>argIndex</parameter>'th argument of a statement prepared by
1546    <function>SPI_prepare</function>. First argument is at index zero.
1547   </para>
1548  </refsect1>
1549
1550  <refsect1>
1551   <title>Arguments</title>
1552
1553   <variablelist>
1554    <varlistentry>
1555     <term><literal>SPIPlanPtr <parameter>plan</parameter></literal></term>
1556     <listitem>
1557      <para>
1558       prepared statement (returned by <function>SPI_prepare</function>)
1559      </para>
1560     </listitem>
1561    </varlistentry>
1562
1563    <varlistentry>
1564     <term><literal>int <parameter>argIndex</parameter></literal></term>
1565     <listitem>
1566      <para>
1567       zero based index of the argument
1568      </para>
1569     </listitem>
1570    </varlistentry>
1571   </variablelist>
1572  </refsect1>
1573
1574  <refsect1>
1575   <title>Return Value</title>
1576   <para>
1577     The type OID of the argument at the given index.
1578     If the <parameter>plan</parameter> is <symbol>NULL</symbol> or invalid,
1579     or <parameter>argIndex</parameter> is less than 0 or
1580     not less than the number of arguments declared for the
1581     <parameter>plan</parameter>,
1582     <varname>SPI_result</varname> is set to <symbol>SPI_ERROR_ARGUMENT</symbol>
1583     and <symbol>InvalidOid</symbol> is returned.
1584   </para>
1585  </refsect1>
1586 </refentry>
1587
1588 <!-- *********************************************** -->
1589
1590 <refentry id="spi-spi-is-cursor-plan">
1591  <indexterm><primary>SPI_is_cursor_plan</primary></indexterm>
1592
1593  <refmeta>
1594   <refentrytitle>SPI_is_cursor_plan</refentrytitle>
1595   <manvolnum>3</manvolnum>
1596  </refmeta>
1597
1598  <refnamediv>
1599   <refname>SPI_is_cursor_plan</refname>
1600   <refpurpose>return <symbol>true</symbol> if a statement
1601   prepared by <function>SPI_prepare</function> can be used with
1602   <function>SPI_cursor_open</function></refpurpose>
1603  </refnamediv>
1604
1605  <refsynopsisdiv>
1606 <synopsis>
1607 bool SPI_is_cursor_plan(SPIPlanPtr <parameter>plan</parameter>)
1608 </synopsis>
1609  </refsynopsisdiv>
1610
1611  <refsect1>
1612   <title>Description</title>
1613
1614   <para>
1615    <function>SPI_is_cursor_plan</function> returns <symbol>true</symbol>
1616    if a statement prepared by <function>SPI_prepare</function> can be passed
1617    as an argument to <function>SPI_cursor_open</function>, or
1618    <symbol>false</symbol> if that is not the case. The criteria are that the
1619    <parameter>plan</parameter> represents one single command and that this
1620    command returns tuples to the caller; for example, <command>SELECT</command>
1621    is allowed unless it contains an <literal>INTO</literal> clause, and
1622    <command>UPDATE</command> is allowed only if it contains a <literal>RETURNING</literal>
1623    clause.
1624   </para>
1625  </refsect1>
1626
1627  <refsect1>
1628   <title>Arguments</title>
1629
1630   <variablelist>
1631    <varlistentry>
1632     <term><literal>SPIPlanPtr <parameter>plan</parameter></literal></term>
1633     <listitem>
1634      <para>
1635       prepared statement (returned by <function>SPI_prepare</function>)
1636      </para>
1637     </listitem>
1638    </varlistentry>
1639   </variablelist>
1640  </refsect1>
1641
1642  <refsect1>
1643   <title>Return Value</title>
1644   <para>
1645     <symbol>true</symbol> or <symbol>false</symbol> to indicate if the
1646     <parameter>plan</parameter> can produce a cursor or not, with
1647     <varname>SPI_result</varname> set to zero.
1648     If it is not possible to determine the answer (for example,
1649     if the <parameter>plan</parameter> is <symbol>NULL</symbol> or invalid,
1650     or if called when not connected to SPI), then
1651     <varname>SPI_result</varname> is set to a suitable error code
1652     and <symbol>false</symbol> is returned.
1653   </para>
1654  </refsect1>
1655 </refentry>
1656
1657 <!-- *********************************************** -->
1658
1659 <refentry id="spi-spi-execute-plan">
1660  <indexterm><primary>SPI_execute_plan</primary></indexterm>
1661
1662  <refmeta>
1663   <refentrytitle>SPI_execute_plan</refentrytitle>
1664   <manvolnum>3</manvolnum>
1665  </refmeta>
1666
1667  <refnamediv>
1668   <refname>SPI_execute_plan</refname>
1669   <refpurpose>execute a statement prepared by <function>SPI_prepare</function></refpurpose>
1670  </refnamediv>
1671
1672  <refsynopsisdiv>
1673 <synopsis>
1674 int SPI_execute_plan(SPIPlanPtr <parameter>plan</parameter>, Datum * <parameter>values</parameter>, const char * <parameter>nulls</parameter>,
1675                      bool <parameter>read_only</parameter>, long <parameter>count</parameter>)
1676 </synopsis>
1677  </refsynopsisdiv>
1678
1679  <refsect1>
1680   <title>Description</title>
1681
1682   <para>
1683    <function>SPI_execute_plan</function> executes a statement prepared by
1684    <function>SPI_prepare</function> or one of its siblings.
1685    <parameter>read_only</parameter> and
1686    <parameter>count</parameter> have the same interpretation as in
1687    <function>SPI_execute</function>.
1688   </para>
1689  </refsect1>
1690
1691  <refsect1>
1692   <title>Arguments</title>
1693
1694   <variablelist>
1695    <varlistentry>
1696     <term><literal>SPIPlanPtr <parameter>plan</parameter></literal></term>
1697     <listitem>
1698      <para>
1699       prepared statement (returned by <function>SPI_prepare</function>)
1700      </para>
1701     </listitem>
1702    </varlistentry>
1703
1704    <varlistentry>
1705     <term><literal>Datum * <parameter>values</parameter></literal></term>
1706     <listitem>
1707      <para>
1708       An array of actual parameter values.  Must have same length as the
1709       statement's number of arguments.
1710      </para>
1711     </listitem>
1712    </varlistentry>
1713
1714    <varlistentry>
1715     <term><literal>const char * <parameter>nulls</parameter></literal></term>
1716     <listitem>
1717      <para>
1718       An array describing which parameters are null.  Must have same length as
1719       the statement's number of arguments.
1720      </para>
1721
1722      <para>
1723       If <parameter>nulls</parameter> is <symbol>NULL</symbol> then
1724       <function>SPI_execute_plan</function> assumes that no parameters
1725       are null.  Otherwise, each entry of the <parameter>nulls</parameter>
1726       array should be <literal>'&nbsp;'</literal> if the corresponding parameter
1727       value is non-null, or <literal>'n'</literal> if the corresponding parameter
1728       value is null.  (In the latter case, the actual value in the
1729       corresponding <parameter>values</parameter> entry doesn't matter.)  Note
1730       that <parameter>nulls</parameter> is not a text string, just an array:
1731       it does not need a <literal>'\0'</literal> terminator.
1732      </para>
1733     </listitem>
1734    </varlistentry>
1735
1736    <varlistentry>
1737     <term><literal>bool <parameter>read_only</parameter></literal></term>
1738     <listitem>
1739      <para><literal>true</literal> for read-only execution</para>
1740     </listitem>
1741    </varlistentry>
1742
1743    <varlistentry>
1744     <term><literal>long <parameter>count</parameter></literal></term>
1745     <listitem>
1746      <para>
1747       maximum number of rows to return,
1748       or <literal>0</literal> for no limit
1749      </para>
1750     </listitem>
1751    </varlistentry>
1752   </variablelist>
1753  </refsect1>
1754
1755  <refsect1>
1756   <title>Return Value</title>
1757
1758   <para>
1759    The return value is the same as for <function>SPI_execute</function>,
1760    with the following additional possible error (negative) results:
1761
1762    <variablelist>
1763     <varlistentry>
1764      <term><symbol>SPI_ERROR_ARGUMENT</symbol></term>
1765      <listitem>
1766       <para>
1767        if <parameter>plan</parameter> is <symbol>NULL</symbol> or invalid,
1768        or <parameter>count</parameter> is less than 0
1769       </para>
1770      </listitem>
1771     </varlistentry>
1772
1773     <varlistentry>
1774      <term><symbol>SPI_ERROR_PARAM</symbol></term>
1775      <listitem>
1776       <para>
1777        if <parameter>values</parameter> is <symbol>NULL</symbol> and
1778        <parameter>plan</parameter> was prepared with some parameters
1779       </para>
1780      </listitem>
1781     </varlistentry>
1782    </variablelist>
1783   </para>
1784
1785   <para>
1786    <varname>SPI_processed</varname> and
1787    <varname>SPI_tuptable</varname> are set as in
1788    <function>SPI_execute</function> if successful.
1789   </para>
1790  </refsect1>
1791 </refentry>
1792
1793 <!-- *********************************************** -->
1794
1795 <refentry id="spi-spi-execute-plan-extended">
1796  <indexterm><primary>SPI_execute_plan_extended</primary></indexterm>
1797
1798  <refmeta>
1799   <refentrytitle>SPI_execute_plan_extended</refentrytitle>
1800   <manvolnum>3</manvolnum>
1801  </refmeta>
1802
1803  <refnamediv>
1804   <refname>SPI_execute_plan_extended</refname>
1805   <refpurpose>execute a statement prepared by <function>SPI_prepare</function></refpurpose>
1806  </refnamediv>
1807
1808  <refsynopsisdiv>
1809 <synopsis>
1810 int SPI_execute_plan_extended(SPIPlanPtr <parameter>plan</parameter>,
1811                               const SPIExecuteOptions * <parameter>options</parameter>)
1812 </synopsis>
1813  </refsynopsisdiv>
1814
1815  <refsect1>
1816   <title>Description</title>
1817
1818   <para>
1819    <function>SPI_execute_plan_extended</function> executes a statement
1820    prepared by <function>SPI_prepare</function> or one of its siblings.
1821    This function is equivalent to <function>SPI_execute_plan</function>,
1822    except that information about the parameter values to be passed to the
1823    query is presented differently, and additional execution-controlling
1824    options can be passed.
1825   </para>
1826
1827   <para>
1828    Query parameter values are represented by
1829    a <literal>ParamListInfo</literal> struct, which is convenient for passing
1830    down values that are already available in that format.  Dynamic parameter
1831    sets can also be used, via hook functions specified
1832    in <literal>ParamListInfo</literal>.
1833   </para>
1834
1835   <para>
1836    Also, instead of always accumulating the result tuples into a
1837    <varname>SPI_tuptable</varname> structure, tuples can be passed to a
1838    caller-supplied <literal>DestReceiver</literal> object as they are
1839    generated by the executor.  This is particularly helpful for queries
1840    that might generate many tuples, since the data can be processed
1841    on-the-fly instead of being accumulated in memory.
1842   </para>
1843  </refsect1>
1844
1845  <refsect1>
1846   <title>Arguments</title>
1847
1848   <variablelist>
1849    <varlistentry>
1850     <term><literal>SPIPlanPtr <parameter>plan</parameter></literal></term>
1851     <listitem>
1852      <para>
1853       prepared statement (returned by <function>SPI_prepare</function>)
1854      </para>
1855     </listitem>
1856    </varlistentry>
1857
1858    <varlistentry>
1859     <term><literal>const SPIExecuteOptions * <parameter>options</parameter></literal></term>
1860     <listitem>
1861      <para>
1862       struct containing optional arguments
1863      </para>
1864     </listitem>
1865    </varlistentry>
1866   </variablelist>
1867
1868   <para>
1869    Callers should always zero out the entire <parameter>options</parameter>
1870    struct, then fill whichever fields they want to set.  This ensures forward
1871    compatibility of code, since any fields that are added to the struct in
1872    future will be defined to behave backwards-compatibly if they are zero.
1873    The currently available <parameter>options</parameter> fields are:
1874   </para>
1875
1876   <variablelist>
1877    <varlistentry>
1878     <term><literal>ParamListInfo <parameter>params</parameter></literal></term>
1879     <listitem>
1880      <para>
1881       data structure containing query parameter types and values; NULL if none
1882      </para>
1883     </listitem>
1884    </varlistentry>
1885
1886    <varlistentry>
1887     <term><literal>bool <parameter>read_only</parameter></literal></term>
1888     <listitem>
1889      <para><literal>true</literal> for read-only execution</para>
1890     </listitem>
1891    </varlistentry>
1892
1893    <varlistentry>
1894     <term><literal>bool <parameter>allow_nonatomic</parameter></literal></term>
1895     <listitem>
1896      <para>
1897       <literal>true</literal> allows non-atomic execution of CALL and DO
1898       statements (but this field is ignored unless
1899       the <symbol>SPI_OPT_NONATOMIC</symbol> flag was passed
1900       to <function>SPI_connect_ext</function>)
1901      </para>
1902     </listitem>
1903    </varlistentry>
1904
1905    <varlistentry>
1906     <term><literal>bool <parameter>must_return_tuples</parameter></literal></term>
1907     <listitem>
1908      <para>
1909       if <literal>true</literal>, raise error if the query is not of a kind
1910       that returns tuples (this does not forbid the case where it happens to
1911       return zero tuples)
1912      </para>
1913     </listitem>
1914    </varlistentry>
1915
1916    <varlistentry>
1917     <term><literal>uint64 <parameter>tcount</parameter></literal></term>
1918     <listitem>
1919      <para>
1920       maximum number of rows to return,
1921       or <literal>0</literal> for no limit
1922      </para>
1923     </listitem>
1924    </varlistentry>
1925
1926    <varlistentry>
1927     <term><literal>DestReceiver * <parameter>dest</parameter></literal></term>
1928     <listitem>
1929      <para>
1930       <literal>DestReceiver</literal> object that will receive any tuples
1931       emitted by the query; if NULL, result tuples are accumulated into
1932       a <varname>SPI_tuptable</varname> structure, as
1933       in <function>SPI_execute_plan</function>
1934      </para>
1935     </listitem>
1936    </varlistentry>
1937
1938    <varlistentry>
1939     <term><literal>ResourceOwner <parameter>owner</parameter></literal></term>
1940     <listitem>
1941      <para>
1942       The resource owner that will hold a reference count on the plan while
1943       it is executed.  If NULL, CurrentResourceOwner is used.  Ignored for
1944       non-saved plans, as SPI does not acquire reference counts on those.
1945      </para>
1946     </listitem>
1947    </varlistentry>
1948   </variablelist>
1949  </refsect1>
1950
1951  <refsect1>
1952   <title>Return Value</title>
1953
1954   <para>
1955    The return value is the same as for <function>SPI_execute_plan</function>.
1956   </para>
1957
1958   <para>
1959    When <parameter>options-&gt;dest</parameter> is NULL,
1960    <varname>SPI_processed</varname> and
1961    <varname>SPI_tuptable</varname> are set as in
1962    <function>SPI_execute_plan</function>.
1963    When <parameter>options-&gt;dest</parameter> is not NULL,
1964    <varname>SPI_processed</varname> is set to zero and
1965    <varname>SPI_tuptable</varname> is set to NULL.  If a tuple count
1966    is required, the caller's <literal>DestReceiver</literal> object must
1967    calculate it.
1968   </para>
1969  </refsect1>
1970 </refentry>
1971
1972 <!-- *********************************************** -->
1973
1974 <refentry id="spi-spi-execute-plan-with-paramlist">
1975  <indexterm><primary>SPI_execute_plan_with_paramlist</primary></indexterm>
1976
1977  <refmeta>
1978   <refentrytitle>SPI_execute_plan_with_paramlist</refentrytitle>
1979   <manvolnum>3</manvolnum>
1980  </refmeta>
1981
1982  <refnamediv>
1983   <refname>SPI_execute_plan_with_paramlist</refname>
1984   <refpurpose>execute a statement prepared by <function>SPI_prepare</function></refpurpose>
1985  </refnamediv>
1986
1987  <refsynopsisdiv>
1988 <synopsis>
1989 int SPI_execute_plan_with_paramlist(SPIPlanPtr <parameter>plan</parameter>,
1990                                     ParamListInfo <parameter>params</parameter>,
1991                                     bool <parameter>read_only</parameter>,
1992                                     long <parameter>count</parameter>)
1993 </synopsis>
1994  </refsynopsisdiv>
1995
1996  <refsect1>
1997   <title>Description</title>
1998
1999   <para>
2000    <function>SPI_execute_plan_with_paramlist</function> executes a statement
2001    prepared by <function>SPI_prepare</function>.
2002    This function is equivalent to <function>SPI_execute_plan</function>
2003    except that information about the parameter values to be passed to the
2004    query is presented differently.  The <literal>ParamListInfo</literal>
2005    representation can be convenient for passing down values that are
2006    already available in that format.  It also supports use of dynamic
2007    parameter sets via hook functions specified in <literal>ParamListInfo</literal>.
2008   </para>
2009
2010   <para>
2011    This function is now deprecated in favor
2012    of <function>SPI_execute_plan_extended</function>.
2013   </para>
2014  </refsect1>
2015
2016  <refsect1>
2017   <title>Arguments</title>
2018
2019   <variablelist>
2020    <varlistentry>
2021     <term><literal>SPIPlanPtr <parameter>plan</parameter></literal></term>
2022     <listitem>
2023      <para>
2024       prepared statement (returned by <function>SPI_prepare</function>)
2025      </para>
2026     </listitem>
2027    </varlistentry>
2028
2029    <varlistentry>
2030     <term><literal>ParamListInfo <parameter>params</parameter></literal></term>
2031     <listitem>
2032      <para>
2033       data structure containing parameter types and values; NULL if none
2034      </para>
2035     </listitem>
2036    </varlistentry>
2037
2038    <varlistentry>
2039     <term><literal>bool <parameter>read_only</parameter></literal></term>
2040     <listitem>
2041      <para><literal>true</literal> for read-only execution</para>
2042     </listitem>
2043    </varlistentry>
2044
2045    <varlistentry>
2046     <term><literal>long <parameter>count</parameter></literal></term>
2047     <listitem>
2048      <para>
2049       maximum number of rows to return,
2050       or <literal>0</literal> for no limit
2051      </para>
2052     </listitem>
2053    </varlistentry>
2054   </variablelist>
2055  </refsect1>
2056
2057  <refsect1>
2058   <title>Return Value</title>
2059
2060   <para>
2061    The return value is the same as for <function>SPI_execute_plan</function>.
2062   </para>
2063
2064   <para>
2065    <varname>SPI_processed</varname> and
2066    <varname>SPI_tuptable</varname> are set as in
2067    <function>SPI_execute_plan</function> if successful.
2068   </para>
2069  </refsect1>
2070 </refentry>
2071
2072 <!-- *********************************************** -->
2073
2074 <refentry id="spi-spi-execp">
2075  <indexterm><primary>SPI_execp</primary></indexterm>
2076
2077  <refmeta>
2078   <refentrytitle>SPI_execp</refentrytitle>
2079   <manvolnum>3</manvolnum>
2080  </refmeta>
2081
2082  <refnamediv>
2083   <refname>SPI_execp</refname>
2084   <refpurpose>execute a statement in read/write mode</refpurpose>
2085  </refnamediv>
2086
2087  <refsynopsisdiv>
2088 <synopsis>
2089 int SPI_execp(SPIPlanPtr <parameter>plan</parameter>, Datum * <parameter>values</parameter>, const char * <parameter>nulls</parameter>, long <parameter>count</parameter>)
2090 </synopsis>
2091  </refsynopsisdiv>
2092
2093  <refsect1>
2094   <title>Description</title>
2095
2096   <para>
2097    <function>SPI_execp</function> is the same as
2098    <function>SPI_execute_plan</function>, with the latter's
2099    <parameter>read_only</parameter> parameter always taken as
2100    <literal>false</literal>.
2101   </para>
2102  </refsect1>
2103
2104  <refsect1>
2105   <title>Arguments</title>
2106
2107   <variablelist>
2108    <varlistentry>
2109     <term><literal>SPIPlanPtr <parameter>plan</parameter></literal></term>
2110     <listitem>
2111      <para>
2112       prepared statement (returned by <function>SPI_prepare</function>)
2113      </para>
2114     </listitem>
2115    </varlistentry>
2116
2117    <varlistentry>
2118     <term><literal>Datum * <parameter>values</parameter></literal></term>
2119     <listitem>
2120      <para>
2121       An array of actual parameter values.  Must have same length as the
2122       statement's number of arguments.
2123      </para>
2124     </listitem>
2125    </varlistentry>
2126
2127    <varlistentry>
2128     <term><literal>const char * <parameter>nulls</parameter></literal></term>
2129     <listitem>
2130      <para>
2131       An array describing which parameters are null.  Must have same length as
2132       the statement's number of arguments.
2133      </para>
2134
2135      <para>
2136       If <parameter>nulls</parameter> is <symbol>NULL</symbol> then
2137       <function>SPI_execp</function> assumes that no parameters
2138       are null.  Otherwise, each entry of the <parameter>nulls</parameter>
2139       array should be <literal>'&nbsp;'</literal> if the corresponding parameter
2140       value is non-null, or <literal>'n'</literal> if the corresponding parameter
2141       value is null.  (In the latter case, the actual value in the
2142       corresponding <parameter>values</parameter> entry doesn't matter.)  Note
2143       that <parameter>nulls</parameter> is not a text string, just an array:
2144       it does not need a <literal>'\0'</literal> terminator.
2145      </para>
2146     </listitem>
2147    </varlistentry>
2148
2149    <varlistentry>
2150     <term><literal>long <parameter>count</parameter></literal></term>
2151     <listitem>
2152      <para>
2153       maximum number of rows to return,
2154       or <literal>0</literal> for no limit
2155      </para>
2156     </listitem>
2157    </varlistentry>
2158   </variablelist>
2159  </refsect1>
2160
2161  <refsect1>
2162   <title>Return Value</title>
2163
2164   <para>
2165    See <function>SPI_execute_plan</function>.
2166   </para>
2167
2168   <para>
2169    <varname>SPI_processed</varname> and
2170    <varname>SPI_tuptable</varname> are set as in
2171    <function>SPI_execute</function> if successful.
2172   </para>
2173  </refsect1>
2174 </refentry>
2175
2176 <!-- *********************************************** -->
2177
2178 <refentry id="spi-spi-cursor-open">
2179  <indexterm><primary>SPI_cursor_open</primary></indexterm>
2180
2181  <refmeta>
2182   <refentrytitle>SPI_cursor_open</refentrytitle>
2183   <manvolnum>3</manvolnum>
2184  </refmeta>
2185
2186  <refnamediv>
2187   <refname>SPI_cursor_open</refname>
2188   <refpurpose>set up a cursor using a statement created with <function>SPI_prepare</function></refpurpose>
2189  </refnamediv>
2190
2191  <refsynopsisdiv>
2192 <synopsis>
2193 Portal SPI_cursor_open(const char * <parameter>name</parameter>, SPIPlanPtr <parameter>plan</parameter>,
2194                        Datum * <parameter>values</parameter>, const char * <parameter>nulls</parameter>,
2195                        bool <parameter>read_only</parameter>)
2196 </synopsis>
2197  </refsynopsisdiv>
2198
2199  <refsect1>
2200   <title>Description</title>
2201
2202   <para>
2203    <function>SPI_cursor_open</function> sets up a cursor (internally,
2204    a portal) that will execute a statement prepared by
2205    <function>SPI_prepare</function>.  The parameters have the same
2206    meanings as the corresponding parameters to
2207    <function>SPI_execute_plan</function>.
2208   </para>
2209
2210   <para>
2211    Using a cursor instead of executing the statement directly has two
2212    benefits.  First, the result rows can be retrieved a few at a time,
2213    avoiding memory overrun for queries that return many rows.  Second,
2214    a portal can outlive the current C function (it can, in fact, live
2215    to the end of the current transaction).  Returning the portal name
2216    to the C function's caller provides a way of returning a row set as
2217    result.
2218   </para>
2219
2220   <para>
2221    The passed-in parameter data will be copied into the cursor's portal, so it
2222    can be freed while the cursor still exists.
2223   </para>
2224  </refsect1>
2225
2226  <refsect1>
2227   <title>Arguments</title>
2228
2229   <variablelist>
2230    <varlistentry>
2231     <term><literal>const char * <parameter>name</parameter></literal></term>
2232     <listitem>
2233      <para>
2234       name for portal, or <symbol>NULL</symbol> to let the system
2235       select a name
2236      </para>
2237     </listitem>
2238    </varlistentry>
2239
2240    <varlistentry>
2241     <term><literal>SPIPlanPtr <parameter>plan</parameter></literal></term>
2242     <listitem>
2243      <para>
2244       prepared statement (returned by <function>SPI_prepare</function>)
2245      </para>
2246     </listitem>
2247    </varlistentry>
2248
2249    <varlistentry>
2250     <term><literal>Datum * <parameter>values</parameter></literal></term>
2251     <listitem>
2252      <para>
2253       An array of actual parameter values.  Must have same length as the
2254       statement's number of arguments.
2255      </para>
2256     </listitem>
2257    </varlistentry>
2258
2259    <varlistentry>
2260     <term><literal>const char * <parameter>nulls</parameter></literal></term>
2261     <listitem>
2262      <para>
2263       An array describing which parameters are null.  Must have same length as
2264       the statement's number of arguments.
2265      </para>
2266
2267      <para>
2268       If <parameter>nulls</parameter> is <symbol>NULL</symbol> then
2269       <function>SPI_cursor_open</function> assumes that no parameters
2270       are null.  Otherwise, each entry of the <parameter>nulls</parameter>
2271       array should be <literal>'&nbsp;'</literal> if the corresponding parameter
2272       value is non-null, or <literal>'n'</literal> if the corresponding parameter
2273       value is null.  (In the latter case, the actual value in the
2274       corresponding <parameter>values</parameter> entry doesn't matter.)  Note
2275       that <parameter>nulls</parameter> is not a text string, just an array:
2276       it does not need a <literal>'\0'</literal> terminator.
2277      </para>
2278     </listitem>
2279    </varlistentry>
2280
2281    <varlistentry>
2282     <term><literal>bool <parameter>read_only</parameter></literal></term>
2283     <listitem>
2284      <para><literal>true</literal> for read-only execution</para>
2285     </listitem>
2286    </varlistentry>
2287   </variablelist>
2288  </refsect1>
2289
2290  <refsect1>
2291   <title>Return Value</title>
2292
2293   <para>
2294    Pointer to portal containing the cursor.  Note there is no error
2295    return convention; any error will be reported via <function>elog</function>.
2296   </para>
2297  </refsect1>
2298 </refentry>
2299
2300 <!-- *********************************************** -->
2301
2302 <refentry id="spi-spi-cursor-open-with-args">
2303  <indexterm><primary>SPI_cursor_open_with_args</primary></indexterm>
2304
2305  <refmeta>
2306   <refentrytitle>SPI_cursor_open_with_args</refentrytitle>
2307   <manvolnum>3</manvolnum>
2308  </refmeta>
2309
2310  <refnamediv>
2311   <refname>SPI_cursor_open_with_args</refname>
2312   <refpurpose>set up a cursor using a query and parameters</refpurpose>
2313  </refnamediv>
2314
2315  <refsynopsisdiv>
2316 <synopsis>
2317 Portal SPI_cursor_open_with_args(const char *<parameter>name</parameter>,
2318                                  const char *<parameter>command</parameter>,
2319                                  int <parameter>nargs</parameter>, Oid *<parameter>argtypes</parameter>,
2320                                  Datum *<parameter>values</parameter>, const char *<parameter>nulls</parameter>,
2321                                  bool <parameter>read_only</parameter>, int <parameter>cursorOptions</parameter>)
2322 </synopsis>
2323  </refsynopsisdiv>
2324
2325  <refsect1>
2326   <title>Description</title>
2327
2328   <para>
2329    <function>SPI_cursor_open_with_args</function> sets up a cursor
2330    (internally, a portal) that will execute the specified query.
2331    Most of the parameters have the same meanings as the corresponding
2332    parameters to <function>SPI_prepare_cursor</function>
2333    and <function>SPI_cursor_open</function>.
2334   </para>
2335
2336   <para>
2337    For one-time query execution, this function should be preferred
2338    over <function>SPI_prepare_cursor</function> followed by
2339    <function>SPI_cursor_open</function>.
2340    If the same command is to be executed with many different parameters,
2341    either method might be faster, depending on the cost of re-planning
2342    versus the benefit of custom plans.
2343   </para>
2344
2345   <para>
2346    The passed-in parameter data will be copied into the cursor's portal, so it
2347    can be freed while the cursor still exists.
2348   </para>
2349
2350   <para>
2351    This function is now deprecated in favor
2352    of <function>SPI_cursor_parse_open</function>, which provides equivalent
2353    functionality using a more modern API for handling query parameters.
2354   </para>
2355  </refsect1>
2356
2357  <refsect1>
2358   <title>Arguments</title>
2359
2360   <variablelist>
2361    <varlistentry>
2362     <term><literal>const char * <parameter>name</parameter></literal></term>
2363     <listitem>
2364      <para>
2365       name for portal, or <symbol>NULL</symbol> to let the system
2366       select a name
2367      </para>
2368     </listitem>
2369    </varlistentry>
2370
2371    <varlistentry>
2372     <term><literal>const char * <parameter>command</parameter></literal></term>
2373     <listitem>
2374      <para>
2375       command string
2376      </para>
2377     </listitem>
2378    </varlistentry>
2379
2380    <varlistentry>
2381     <term><literal>int <parameter>nargs</parameter></literal></term>
2382     <listitem>
2383      <para>
2384       number of input parameters (<literal>$1</literal>, <literal>$2</literal>, etc.)
2385      </para>
2386     </listitem>
2387    </varlistentry>
2388
2389    <varlistentry>
2390     <term><literal>Oid * <parameter>argtypes</parameter></literal></term>
2391     <listitem>
2392      <para>
2393       an array of length <parameter>nargs</parameter>, containing the
2394       <acronym>OID</acronym>s of the data types of the parameters
2395      </para>
2396     </listitem>
2397    </varlistentry>
2398
2399    <varlistentry>
2400     <term><literal>Datum * <parameter>values</parameter></literal></term>
2401     <listitem>
2402      <para>
2403       an array of length <parameter>nargs</parameter>, containing the actual
2404       parameter values
2405      </para>
2406     </listitem>
2407    </varlistentry>
2408
2409    <varlistentry>
2410     <term><literal>const char * <parameter>nulls</parameter></literal></term>
2411     <listitem>
2412      <para>
2413       an array of length <parameter>nargs</parameter>, describing which
2414       parameters are null
2415      </para>
2416
2417      <para>
2418       If <parameter>nulls</parameter> is <symbol>NULL</symbol> then
2419       <function>SPI_cursor_open_with_args</function> assumes that no parameters
2420       are null.  Otherwise, each entry of the <parameter>nulls</parameter>
2421       array should be <literal>'&nbsp;'</literal> if the corresponding parameter
2422       value is non-null, or <literal>'n'</literal> if the corresponding parameter
2423       value is null.  (In the latter case, the actual value in the
2424       corresponding <parameter>values</parameter> entry doesn't matter.)  Note
2425       that <parameter>nulls</parameter> is not a text string, just an array:
2426       it does not need a <literal>'\0'</literal> terminator.
2427      </para>
2428     </listitem>
2429    </varlistentry>
2430
2431    <varlistentry>
2432     <term><literal>bool <parameter>read_only</parameter></literal></term>
2433     <listitem>
2434      <para><literal>true</literal> for read-only execution</para>
2435     </listitem>
2436    </varlistentry>
2437
2438    <varlistentry>
2439     <term><literal>int <parameter>cursorOptions</parameter></literal></term>
2440     <listitem>
2441      <para>
2442       integer bit mask of cursor options; zero produces default behavior
2443      </para>
2444     </listitem>
2445    </varlistentry>
2446   </variablelist>
2447  </refsect1>
2448
2449  <refsect1>
2450   <title>Return Value</title>
2451
2452   <para>
2453    Pointer to portal containing the cursor.  Note there is no error
2454    return convention; any error will be reported via <function>elog</function>.
2455   </para>
2456  </refsect1>
2457 </refentry>
2458
2459 <!-- *********************************************** -->
2460
2461 <refentry id="spi-spi-cursor-open-with-paramlist">
2462  <indexterm><primary>SPI_cursor_open_with_paramlist</primary></indexterm>
2463
2464  <refmeta>
2465   <refentrytitle>SPI_cursor_open_with_paramlist</refentrytitle>
2466   <manvolnum>3</manvolnum>
2467  </refmeta>
2468
2469  <refnamediv>
2470   <refname>SPI_cursor_open_with_paramlist</refname>
2471   <refpurpose>set up a cursor using parameters</refpurpose>
2472  </refnamediv>
2473
2474  <refsynopsisdiv>
2475 <synopsis>
2476 Portal SPI_cursor_open_with_paramlist(const char *<parameter>name</parameter>,
2477                                       SPIPlanPtr <parameter>plan</parameter>,
2478                                       ParamListInfo <parameter>params</parameter>,
2479                                       bool <parameter>read_only</parameter>)
2480 </synopsis>
2481  </refsynopsisdiv>
2482
2483  <refsect1>
2484   <title>Description</title>
2485
2486   <para>
2487    <function>SPI_cursor_open_with_paramlist</function> sets up a cursor
2488    (internally, a portal) that will execute a statement prepared by
2489    <function>SPI_prepare</function>.
2490    This function is equivalent to <function>SPI_cursor_open</function>
2491    except that information about the parameter values to be passed to the
2492    query is presented differently.  The <literal>ParamListInfo</literal>
2493    representation can be convenient for passing down values that are
2494    already available in that format.  It also supports use of dynamic
2495    parameter sets via hook functions specified in <literal>ParamListInfo</literal>.
2496   </para>
2497
2498   <para>
2499    The passed-in parameter data will be copied into the cursor's portal, so it
2500    can be freed while the cursor still exists.
2501   </para>
2502  </refsect1>
2503
2504  <refsect1>
2505   <title>Arguments</title>
2506
2507   <variablelist>
2508    <varlistentry>
2509     <term><literal>const char * <parameter>name</parameter></literal></term>
2510     <listitem>
2511      <para>
2512       name for portal, or <symbol>NULL</symbol> to let the system
2513       select a name
2514      </para>
2515     </listitem>
2516    </varlistentry>
2517
2518    <varlistentry>
2519     <term><literal>SPIPlanPtr <parameter>plan</parameter></literal></term>
2520     <listitem>
2521      <para>
2522       prepared statement (returned by <function>SPI_prepare</function>)
2523      </para>
2524     </listitem>
2525    </varlistentry>
2526
2527    <varlistentry>
2528     <term><literal>ParamListInfo <parameter>params</parameter></literal></term>
2529     <listitem>
2530      <para>
2531       data structure containing parameter types and values; NULL if none
2532      </para>
2533     </listitem>
2534    </varlistentry>
2535
2536    <varlistentry>
2537     <term><literal>bool <parameter>read_only</parameter></literal></term>
2538     <listitem>
2539      <para><literal>true</literal> for read-only execution</para>
2540     </listitem>
2541    </varlistentry>
2542   </variablelist>
2543  </refsect1>
2544
2545  <refsect1>
2546   <title>Return Value</title>
2547
2548   <para>
2549    Pointer to portal containing the cursor.  Note there is no error
2550    return convention; any error will be reported via <function>elog</function>.
2551   </para>
2552  </refsect1>
2553 </refentry>
2554
2555 <!-- *********************************************** -->
2556
2557 <refentry id="spi-spi-cursor-parse-open">
2558  <indexterm><primary>SPI_cursor_parse_open</primary></indexterm>
2559
2560  <refmeta>
2561   <refentrytitle>SPI_cursor_parse_open</refentrytitle>
2562   <manvolnum>3</manvolnum>
2563  </refmeta>
2564
2565  <refnamediv>
2566   <refname>SPI_cursor_parse_open</refname>
2567   <refpurpose>set up a cursor using a query string and parameters</refpurpose>
2568  </refnamediv>
2569
2570  <refsynopsisdiv>
2571 <synopsis>
2572 Portal SPI_cursor_parse_open(const char *<parameter>name</parameter>,
2573                              const char *<parameter>command</parameter>,
2574                              const SPIParseOpenOptions * <parameter>options</parameter>)
2575 </synopsis>
2576  </refsynopsisdiv>
2577
2578  <refsect1>
2579   <title>Description</title>
2580
2581   <para>
2582    <function>SPI_cursor_parse_open</function> sets up a cursor
2583    (internally, a portal) that will execute the specified query string.
2584    This is comparable to <function>SPI_prepare_cursor</function> followed
2585    by <function>SPI_cursor_open_with_paramlist</function>, except that
2586    parameter references within the query string are handled entirely by
2587    supplying a <literal>ParamListInfo</literal> object.
2588   </para>
2589
2590   <para>
2591    For one-time query execution, this function should be preferred
2592    over <function>SPI_prepare_cursor</function> followed by
2593    <function>SPI_cursor_open_with_paramlist</function>.
2594    If the same command is to be executed with many different parameters,
2595    either method might be faster, depending on the cost of re-planning
2596    versus the benefit of custom plans.
2597   </para>
2598
2599   <para>
2600    The <parameter>options-&gt;params</parameter> object should normally
2601    mark each parameter with the <literal>PARAM_FLAG_CONST</literal> flag,
2602    since a one-shot plan is always used for the query.
2603   </para>
2604
2605   <para>
2606    The passed-in parameter data will be copied into the cursor's portal, so it
2607    can be freed while the cursor still exists.
2608   </para>
2609  </refsect1>
2610
2611  <refsect1>
2612   <title>Arguments</title>
2613
2614   <variablelist>
2615    <varlistentry>
2616     <term><literal>const char * <parameter>name</parameter></literal></term>
2617     <listitem>
2618      <para>
2619       name for portal, or <symbol>NULL</symbol> to let the system
2620       select a name
2621      </para>
2622     </listitem>
2623    </varlistentry>
2624
2625    <varlistentry>
2626     <term><literal>const char * <parameter>command</parameter></literal></term>
2627     <listitem>
2628      <para>
2629       command string
2630      </para>
2631     </listitem>
2632    </varlistentry>
2633
2634    <varlistentry>
2635     <term><literal>const SPIParseOpenOptions * <parameter>options</parameter></literal></term>
2636     <listitem>
2637      <para>
2638       struct containing optional arguments
2639      </para>
2640     </listitem>
2641    </varlistentry>
2642   </variablelist>
2643
2644   <para>
2645    Callers should always zero out the entire <parameter>options</parameter>
2646    struct, then fill whichever fields they want to set.  This ensures forward
2647    compatibility of code, since any fields that are added to the struct in
2648    future will be defined to behave backwards-compatibly if they are zero.
2649    The currently available <parameter>options</parameter> fields are:
2650   </para>
2651
2652   <variablelist>
2653    <varlistentry>
2654     <term><literal>ParamListInfo <parameter>params</parameter></literal></term>
2655     <listitem>
2656      <para>
2657       data structure containing query parameter types and values; NULL if none
2658      </para>
2659     </listitem>
2660    </varlistentry>
2661
2662    <varlistentry>
2663     <term><literal>int <parameter>cursorOptions</parameter></literal></term>
2664     <listitem>
2665      <para>
2666       integer bit mask of cursor options; zero produces default behavior
2667      </para>
2668     </listitem>
2669    </varlistentry>
2670
2671    <varlistentry>
2672     <term><literal>bool <parameter>read_only</parameter></literal></term>
2673     <listitem>
2674      <para><literal>true</literal> for read-only execution</para>
2675     </listitem>
2676    </varlistentry>
2677   </variablelist>
2678  </refsect1>
2679
2680  <refsect1>
2681   <title>Return Value</title>
2682
2683   <para>
2684    Pointer to portal containing the cursor.  Note there is no error
2685    return convention; any error will be reported via <function>elog</function>.
2686   </para>
2687  </refsect1>
2688 </refentry>
2689
2690 <!-- *********************************************** -->
2691
2692 <refentry id="spi-spi-cursor-find">
2693  <indexterm><primary>SPI_cursor_find</primary></indexterm>
2694
2695  <refmeta>
2696   <refentrytitle>SPI_cursor_find</refentrytitle>
2697   <manvolnum>3</manvolnum>
2698  </refmeta>
2699
2700  <refnamediv>
2701   <refname>SPI_cursor_find</refname>
2702   <refpurpose>find an existing cursor by name</refpurpose>
2703  </refnamediv>
2704
2705  <refsynopsisdiv>
2706 <synopsis>
2707 Portal SPI_cursor_find(const char * <parameter>name</parameter>)
2708 </synopsis>
2709  </refsynopsisdiv>
2710
2711  <refsect1>
2712   <title>Description</title>
2713
2714   <para>
2715    <function>SPI_cursor_find</function> finds an existing portal by
2716    name.  This is primarily useful to resolve a cursor name returned
2717    as text by some other function.
2718   </para>
2719  </refsect1>
2720
2721  <refsect1>
2722   <title>Arguments</title>
2723
2724   <variablelist>
2725    <varlistentry>
2726     <term><literal>const char * <parameter>name</parameter></literal></term>
2727     <listitem>
2728      <para>
2729       name of the portal
2730      </para>
2731     </listitem>
2732    </varlistentry>
2733   </variablelist>
2734  </refsect1>
2735
2736  <refsect1>
2737   <title>Return Value</title>
2738
2739   <para>
2740    pointer to the portal with the specified name, or
2741    <symbol>NULL</symbol> if none was found
2742   </para>
2743  </refsect1>
2744
2745  <refsect1>
2746   <title>Notes</title>
2747
2748   <para>
2749    Beware that this function can return a <type>Portal</type> object
2750    that does not have cursor-like properties; for example it might not
2751    return tuples.  If you simply pass the <type>Portal</type> pointer
2752    to other SPI functions, they can defend themselves against such
2753    cases, but caution is appropriate when directly inspecting
2754    the <type>Portal</type>.
2755   </para>
2756  </refsect1>
2757 </refentry>
2758
2759 <!-- *********************************************** -->
2760
2761 <refentry id="spi-spi-cursor-fetch">
2762  <indexterm><primary>SPI_cursor_fetch</primary></indexterm>
2763
2764  <refmeta>
2765   <refentrytitle>SPI_cursor_fetch</refentrytitle>
2766   <manvolnum>3</manvolnum>
2767  </refmeta>
2768
2769  <refnamediv>
2770   <refname>SPI_cursor_fetch</refname>
2771   <refpurpose>fetch some rows from a cursor</refpurpose>
2772  </refnamediv>
2773
2774  <refsynopsisdiv>
2775 <synopsis>
2776 void SPI_cursor_fetch(Portal <parameter>portal</parameter>, bool <parameter>forward</parameter>, long <parameter>count</parameter>)
2777 </synopsis>
2778  </refsynopsisdiv>
2779
2780  <refsect1>
2781   <title>Description</title>
2782
2783   <para>
2784    <function>SPI_cursor_fetch</function> fetches some rows from a
2785    cursor.  This is equivalent to a subset of the SQL command
2786    <command>FETCH</command> (see <function>SPI_scroll_cursor_fetch</function>
2787    for more functionality).
2788   </para>
2789  </refsect1>
2790
2791  <refsect1>
2792   <title>Arguments</title>
2793
2794   <variablelist>
2795    <varlistentry>
2796     <term><literal>Portal <parameter>portal</parameter></literal></term>
2797     <listitem>
2798      <para>
2799       portal containing the cursor
2800      </para>
2801     </listitem>
2802    </varlistentry>
2803
2804    <varlistentry>
2805     <term><literal>bool <parameter>forward</parameter></literal></term>
2806     <listitem>
2807      <para>
2808       true for fetch forward, false for fetch backward
2809      </para>
2810     </listitem>
2811    </varlistentry>
2812
2813    <varlistentry>
2814     <term><literal>long <parameter>count</parameter></literal></term>
2815     <listitem>
2816      <para>
2817       maximum number of rows to fetch
2818      </para>
2819     </listitem>
2820    </varlistentry>
2821   </variablelist>
2822  </refsect1>
2823
2824  <refsect1>
2825   <title>Return Value</title>
2826
2827   <para>
2828    <varname>SPI_processed</varname> and
2829    <varname>SPI_tuptable</varname> are set as in
2830    <function>SPI_execute</function> if successful.
2831   </para>
2832  </refsect1>
2833
2834  <refsect1>
2835   <title>Notes</title>
2836
2837   <para>
2838    Fetching backward may fail if the cursor's plan was not created
2839    with the <symbol>CURSOR_OPT_SCROLL</symbol> option.
2840   </para>
2841  </refsect1>
2842 </refentry>
2843
2844 <!-- *********************************************** -->
2845
2846 <refentry id="spi-spi-cursor-move">
2847  <indexterm><primary>SPI_cursor_move</primary></indexterm>
2848
2849  <refmeta>
2850   <refentrytitle>SPI_cursor_move</refentrytitle>
2851   <manvolnum>3</manvolnum>
2852  </refmeta>
2853
2854  <refnamediv>
2855   <refname>SPI_cursor_move</refname>
2856   <refpurpose>move a cursor</refpurpose>
2857  </refnamediv>
2858
2859  <refsynopsisdiv>
2860 <synopsis>
2861 void SPI_cursor_move(Portal <parameter>portal</parameter>, bool <parameter>forward</parameter>, long <parameter>count</parameter>)
2862 </synopsis>
2863  </refsynopsisdiv>
2864
2865  <refsect1>
2866   <title>Description</title>
2867
2868   <para>
2869    <function>SPI_cursor_move</function> skips over some number of rows
2870    in a cursor.  This is equivalent to a subset of the SQL command
2871    <command>MOVE</command> (see <function>SPI_scroll_cursor_move</function>
2872    for more functionality).
2873   </para>
2874  </refsect1>
2875
2876  <refsect1>
2877   <title>Arguments</title>
2878
2879   <variablelist>
2880    <varlistentry>
2881     <term><literal>Portal <parameter>portal</parameter></literal></term>
2882     <listitem>
2883      <para>
2884       portal containing the cursor
2885      </para>
2886     </listitem>
2887    </varlistentry>
2888
2889    <varlistentry>
2890     <term><literal>bool <parameter>forward</parameter></literal></term>
2891     <listitem>
2892      <para>
2893       true for move forward, false for move backward
2894      </para>
2895     </listitem>
2896    </varlistentry>
2897
2898    <varlistentry>
2899     <term><literal>long <parameter>count</parameter></literal></term>
2900     <listitem>
2901      <para>
2902       maximum number of rows to move
2903      </para>
2904     </listitem>
2905    </varlistentry>
2906   </variablelist>
2907  </refsect1>
2908
2909  <refsect1>
2910   <title>Notes</title>
2911
2912   <para>
2913    Moving backward may fail if the cursor's plan was not created
2914    with the <symbol>CURSOR_OPT_SCROLL</symbol> option.
2915   </para>
2916  </refsect1>
2917 </refentry>
2918
2919 <!-- *********************************************** -->
2920
2921 <refentry id="spi-spi-scroll-cursor-fetch">
2922  <indexterm><primary>SPI_scroll_cursor_fetch</primary></indexterm>
2923
2924  <refmeta>
2925   <refentrytitle>SPI_scroll_cursor_fetch</refentrytitle>
2926   <manvolnum>3</manvolnum>
2927  </refmeta>
2928
2929  <refnamediv>
2930   <refname>SPI_scroll_cursor_fetch</refname>
2931   <refpurpose>fetch some rows from a cursor</refpurpose>
2932  </refnamediv>
2933
2934  <refsynopsisdiv>
2935 <synopsis>
2936 void SPI_scroll_cursor_fetch(Portal <parameter>portal</parameter>, FetchDirection <parameter>direction</parameter>,
2937                              long <parameter>count</parameter>)
2938 </synopsis>
2939  </refsynopsisdiv>
2940
2941  <refsect1>
2942   <title>Description</title>
2943
2944   <para>
2945    <function>SPI_scroll_cursor_fetch</function> fetches some rows from a
2946    cursor.  This is equivalent to the SQL command <command>FETCH</command>.
2947   </para>
2948  </refsect1>
2949
2950  <refsect1>
2951   <title>Arguments</title>
2952
2953   <variablelist>
2954    <varlistentry>
2955     <term><literal>Portal <parameter>portal</parameter></literal></term>
2956     <listitem>
2957      <para>
2958       portal containing the cursor
2959      </para>
2960     </listitem>
2961    </varlistentry>
2962
2963    <varlistentry>
2964     <term><literal>FetchDirection <parameter>direction</parameter></literal></term>
2965     <listitem>
2966      <para>
2967       one of <symbol>FETCH_FORWARD</symbol>,
2968       <symbol>FETCH_BACKWARD</symbol>,
2969       <symbol>FETCH_ABSOLUTE</symbol> or
2970       <symbol>FETCH_RELATIVE</symbol>
2971      </para>
2972     </listitem>
2973    </varlistentry>
2974
2975    <varlistentry>
2976     <term><literal>long <parameter>count</parameter></literal></term>
2977     <listitem>
2978      <para>
2979       number of rows to fetch for
2980       <symbol>FETCH_FORWARD</symbol> or
2981       <symbol>FETCH_BACKWARD</symbol>; absolute row number to fetch for
2982       <symbol>FETCH_ABSOLUTE</symbol>; or relative row number to fetch for
2983       <symbol>FETCH_RELATIVE</symbol>
2984      </para>
2985     </listitem>
2986    </varlistentry>
2987   </variablelist>
2988  </refsect1>
2989
2990  <refsect1>
2991   <title>Return Value</title>
2992
2993   <para>
2994    <varname>SPI_processed</varname> and
2995    <varname>SPI_tuptable</varname> are set as in
2996    <function>SPI_execute</function> if successful.
2997   </para>
2998  </refsect1>
2999
3000  <refsect1>
3001   <title>Notes</title>
3002
3003   <para>
3004    See the SQL <xref linkend="sql-fetch"/> command
3005    for details of the interpretation of the
3006    <parameter>direction</parameter> and
3007    <parameter>count</parameter> parameters.
3008   </para>
3009
3010   <para>
3011    Direction values other than <symbol>FETCH_FORWARD</symbol>
3012    may fail if the cursor's plan was not created
3013    with the <symbol>CURSOR_OPT_SCROLL</symbol> option.
3014   </para>
3015  </refsect1>
3016 </refentry>
3017
3018 <!-- *********************************************** -->
3019
3020 <refentry id="spi-spi-scroll-cursor-move">
3021  <indexterm><primary>SPI_scroll_cursor_move</primary></indexterm>
3022
3023  <refmeta>
3024   <refentrytitle>SPI_scroll_cursor_move</refentrytitle>
3025   <manvolnum>3</manvolnum>
3026  </refmeta>
3027
3028  <refnamediv>
3029   <refname>SPI_scroll_cursor_move</refname>
3030   <refpurpose>move a cursor</refpurpose>
3031  </refnamediv>
3032
3033  <refsynopsisdiv>
3034 <synopsis>
3035 void SPI_scroll_cursor_move(Portal <parameter>portal</parameter>, FetchDirection <parameter>direction</parameter>,
3036                             long <parameter>count</parameter>)
3037 </synopsis>
3038  </refsynopsisdiv>
3039
3040  <refsect1>
3041   <title>Description</title>
3042
3043   <para>
3044    <function>SPI_scroll_cursor_move</function> skips over some number of rows
3045    in a cursor.  This is equivalent to the SQL command
3046    <command>MOVE</command>.
3047   </para>
3048  </refsect1>
3049
3050  <refsect1>
3051   <title>Arguments</title>
3052
3053   <variablelist>
3054    <varlistentry>
3055     <term><literal>Portal <parameter>portal</parameter></literal></term>
3056     <listitem>
3057      <para>
3058       portal containing the cursor
3059      </para>
3060     </listitem>
3061    </varlistentry>
3062
3063    <varlistentry>
3064     <term><literal>FetchDirection <parameter>direction</parameter></literal></term>
3065     <listitem>
3066      <para>
3067       one of <symbol>FETCH_FORWARD</symbol>,
3068       <symbol>FETCH_BACKWARD</symbol>,
3069       <symbol>FETCH_ABSOLUTE</symbol> or
3070       <symbol>FETCH_RELATIVE</symbol>
3071      </para>
3072     </listitem>
3073    </varlistentry>
3074
3075    <varlistentry>
3076     <term><literal>long <parameter>count</parameter></literal></term>
3077     <listitem>
3078      <para>
3079       number of rows to move for
3080       <symbol>FETCH_FORWARD</symbol> or
3081       <symbol>FETCH_BACKWARD</symbol>; absolute row number to move to for
3082       <symbol>FETCH_ABSOLUTE</symbol>; or relative row number to move to for
3083       <symbol>FETCH_RELATIVE</symbol>
3084      </para>
3085     </listitem>
3086    </varlistentry>
3087   </variablelist>
3088  </refsect1>
3089
3090  <refsect1>
3091   <title>Return Value</title>
3092
3093   <para>
3094    <varname>SPI_processed</varname> is set as in
3095    <function>SPI_execute</function> if successful.
3096    <varname>SPI_tuptable</varname> is set to <symbol>NULL</symbol>, since
3097    no rows are returned by this function.
3098   </para>
3099  </refsect1>
3100
3101  <refsect1>
3102   <title>Notes</title>
3103
3104   <para>
3105    See the SQL <xref linkend="sql-fetch"/> command
3106    for details of the interpretation of the
3107    <parameter>direction</parameter> and
3108    <parameter>count</parameter> parameters.
3109   </para>
3110
3111   <para>
3112    Direction values other than <symbol>FETCH_FORWARD</symbol>
3113    may fail if the cursor's plan was not created
3114    with the <symbol>CURSOR_OPT_SCROLL</symbol> option.
3115   </para>
3116  </refsect1>
3117 </refentry>
3118
3119 <!-- *********************************************** -->
3120
3121 <refentry id="spi-spi-cursor-close">
3122  <indexterm><primary>SPI_cursor_close</primary></indexterm>
3123
3124  <refmeta>
3125   <refentrytitle>SPI_cursor_close</refentrytitle>
3126   <manvolnum>3</manvolnum>
3127  </refmeta>
3128
3129  <refnamediv>
3130   <refname>SPI_cursor_close</refname>
3131   <refpurpose>close a cursor</refpurpose>
3132  </refnamediv>
3133
3134  <refsynopsisdiv>
3135 <synopsis>
3136 void SPI_cursor_close(Portal <parameter>portal</parameter>)
3137 </synopsis>
3138  </refsynopsisdiv>
3139
3140  <refsect1>
3141   <title>Description</title>
3142
3143   <para>
3144    <function>SPI_cursor_close</function> closes a previously created
3145    cursor and releases its portal storage.
3146   </para>
3147
3148   <para>
3149    All open cursors are closed automatically at the end of a
3150    transaction.  <function>SPI_cursor_close</function> need only be
3151    invoked if it is desirable to release resources sooner.
3152   </para>
3153  </refsect1>
3154
3155  <refsect1>
3156   <title>Arguments</title>
3157
3158   <variablelist>
3159    <varlistentry>
3160     <term><literal>Portal <parameter>portal</parameter></literal></term>
3161     <listitem>
3162      <para>
3163       portal containing the cursor
3164      </para>
3165     </listitem>
3166    </varlistentry>
3167   </variablelist>
3168  </refsect1>
3169 </refentry>
3170
3171 <!-- *********************************************** -->
3172
3173 <refentry id="spi-spi-keepplan">
3174  <indexterm><primary>SPI_keepplan</primary></indexterm>
3175
3176  <refmeta>
3177   <refentrytitle>SPI_keepplan</refentrytitle>
3178   <manvolnum>3</manvolnum>
3179  </refmeta>
3180
3181  <refnamediv>
3182   <refname>SPI_keepplan</refname>
3183   <refpurpose>save a prepared statement</refpurpose>
3184  </refnamediv>
3185
3186  <refsynopsisdiv>
3187 <synopsis>
3188 int SPI_keepplan(SPIPlanPtr <parameter>plan</parameter>)
3189 </synopsis>
3190  </refsynopsisdiv>
3191
3192  <refsect1>
3193   <title>Description</title>
3194
3195   <para>
3196    <function>SPI_keepplan</function> saves a passed statement (prepared by
3197    <function>SPI_prepare</function>) so that it will not be freed
3198    by <function>SPI_finish</function> nor by the transaction manager.
3199    This gives you the ability to reuse prepared statements in the subsequent
3200    invocations of your C function in the current session.
3201   </para>
3202  </refsect1>
3203
3204  <refsect1>
3205   <title>Arguments</title>
3206
3207   <variablelist>
3208    <varlistentry>
3209     <term><literal>SPIPlanPtr <parameter>plan</parameter></literal></term>
3210     <listitem>
3211      <para>
3212       the prepared statement to be saved
3213      </para>
3214     </listitem>
3215    </varlistentry>
3216   </variablelist>
3217  </refsect1>
3218
3219  <refsect1>
3220   <title>Return Value</title>
3221
3222   <para>
3223    0 on success;
3224    <symbol>SPI_ERROR_ARGUMENT</symbol> if <parameter>plan</parameter>
3225    is <symbol>NULL</symbol> or invalid
3226   </para>
3227  </refsect1>
3228
3229  <refsect1>
3230   <title>Notes</title>
3231
3232   <para>
3233    The passed-in statement is relocated to permanent storage by means
3234    of pointer adjustment (no data copying is required).  If you later
3235    wish to delete it, use <function>SPI_freeplan</function> on it.
3236   </para>
3237  </refsect1>
3238 </refentry>
3239
3240 <!-- *********************************************** -->
3241
3242 <refentry id="spi-spi-saveplan">
3243  <indexterm><primary>SPI_saveplan</primary></indexterm>
3244
3245  <refmeta>
3246   <refentrytitle>SPI_saveplan</refentrytitle>
3247   <manvolnum>3</manvolnum>
3248  </refmeta>
3249
3250  <refnamediv>
3251   <refname>SPI_saveplan</refname>
3252   <refpurpose>save a prepared statement</refpurpose>
3253  </refnamediv>
3254
3255  <refsynopsisdiv>
3256 <synopsis>
3257 SPIPlanPtr SPI_saveplan(SPIPlanPtr <parameter>plan</parameter>)
3258 </synopsis>
3259  </refsynopsisdiv>
3260
3261  <refsect1>
3262   <title>Description</title>
3263
3264   <para>
3265    <function>SPI_saveplan</function> copies a passed statement (prepared by
3266    <function>SPI_prepare</function>) into memory that will not be freed
3267    by <function>SPI_finish</function> nor by the transaction manager,
3268    and returns a pointer to the copied statement.  This gives you the
3269    ability to reuse prepared statements in the subsequent invocations of
3270    your C function in the current session.
3271   </para>
3272  </refsect1>
3273
3274  <refsect1>
3275   <title>Arguments</title>
3276
3277   <variablelist>
3278    <varlistentry>
3279     <term><literal>SPIPlanPtr <parameter>plan</parameter></literal></term>
3280     <listitem>
3281      <para>
3282       the prepared statement to be saved
3283      </para>
3284     </listitem>
3285    </varlistentry>
3286   </variablelist>
3287  </refsect1>
3288
3289  <refsect1>
3290   <title>Return Value</title>
3291
3292   <para>
3293    Pointer to the copied statement; or <symbol>NULL</symbol> if unsuccessful.
3294    On error, <varname>SPI_result</varname> is set thus:
3295
3296    <variablelist>
3297     <varlistentry>
3298      <term><symbol>SPI_ERROR_ARGUMENT</symbol></term>
3299      <listitem>
3300       <para>
3301        if <parameter>plan</parameter> is <symbol>NULL</symbol> or invalid
3302       </para>
3303      </listitem>
3304     </varlistentry>
3305
3306     <varlistentry>
3307      <term><symbol>SPI_ERROR_UNCONNECTED</symbol></term>
3308      <listitem>
3309       <para>
3310        if called from an unconnected C function
3311       </para>
3312      </listitem>
3313     </varlistentry>
3314    </variablelist>
3315   </para>
3316  </refsect1>
3317
3318  <refsect1>
3319   <title>Notes</title>
3320
3321   <para>
3322    The originally passed-in statement is not freed, so you might wish to do
3323    <function>SPI_freeplan</function> on it to avoid leaking memory
3324    until <function>SPI_finish</function>.
3325   </para>
3326
3327   <para>
3328    In most cases, <function>SPI_keepplan</function> is preferred to this
3329    function, since it accomplishes largely the same result without needing
3330    to physically copy the prepared statement's data structures.
3331   </para>
3332  </refsect1>
3333 </refentry>
3334
3335 <!-- *********************************************** -->
3336
3337 <refentry id="spi-spi-register-relation">
3338  <indexterm><primary>SPI_register_relation</primary></indexterm>
3339
3340  <indexterm>
3341   <primary>ephemeral named relation</primary>
3342   <secondary>registering with SPI</secondary>
3343  </indexterm>
3344
3345  <refmeta>
3346   <refentrytitle>SPI_register_relation</refentrytitle>
3347   <manvolnum>3</manvolnum>
3348  </refmeta>
3349
3350  <refnamediv>
3351   <refname>SPI_register_relation</refname>
3352   <refpurpose>make an ephemeral named relation available by name in SPI queries</refpurpose>
3353  </refnamediv>
3354
3355  <refsynopsisdiv>
3356 <synopsis>
3357 int SPI_register_relation(EphemeralNamedRelation <parameter>enr</parameter>)
3358 </synopsis>
3359  </refsynopsisdiv>
3360
3361  <refsect1>
3362   <title>Description</title>
3363
3364   <para>
3365    <function>SPI_register_relation</function> makes an ephemeral named
3366    relation, with associated information, available to queries planned and
3367    executed through the current SPI connection.
3368   </para>
3369  </refsect1>
3370
3371  <refsect1>
3372   <title>Arguments</title>
3373
3374   <variablelist>
3375    <varlistentry>
3376     <term><literal>EphemeralNamedRelation <parameter>enr</parameter></literal></term>
3377     <listitem>
3378      <para>
3379       the ephemeral named relation registry entry
3380      </para>
3381     </listitem>
3382    </varlistentry>
3383   </variablelist>
3384  </refsect1>
3385
3386  <refsect1>
3387   <title>Return Value</title>
3388
3389   <para>
3390    If the execution of the command was successful then the following
3391    (nonnegative) value will be returned:
3392
3393    <variablelist>
3394     <varlistentry>
3395      <term><symbol>SPI_OK_REL_REGISTER</symbol></term>
3396      <listitem>
3397       <para>
3398        if the relation has been successfully registered by name
3399       </para>
3400      </listitem>
3401     </varlistentry>
3402    </variablelist>
3403   </para>
3404
3405   <para>
3406    On error, one of the following negative values is returned:
3407
3408    <variablelist>
3409     <varlistentry>
3410      <term><symbol>SPI_ERROR_ARGUMENT</symbol></term>
3411      <listitem>
3412       <para>
3413        if <parameter>enr</parameter> is <symbol>NULL</symbol> or its
3414        <varname>name</varname> field is <symbol>NULL</symbol>
3415       </para>
3416      </listitem>
3417     </varlistentry>
3418
3419     <varlistentry>
3420      <term><symbol>SPI_ERROR_UNCONNECTED</symbol></term>
3421      <listitem>
3422       <para>
3423        if called from an unconnected C function
3424       </para>
3425      </listitem>
3426     </varlistentry>
3427
3428     <varlistentry>
3429      <term><symbol>SPI_ERROR_REL_DUPLICATE</symbol></term>
3430      <listitem>
3431       <para>
3432        if the name specified in the <varname>name</varname> field of
3433        <parameter>enr</parameter> is already registered for this connection
3434       </para>
3435      </listitem>
3436     </varlistentry>
3437    </variablelist>
3438   </para>
3439  </refsect1>
3440 </refentry>
3441
3442 <!-- *********************************************** -->
3443
3444 <refentry id="spi-spi-unregister-relation">
3445  <indexterm><primary>SPI_unregister_relation</primary></indexterm>
3446
3447  <indexterm>
3448   <primary>ephemeral named relation</primary>
3449   <secondary>unregistering from SPI</secondary>
3450  </indexterm>
3451
3452  <refmeta>
3453   <refentrytitle>SPI_unregister_relation</refentrytitle>
3454   <manvolnum>3</manvolnum>
3455  </refmeta>
3456
3457  <refnamediv>
3458   <refname>SPI_unregister_relation</refname>
3459   <refpurpose>remove an ephemeral named relation from the registry</refpurpose>
3460  </refnamediv>
3461
3462  <refsynopsisdiv>
3463 <synopsis>
3464 int SPI_unregister_relation(const char * <parameter>name</parameter>)
3465 </synopsis>
3466  </refsynopsisdiv>
3467
3468  <refsect1>
3469   <title>Description</title>
3470
3471   <para>
3472    <function>SPI_unregister_relation</function> removes an ephemeral named
3473    relation from the registry for the current connection.
3474   </para>
3475  </refsect1>
3476
3477  <refsect1>
3478   <title>Arguments</title>
3479
3480   <variablelist>
3481    <varlistentry>
3482     <term><literal>const char * <parameter>name</parameter></literal></term>
3483     <listitem>
3484      <para>
3485       the relation registry entry name
3486      </para>
3487     </listitem>
3488    </varlistentry>
3489   </variablelist>
3490  </refsect1>
3491
3492  <refsect1>
3493   <title>Return Value</title>
3494
3495   <para>
3496    If the execution of the command was successful then the following
3497    (nonnegative) value will be returned:
3498
3499    <variablelist>
3500     <varlistentry>
3501      <term><symbol>SPI_OK_REL_UNREGISTER</symbol></term>
3502      <listitem>
3503       <para>
3504        if the tuplestore has been successfully removed from the registry
3505       </para>
3506      </listitem>
3507     </varlistentry>
3508    </variablelist>
3509   </para>
3510
3511   <para>
3512    On error, one of the following negative values is returned:
3513
3514    <variablelist>
3515     <varlistentry>
3516      <term><symbol>SPI_ERROR_ARGUMENT</symbol></term>
3517      <listitem>
3518       <para>
3519        if <parameter>name</parameter> is <symbol>NULL</symbol>
3520       </para>
3521      </listitem>
3522     </varlistentry>
3523
3524     <varlistentry>
3525      <term><symbol>SPI_ERROR_UNCONNECTED</symbol></term>
3526      <listitem>
3527       <para>
3528        if called from an unconnected C function
3529       </para>
3530      </listitem>
3531     </varlistentry>
3532
3533     <varlistentry>
3534      <term><symbol>SPI_ERROR_REL_NOT_FOUND</symbol></term>
3535      <listitem>
3536       <para>
3537        if <parameter>name</parameter> is not found in the registry for the
3538        current connection
3539       </para>
3540      </listitem>
3541     </varlistentry>
3542    </variablelist>
3543   </para>
3544  </refsect1>
3545 </refentry>
3546
3547 <!-- *********************************************** -->
3548
3549 <refentry id="spi-spi-register-trigger-data">
3550  <indexterm><primary>SPI_register_trigger_data</primary></indexterm>
3551
3552  <indexterm>
3553   <primary>ephemeral named relation</primary>
3554   <secondary>registering with SPI</secondary>
3555  </indexterm>
3556
3557  <indexterm>
3558   <primary>transition tables</primary>
3559   <secondary>implementation in PLs</secondary>
3560  </indexterm>
3561
3562  <refmeta>
3563   <refentrytitle>SPI_register_trigger_data</refentrytitle>
3564   <manvolnum>3</manvolnum>
3565  </refmeta>
3566
3567  <refnamediv>
3568   <refname>SPI_register_trigger_data</refname>
3569   <refpurpose>make ephemeral trigger data available in SPI queries</refpurpose>
3570  </refnamediv>
3571
3572  <refsynopsisdiv>
3573 <synopsis>
3574 int SPI_register_trigger_data(TriggerData *<parameter>tdata</parameter>)
3575 </synopsis>
3576  </refsynopsisdiv>
3577
3578  <refsect1>
3579   <title>Description</title>
3580
3581   <para>
3582    <function>SPI_register_trigger_data</function> makes any ephemeral
3583    relations captured by a trigger available to queries planned and executed
3584    through the current SPI connection.  Currently, this means the transition
3585    tables captured by an <literal>AFTER</literal> trigger defined with a
3586    <literal>REFERENCING OLD/NEW TABLE AS</literal> ... clause.  This function
3587    should be called by a PL trigger handler function after connecting.
3588   </para>
3589  </refsect1>
3590
3591  <refsect1>
3592   <title>Arguments</title>
3593
3594   <variablelist>
3595    <varlistentry>
3596     <term><literal>TriggerData *<parameter>tdata</parameter></literal></term>
3597     <listitem>
3598      <para>
3599        the <structname>TriggerData</structname> object passed to a trigger
3600        handler function as <literal>fcinfo->context</literal>
3601      </para>
3602     </listitem>
3603    </varlistentry>
3604   </variablelist>
3605  </refsect1>
3606
3607  <refsect1>
3608   <title>Return Value</title>
3609
3610   <para>
3611    If the execution of the command was successful then the following
3612    (nonnegative) value will be returned:
3613
3614    <variablelist>
3615     <varlistentry>
3616      <term><symbol>SPI_OK_TD_REGISTER</symbol></term>
3617      <listitem>
3618       <para>
3619        if the captured trigger data (if any) has been successfully registered
3620       </para>
3621      </listitem>
3622     </varlistentry>
3623    </variablelist>
3624   </para>
3625
3626   <para>
3627    On error, one of the following negative values is returned:
3628
3629    <variablelist>
3630     <varlistentry>
3631      <term><symbol>SPI_ERROR_ARGUMENT</symbol></term>
3632      <listitem>
3633       <para>
3634        if <parameter>tdata</parameter> is <symbol>NULL</symbol>
3635       </para>
3636      </listitem>
3637     </varlistentry>
3638
3639     <varlistentry>
3640      <term><symbol>SPI_ERROR_UNCONNECTED</symbol></term>
3641      <listitem>
3642       <para>
3643        if called from an unconnected C function
3644       </para>
3645      </listitem>
3646     </varlistentry>
3647
3648     <varlistentry>
3649      <term><symbol>SPI_ERROR_REL_DUPLICATE</symbol></term>
3650      <listitem>
3651       <para>
3652        if the name of any trigger data transient relation is already
3653        registered for this connection
3654       </para>
3655      </listitem>
3656     </varlistentry>
3657    </variablelist>
3658   </para>
3659  </refsect1>
3660 </refentry>
3661
3662 <!-- *********************************************** -->
3663
3664 </sect1>
3665
3666 <sect1 id="spi-interface-support">
3667  <title>Interface Support Functions</title>
3668
3669  <para>
3670   The functions described here provide an interface for extracting
3671   information from result sets returned by <function>SPI_execute</function> and
3672   other SPI functions.
3673  </para>
3674
3675  <para>
3676   All functions described in this section can be used by both
3677   connected and unconnected C functions.
3678  </para>
3679
3680 <!-- *********************************************** -->
3681
3682 <refentry id="spi-spi-fname">
3683  <indexterm><primary>SPI_fname</primary></indexterm>
3684
3685  <refmeta>
3686   <refentrytitle>SPI_fname</refentrytitle>
3687   <manvolnum>3</manvolnum>
3688  </refmeta>
3689
3690  <refnamediv>
3691   <refname>SPI_fname</refname>
3692   <refpurpose>determine the column name for the specified column number</refpurpose>
3693  </refnamediv>
3694
3695  <refsynopsisdiv>
3696 <synopsis>
3697 char * SPI_fname(TupleDesc <parameter>rowdesc</parameter>, int <parameter>colnumber</parameter>)
3698 </synopsis>
3699  </refsynopsisdiv>
3700
3701  <refsect1>
3702   <title>Description</title>
3703
3704   <para>
3705    <function>SPI_fname</function> returns a copy of the column name of the
3706    specified column.  (You can use <function>pfree</function> to
3707    release the copy of the name when you don't need it anymore.)
3708   </para>
3709  </refsect1>
3710
3711  <refsect1>
3712   <title>Arguments</title>
3713
3714   <variablelist>
3715    <varlistentry>
3716     <term><literal>TupleDesc <parameter>rowdesc</parameter></literal></term>
3717     <listitem>
3718      <para>
3719       input row description
3720      </para>
3721     </listitem>
3722    </varlistentry>
3723
3724    <varlistentry>
3725     <term><literal>int <parameter>colnumber</parameter></literal></term>
3726     <listitem>
3727      <para>
3728       column number (count starts at 1)
3729      </para>
3730     </listitem>
3731    </varlistentry>
3732   </variablelist>
3733  </refsect1>
3734
3735  <refsect1>
3736   <title>Return Value</title>
3737
3738   <para>
3739    The column name; <symbol>NULL</symbol> if
3740    <parameter>colnumber</parameter> is out of range.
3741    <varname>SPI_result</varname> set to
3742    <symbol>SPI_ERROR_NOATTRIBUTE</symbol> on error.
3743   </para>
3744  </refsect1>
3745 </refentry>
3746
3747 <!-- *********************************************** -->
3748
3749 <refentry id="spi-spi-fnumber">
3750  <indexterm><primary>SPI_fnumber</primary></indexterm>
3751
3752  <refmeta>
3753   <refentrytitle>SPI_fnumber</refentrytitle>
3754   <manvolnum>3</manvolnum>
3755  </refmeta>
3756
3757  <refnamediv>
3758   <refname>SPI_fnumber</refname>
3759   <refpurpose>determine the column number for the specified column name</refpurpose>
3760  </refnamediv>
3761
3762  <refsynopsisdiv>
3763 <synopsis>
3764 int SPI_fnumber(TupleDesc <parameter>rowdesc</parameter>, const char * <parameter>colname</parameter>)
3765 </synopsis>
3766  </refsynopsisdiv>
3767
3768  <refsect1>
3769   <title>Description</title>
3770
3771   <para>
3772    <function>SPI_fnumber</function> returns the column number for the
3773    column with the specified name.
3774   </para>
3775
3776   <para>
3777    If <parameter>colname</parameter> refers to a system column (e.g.,
3778    <literal>ctid</literal>) then the appropriate negative column number will
3779    be returned.  The caller should be careful to test the return value
3780    for exact equality to <symbol>SPI_ERROR_NOATTRIBUTE</symbol> to
3781    detect an error; testing the result for less than or equal to 0 is
3782    not correct unless system columns should be rejected.
3783   </para>
3784  </refsect1>
3785
3786  <refsect1>
3787   <title>Arguments</title>
3788
3789   <variablelist>
3790    <varlistentry>
3791     <term><literal>TupleDesc <parameter>rowdesc</parameter></literal></term>
3792     <listitem>
3793      <para>
3794       input row description
3795      </para>
3796     </listitem>
3797    </varlistentry>
3798
3799    <varlistentry>
3800     <term><literal>const char * <parameter>colname</parameter></literal></term>
3801     <listitem>
3802      <para>
3803       column name
3804      </para>
3805     </listitem>
3806    </varlistentry>
3807   </variablelist>
3808  </refsect1>
3809
3810  <refsect1>
3811   <title>Return Value</title>
3812
3813   <para>
3814    Column number (count starts at 1 for user-defined columns), or
3815    <symbol>SPI_ERROR_NOATTRIBUTE</symbol> if the named column was not
3816    found.
3817   </para>
3818  </refsect1>
3819 </refentry>
3820
3821 <!-- *********************************************** -->
3822
3823 <refentry id="spi-spi-getvalue">
3824  <indexterm><primary>SPI_getvalue</primary></indexterm>
3825
3826  <refmeta>
3827   <refentrytitle>SPI_getvalue</refentrytitle>
3828   <manvolnum>3</manvolnum>
3829  </refmeta>
3830
3831  <refnamediv>
3832   <refname>SPI_getvalue</refname>
3833   <refpurpose>return the string value of the specified column</refpurpose>
3834  </refnamediv>
3835
3836  <refsynopsisdiv>
3837 <synopsis>
3838 char * SPI_getvalue(HeapTuple <parameter>row</parameter>, TupleDesc <parameter>rowdesc</parameter>, int <parameter>colnumber</parameter>)
3839 </synopsis>
3840  </refsynopsisdiv>
3841
3842  <refsect1>
3843   <title>Description</title>
3844
3845   <para>
3846    <function>SPI_getvalue</function> returns the string representation
3847    of the value of the specified column.
3848   </para>
3849
3850   <para>
3851    The result is returned in memory allocated using
3852    <function>palloc</function>.  (You can use
3853    <function>pfree</function> to release the memory when you don't
3854    need it anymore.)
3855   </para>
3856  </refsect1>
3857
3858  <refsect1>
3859   <title>Arguments</title>
3860
3861   <variablelist>
3862    <varlistentry>
3863     <term><literal>HeapTuple <parameter>row</parameter></literal></term>
3864     <listitem>
3865      <para>
3866       input row to be examined
3867      </para>
3868     </listitem>
3869    </varlistentry>
3870
3871    <varlistentry>
3872     <term><literal>TupleDesc <parameter>rowdesc</parameter></literal></term>
3873     <listitem>
3874      <para>
3875       input row description
3876      </para>
3877     </listitem>
3878    </varlistentry>
3879
3880    <varlistentry>
3881     <term><literal>int <parameter>colnumber</parameter></literal></term>
3882     <listitem>
3883      <para>
3884       column number (count starts at 1)
3885      </para>
3886     </listitem>
3887    </varlistentry>
3888   </variablelist>
3889  </refsect1>
3890
3891  <refsect1>
3892   <title>Return Value</title>
3893
3894   <para>
3895    Column value, or <symbol>NULL</symbol> if the column is null,
3896    <parameter>colnumber</parameter> is out of range
3897    (<varname>SPI_result</varname> is set to
3898    <symbol>SPI_ERROR_NOATTRIBUTE</symbol>), or no output function is
3899    available (<varname>SPI_result</varname> is set to
3900    <symbol>SPI_ERROR_NOOUTFUNC</symbol>).
3901   </para>
3902  </refsect1>
3903 </refentry>
3904
3905 <!-- *********************************************** -->
3906
3907 <refentry id="spi-spi-getbinval">
3908  <indexterm><primary>SPI_getbinval</primary></indexterm>
3909
3910  <refmeta>
3911   <refentrytitle>SPI_getbinval</refentrytitle>
3912   <manvolnum>3</manvolnum>
3913  </refmeta>
3914
3915  <refnamediv>
3916   <refname>SPI_getbinval</refname>
3917   <refpurpose>return the binary value of the specified column</refpurpose>
3918  </refnamediv>
3919
3920  <refsynopsisdiv>
3921 <synopsis>
3922 Datum SPI_getbinval(HeapTuple <parameter>row</parameter>, TupleDesc <parameter>rowdesc</parameter>, int <parameter>colnumber</parameter>,
3923                     bool * <parameter>isnull</parameter>)
3924 </synopsis>
3925  </refsynopsisdiv>
3926
3927  <refsect1>
3928   <title>Description</title>
3929
3930   <para>
3931    <function>SPI_getbinval</function> returns the value of the
3932    specified column in the internal form (as type <type>Datum</type>).
3933   </para>
3934
3935   <para>
3936    This function does not allocate new space for the datum.  In the
3937    case of a pass-by-reference data type, the return value will be a
3938    pointer into the passed row.
3939   </para>
3940  </refsect1>
3941
3942  <refsect1>
3943   <title>Arguments</title>
3944
3945   <variablelist>
3946    <varlistentry>
3947     <term><literal>HeapTuple <parameter>row</parameter></literal></term>
3948     <listitem>
3949      <para>
3950       input row to be examined
3951      </para>
3952     </listitem>
3953    </varlistentry>
3954
3955    <varlistentry>
3956     <term><literal>TupleDesc <parameter>rowdesc</parameter></literal></term>
3957     <listitem>
3958      <para>
3959       input row description
3960      </para>
3961     </listitem>
3962    </varlistentry>
3963
3964    <varlistentry>
3965     <term><literal>int <parameter>colnumber</parameter></literal></term>
3966     <listitem>
3967      <para>
3968       column number (count starts at 1)
3969      </para>
3970     </listitem>
3971    </varlistentry>
3972
3973    <varlistentry>
3974     <term><literal>bool * <parameter>isnull</parameter></literal></term>
3975     <listitem>
3976      <para>
3977       flag for a null value in the column
3978      </para>
3979     </listitem>
3980    </varlistentry>
3981   </variablelist>
3982  </refsect1>
3983
3984  <refsect1>
3985   <title>Return Value</title>
3986
3987   <para>
3988    The binary value of the column is returned.  The variable pointed
3989    to by <parameter>isnull</parameter> is set to true if the column is
3990    null, else to false.
3991   </para>
3992
3993   <para>
3994    <varname>SPI_result</varname> is set to
3995    <symbol>SPI_ERROR_NOATTRIBUTE</symbol> on error.
3996   </para>
3997  </refsect1>
3998 </refentry>
3999
4000 <!-- *********************************************** -->
4001
4002 <refentry id="spi-spi-gettype">
4003  <indexterm><primary>SPI_gettype</primary></indexterm>
4004
4005  <refmeta>
4006   <refentrytitle>SPI_gettype</refentrytitle>
4007   <manvolnum>3</manvolnum>
4008  </refmeta>
4009
4010  <refnamediv>
4011   <refname>SPI_gettype</refname>
4012   <refpurpose>return the data type name of the specified column</refpurpose>
4013  </refnamediv>
4014
4015  <refsynopsisdiv>
4016 <synopsis>
4017 char * SPI_gettype(TupleDesc <parameter>rowdesc</parameter>, int <parameter>colnumber</parameter>)
4018 </synopsis>
4019  </refsynopsisdiv>
4020
4021  <refsect1>
4022   <title>Description</title>
4023
4024   <para>
4025    <function>SPI_gettype</function> returns a copy of the data type name of the
4026    specified column.  (You can use <function>pfree</function> to
4027    release the copy of the name when you don't need it anymore.)
4028   </para>
4029  </refsect1>
4030
4031  <refsect1>
4032   <title>Arguments</title>
4033
4034   <variablelist>
4035    <varlistentry>
4036     <term><literal>TupleDesc <parameter>rowdesc</parameter></literal></term>
4037     <listitem>
4038      <para>
4039       input row description
4040      </para>
4041     </listitem>
4042    </varlistentry>
4043
4044    <varlistentry>
4045     <term><literal>int <parameter>colnumber</parameter></literal></term>
4046     <listitem>
4047      <para>
4048       column number (count starts at 1)
4049      </para>
4050     </listitem>
4051    </varlistentry>
4052   </variablelist>
4053  </refsect1>
4054
4055  <refsect1>
4056   <title>Return Value</title>
4057
4058   <para>
4059    The data type name of the specified column, or
4060    <symbol>NULL</symbol> on error.  <varname>SPI_result</varname> is
4061    set to <symbol>SPI_ERROR_NOATTRIBUTE</symbol> on error.
4062   </para>
4063  </refsect1>
4064 </refentry>
4065
4066 <!-- *********************************************** -->
4067
4068 <refentry id="spi-spi-gettypeid">
4069  <indexterm><primary>SPI_gettypeid</primary></indexterm>
4070
4071  <refmeta>
4072   <refentrytitle>SPI_gettypeid</refentrytitle>
4073   <manvolnum>3</manvolnum>
4074  </refmeta>
4075
4076  <refnamediv>
4077   <refname>SPI_gettypeid</refname>
4078   <refpurpose>return the data type <acronym>OID</acronym> of the specified column</refpurpose>
4079  </refnamediv>
4080
4081  <refsynopsisdiv>
4082 <synopsis>
4083 Oid SPI_gettypeid(TupleDesc <parameter>rowdesc</parameter>, int <parameter>colnumber</parameter>)
4084 </synopsis>
4085  </refsynopsisdiv>
4086
4087  <refsect1>
4088   <title>Description</title>
4089
4090   <para>
4091    <function>SPI_gettypeid</function> returns the
4092    <acronym>OID</acronym> of the data type of the specified column.
4093   </para>
4094  </refsect1>
4095
4096  <refsect1>
4097   <title>Arguments</title>
4098
4099   <variablelist>
4100    <varlistentry>
4101     <term><literal>TupleDesc <parameter>rowdesc</parameter></literal></term>
4102     <listitem>
4103      <para>
4104       input row description
4105      </para>
4106     </listitem>
4107    </varlistentry>
4108
4109    <varlistentry>
4110     <term><literal>int <parameter>colnumber</parameter></literal></term>
4111     <listitem>
4112      <para>
4113       column number (count starts at 1)
4114      </para>
4115     </listitem>
4116    </varlistentry>
4117   </variablelist>
4118  </refsect1>
4119
4120  <refsect1>
4121   <title>Return Value</title>
4122
4123   <para>
4124    The <acronym>OID</acronym> of the data type of the specified column
4125    or <symbol>InvalidOid</symbol> on error.  On error,
4126    <varname>SPI_result</varname> is set to
4127    <symbol>SPI_ERROR_NOATTRIBUTE</symbol>.
4128   </para>
4129  </refsect1>
4130 </refentry>
4131
4132 <!-- *********************************************** -->
4133
4134 <refentry id="spi-spi-getrelname">
4135  <indexterm><primary>SPI_getrelname</primary></indexterm>
4136
4137  <refmeta>
4138   <refentrytitle>SPI_getrelname</refentrytitle>
4139   <manvolnum>3</manvolnum>
4140  </refmeta>
4141
4142  <refnamediv>
4143   <refname>SPI_getrelname</refname>
4144   <refpurpose>return the name of the specified relation</refpurpose>
4145  </refnamediv>
4146
4147  <refsynopsisdiv>
4148 <synopsis>
4149 char * SPI_getrelname(Relation <parameter>rel</parameter>)
4150 </synopsis>
4151  </refsynopsisdiv>
4152
4153  <refsect1>
4154   <title>Description</title>
4155
4156   <para>
4157    <function>SPI_getrelname</function> returns a copy of the name of the
4158    specified relation.  (You can use <function>pfree</function> to
4159    release the copy of the name when you don't need it anymore.)
4160   </para>
4161  </refsect1>
4162
4163  <refsect1>
4164   <title>Arguments</title>
4165
4166   <variablelist>
4167    <varlistentry>
4168     <term><literal>Relation <parameter>rel</parameter></literal></term>
4169     <listitem>
4170      <para>
4171       input relation
4172      </para>
4173     </listitem>
4174    </varlistentry>
4175   </variablelist>
4176  </refsect1>
4177
4178  <refsect1>
4179   <title>Return Value</title>
4180
4181   <para>
4182    The name of the specified relation.
4183   </para>
4184  </refsect1>
4185 </refentry>
4186
4187 <refentry id="spi-spi-getnspname">
4188  <indexterm><primary>SPI_getnspname</primary></indexterm>
4189
4190  <refmeta>
4191   <refentrytitle>SPI_getnspname</refentrytitle>
4192   <manvolnum>3</manvolnum>
4193  </refmeta>
4194
4195  <refnamediv>
4196   <refname>SPI_getnspname</refname>
4197   <refpurpose>return the namespace of the specified relation</refpurpose>
4198  </refnamediv>
4199
4200  <refsynopsisdiv>
4201 <synopsis>
4202 char * SPI_getnspname(Relation <parameter>rel</parameter>)
4203 </synopsis>
4204  </refsynopsisdiv>
4205
4206  <refsect1>
4207   <title>Description</title>
4208
4209   <para>
4210    <function>SPI_getnspname</function> returns a copy of the name of
4211    the namespace that the specified <structname>Relation</structname>
4212    belongs to. This is equivalent to the relation's schema. You should
4213    <function>pfree</function> the return value of this function when
4214    you are finished with it.
4215   </para>
4216  </refsect1>
4217
4218  <refsect1>
4219   <title>Arguments</title>
4220
4221   <variablelist>
4222    <varlistentry>
4223     <term><literal>Relation <parameter>rel</parameter></literal></term>
4224     <listitem>
4225      <para>
4226       input relation
4227      </para>
4228     </listitem>
4229    </varlistentry>
4230   </variablelist>
4231  </refsect1>
4232
4233  <refsect1>
4234   <title>Return Value</title>
4235
4236   <para>
4237    The name of the specified relation's namespace.
4238   </para>
4239  </refsect1>
4240 </refentry>
4241
4242 <refentry id="spi-spi-result-code-string">
4243  <indexterm><primary>SPI_result_code_string</primary></indexterm>
4244
4245  <refmeta>
4246   <refentrytitle>SPI_result_code_string</refentrytitle>
4247   <manvolnum>3</manvolnum>
4248  </refmeta>
4249
4250  <refnamediv>
4251   <refname>SPI_result_code_string</refname>
4252   <refpurpose>return error code as string</refpurpose>
4253  </refnamediv>
4254
4255  <refsynopsisdiv>
4256 <synopsis>
4257 const char * SPI_result_code_string(int <parameter>code</parameter>);
4258 </synopsis>
4259  </refsynopsisdiv>
4260
4261  <refsect1>
4262   <title>Description</title>
4263
4264   <para>
4265    <function>SPI_result_code_string</function> returns a string representation
4266    of the result code returned by various SPI functions or stored
4267    in <varname>SPI_result</varname>.
4268   </para>
4269  </refsect1>
4270
4271  <refsect1>
4272   <title>Arguments</title>
4273
4274   <variablelist>
4275    <varlistentry>
4276     <term><literal>int <parameter>code</parameter></literal></term>
4277     <listitem>
4278      <para>
4279       result code
4280      </para>
4281     </listitem>
4282    </varlistentry>
4283   </variablelist>
4284  </refsect1>
4285
4286  <refsect1>
4287   <title>Return Value</title>
4288
4289   <para>
4290    A string representation of the result code.
4291   </para>
4292  </refsect1>
4293 </refentry>
4294
4295  </sect1>
4296
4297  <sect1 id="spi-memory">
4298   <title>Memory Management</title>
4299
4300   <para>
4301     <indexterm>
4302      <primary>memory context</primary>
4303      <secondary>in SPI</secondary>
4304     </indexterm>
4305    <productname>PostgreSQL</productname> allocates memory within
4306    <firstterm>memory contexts</firstterm>, which provide a convenient method of
4307    managing allocations made in many different places that need to
4308    live for differing amounts of time.  Destroying a context releases
4309    all the memory that was allocated in it.  Thus, it is not necessary
4310    to keep track of individual objects to avoid memory leaks; instead
4311    only a relatively small number of contexts have to be managed.
4312    <function>palloc</function> and related functions allocate memory
4313    from the <quote>current</quote> context.
4314   </para>
4315
4316   <para>
4317    <function>SPI_connect</function> creates a new memory context and
4318    makes it current.  <function>SPI_finish</function> restores the
4319    previous current memory context and destroys the context created by
4320    <function>SPI_connect</function>.  These actions ensure that
4321    transient memory allocations made inside your C function are
4322    reclaimed at C function exit, avoiding memory leakage.
4323   </para>
4324
4325   <para>
4326    However, if your C function needs to return an object in allocated
4327    memory (such as a value of a pass-by-reference data type), you
4328    cannot allocate that memory using <function>palloc</function>, at
4329    least not while you are connected to SPI.  If you try, the object
4330    will be deallocated by <function>SPI_finish</function>, and your
4331    C function will not work reliably.  To solve this problem, use
4332    <function>SPI_palloc</function> to allocate memory for your return
4333    object.  <function>SPI_palloc</function> allocates memory in the
4334    <quote>upper executor context</quote>, that is, the memory context
4335    that was current when <function>SPI_connect</function> was called,
4336    which is precisely the right context for a value returned from your
4337    C function.  Several of the other utility functions described in
4338    this section also return objects created in the upper executor context.
4339   </para>
4340
4341   <para>
4342    When <function>SPI_connect</function> is called, the private
4343    context of the C function, which is created by
4344    <function>SPI_connect</function>, is made the current context.  All
4345    allocations made by <function>palloc</function>,
4346    <function>repalloc</function>, or SPI utility functions (except as
4347    described in this section) are made in this context.  When a
4348    C function disconnects from the SPI manager (via
4349    <function>SPI_finish</function>) the current context is restored to
4350    the upper executor context, and all allocations made in the
4351    C function memory context are freed and cannot be used any more.
4352   </para>
4353
4354 <!-- *********************************************** -->
4355
4356 <refentry id="spi-spi-palloc">
4357  <indexterm><primary>SPI_palloc</primary></indexterm>
4358
4359  <refmeta>
4360   <refentrytitle>SPI_palloc</refentrytitle>
4361   <manvolnum>3</manvolnum>
4362  </refmeta>
4363
4364  <refnamediv>
4365   <refname>SPI_palloc</refname>
4366   <refpurpose>allocate memory in the upper executor context</refpurpose>
4367  </refnamediv>
4368
4369  <refsynopsisdiv>
4370 <synopsis>
4371 void * SPI_palloc(Size <parameter>size</parameter>)
4372 </synopsis>
4373  </refsynopsisdiv>
4374
4375  <refsect1>
4376   <title>Description</title>
4377
4378   <para>
4379    <function>SPI_palloc</function> allocates memory in the upper
4380    executor context.
4381   </para>
4382
4383   <para>
4384    This function can only be used while connected to SPI.
4385    Otherwise, it throws an error.
4386   </para>
4387  </refsect1>
4388
4389  <refsect1>
4390   <title>Arguments</title>
4391
4392   <variablelist>
4393    <varlistentry>
4394     <term><literal>Size <parameter>size</parameter></literal></term>
4395     <listitem>
4396      <para>
4397       size in bytes of storage to allocate
4398      </para>
4399     </listitem>
4400    </varlistentry>
4401   </variablelist>
4402  </refsect1>
4403
4404  <refsect1>
4405   <title>Return Value</title>
4406
4407   <para>
4408    pointer to new storage space of the specified size
4409   </para>
4410  </refsect1>
4411 </refentry>
4412
4413 <!-- *********************************************** -->
4414
4415 <refentry id="spi-realloc">
4416  <indexterm><primary>SPI_repalloc</primary></indexterm>
4417
4418  <refmeta>
4419   <refentrytitle>SPI_repalloc</refentrytitle>
4420   <manvolnum>3</manvolnum>
4421  </refmeta>
4422
4423  <refnamediv>
4424   <refname>SPI_repalloc</refname>
4425   <refpurpose>reallocate memory in the upper executor context</refpurpose>
4426  </refnamediv>
4427
4428  <refsynopsisdiv>
4429 <synopsis>
4430 void * SPI_repalloc(void * <parameter>pointer</parameter>, Size <parameter>size</parameter>)
4431 </synopsis>
4432  </refsynopsisdiv>
4433
4434  <refsect1>
4435   <title>Description</title>
4436
4437   <para>
4438    <function>SPI_repalloc</function> changes the size of a memory
4439    segment previously allocated using <function>SPI_palloc</function>.
4440   </para>
4441
4442   <para>
4443    This function is no longer different from plain
4444    <function>repalloc</function>.  It's kept just for backward
4445    compatibility of existing code.
4446   </para>
4447  </refsect1>
4448
4449  <refsect1>
4450   <title>Arguments</title>
4451
4452   <variablelist>
4453    <varlistentry>
4454     <term><literal>void * <parameter>pointer</parameter></literal></term>
4455     <listitem>
4456      <para>
4457       pointer to existing storage to change
4458      </para>
4459     </listitem>
4460    </varlistentry>
4461
4462    <varlistentry>
4463     <term><literal>Size <parameter>size</parameter></literal></term>
4464     <listitem>
4465      <para>
4466       size in bytes of storage to allocate
4467      </para>
4468     </listitem>
4469    </varlistentry>
4470   </variablelist>
4471  </refsect1>
4472
4473  <refsect1>
4474   <title>Return Value</title>
4475
4476   <para>
4477    pointer to new storage space of specified size with the contents
4478    copied from the existing area
4479   </para>
4480  </refsect1>
4481 </refentry>
4482
4483 <!-- *********************************************** -->
4484
4485 <refentry id="spi-spi-pfree">
4486  <indexterm><primary>SPI_pfree</primary></indexterm>
4487
4488  <refmeta>
4489   <refentrytitle>SPI_pfree</refentrytitle>
4490   <manvolnum>3</manvolnum>
4491  </refmeta>
4492
4493  <refnamediv>
4494   <refname>SPI_pfree</refname>
4495   <refpurpose>free memory in the upper executor context</refpurpose>
4496  </refnamediv>
4497
4498  <refsynopsisdiv>
4499 <synopsis>
4500 void SPI_pfree(void * <parameter>pointer</parameter>)
4501 </synopsis>
4502  </refsynopsisdiv>
4503
4504  <refsect1>
4505   <title>Description</title>
4506
4507   <para>
4508    <function>SPI_pfree</function> frees memory previously allocated
4509    using <function>SPI_palloc</function> or
4510    <function>SPI_repalloc</function>.
4511   </para>
4512
4513   <para>
4514    This function is no longer different from plain
4515    <function>pfree</function>.  It's kept just for backward
4516    compatibility of existing code.
4517   </para>
4518  </refsect1>
4519
4520  <refsect1>
4521   <title>Arguments</title>
4522
4523   <variablelist>
4524    <varlistentry>
4525     <term><literal>void * <parameter>pointer</parameter></literal></term>
4526     <listitem>
4527      <para>
4528       pointer to existing storage to free
4529      </para>
4530     </listitem>
4531    </varlistentry>
4532   </variablelist>
4533  </refsect1>
4534 </refentry>
4535
4536 <!-- *********************************************** -->
4537
4538 <refentry id="spi-spi-copytuple">
4539  <indexterm><primary>SPI_copytuple</primary></indexterm>
4540
4541  <refmeta>
4542   <refentrytitle>SPI_copytuple</refentrytitle>
4543   <manvolnum>3</manvolnum>
4544  </refmeta>
4545
4546  <refnamediv>
4547   <refname>SPI_copytuple</refname>
4548   <refpurpose>make a copy of a row in the upper executor context</refpurpose>
4549  </refnamediv>
4550
4551  <refsynopsisdiv>
4552 <synopsis>
4553 HeapTuple SPI_copytuple(HeapTuple <parameter>row</parameter>)
4554 </synopsis>
4555  </refsynopsisdiv>
4556
4557  <refsect1>
4558   <title>Description</title>
4559
4560   <para>
4561    <function>SPI_copytuple</function> makes a copy of a row in the
4562    upper executor context.  This is normally used to return a modified
4563    row from a trigger.  In a function declared to return a composite
4564    type, use <function>SPI_returntuple</function> instead.
4565   </para>
4566
4567   <para>
4568    This function can only be used while connected to SPI.
4569    Otherwise, it returns NULL and sets <varname>SPI_result</varname> to
4570    <symbol>SPI_ERROR_UNCONNECTED</symbol>.
4571   </para>
4572  </refsect1>
4573
4574  <refsect1>
4575   <title>Arguments</title>
4576
4577   <variablelist>
4578    <varlistentry>
4579     <term><literal>HeapTuple <parameter>row</parameter></literal></term>
4580     <listitem>
4581      <para>
4582       row to be copied
4583      </para>
4584     </listitem>
4585    </varlistentry>
4586   </variablelist>
4587  </refsect1>
4588
4589  <refsect1>
4590   <title>Return Value</title>
4591
4592   <para>
4593    the copied row, or <symbol>NULL</symbol> on error
4594    (see <varname>SPI_result</varname> for an error indication)
4595   </para>
4596  </refsect1>
4597 </refentry>
4598
4599 <!-- *********************************************** -->
4600
4601 <refentry id="spi-spi-returntuple">
4602  <indexterm><primary>SPI_returntuple</primary></indexterm>
4603
4604  <refmeta>
4605   <refentrytitle>SPI_returntuple</refentrytitle>
4606   <manvolnum>3</manvolnum>
4607  </refmeta>
4608
4609  <refnamediv>
4610   <refname>SPI_returntuple</refname>
4611   <refpurpose>prepare to return a tuple as a Datum</refpurpose>
4612  </refnamediv>
4613
4614  <refsynopsisdiv>
4615 <synopsis>
4616 HeapTupleHeader SPI_returntuple(HeapTuple <parameter>row</parameter>, TupleDesc <parameter>rowdesc</parameter>)
4617 </synopsis>
4618  </refsynopsisdiv>
4619
4620  <refsect1>
4621   <title>Description</title>
4622
4623   <para>
4624    <function>SPI_returntuple</function> makes a copy of a row in
4625    the upper executor context, returning it in the form of a row type <type>Datum</type>.
4626    The returned pointer need only be converted to <type>Datum</type> via <function>PointerGetDatum</function>
4627    before returning.
4628   </para>
4629
4630   <para>
4631    This function can only be used while connected to SPI.
4632    Otherwise, it returns NULL and sets <varname>SPI_result</varname> to
4633    <symbol>SPI_ERROR_UNCONNECTED</symbol>.
4634   </para>
4635
4636   <para>
4637    Note that this should be used for functions that are declared to return
4638    composite types.  It is not used for triggers; use
4639    <function>SPI_copytuple</function> for returning a modified row in a trigger.
4640   </para>
4641  </refsect1>
4642
4643  <refsect1>
4644   <title>Arguments</title>
4645
4646   <variablelist>
4647    <varlistentry>
4648     <term><literal>HeapTuple <parameter>row</parameter></literal></term>
4649     <listitem>
4650      <para>
4651       row to be copied
4652      </para>
4653     </listitem>
4654    </varlistentry>
4655
4656    <varlistentry>
4657     <term><literal>TupleDesc <parameter>rowdesc</parameter></literal></term>
4658     <listitem>
4659      <para>
4660       descriptor for row (pass the same descriptor each time for most
4661       effective caching)
4662      </para>
4663     </listitem>
4664    </varlistentry>
4665   </variablelist>
4666  </refsect1>
4667
4668  <refsect1>
4669   <title>Return Value</title>
4670
4671   <para>
4672    <type>HeapTupleHeader</type> pointing to copied row,
4673    or <symbol>NULL</symbol> on error
4674    (see <varname>SPI_result</varname> for an error indication)
4675   </para>
4676  </refsect1>
4677 </refentry>
4678
4679 <!-- *********************************************** -->
4680
4681 <refentry id="spi-spi-modifytuple">
4682  <indexterm><primary>SPI_modifytuple</primary></indexterm>
4683
4684  <refmeta>
4685   <refentrytitle>SPI_modifytuple</refentrytitle>
4686   <manvolnum>3</manvolnum>
4687  </refmeta>
4688
4689  <refnamediv>
4690   <refname>SPI_modifytuple</refname>
4691   <refpurpose>create a row by replacing selected fields of a given row</refpurpose>
4692  </refnamediv>
4693
4694  <refsynopsisdiv>
4695 <synopsis>
4696 HeapTuple SPI_modifytuple(Relation <parameter>rel</parameter>, HeapTuple <parameter>row</parameter>, int <parameter>ncols</parameter>,
4697                           int * <parameter>colnum</parameter>, Datum * <parameter>values</parameter>, const char * <parameter>nulls</parameter>)
4698 </synopsis>
4699  </refsynopsisdiv>
4700
4701  <refsect1>
4702   <title>Description</title>
4703
4704   <para>
4705    <function>SPI_modifytuple</function> creates a new row by
4706    substituting new values for selected columns, copying the original
4707    row's columns at other positions.  The input row is not modified.
4708    The new row is returned in the upper executor context.
4709   </para>
4710
4711   <para>
4712    This function can only be used while connected to SPI.
4713    Otherwise, it returns NULL and sets <varname>SPI_result</varname> to
4714    <symbol>SPI_ERROR_UNCONNECTED</symbol>.
4715   </para>
4716  </refsect1>
4717
4718  <refsect1>
4719   <title>Arguments</title>
4720
4721   <variablelist>
4722    <varlistentry>
4723     <term><literal>Relation <parameter>rel</parameter></literal></term>
4724     <listitem>
4725      <para>
4726       Used only as the source of the row descriptor for the row.
4727       (Passing a relation rather than a row descriptor is a
4728       misfeature.)
4729      </para>
4730     </listitem>
4731    </varlistentry>
4732
4733    <varlistentry>
4734     <term><literal>HeapTuple <parameter>row</parameter></literal></term>
4735     <listitem>
4736      <para>
4737       row to be modified
4738      </para>
4739     </listitem>
4740    </varlistentry>
4741
4742    <varlistentry>
4743     <term><literal>int <parameter>ncols</parameter></literal></term>
4744     <listitem>
4745      <para>
4746       number of columns to be changed
4747      </para>
4748     </listitem>
4749    </varlistentry>
4750
4751    <varlistentry>
4752     <term><literal>int * <parameter>colnum</parameter></literal></term>
4753     <listitem>
4754      <para>
4755       an array of length <parameter>ncols</parameter>, containing the numbers
4756       of the columns that are to be changed (column numbers start at 1)
4757      </para>
4758     </listitem>
4759    </varlistentry>
4760
4761    <varlistentry>
4762     <term><literal>Datum * <parameter>values</parameter></literal></term>
4763     <listitem>
4764      <para>
4765       an array of length <parameter>ncols</parameter>, containing the
4766       new values for the specified columns
4767      </para>
4768     </listitem>
4769    </varlistentry>
4770
4771    <varlistentry>
4772     <term><literal>const char * <parameter>nulls</parameter></literal></term>
4773     <listitem>
4774      <para>
4775       an array of length <parameter>ncols</parameter>, describing which
4776       new values are null
4777      </para>
4778
4779      <para>
4780       If <parameter>nulls</parameter> is <symbol>NULL</symbol> then
4781       <function>SPI_modifytuple</function> assumes that no new values
4782       are null.  Otherwise, each entry of the <parameter>nulls</parameter>
4783       array should be <literal>'&nbsp;'</literal> if the corresponding new value is
4784       non-null, or <literal>'n'</literal> if the corresponding new value is
4785       null.  (In the latter case, the actual value in the corresponding
4786       <parameter>values</parameter> entry doesn't matter.)  Note that
4787       <parameter>nulls</parameter> is not a text string, just an array: it
4788       does not need a <literal>'\0'</literal> terminator.
4789      </para>
4790     </listitem>
4791    </varlistentry>
4792   </variablelist>
4793  </refsect1>
4794
4795  <refsect1>
4796   <title>Return Value</title>
4797
4798   <para>
4799    new row with modifications, allocated in the upper executor
4800    context, or <symbol>NULL</symbol> on error
4801    (see <varname>SPI_result</varname> for an error indication)
4802   </para>
4803
4804   <para>
4805    On error, <varname>SPI_result</varname> is set as follows:
4806    <variablelist>
4807     <varlistentry>
4808      <term><symbol>SPI_ERROR_ARGUMENT</symbol></term>
4809      <listitem>
4810       <para>
4811        if <parameter>rel</parameter> is <symbol>NULL</symbol>, or if
4812        <parameter>row</parameter> is <symbol>NULL</symbol>, or if <parameter>ncols</parameter>
4813        is less than or equal to 0, or if <parameter>colnum</parameter> is
4814        <symbol>NULL</symbol>, or if <parameter>values</parameter> is <symbol>NULL</symbol>.
4815       </para>
4816      </listitem>
4817     </varlistentry>
4818
4819     <varlistentry>
4820      <term><symbol>SPI_ERROR_NOATTRIBUTE</symbol></term>
4821      <listitem>
4822       <para>
4823        if <parameter>colnum</parameter> contains an invalid column number (less
4824        than or equal to 0 or greater than the number of columns in
4825        <parameter>row</parameter>)
4826       </para>
4827      </listitem>
4828     </varlistentry>
4829
4830     <varlistentry>
4831      <term><symbol>SPI_ERROR_UNCONNECTED</symbol></term>
4832      <listitem>
4833       <para>
4834        if SPI is not active
4835       </para>
4836      </listitem>
4837     </varlistentry>
4838    </variablelist>
4839   </para>
4840  </refsect1>
4841 </refentry>
4842
4843 <!-- *********************************************** -->
4844
4845 <refentry id="spi-spi-freetuple">
4846  <indexterm><primary>SPI_freetuple</primary></indexterm>
4847
4848  <refmeta>
4849   <refentrytitle>SPI_freetuple</refentrytitle>
4850   <manvolnum>3</manvolnum>
4851  </refmeta>
4852
4853  <refnamediv>
4854   <refname>SPI_freetuple</refname>
4855   <refpurpose>free a row allocated in the upper executor context</refpurpose>
4856  </refnamediv>
4857
4858  <refsynopsisdiv>
4859 <synopsis>
4860 void SPI_freetuple(HeapTuple <parameter>row</parameter>)
4861 </synopsis>
4862  </refsynopsisdiv>
4863
4864  <refsect1>
4865   <title>Description</title>
4866
4867   <para>
4868    <function>SPI_freetuple</function> frees a row previously allocated
4869    in the upper executor context.
4870   </para>
4871
4872   <para>
4873    This function is no longer different from plain
4874    <function>heap_freetuple</function>.  It's kept just for backward
4875    compatibility of existing code.
4876   </para>
4877  </refsect1>
4878
4879  <refsect1>
4880   <title>Arguments</title>
4881
4882   <variablelist>
4883    <varlistentry>
4884     <term><literal>HeapTuple <parameter>row</parameter></literal></term>
4885     <listitem>
4886      <para>
4887       row to free
4888      </para>
4889     </listitem>
4890    </varlistentry>
4891   </variablelist>
4892  </refsect1>
4893 </refentry>
4894
4895 <!-- *********************************************** -->
4896
4897 <refentry id="spi-spi-freetupletable">
4898  <indexterm><primary>SPI_freetuptable</primary></indexterm>
4899
4900  <refmeta>
4901   <refentrytitle>SPI_freetuptable</refentrytitle>
4902   <manvolnum>3</manvolnum>
4903  </refmeta>
4904
4905  <refnamediv>
4906   <refname>SPI_freetuptable</refname>
4907   <refpurpose>free a row set created by <function>SPI_execute</function> or a similar
4908   function</refpurpose>
4909  </refnamediv>
4910
4911  <refsynopsisdiv>
4912 <synopsis>
4913 void SPI_freetuptable(SPITupleTable * <parameter>tuptable</parameter>)
4914 </synopsis>
4915  </refsynopsisdiv>
4916
4917  <refsect1>
4918   <title>Description</title>
4919
4920   <para>
4921    <function>SPI_freetuptable</function> frees a row set created by a
4922    prior SPI command execution function, such as
4923    <function>SPI_execute</function>.  Therefore, this function is often called
4924    with the global variable <varname>SPI_tuptable</varname> as
4925    argument.
4926   </para>
4927
4928   <para>
4929    This function is useful if an SPI-using C function needs to execute
4930    multiple commands and does not want to keep the results of earlier
4931    commands around until it ends.  Note that any unfreed row sets will
4932    be freed anyway at <function>SPI_finish</function>.
4933    Also, if a subtransaction is started and then aborted within execution
4934    of an SPI-using C function, SPI automatically frees any row sets created while
4935    the subtransaction was running.
4936   </para>
4937
4938   <para>
4939    Beginning in <productname>PostgreSQL</productname> 9.3,
4940    <function>SPI_freetuptable</function> contains guard logic to protect
4941    against duplicate deletion requests for the same row set.  In previous
4942    releases, duplicate deletions would lead to crashes.
4943   </para>
4944  </refsect1>
4945
4946  <refsect1>
4947   <title>Arguments</title>
4948
4949   <variablelist>
4950    <varlistentry>
4951     <term><literal>SPITupleTable * <parameter>tuptable</parameter></literal></term>
4952     <listitem>
4953      <para>
4954       pointer to row set to free, or NULL to do nothing
4955      </para>
4956     </listitem>
4957    </varlistentry>
4958   </variablelist>
4959  </refsect1>
4960 </refentry>
4961
4962 <!-- *********************************************** -->
4963
4964 <refentry id="spi-spi-freeplan">
4965  <indexterm><primary>SPI_freeplan</primary></indexterm>
4966
4967  <refmeta>
4968   <refentrytitle>SPI_freeplan</refentrytitle>
4969   <manvolnum>3</manvolnum>
4970  </refmeta>
4971
4972  <refnamediv>
4973   <refname>SPI_freeplan</refname>
4974   <refpurpose>free a previously saved prepared statement</refpurpose>
4975  </refnamediv>
4976
4977  <refsynopsisdiv>
4978 <synopsis>
4979 int SPI_freeplan(SPIPlanPtr <parameter>plan</parameter>)
4980 </synopsis>
4981  </refsynopsisdiv>
4982
4983  <refsect1>
4984   <title>Description</title>
4985
4986   <para>
4987    <function>SPI_freeplan</function> releases a prepared statement
4988    previously returned by <function>SPI_prepare</function> or saved by
4989    <function>SPI_keepplan</function> or <function>SPI_saveplan</function>.
4990   </para>
4991  </refsect1>
4992
4993  <refsect1>
4994   <title>Arguments</title>
4995
4996   <variablelist>
4997    <varlistentry>
4998     <term><literal>SPIPlanPtr <parameter>plan</parameter></literal></term>
4999     <listitem>
5000      <para>
5001       pointer to statement to free
5002      </para>
5003     </listitem>
5004    </varlistentry>
5005   </variablelist>
5006  </refsect1>
5007
5008  <refsect1>
5009   <title>Return Value</title>
5010
5011   <para>
5012    0 on success;
5013    <symbol>SPI_ERROR_ARGUMENT</symbol> if <parameter>plan</parameter>
5014    is <symbol>NULL</symbol> or invalid
5015   </para>
5016  </refsect1>
5017 </refentry>
5018
5019  </sect1>
5020
5021  <sect1 id="spi-transaction">
5022   <title>Transaction Management</title>
5023
5024   <para>
5025    It is not possible to run transaction control commands such
5026    as <command>COMMIT</command> and <command>ROLLBACK</command> through SPI
5027    functions such as <function>SPI_execute</function>.  There are, however,
5028    separate interface functions that allow transaction control through SPI.
5029   </para>
5030
5031   <para>
5032    It is not generally safe and sensible to start and end transactions in
5033    arbitrary user-defined SQL-callable functions without taking into account
5034    the context in which they are called.  For example, a transaction boundary
5035    in the middle of a function that is part of a complex SQL expression that
5036    is part of some SQL command will probably result in obscure internal errors
5037    or crashes.  The interface functions presented here are primarily intended
5038    to be used by procedural language implementations to support transaction
5039    management in SQL-level procedures that are invoked by the <command>CALL</command>
5040    command, taking the context of the <command>CALL</command> invocation into
5041    account.  SPI-using procedures implemented in C can implement the same logic, but
5042    the details of that are beyond the scope of this documentation.
5043   </para>
5044
5045 <!-- *********************************************** -->
5046
5047 <refentry id="spi-spi-commit">
5048  <indexterm><primary>SPI_commit</primary></indexterm>
5049  <indexterm><primary>SPI_commit_and_chain</primary></indexterm>
5050
5051  <refmeta>
5052   <refentrytitle>SPI_commit</refentrytitle>
5053   <manvolnum>3</manvolnum>
5054  </refmeta>
5055
5056  <refnamediv>
5057   <refname>SPI_commit</refname>
5058   <refname>SPI_commit_and_chain</refname>
5059   <refpurpose>commit the current transaction</refpurpose>
5060  </refnamediv>
5061
5062  <refsynopsisdiv>
5063 <synopsis>
5064 void SPI_commit(void)
5065 </synopsis>
5066
5067 <synopsis>
5068 void SPI_commit_and_chain(void)
5069 </synopsis>
5070  </refsynopsisdiv>
5071
5072  <refsect1>
5073   <title>Description</title>
5074
5075   <para>
5076    <function>SPI_commit</function> commits the current transaction.  It is
5077    approximately equivalent to running the SQL
5078    command <command>COMMIT</command>.  After the transaction is committed, a
5079    new transaction is automatically started using default transaction
5080    characteristics, so that the caller can continue using SPI facilities.
5081    If there is a failure during commit, the current transaction is instead
5082    rolled back and a new transaction is started, after which the error is
5083    thrown in the usual way.
5084   </para>
5085
5086   <para>
5087    <function>SPI_commit_and_chain</function> is the same, but the new
5088    transaction is started with the same transaction
5089    characteristics as the just finished one, like with the SQL command
5090    <command>COMMIT AND CHAIN</command>.
5091   </para>
5092
5093   <para>
5094    These functions can only be executed if the SPI connection has been set as
5095    nonatomic in the call to <function>SPI_connect_ext</function>.
5096   </para>
5097  </refsect1>
5098 </refentry>
5099
5100 <!-- *********************************************** -->
5101
5102 <refentry id="spi-spi-rollback">
5103  <indexterm><primary>SPI_rollback</primary></indexterm>
5104  <indexterm><primary>SPI_rollback_and_chain</primary></indexterm>
5105
5106  <refmeta>
5107   <refentrytitle>SPI_rollback</refentrytitle>
5108   <manvolnum>3</manvolnum>
5109  </refmeta>
5110
5111  <refnamediv>
5112   <refname>SPI_rollback</refname>
5113   <refname>SPI_rollback_and_chain</refname>
5114   <refpurpose>abort the current transaction</refpurpose>
5115  </refnamediv>
5116
5117  <refsynopsisdiv>
5118 <synopsis>
5119 void SPI_rollback(void)
5120 </synopsis>
5121
5122 <synopsis>
5123 void SPI_rollback_and_chain(void)
5124 </synopsis>
5125  </refsynopsisdiv>
5126
5127  <refsect1>
5128   <title>Description</title>
5129
5130   <para>
5131    <function>SPI_rollback</function> rolls back the current transaction.  It
5132    is approximately equivalent to running the SQL
5133    command <command>ROLLBACK</command>.  After the transaction is rolled back,
5134    a new transaction is automatically started using default transaction
5135    characteristics, so that the caller can continue using SPI facilities.
5136   </para>
5137   <para>
5138    <function>SPI_rollback_and_chain</function> is the same, but the new
5139    transaction is started with the same transaction
5140    characteristics as the just finished one, like with the SQL command
5141    <command>ROLLBACK AND CHAIN</command>.
5142   </para>
5143
5144   <para>
5145    These functions can only be executed if the SPI connection has been set as
5146    nonatomic in the call to <function>SPI_connect_ext</function>.
5147   </para>
5148  </refsect1>
5149 </refentry>
5150
5151 <!-- *********************************************** -->
5152
5153 <refentry id="spi-spi-start-transaction">
5154  <indexterm><primary>SPI_start_transaction</primary></indexterm>
5155
5156  <refmeta>
5157   <refentrytitle>SPI_start_transaction</refentrytitle>
5158   <manvolnum>3</manvolnum>
5159  </refmeta>
5160
5161  <refnamediv>
5162   <refname>SPI_start_transaction</refname>
5163   <refpurpose>obsolete function</refpurpose>
5164  </refnamediv>
5165
5166  <refsynopsisdiv>
5167 <synopsis>
5168 void SPI_start_transaction(void)
5169 </synopsis>
5170  </refsynopsisdiv>
5171
5172  <refsect1>
5173   <title>Description</title>
5174
5175   <para>
5176    <function>SPI_start_transaction</function> does nothing, and exists
5177    only for code compatibility with
5178    earlier <productname>PostgreSQL</productname> releases.  It used to
5179    be required after calling <function>SPI_commit</function>
5180    or <function>SPI_rollback</function>, but now those functions start
5181    a new transaction automatically.
5182   </para>
5183  </refsect1>
5184 </refentry>
5185
5186  </sect1>
5187
5188  <sect1 id="spi-visibility">
5189   <title>Visibility of Data Changes</title>
5190
5191   <para>
5192    The following rules govern the visibility of data changes in
5193    functions that use SPI (or any other C function):
5194
5195    <itemizedlist>
5196     <listitem>
5197      <para>
5198       During the execution of an SQL command, any data changes made by
5199       the command are invisible to the command itself.  For
5200       example, in:
5201 <programlisting>
5202 INSERT INTO a SELECT * FROM a;
5203 </programlisting>
5204       the inserted rows are invisible to the <command>SELECT</command>
5205       part.
5206      </para>
5207     </listitem>
5208
5209     <listitem>
5210      <para>
5211       Changes made by a command C are visible to all commands that are
5212       started after C, no matter whether they are started inside C
5213       (during the execution of C) or after C is done.
5214      </para>
5215     </listitem>
5216
5217     <listitem>
5218      <para>
5219       Commands executed via SPI inside a function called by an SQL command
5220       (either an ordinary function or a trigger) follow one or the
5221       other of the above rules depending on the read/write flag passed
5222       to SPI.  Commands executed in read-only mode follow the first
5223       rule: they cannot see changes of the calling command.  Commands executed
5224       in read-write mode follow the second rule: they can see all changes made
5225       so far.
5226      </para>
5227     </listitem>
5228
5229     <listitem>
5230      <para>
5231       All standard procedural languages set the SPI read-write mode
5232       depending on the volatility attribute of the function.  Commands of
5233       <literal>STABLE</literal> and <literal>IMMUTABLE</literal> functions are done in
5234       read-only mode, while commands of <literal>VOLATILE</literal> functions are
5235       done in read-write mode.  While authors of C functions are able to
5236       violate this convention, it's unlikely to be a good idea to do so.
5237      </para>
5238     </listitem>
5239    </itemizedlist>
5240   </para>
5241
5242   <para>
5243    The next section contains an example that illustrates the
5244    application of these rules.
5245   </para>
5246  </sect1>
5247
5248  <sect1 id="spi-examples">
5249   <title>Examples</title>
5250
5251   <para>
5252    This section contains a very simple example of SPI usage. The
5253    C function <function>execq</function> takes an SQL command as its
5254    first argument and a row count as its second, executes the command
5255    using <function>SPI_exec</function> and returns the number of rows
5256    that were processed by the command.  You can find more complex
5257    examples for SPI in the source tree in
5258    <filename>src/test/regress/regress.c</filename> and in the
5259    <xref linkend="contrib-spi"/> module.
5260   </para>
5261
5262 <programlisting>
5263 #include "postgres.h"
5264
5265 #include "executor/spi.h"
5266 #include "utils/builtins.h"
5267
5268 PG_MODULE_MAGIC;
5269
5270 PG_FUNCTION_INFO_V1(execq);
5271
5272 Datum
5273 execq(PG_FUNCTION_ARGS)
5274 {
5275     char *command;
5276     int cnt;
5277     int ret;
5278     uint64 proc;
5279
5280     /* Convert given text object to a C string */
5281     command = text_to_cstring(PG_GETARG_TEXT_PP(0));
5282     cnt = PG_GETARG_INT32(1);
5283
5284     SPI_connect();
5285
5286     ret = SPI_exec(command, cnt);
5287
5288     proc = SPI_processed;
5289
5290     /*
5291      * If some rows were fetched, print them via elog(INFO).
5292      */
5293     if (ret &gt; 0 &amp;&amp; SPI_tuptable != NULL)
5294     {
5295         SPITupleTable *tuptable = SPI_tuptable;
5296         TupleDesc tupdesc = tuptable-&gt;tupdesc;
5297         char buf[8192];
5298         uint64 j;
5299
5300         for (j = 0; j &lt; tuptable-&gt;numvals; j++)
5301         {
5302             HeapTuple tuple = tuptable-&gt;vals[j];
5303             int i;
5304
5305             for (i = 1, buf[0] = 0; i &lt;= tupdesc-&gt;natts; i++)
5306                 snprintf(buf + strlen(buf), sizeof(buf) - strlen(buf), " %s%s",
5307                         SPI_getvalue(tuple, tupdesc, i),
5308                         (i == tupdesc-&gt;natts) ? " " : " |");
5309             elog(INFO, "EXECQ: %s", buf);
5310         }
5311     }
5312
5313     SPI_finish();
5314     pfree(command);
5315
5316     PG_RETURN_INT64(proc);
5317 }
5318 </programlisting>
5319
5320   <para>
5321    This is how you declare the function after having compiled it into
5322    a shared library (details are in <xref linkend="dfunc"/>.):
5323
5324 <programlisting>
5325 CREATE FUNCTION execq(text, integer) RETURNS int8
5326     AS '<replaceable>filename</replaceable>'
5327     LANGUAGE C STRICT;
5328 </programlisting>
5329   </para>
5330
5331   <para>
5332    Here is a sample session:
5333
5334 <programlisting>
5335 =&gt; SELECT execq('CREATE TABLE a (x integer)', 0);
5336  execq
5337 -------
5338      0
5339 (1 row)
5340
5341 =&gt; INSERT INTO a VALUES (execq('INSERT INTO a VALUES (0)', 0));
5342 INSERT 0 1
5343 =&gt; SELECT execq('SELECT * FROM a', 0);
5344 INFO:  EXECQ:  0    <lineannotation>-- inserted by execq</lineannotation>
5345 INFO:  EXECQ:  1    <lineannotation>-- returned by execq and inserted by upper INSERT</lineannotation>
5346
5347  execq
5348 -------
5349      2
5350 (1 row)
5351
5352 =&gt; SELECT execq('INSERT INTO a SELECT x + 2 FROM a RETURNING *', 1);
5353 INFO:  EXECQ:  2    <lineannotation>-- 0 + 2, then execution was stopped by count</lineannotation>
5354  execq
5355 -------
5356      1
5357 (1 row)
5358
5359 =&gt; SELECT execq('SELECT * FROM a', 10);
5360 INFO:  EXECQ:  0
5361 INFO:  EXECQ:  1
5362 INFO:  EXECQ:  2
5363
5364  execq
5365 -------
5366      3              <lineannotation>-- 10 is the max value only, 3 is the real number of rows</lineannotation>
5367 (1 row)
5368
5369 =&gt; SELECT execq('INSERT INTO a SELECT x + 10 FROM a', 1);
5370  execq
5371 -------
5372      3              <lineannotation>-- all rows processed; count does not stop it, because nothing is returned</lineannotation>
5373 (1 row)
5374
5375 =&gt; SELECT * FROM a;
5376  x
5377 ----
5378   0
5379   1
5380   2
5381  10
5382  11
5383  12
5384 (6 rows)
5385
5386 =&gt; DELETE FROM a;
5387 DELETE 6
5388 =&gt; INSERT INTO a VALUES (execq('SELECT * FROM a', 0) + 1);
5389 INSERT 0 1
5390 =&gt; SELECT * FROM a;
5391  x
5392 ---
5393  1                  <lineannotation>-- 0 (no rows in a) + 1</lineannotation>
5394 (1 row)
5395
5396 =&gt; INSERT INTO a VALUES (execq('SELECT * FROM a', 0) + 1);
5397 INFO:  EXECQ:  1
5398 INSERT 0 1
5399 =&gt; SELECT * FROM a;
5400  x
5401 ---
5402  1
5403  2                  <lineannotation>-- 1 (there was one row in a) + 1</lineannotation>
5404 (2 rows)
5405
5406 <lineannotation>-- This demonstrates the data changes visibility rule.</lineannotation>
5407 <lineannotation>-- execq is called twice and sees different numbers of rows each time:</lineannotation>
5408
5409 =&gt; INSERT INTO a SELECT execq('SELECT * FROM a', 0) * x FROM a;
5410 INFO:  EXECQ:  1    <lineannotation>-- results from first execq</lineannotation>
5411 INFO:  EXECQ:  2
5412 INFO:  EXECQ:  1    <lineannotation>-- results from second execq</lineannotation>
5413 INFO:  EXECQ:  2
5414 INFO:  EXECQ:  2
5415 INSERT 0 2
5416 =&gt; SELECT * FROM a;
5417  x
5418 ---
5419  1
5420  2
5421  2                  <lineannotation>-- 2 rows * 1 (x in first row)</lineannotation>
5422  6                  <lineannotation>-- 3 rows (2 + 1 just inserted) * 2 (x in second row)</lineannotation>
5423 (4 rows)
5424 </programlisting>
5425   </para>
5426  </sect1>
5427 </chapter>