<?xml version="1.0" encoding="UTF-8" standalone="no"?> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>Chapter 47. Server Programming Interface</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="plpython-envar.html" title="46.11. Environment Variables" /><link rel="next" href="spi-interface.html" title="47.1. Interface Functions" /></head><body><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">Chapter 47. Server Programming Interface</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="plpython-envar.html" title="46.11. Environment Variables">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="server-programming.html" title="Part V. Server Programming">Up</a></td><th width="60%" align="center">Part V. Server Programming</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 11.5 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="spi-interface.html" title="47.1. Interface Functions">Next</a></td></tr></table><hr></hr></div><div class="chapter" id="SPI"><div class="titlepage"><div><div><h2 class="title">Chapter 47. Server Programming Interface</h2></div></div></div><div class="toc"><p><strong>Table of Contents</strong></p><dl class="toc"><dt><span class="sect1"><a href="spi-interface.html">47.1. Interface Functions</a></span></dt><dd><dl><dt><span class="refentrytitle"><a href="spi-spi-connect.html">SPI_connect</a></span><span class="refpurpose"> — connect a C function to the SPI manager</span></dt><dt><span class="refentrytitle"><a href="spi-spi-finish.html">SPI_finish</a></span><span class="refpurpose"> — disconnect a C function from the SPI manager</span></dt><dt><span class="refentrytitle"><a href="spi-spi-execute.html">SPI_execute</a></span><span class="refpurpose"> — execute a command</span></dt><dt><span class="refentrytitle"><a href="spi-spi-exec.html">SPI_exec</a></span><span class="refpurpose"> — execute a read/write command</span></dt><dt><span class="refentrytitle"><a href="spi-spi-execute-with-args.html">SPI_execute_with_args</a></span><span class="refpurpose"> — execute a command with out-of-line parameters</span></dt><dt><span class="refentrytitle"><a href="spi-spi-prepare.html">SPI_prepare</a></span><span class="refpurpose"> — prepare a statement, without executing it yet</span></dt><dt><span class="refentrytitle"><a href="spi-spi-prepare-cursor.html">SPI_prepare_cursor</a></span><span class="refpurpose"> — prepare a statement, without executing it yet</span></dt><dt><span class="refentrytitle"><a href="spi-spi-prepare-params.html">SPI_prepare_params</a></span><span class="refpurpose"> — prepare a statement, without executing it yet</span></dt><dt><span class="refentrytitle"><a href="spi-spi-getargcount.html">SPI_getargcount</a></span><span class="refpurpose"> — return the number of arguments needed by a statement prepared by <code class="function">SPI_prepare</code></span></dt><dt><span class="refentrytitle"><a href="spi-spi-getargtypeid.html">SPI_getargtypeid</a></span><span class="refpurpose"> — return the data type OID for an argument of a statement prepared by <code class="function">SPI_prepare</code></span></dt><dt><span class="refentrytitle"><a href="spi-spi-is-cursor-plan.html">SPI_is_cursor_plan</a></span><span class="refpurpose"> — return <code class="symbol">true</code> if a statement prepared by <code class="function">SPI_prepare</code> can be used with <code class="function">SPI_cursor_open</code></span></dt><dt><span class="refentrytitle"><a href="spi-spi-execute-plan.html">SPI_execute_plan</a></span><span class="refpurpose"> — execute a statement prepared by <code class="function">SPI_prepare</code></span></dt><dt><span class="refentrytitle"><a href="spi-spi-execute-plan-with-paramlist.html">SPI_execute_plan_with_paramlist</a></span><span class="refpurpose"> — execute a statement prepared by <code class="function">SPI_prepare</code></span></dt><dt><span class="refentrytitle"><a href="spi-spi-execp.html">SPI_execp</a></span><span class="refpurpose"> — execute a statement in read/write mode</span></dt><dt><span class="refentrytitle"><a href="spi-spi-cursor-open.html">SPI_cursor_open</a></span><span class="refpurpose"> — set up a cursor using a statement created with <code class="function">SPI_prepare</code></span></dt><dt><span class="refentrytitle"><a href="spi-spi-cursor-open-with-args.html">SPI_cursor_open_with_args</a></span><span class="refpurpose"> — set up a cursor using a query and parameters</span></dt><dt><span class="refentrytitle"><a href="spi-spi-cursor-open-with-paramlist.html">SPI_cursor_open_with_paramlist</a></span><span class="refpurpose"> — set up a cursor using parameters</span></dt><dt><span class="refentrytitle"><a href="spi-spi-cursor-find.html">SPI_cursor_find</a></span><span class="refpurpose"> — find an existing cursor by name</span></dt><dt><span class="refentrytitle"><a href="spi-spi-cursor-fetch.html">SPI_cursor_fetch</a></span><span class="refpurpose"> — fetch some rows from a cursor</span></dt><dt><span class="refentrytitle"><a href="spi-spi-cursor-move.html">SPI_cursor_move</a></span><span class="refpurpose"> — move a cursor</span></dt><dt><span class="refentrytitle"><a href="spi-spi-scroll-cursor-fetch.html">SPI_scroll_cursor_fetch</a></span><span class="refpurpose"> — fetch some rows from a cursor</span></dt><dt><span class="refentrytitle"><a href="spi-spi-scroll-cursor-move.html">SPI_scroll_cursor_move</a></span><span class="refpurpose"> — move a cursor</span></dt><dt><span class="refentrytitle"><a href="spi-spi-cursor-close.html">SPI_cursor_close</a></span><span class="refpurpose"> — close a cursor</span></dt><dt><span class="refentrytitle"><a href="spi-spi-keepplan.html">SPI_keepplan</a></span><span class="refpurpose"> — save a prepared statement</span></dt><dt><span class="refentrytitle"><a href="spi-spi-saveplan.html">SPI_saveplan</a></span><span class="refpurpose"> — save a prepared statement</span></dt><dt><span class="refentrytitle"><a href="spi-spi-register-relation.html">SPI_register_relation</a></span><span class="refpurpose"> — make an ephemeral named relation available by name in SPI queries</span></dt><dt><span class="refentrytitle"><a href="spi-spi-unregister-relation.html">SPI_unregister_relation</a></span><span class="refpurpose"> — remove an ephemeral named relation from the registry</span></dt><dt><span class="refentrytitle"><a href="spi-spi-register-trigger-data.html">SPI_register_trigger_data</a></span><span class="refpurpose"> — make ephemeral trigger data available in SPI queries</span></dt></dl></dd><dt><span class="sect1"><a href="spi-interface-support.html">47.2. Interface Support Functions</a></span></dt><dd><dl><dt><span class="refentrytitle"><a href="spi-spi-fname.html">SPI_fname</a></span><span class="refpurpose"> — determine the column name for the specified column number</span></dt><dt><span class="refentrytitle"><a href="spi-spi-fnumber.html">SPI_fnumber</a></span><span class="refpurpose"> — determine the column number for the specified column name</span></dt><dt><span class="refentrytitle"><a href="spi-spi-getvalue.html">SPI_getvalue</a></span><span class="refpurpose"> — return the string value of the specified column</span></dt><dt><span class="refentrytitle"><a href="spi-spi-getbinval.html">SPI_getbinval</a></span><span class="refpurpose"> — return the binary value of the specified column</span></dt><dt><span class="refentrytitle"><a href="spi-spi-gettype.html">SPI_gettype</a></span><span class="refpurpose"> — return the data type name of the specified column</span></dt><dt><span class="refentrytitle"><a href="spi-spi-gettypeid.html">SPI_gettypeid</a></span><span class="refpurpose"> — return the data type <acronym class="acronym">OID</acronym> of the specified column</span></dt><dt><span class="refentrytitle"><a href="spi-spi-getrelname.html">SPI_getrelname</a></span><span class="refpurpose"> — return the name of the specified relation</span></dt><dt><span class="refentrytitle"><a href="spi-spi-getnspname.html">SPI_getnspname</a></span><span class="refpurpose"> — return the namespace of the specified relation</span></dt><dt><span class="refentrytitle"><a href="spi-spi-result-code-string.html">SPI_result_code_string</a></span><span class="refpurpose"> — return error code as string</span></dt></dl></dd><dt><span class="sect1"><a href="spi-memory.html">47.3. Memory Management</a></span></dt><dd><dl><dt><span class="refentrytitle"><a href="spi-spi-palloc.html">SPI_palloc</a></span><span class="refpurpose"> — allocate memory in the upper executor context</span></dt><dt><span class="refentrytitle"><a href="spi-realloc.html">SPI_repalloc</a></span><span class="refpurpose"> — reallocate memory in the upper executor context</span></dt><dt><span class="refentrytitle"><a href="spi-spi-pfree.html">SPI_pfree</a></span><span class="refpurpose"> — free memory in the upper executor context</span></dt><dt><span class="refentrytitle"><a href="spi-spi-copytuple.html">SPI_copytuple</a></span><span class="refpurpose"> — make a copy of a row in the upper executor context</span></dt><dt><span class="refentrytitle"><a href="spi-spi-returntuple.html">SPI_returntuple</a></span><span class="refpurpose"> — prepare to return a tuple as a Datum</span></dt><dt><span class="refentrytitle"><a href="spi-spi-modifytuple.html">SPI_modifytuple</a></span><span class="refpurpose"> — create a row by replacing selected fields of a given row</span></dt><dt><span class="refentrytitle"><a href="spi-spi-freetuple.html">SPI_freetuple</a></span><span class="refpurpose"> — free a row allocated in the upper executor context</span></dt><dt><span class="refentrytitle"><a href="spi-spi-freetupletable.html">SPI_freetuptable</a></span><span class="refpurpose"> — free a row set created by <code class="function">SPI_execute</code> or a similar function</span></dt><dt><span class="refentrytitle"><a href="spi-spi-freeplan.html">SPI_freeplan</a></span><span class="refpurpose"> — free a previously saved prepared statement</span></dt></dl></dd><dt><span class="sect1"><a href="spi-transaction.html">47.4. Transaction Management</a></span></dt><dd><dl><dt><span class="refentrytitle"><a href="spi-spi-commit.html">SPI_commit</a></span><span class="refpurpose"> — commit the current transaction</span></dt><dt><span class="refentrytitle"><a href="spi-spi-rollback.html">SPI_rollback</a></span><span class="refpurpose"> — abort the current transaction</span></dt><dt><span class="refentrytitle"><a href="spi-spi-start-transaction.html">SPI_start_transaction</a></span><span class="refpurpose"> — start a new transaction</span></dt></dl></dd><dt><span class="sect1"><a href="spi-visibility.html">47.5. Visibility of Data Changes</a></span></dt><dt><span class="sect1"><a href="spi-examples.html">47.6. Examples</a></span></dt></dl></div><a id="id-1.8.12.2" class="indexterm"></a><p> The <em class="firstterm">Server Programming Interface</em> (<acronym class="acronym">SPI</acronym>) gives writers of user-defined <acronym class="acronym">C</acronym> functions the ability to run <acronym class="acronym">SQL</acronym> commands inside their functions. <acronym class="acronym">SPI</acronym> is a set of interface functions to simplify access to the parser, planner, and executor. <acronym class="acronym">SPI</acronym> also does some memory management. </p><div class="note"><h3 class="title">Note</h3><p> The available procedural languages provide various means to execute SQL commands from functions. Most of these facilities are based on SPI, so this documentation might be of use for users of those languages as well. </p></div><p> Note that if a command invoked via SPI fails, then control will not be returned to your C function. Rather, the transaction or subtransaction in which your C function executes will be rolled back. (This might seem surprising given that the SPI functions mostly have documented error-return conventions. Those conventions only apply for errors detected within the SPI functions themselves, however.) It is possible to recover control after an error by establishing your own subtransaction surrounding SPI calls that might fail. </p><p> <acronym class="acronym">SPI</acronym> functions return a nonnegative result on success (either via a returned integer value or in the global variable <code class="varname">SPI_result</code>, as described below). On error, a negative result or <code class="symbol">NULL</code> will be returned. </p><p> Source code files that use SPI must include the header file <code class="filename">executor/spi.h</code>. </p></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="plpython-envar.html">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="server-programming.html">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="spi-interface.html">Next</a></td></tr><tr><td width="40%" align="left" valign="top">46.11. Environment Variables </td><td width="20%" align="center"><a accesskey="h" href="index.html">Home</a></td><td width="40%" align="right" valign="top"> 47.1. Interface Functions</td></tr></table></div></body></html>
