-<Chapter id="spi">
-<DocInfo>
-<AuthorGroup>
-<Author>
-<FirstName>Vadim</FirstName>
-<Surname>Mikheev</Surname>
-</Author>
-</AuthorGroup>
-<Date>Transcribed 1998-01-16</Date>
-</DocInfo>
-
-<Title>Server Programming Interface</Title>
-
-<Para>
-The <FirstTerm>Server Programming Interface</FirstTerm>
-(<Acronym>SPI</Acronym>) gives users the
-ability to run <Acronym>SQL</Acronym> queries inside user-defined
-<Acronym>C</Acronym> functions.
-The available Procedural Languages (<Acronym>PL</Acronym>) give an alternate
-means to access these capabilities.
-</Para>
-
-<Para>
-In fact, <Acronym>SPI</Acronym> is just a set of native interface functions
-to simplify access to the Parser, Planner, Optimizer and Executor.
-<Acronym>SPI</Acronym> also does some memory management.
-</Para>
-
-<Para>
-To avoid misunderstanding we'll use <FirstTerm>function</FirstTerm>
-to mean <Acronym>SPI</Acronym> interface functions and
-<FirstTerm>procedure</FirstTerm> for user-defined C-functions
-using <Acronym>SPI</Acronym>.
-</Para>
-
-<Para>
-Procedures which use <Acronym>SPI</Acronym> are called by the
-Executor. The <Acronym>SPI</Acronym> calls recursively invoke the
-Executor in turn to run queries. When the Executor is invoked
-recursively, it may itself call procedures which may make
-<Acronym>SPI</Acronym> calls.
-</Para>
-
-<Para>
-Note, that if during execution of a query from a procedure the transaction
-is aborted then control will not be returned to your procedure. Rather, all work
-will be rolled back and the server will wait for the next command from the
-client. This will be changed in future versions.
-</Para>
-
-<Para>
-Other restrictions are the inability to execute BEGIN, END and ABORT
-(transaction control statements) and cursor operations. This will also be
-changed in the future.
-</Para>
-
-<Para>
-If successful, <Acronym>SPI</Acronym> functions return a non-negative result (either via
-a returned integer value or in SPI_result global variable, as described below).
-On error, a negative or NULL result will be returned.
-</Para>
-
-<Sect1 id="spi-interface">
-<Title>Interface Functions</Title>
-
-<REFENTRY ID="SPI-SPICONNECT">
-<REFMETA>
-<REFENTRYTITLE>SPI_connect</REFENTRYTITLE>
-<REFMISCINFO>SPI - Connection Management</REFMISCINFO>
-</REFMETA>
-<REFNAMEDIV>
-<REFNAME>SPI_connect
-</REFNAME>
-<REFPURPOSE>
- Connects your procedure to the SPI manager.
-</REFPURPOSE>
-<INDEXTERM ID="IX-SPI-SPICONNECT-1"><PRIMARY>SPI</PRIMARY><SECONDARY>connecting</SECONDARY></INDEXTERM>
-<INDEXTERM ID="IX-SPI-SPICONNECT-2"><PRIMARY>SPI_connect</PRIMARY></INDEXTERM>
-</REFNAMEDIV>
-<REFSYNOPSISDIV>
-<REFSYNOPSISDIVINFO>
-<DATE>1997-12-24</DATE>
-</REFSYNOPSISDIVINFO>
-<SYNOPSIS>
+<!-- $PostgreSQL: pgsql/doc/src/sgml/spi.sgml,v 1.56 2007/04/16 01:14:55 tgl Exp $ -->
+
+<chapter id="spi">
+ <title>Server Programming Interface</title>
+
+ <indexterm zone="spi">
+ <primary>SPI</primary>
+ </indexterm>
+
+ <para>
+ The <firstterm>Server Programming Interface</firstterm>
+ (<acronym>SPI</acronym>) gives writers of user-defined
+ <acronym>C</acronym> functions the ability to run
+ <acronym>SQL</acronym> commands inside their functions.
+ <acronym>SPI</acronym> is a set of
+ interface functions to simplify access to the parser, planner,
+ and executor. <acronym>SPI</acronym> also does some
+ memory management.
+ </para>
+
+ <note>
+ <para>
+ The available procedural languages provide various means to
+ execute SQL commands from procedures. Most of these facilities are
+ based on SPI, so this documentation might be of use for users
+ of those languages as well.
+ </para>
+ </note>
+
+ <para>
+ To avoid misunderstanding we'll use the term <quote>function</quote>
+ when we speak of <acronym>SPI</acronym> interface functions and
+ <quote>procedure</quote> for a user-defined C-function that is
+ using <acronym>SPI</acronym>.
+ </para>
+
+ <para>
+ Note that if a command invoked via SPI fails, then control will not be
+ returned to your procedure. Rather, the
+ transaction or subtransaction in which your procedure 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. This is not currently
+ documented because the mechanisms required are still in flux.
+ </para>
+
+ <para>
+ <acronym>SPI</acronym> functions return a nonnegative result on
+ success (either via a returned integer value or in the global
+ variable <varname>SPI_result</varname>, as described below). On
+ error, a negative result or <symbol>NULL</symbol> will be returned.
+ </para>
+
+ <para>
+ Source code files that use SPI must include the header file
+ <filename>executor/spi.h</filename>.
+ </para>
+
+
+<sect1 id="spi-interface">
+ <title>Interface Functions</title>
+
+ <refentry id="spi-spi-connect">
+ <refmeta>
+ <refentrytitle>SPI_connect</refentrytitle>
+ </refmeta>
+
+ <refnamediv>
+ <refname>SPI_connect</refname>
+ <refpurpose>connect a procedure to the SPI manager</refpurpose>
+ </refnamediv>
+
+ <indexterm><primary>SPI_connect</primary></indexterm>
+
+ <refsynopsisdiv>
+<synopsis>
int SPI_connect(void)
-</SYNOPSIS>
-
-<REFSECT2 ID="R2-SPI-SPICONNECT-1">
-<REFSECT2INFO>
-<DATE>1997-12-24</DATE>
-</REFSECT2INFO>
-<TITLE>Inputs
-</TITLE>
-<PARA>None
-</PARA>
-</REFSECT2>
-
-<REFSECT2 ID="R2-SPI-SPICONNECT-2">
-<REFSECT2INFO>
-<DATE>1997-12-24</DATE>
-</REFSECT2INFO>
-<TITLE>Outputs
-</TITLE>
-<VARIABLELIST>
-<VARLISTENTRY>
-<TERM>int
-</TERM>
-<LISTITEM>
-<PARA>
-Return status
-<VARIABLELIST>
-<VARLISTENTRY>
-<TERM><ReturnValue>SPI_OK_CONNECT</ReturnValue>
-</TERM>
-<LISTITEM>
-<PARA>
- if connected
-</PARA>
-</LISTITEM>
-</VARLISTENTRY>
-<VARLISTENTRY>
-<TERM><ReturnValue>SPI_ERROR_CONNECT</ReturnValue>
-</TERM>
-<LISTITEM>
-<PARA>
- if not connected
-</PARA>
-</LISTITEM>
-</VARLISTENTRY>
-</VARIABLELIST>
-</para>
-</LISTITEM>
-</VARLISTENTRY>
-</VARIABLELIST>
-</REFSECT2>
-</REFSYNOPSISDIV>
-
-<REFSECT1 ID="R1-SPI-SPICONNECT-1">
-<REFSECT1INFO>
-<DATE>1997-12-24</DATE>
-</REFSECT1INFO>
-<TITLE>Description
-</TITLE>
-<PARA>
-<FUNCTION>SPI_connect</FUNCTION> opens a connection to the <ProductName>Postgres</ProductName> backend.
- You should call this function if you will need to execute queries. Some
- utility SPI functions may be called from un-connected procedures.
-</PARA>
-<PARA>
+</synopsis>
+ </refsynopsisdiv>
+
+ <refsect1>
+ <title>Description</title>
+
+ <para>
+ <function>SPI_connect</function> opens a connection from a
+ procedure invocation to the SPI manager. You must call this
+ function if you want to execute commands through SPI. Some utility
+ SPI functions can be called from unconnected procedures.
+ </para>
+
+ <para>
If your procedure is already connected,
- <Function>SPI_connect</Function> will return an
- <ReturnValue>SPI_ERROR_CONNECT</ReturnValue> error. Note that this
- may happen if a procedure which has called
- <Function>SPI_connect</Function> directly calls another procedure
- which itself calls <Function>SPI_connect</Function>. While
- recursive calls to the <Acronym>SPI</Acronym> manager are permitted
- when an <Acronym>SPI</Acronym> query invokes another function which
- uses <Acronym>SPI</Acronym>, directly nested calls to
- <Function>SPI_connect</Function> and
- <Function>SPI_finish</Function> are forbidden.
-</PARA>
-</REFSECT1>
-<REFSECT1 ID="R1-SPI-SPICONNECT-2">
-<TITLE>Usage
-</TITLE>
-<PARA>
-<!--
-XXX thomas 1997-12-24
--->
-</PARA>
-</REFSECT1>
-<REFSECT1 ID="R1-SPI-SPICONNECT-3">
-<TITLE>Algorithm
-</TITLE>
-<PARA><FUNCTION>SPI_connect</FUNCTION> performs the following:
-</PARA>
-<VARIABLELIST>
-<VARLISTENTRY>
-<TERM>•
-</TERM>
-<LISTITEM>
-<PARA>
- Initializes the SPI internal
- structures for query execution and memory management.
-</PARA>
-</LISTITEM>
-</VARLISTENTRY>
-</VARIABLELIST>
-<PARA>
-</PARA>
-</REFSECT1>
-<!--
-<REFSECT1 ID="R1-SPI-SPICONNECT-4">
-<TITLE>Structures
-</TITLE>
-<PARA>None
-</PARA>
-</REFSECT1>
--->
-</REFENTRY>
+ <function>SPI_connect</function> will return the error code
+ <returnvalue>SPI_ERROR_CONNECT</returnvalue>. This could happen if
+ a procedure that has called <function>SPI_connect</function>
+ directly calls another procedure that calls
+ <function>SPI_connect</function>. While recursive calls to the
+ <acronym>SPI</acronym> manager are permitted when an SQL command
+ called through SPI invokes another function that uses
+ <acronym>SPI</acronym>, directly nested calls to
+ <function>SPI_connect</function> and
+ <function>SPI_finish</function> are forbidden.
+ (But see <function>SPI_push</function> and <function>SPI_pop</function>.)
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Return Value</title>
+
+ <variablelist>
+ <varlistentry>
+ <term><symbol>SPI_OK_CONNECT</symbol></term>
+ <listitem>
+ <para>
+ on success
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><symbol>SPI_ERROR_CONNECT</symbol></term>
+ <listitem>
+ <para>
+ on error
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </refsect1>
+</refentry>
-<!-- *********************************************** -->
-<!-- *********************************************** -->
<!-- *********************************************** -->
-<REFENTRY ID="SPI-SPIFINISH">
-<REFMETA>
-<REFENTRYTITLE>SPI_finish</REFENTRYTITLE>
-<REFMISCINFO>SPI - Connection Management</REFMISCINFO>
-</REFMETA>
-<REFNAMEDIV>
-<REFNAME>SPI_finish
-</REFNAME>
-<REFPURPOSE>
- Disconnects your procedure from the SPI manager.
-</REFPURPOSE>
-<INDEXTERM ID="IX-SPI-SPIFINISH-1"><PRIMARY>SPI</PRIMARY><SECONDARY>disconnecting</SECONDARY></INDEXTERM>
-<INDEXTERM ID="IX-SPI-SPIFINISH-2"><PRIMARY>SPI_finish</PRIMARY></INDEXTERM>
-</REFNAMEDIV>
-<REFSYNOPSISDIV>
-<REFSYNOPSISDIVINFO>
-<DATE>1997-12-24</DATE>
-</REFSYNOPSISDIVINFO>
-<SYNOPSIS>
-SPI_finish(void)
-</SYNOPSIS>
-
-<REFSECT2 ID="R2-SPI-SPIFINISH-1">
-<REFSECT2INFO>
-<DATE>1997-12-24</DATE>
-</REFSECT2INFO>
-<TITLE>Inputs
-</TITLE>
-<PARA>None
-</PARA>
-</REFSECT2>
-
-<REFSECT2 ID="R2-SPI-SPIFINISH-2">
-<REFSECT2INFO>
-<DATE>1997-12-24</DATE>
-</REFSECT2INFO>
-<TITLE>Outputs
-</TITLE>
-<VARIABLELIST>
-<VARLISTENTRY>
-<TERM>int
-</TERM>
-<LISTITEM>
-<PARA>
-<SimpleList>
-<Member>
-<ReturnValue>SPI_OK_FINISH</ReturnValue>
- if properly disconnected
-</Member>
-<Member>
-<ReturnValue>SPI_ERROR_UNCONNECTED</ReturnValue>
- if called from an un-connected procedure
-</Member>
-</SimpleList>
-</PARA>
-</LISTITEM>
-</VARLISTENTRY>
-</VARIABLELIST>
-</REFSECT2>
-</REFSYNOPSISDIV>
-
-<REFSECT1 ID="R1-SPI-SPIFINISH-1">
-<REFSECT1INFO>
-<DATE>1997-12-24</DATE>
-</REFSECT1INFO>
-<TITLE>Description
-</TITLE>
-<PARA>
-<FUNCTION>SPI_finish</FUNCTION> closes an existing connection to the <ProductName>Postgres</ProductName> backend.
- You should call this function after completing operations through the SPI manager.
-</para>
-<PARA>
- You may get the error return <ReturnValue>SPI_ERROR_UNCONNECTED</ReturnValue> if <Function>SPI_finish</Function> is
- called without having a current valid connection.
- There is no fundamental problem
- with this; it means that nothing was done by the SPI manager.
-</PARA>
-</REFSECT1>
-<REFSECT1 ID="R1-SPI-SPIFINISH-2">
-<TITLE>Usage
-</TITLE>
-<PARA>
- <Function>SPI_finish</Function> <Emphasis>must</Emphasis> be called as a final step by a connected procedure
- or you may get
- unpredictable results! Note that you can safely skip the call to <Function>SPI_finish</Function>
- if you abort the transaction (via elog(ERROR)).
-
-</PARA>
-</REFSECT1>
-<REFSECT1 ID="R1-SPI-SPIFINISH-3">
-<TITLE>Algorithm
-</TITLE>
-<PARA><FUNCTION>SPI_finish</FUNCTION> performs the following:
-</PARA>
-<VARIABLELIST>
-<VARLISTENTRY>
-<TERM>•
-</TERM>
-<LISTITEM>
-<PARA>
- Disconnects your procedure from the SPI manager and frees all memory
- allocations made by your procedure via <Function>palloc</Function> since
- the <Function>SPI_connect</Function>.
- These allocations can't be used any more! See Memory management.
-</PARA>
-</LISTITEM>
-</VARLISTENTRY>
-</VARIABLELIST>
-<PARA>
-</PARA>
-</REFSECT1>
-<!--
-<REFSECT1 ID="R1-SPI-SPIFINISH-4">
-<TITLE>Structures
-</TITLE>
-<PARA>None
-</PARA>
-</REFSECT1>
--->
-</REFENTRY>
+<refentry id="spi-spi-finish">
+ <refmeta>
+ <refentrytitle>SPI_finish</refentrytitle>
+ </refmeta>
+
+ <refnamediv>
+ <refname>SPI_finish</refname>
+ <refpurpose>disconnect a procedure from the SPI manager</refpurpose>
+ </refnamediv>
+
+ <indexterm><primary>SPI_finish</primary></indexterm>
+
+ <refsynopsisdiv>
+<synopsis>
+int SPI_finish(void)
+</synopsis>
+ </refsynopsisdiv>
+
+ <refsect1>
+ <title>Description</title>
+
+ <para>
+ <function>SPI_finish</function> closes an existing connection to
+ the SPI manager. You must call this function after completing the
+ SPI operations needed during your procedure's current invocation.
+ You do not need to worry about making this happen, however, if you
+ abort the transaction via <literal>elog(ERROR)</literal>. In that
+ case SPI will clean itself up automatically.
+ </para>
+
+ <para>
+ If <function>SPI_finish</function> is called without having a valid
+ connection, it will return <symbol>SPI_ERROR_UNCONNECTED</symbol>.
+ There is no fundamental problem with this; it means that the SPI
+ manager has nothing to do.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Return Value</title>
+
+ <variablelist>
+ <varlistentry>
+ <term><symbol>SPI_OK_FINISH</symbol></term>
+ <listitem>
+ <para>
+ if properly disconnected
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><symbol>SPI_ERROR_UNCONNECTED</symbol></term>
+ <listitem>
+ <para>
+ if called from an unconnected procedure
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </refsect1>
+</refentry>
-<!-- *********************************************** -->
-<!-- *********************************************** -->
<!-- *********************************************** -->
-<REFENTRY ID="SPI-SPIEXEC">
-<REFMETA>
-<REFENTRYTITLE>SPI_exec</REFENTRYTITLE>
-<REFMISCINFO>SPI - Connection Management</REFMISCINFO>
-</REFMETA>
-<REFNAMEDIV>
-<REFNAME>SPI_exec
-</REFNAME>
-<REFPURPOSE>
- Creates an execution plan (parser+planner+optimizer) and executes a query.
-</REFPURPOSE>
-<INDEXTERM ID="IX-SPI-SPIEXEC-1"><PRIMARY>SPI</PRIMARY><SECONDARY>executing</SECONDARY></INDEXTERM>
-<INDEXTERM ID="IX-SPI-SPIEXEC-2"><PRIMARY>SPI_exec</PRIMARY></INDEXTERM>
-</REFNAMEDIV>
-<REFSYNOPSISDIV>
-<REFSYNOPSISDIVINFO>
-<DATE>1997-12-24</DATE>
-</REFSYNOPSISDIVINFO>
-<SYNOPSIS>
-SPI_exec(<REPLACEABLE CLASS="PARAMETER">query</REPLACEABLE>, <REPLACEABLE CLASS="PARAMETER">tcount</REPLACEABLE>)
-</SYNOPSIS>
-
-<REFSECT2 ID="R2-SPI-SPIEXEC-1">
-<REFSECT2INFO>
-<DATE>1997-12-24</DATE>
-</REFSECT2INFO>
-<TITLE>Inputs
-</TITLE>
-<VARIABLELIST>
-<VARLISTENTRY>
-<TERM>
-char *<REPLACEABLE CLASS="PARAMETER">query</REPLACEABLE>
-</TERM>
-<LISTITEM>
-<PARA>
-String containing query plan
-</PARA>
-</LISTITEM>
-</VARLISTENTRY>
-<VARLISTENTRY>
-<TERM>
-int <REPLACEABLE CLASS="PARAMETER">tcount</REPLACEABLE>
-</TERM>
-<LISTITEM>
-<PARA>
-Maximum number of tuples to return
-</PARA>
-</LISTITEM>
-</VARLISTENTRY>
-</VARIABLELIST>
-</REFSECT2>
-
-<REFSECT2 ID="R2-SPI-SPIEXEC-2">
-<REFSECT2INFO>
-<DATE>1997-12-24</DATE>
-</REFSECT2INFO>
-<TITLE>Outputs
-</TITLE>
-<VARIABLELIST>
-<VARLISTENTRY>
-<TERM>int
-</TERM>
-<LISTITEM>
-<PARA>
-<SimpleList>
-<Member>
- <ReturnValue>SPI_OK_EXEC</ReturnValue> if properly disconnected
-</Member>
-<Member>
- <ReturnValue>SPI_ERROR_UNCONNECTED</ReturnValue> if called from an un-connected procedure
-</Member>
-<Member>
- <ReturnValue>SPI_ERROR_ARGUMENT</ReturnValue> if query is NULL or <REPLACEABLE CLASS="PARAMETER">tcount</REPLACEABLE> < 0.
-</Member>
-<Member>
- <ReturnValue>SPI_ERROR_UNCONNECTED</ReturnValue> if procedure is unconnected.
-</Member>
-<Member>
- <ReturnValue>SPI_ERROR_COPY</ReturnValue> if COPY TO/FROM stdin.
-</Member>
-<Member>
- <ReturnValue>SPI_ERROR_CURSOR</ReturnValue> if DECLARE/CLOSE CURSOR, FETCH.
-</Member>
-<Member>
- <ReturnValue>SPI_ERROR_TRANSACTION</ReturnValue> if BEGIN/ABORT/END.
-</Member>
-<Member>
- <ReturnValue>SPI_ERROR_OPUNKNOWN</ReturnValue> if type of query is unknown (this shouldn't occur).
-</Member>
-</SimpleList>
-</para>
-<Para>
- If execution of your query was successful then one of the following
- (non-negative) values will be returned:
-<SimpleList>
-<Member>
- <ReturnValue>SPI_OK_UTILITY</ReturnValue> if some utility (e.g. CREATE TABLE ...) was executed
-</Member>
-<Member>
- <ReturnValue>SPI_OK_SELECT</ReturnValue> if SELECT (but not SELECT ... INTO!) was executed
-</Member>
-<Member>
- <ReturnValue>SPI_OK_SELINTO</ReturnValue> if SELECT ... INTO was executed
-</Member>
-<Member>
- <ReturnValue>SPI_OK_INSERT</ReturnValue> if INSERT (or INSERT ... SELECT) was executed
-</Member>
-<Member>
- <ReturnValue>SPI_OK_DELETE</ReturnValue> if DELETE was executed
-</Member>
-<Member>
- <ReturnValue>SPI_OK_UPDATE</ReturnValue> if UPDATE was executed
-</Member>
-</SimpleList>
-</PARA>
-</LISTITEM>
-</VARLISTENTRY>
-</VARIABLELIST>
-</REFSECT2>
-</REFSYNOPSISDIV>
-
-<REFSECT1 ID="R1-SPI-SPIEXEC-1">
-<REFSECT1INFO>
-<DATE>1997-12-24</DATE>
-</REFSECT1INFO>
-<TITLE>Description
-</TITLE>
-<PARA>
-<FUNCTION>SPI_exec</FUNCTION> creates an execution plan (parser+planner+optimizer)
- and executes the query for <REPLACEABLE CLASS="PARAMETER">tcount</REPLACEABLE> tuples.
-
-</PARA>
-</REFSECT1>
-<REFSECT1 ID="R1-SPI-SPIEXEC-2">
-<TITLE>Usage
-</TITLE>
-<PARA>
- This should only be called from a connected procedure.
- If <REPLACEABLE CLASS="PARAMETER">tcount</REPLACEABLE> is zero then it executes the query for all tuples returned by the
- query scan. Using <REPLACEABLE CLASS="PARAMETER">tcount</REPLACEABLE> > 0 you may restrict the number of tuples for
- which the query will be executed. For example,
-
-<ProgramListing>
-SPI_exec ("insert into table select * from table", 5);
-</ProgramListing>
-
-will allow at most 5 tuples to be inserted into table.
-
- If execution of your query was successful then a non-negative value will be returned.
-
-<Note>
-<Para>
-You may pass many queries in one string or query string may be
- re-written by RULEs. <Function>SPI_exec</Function> returns the result for the last query
- executed.
-</Para>
-</Note>
-</para>
-<Para>
- The actual number of tuples for which the (last) query was executed is
- returned in the global variable SPI_processed (if not <ReturnValue>SPI_OK_UTILITY</ReturnValue>).
-
- If <ReturnValue>SPI_OK_SELECT</ReturnValue> returned and SPI_processed > 0 then you may use global
- pointer SPITupleTable *SPI_tuptable to access the selected tuples:
-
- Also NOTE, that <Function>SPI_finish</Function> frees and makes all SPITupleTables
- unusable! (See Memory management).
-</Para>
-
-<Para>
- <Function>SPI_exec</Function> may return one of the following (negative) values:
-<SimpleList>
-<Member>
- <ReturnValue>SPI_ERROR_ARGUMENT</ReturnValue> if query is NULL or <REPLACEABLE CLASS="PARAMETER">tcount</REPLACEABLE> < 0.
-</Member>
-<Member>
- <ReturnValue>SPI_ERROR_UNCONNECTED</ReturnValue> if procedure is unconnected.
-</Member>
-<Member>
- <ReturnValue>SPI_ERROR_COPY</ReturnValue> if COPY TO/FROM stdin.
-</Member>
-<Member>
- <ReturnValue>SPI_ERROR_CURSOR</ReturnValue> if DECLARE/CLOSE CURSOR, FETCH.
-</Member>
-<Member>
- <ReturnValue>SPI_ERROR_TRANSACTION</ReturnValue> if BEGIN/ABORT/END.
-</Member>
-<Member>
- <ReturnValue>SPI_ERROR_OPUNKNOWN</ReturnValue> if type of query is unknown (this shouldn't occur).
-</Member>
-</SimpleList>
-
-</PARA>
-</REFSECT1>
-<REFSECT1 ID="R1-SPI-SPIEXEC-3">
-<TITLE>Algorithm
-</TITLE>
-<PARA><FUNCTION>SPI_exec</FUNCTION> performs the following:
-</PARA>
-<VARIABLELIST>
-<VARLISTENTRY>
-<TERM>•
-</TERM>
-<LISTITEM>
-<PARA>
- Disconnects your procedure from the SPI manager and frees all memory
- allocations made by your procedure via <Function>palloc</Function> since the <Function>SPI_connect</Function>.
- These allocations can't be used any more! See Memory management.
-</PARA>
-</LISTITEM>
-</VARLISTENTRY>
-</VARIABLELIST>
-<PARA>
-</PARA>
-</REFSECT1>
-<!--
-<REFSECT1 ID="R1-SPI-SPIEXEC-4">
-<TITLE>Structures
-</TITLE>
-<PARA>
- If <ReturnValue>SPI_OK_SELECT</ReturnValue> returned and SPI_processed > 0 then you may use the global
- pointer SPITupleTable *SPI_tuptable to access the selected tuples.
-
-<Para>
- Structure SPITupleTable is defined in spi.h:
-<ProgramListing>
- typedef struct
- {
- uint32 alloced; /* # of alloced vals */
- uint32 free; /* # of free vals */
- TupleDesc tupdesc; /* tuple descriptor */
- HeapTuple *vals; /* tuples */
- } SPITupleTable;
-</ProgramListing>
-
-<Para>
- HeapTuple *vals is an array of pointers to tuples. TupleDesc tupdesc is
- a tuple descriptor which you may pass to SPI functions dealing with
- tuples.
-
-<Para>
- NOTE! Functions <Function>SPI_exec</Function>, <Function>SPI_execp</Function> and <Function>SPI_prepare</Function> change both
- SPI_processed and SPI_tuptable (just the pointer, not the contents of the
- structure)! So, save them in local procedure variables if you need them.
-
-<Para>
- Also NOTE, that <Function>SPI_finish</Function> frees and makes all SPITupleTables
- unusable! (See Memory management).
-</PARA>
-</REFSECT1>
--->
-</REFENTRY>
+<refentry id="spi-spi-push">
+ <refmeta>
+ <refentrytitle>SPI_push</refentrytitle>
+ </refmeta>
+
+ <refnamediv>
+ <refname>SPI_push</refname>
+ <refpurpose>push SPI stack to allow recursive SPI usage</refpurpose>
+ </refnamediv>
+
+ <indexterm><primary>SPI_push</primary></indexterm>
+
+ <refsynopsisdiv>
+<synopsis>
+void SPI_push(void)
+</synopsis>
+ </refsynopsisdiv>
+
+ <refsect1>
+ <title>Description</title>
+
+ <para>
+ <function>SPI_push</function> should be called before executing another
+ procedure that might itself wish to use SPI.
+ After <function>SPI_push</function>, SPI is no longer in a
+ <quote>connected</> state, and SPI function calls will be rejected unless
+ a fresh <function>SPI_connect</function> is done. This ensures a clean
+ separation between your procedure's SPI state and that of another procedure
+ you call. After the other procedure returns, call
+ <function>SPI_pop</function> to restore access to your own SPI state.
+ </para>
+
+ <para>
+ Note that <function>SPI_execute</function> and related functions
+ automatically do the equivalent of <function>SPI_push</function> before
+ passing control back to the SQL execution engine, so it is not necessary
+ for you to worry about this when using those functions.
+ Only when you are directly calling arbitrary code that might contain
+ <function>SPI_connect</function> calls do you need to issue
+ <function>SPI_push</function> and <function>SPI_pop</function>.
+ </para>
+ </refsect1>
+
+</refentry>
-<!-- *********************************************** -->
-<!-- *********************************************** -->
<!-- *********************************************** -->
-<REFENTRY ID="SPI-SPIPREPARE">
-<REFMETA>
-<REFENTRYTITLE>SPI_prepare</REFENTRYTITLE>
-<REFMISCINFO>SPI - Plan Preparation</REFMISCINFO>
-</REFMETA>
-<REFNAMEDIV>
-<REFNAME>SPI_prepare
-</REFNAME>
-<REFPURPOSE>
- Connects your procedure to the SPI manager.
-</REFPURPOSE>
-<INDEXTERM ID="IX-SPI-SPIPREPARE-1"><PRIMARY>SPI</PRIMARY><SECONDARY>connecting</SECONDARY></INDEXTERM>
-<INDEXTERM ID="IX-SPI-SPIPREPARE-2"><PRIMARY>SPI_prepare</PRIMARY></INDEXTERM>
-</REFNAMEDIV>
-<REFSYNOPSISDIV>
-<REFSYNOPSISDIVINFO>
-<DATE>1997-12-24</DATE>
-</REFSYNOPSISDIVINFO>
-<SYNOPSIS>
-SPI_prepare(<REPLACEABLE CLASS="PARAMETER">query</REPLACEABLE>, <REPLACEABLE CLASS="PARAMETER">nargs</REPLACEABLE>, <REPLACEABLE CLASS="PARAMETER">argtypes</REPLACEABLE>)
-</SYNOPSIS>
-
-<REFSECT2 ID="R2-SPI-SPIPREPARE-1">
-<REFSECT2INFO>
-<DATE>1997-12-24</DATE>
-</REFSECT2INFO>
-<TITLE>Inputs
-</TITLE>
-<VARIABLELIST>
-<VARLISTENTRY>
-<TERM>
-<REPLACEABLE CLASS="PARAMETER">query</REPLACEABLE>
-</TERM>
-<LISTITEM>
-<PARA>
-Query string
-</PARA>
-</LISTITEM>
-</VARLISTENTRY>
-<VARLISTENTRY>
-<TERM>
-<REPLACEABLE CLASS="PARAMETER">nargs</REPLACEABLE>
-</TERM>
-<LISTITEM>
-<PARA>
-Number of input parameters ($1 ... $nargs - as in SQL-functions)
-</PARA>
-</LISTITEM>
-</VARLISTENTRY>
-<VARLISTENTRY>
-<TERM>
-<REPLACEABLE CLASS="PARAMETER">argtypes</REPLACEABLE>
-</TERM>
-<LISTITEM>
-<PARA>
-Pointer list of type <Acronym>OID</Acronym>s to input arguments
-</PARA>
-</LISTITEM>
-</VARLISTENTRY>
-</VARIABLELIST>
-</REFSECT2>
-
-<REFSECT2 ID="R2-SPI-SPIPREPARE-2">
-<REFSECT2INFO>
-<DATE>1997-12-24</DATE>
-</REFSECT2INFO>
-<TITLE>Outputs
-</TITLE>
-<VARIABLELIST>
-<VARLISTENTRY>
-<TERM>void *
-</TERM>
-<LISTITEM>
-<PARA>
-Pointer to an execution plan (parser+planner+optimizer)
-</PARA>
-</LISTITEM>
-</VARLISTENTRY>
-</VARIABLELIST>
-</REFSECT2>
-</REFSYNOPSISDIV>
-
-<REFSECT1 ID="R1-SPI-SPIPREPARE-1">
-<REFSECT1INFO>
-<DATE>1997-12-24</DATE>
-</REFSECT1INFO>
-<TITLE>Description
-</TITLE>
-<PARA>
-<FUNCTION>SPI_prepare</FUNCTION>
- creates and returns an execution plan (parser+planner+optimizer) but doesn't
- execute the query. Should only be called from a connected procedure.
-
-</PARA>
-</REFSECT1>
-<REFSECT1 ID="R1-SPI-SPIPREPARE-2">
-<TITLE>Usage
-</TITLE>
-<PARA>
- nargs is number of parameters ($1 ... $nargs - as in SQL-functions),
- and nargs may be 0 only if there is not any $1 in query.
-</para>
-<Para>
- Execution of prepared execution plans is sometimes much faster so this
- feature may be useful if the same query will be executed many times.
-</para>
-<Para>
-The plan returned by <Function>SPI_prepare</Function> may be used only in current
- invocation of the procedure since <Function>SPI_finish</Function> frees memory allocated for a plan.
- See <Function>SPI_saveplan</Function>.
-</para>
-<Para>
- If successful, a non-null pointer will be returned. Otherwise, you'll get
- a NULL plan. In both cases SPI_result will be set like the value returned
- by SPI_exec, except that it is set to
- <ReturnValue>SPI_ERROR_ARGUMENT</ReturnValue> if query is NULL or nargs < 0 or nargs > 0 && argtypes
- is NULL.
-
-</PARA>
-</REFSECT1>
-<!--
-<REFSECT1 ID="R1-SPI-SPIPREPARE-3">
-<TITLE>Algorithm
-</TITLE>
-<PARA><FUNCTION>SPI_prepare</FUNCTION> performs the following:
-</PARA>
-<VARIABLELIST>
-<VARLISTENTRY>
-<TERM>•
-</TERM>
-<LISTITEM>
-<PARA>
-TBD
-</PARA>
-</LISTITEM>
-</VARLISTENTRY>
-</VARIABLELIST>
-<PARA>
-</PARA>
-</REFSECT1>
--->
-<!--
-<REFSECT1 ID="R1-SPI-SPIPREPARE-4">
-<TITLE>Structures
-</TITLE>
-<PARA>None
-</PARA>
-</REFSECT1>
--->
-</REFENTRY>
+<refentry id="spi-spi-pop">
+ <refmeta>
+ <refentrytitle>SPI_pop</refentrytitle>
+ </refmeta>
-<!-- *********************************************** -->
-<!-- *********************************************** -->
-<!-- *********************************************** -->
+ <refnamediv>
+ <refname>SPI_pop</refname>
+ <refpurpose>pop SPI stack to return from recursive SPI usage</refpurpose>
+ </refnamediv>
-<REFENTRY ID="SPI-SPISAVEPLAN">
-<REFMETA>
-<REFENTRYTITLE>SPI_saveplan</REFENTRYTITLE>
-<REFMISCINFO>SPI - Plan Storage</REFMISCINFO>
-</REFMETA>
-<REFNAMEDIV>
-<REFNAME>SPI_saveplan
-</REFNAME>
-<REFPURPOSE>
- Saves a passed plan
-</REFPURPOSE>
-<INDEXTERM ID="IX-SPI-SPISAVEPLAN-1"><PRIMARY>SPI</PRIMARY><SECONDARY>connecting</SECONDARY></INDEXTERM>
-<INDEXTERM ID="IX-SPI-SPISAVEPLAN-2"><PRIMARY>SPI_saveplan</PRIMARY></INDEXTERM>
-</REFNAMEDIV>
-<REFSYNOPSISDIV>
-<REFSYNOPSISDIVINFO>
-<DATE>1997-12-24</DATE>
-</REFSYNOPSISDIVINFO>
-<SYNOPSIS>
-SPI_saveplan(<REPLACEABLE CLASS="PARAMETER">plan</REPLACEABLE>)
-</SYNOPSIS>
-
-<REFSECT2 ID="R2-SPI-SPISAVEPLAN-1">
-<REFSECT2INFO>
-<DATE>1997-12-24</DATE>
-</REFSECT2INFO>
-<TITLE>Inputs
-</TITLE>
-<VARIABLELIST>
-<VARLISTENTRY>
-<TERM>
-void *<REPLACEABLE CLASS="PARAMETER">query</REPLACEABLE>
-</TERM>
-<LISTITEM>
-<PARA>
-Passed plan
-</PARA>
-</LISTITEM>
-</VARLISTENTRY>
-</VARIABLELIST>
-</REFSECT2>
-
-<REFSECT2 ID="R2-SPI-SPISAVEPLAN-2">
-<REFSECT2INFO>
-<DATE>1997-12-24</DATE>
-</REFSECT2INFO>
-<TITLE>Outputs
-</TITLE>
-<VARIABLELIST>
-<VARLISTENTRY>
-<TERM>void *
-</TERM>
-<LISTITEM>
-<PARA>
-Execution plan location. NULL if unsuccessful.
-</PARA>
-</LISTITEM>
-</VARLISTENTRY>
-<VARLISTENTRY>
-<TERM>SPI_result
-</TERM>
-<LISTITEM>
-<PARA>
-<SimpleList>
-<Member>
- <ReturnValue>SPI_ERROR_ARGUMENT</ReturnValue> if plan is NULL
-</Member>
-<Member>
- <ReturnValue>SPI_ERROR_UNCONNECTED</ReturnValue> if procedure is un-connected
-</Member>
-</SimpleList>
-</PARA>
-</LISTITEM>
-</VARLISTENTRY>
-</VARIABLELIST>
-</REFSECT2>
-</REFSYNOPSISDIV>
-
-<REFSECT1 ID="R1-SPI-SPISAVEPLAN-1">
-<REFSECT1INFO>
-<DATE>1997-12-24</DATE>
-</REFSECT1INFO>
-<TITLE>Description
-</TITLE>
-<PARA>
-<FUNCTION>SPI_saveplan</FUNCTION>
- stores a plan prepared by <Function>SPI_prepare</Function> in safe memory
- protected from freeing by <Function>SPI_finish</Function> or the transaction manager.
-</para>
-<Para>
- In the current version of <ProductName>Postgres</ProductName> there is no ability to
- store prepared plans in the system
- catalog and fetch them from there for execution. This will be implemented
- in future versions.
-
- As an alternative, there is the ability to reuse prepared plans in the
- consequent invocations of your procedure in the current session.
- Use <Function>SPI_execp</Function> to execute this saved plan.
-</PARA>
-</REFSECT1>
-<REFSECT1 ID="R1-SPI-SPISAVEPLAN-2">
-<TITLE>Usage
-</TITLE>
-<Para>
- <Function>SPI_saveplan</Function> saves a passed plan (prepared by <Function>SPI_prepare</Function>) in memory
- protected from freeing by <Function>SPI_finish</Function> and by the transaction manager and
- returns a pointer to the saved plan. You may save the pointer returned in
- a local variable. Always check if this pointer is NULL or not either when
- preparing a plan or using an already prepared plan in SPI_execp (see below).
-
-<Note>
-<Para>
- If one of the objects (a relation, function, etc.) referenced by the prepared
- plan is dropped during your session (by your backend or another process) then the
- results of <Function>SPI_execp</Function> for this plan will be unpredictable.
-</Para>
-</Note>
-
-</PARA>
-</REFSECT1>
-<!--
-<REFSECT1 ID="R1-SPI-SPISAVEPLAN-3">
-<TITLE>Algorithm
-</TITLE>
-<PARA><FUNCTION>SPI_saveplan</FUNCTION> performs the following:
-</PARA>
-<VARIABLELIST>
-<VARLISTENTRY>
-<TERM>•
-</TERM>
-<LISTITEM>
-<PARA>
-TBD
-</PARA>
-</LISTITEM>
-</VARLISTENTRY>
-</VARIABLELIST>
-<PARA>
-</PARA>
-</REFSECT1>
--->
-<!--
-<REFSECT1 ID="R1-SPI-SPISAVEPLAN-4">
-<TITLE>Structures
-</TITLE>
-<PARA>None
-</PARA>
-</REFSECT1>
--->
-</REFENTRY>
+ <indexterm><primary>SPI_pop</primary></indexterm>
-<!-- *********************************************** -->
-<!-- *********************************************** -->
-<!-- *********************************************** -->
+ <refsynopsisdiv>
+<synopsis>
+void SPI_pop(void)
+</synopsis>
+ </refsynopsisdiv>
-<REFENTRY ID="SPI-SPIEXECP">
-<REFMETA>
-<REFENTRYTITLE>SPI_execp</REFENTRYTITLE>
-<REFMISCINFO>SPI - Plan Execution</REFMISCINFO>
-</REFMETA>
-<REFNAMEDIV>
-<REFNAME>SPI_execp
-</REFNAME>
-<REFPURPOSE>
-Executes a plan from <Function>SPI_saveplan</Function>
-</REFPURPOSE>
-<INDEXTERM ID="IX-SPI-SPIEXECP-1"><PRIMARY>SPI</PRIMARY><SECONDARY>connecting</SECONDARY></INDEXTERM>
-<INDEXTERM ID="IX-SPI-SPIEXECP-2"><PRIMARY>SPI_execp</PRIMARY></INDEXTERM>
-</REFNAMEDIV>
-<REFSYNOPSISDIV>
-<REFSYNOPSISDIVINFO>
-<DATE>1997-12-24</DATE>
-</REFSYNOPSISDIVINFO>
-<SYNOPSIS>
-SPI_execp(<REPLACEABLE CLASS="PARAMETER">plan</REPLACEABLE>,
-<REPLACEABLE CLASS="PARAMETER">values</REPLACEABLE>,
-<REPLACEABLE CLASS="PARAMETER">nulls</REPLACEABLE>,
-<REPLACEABLE CLASS="PARAMETER">tcount</REPLACEABLE>)
-</SYNOPSIS>
-
-<REFSECT2 ID="R2-SPI-SPIEXECP-1">
-<REFSECT2INFO>
-<DATE>1997-12-24</DATE>
-</REFSECT2INFO>
-<TITLE>Inputs
-</TITLE>
-<VARIABLELIST>
-<VARLISTENTRY>
-<TERM>
-void *<REPLACEABLE CLASS="PARAMETER">plan</REPLACEABLE>
-</TERM>
-<LISTITEM>
-<PARA>
-Execution plan
-</PARA>
-</LISTITEM>
-</VARLISTENTRY>
-<VARLISTENTRY>
-<TERM>
-Datum *<REPLACEABLE CLASS="PARAMETER">values</REPLACEABLE>
-</TERM>
-<LISTITEM>
-<PARA>
-Actual parameter values
-</PARA>
-</LISTITEM>
-</VARLISTENTRY>
-<VARLISTENTRY>
-<TERM>
-char *<REPLACEABLE CLASS="PARAMETER">nulls</REPLACEABLE>
-</TERM>
-<LISTITEM>
-<PARA>
-Array describing what parameters get NULLs
-<SimpleList>
-<Member><literal>n</literal> indicates NULL allowed</Member>
-<Member>A space indicates NULL not allowed</Member>
-</SimpleList>
-</PARA>
-</LISTITEM>
-</VARLISTENTRY>
-<VARLISTENTRY>
-<TERM>
-int <REPLACEABLE CLASS="PARAMETER">tcount</REPLACEABLE>
-</TERM>
-<LISTITEM>
-<PARA>
-Number of tuples for which plan is to be executed
-</PARA>
-</LISTITEM>
-</VARLISTENTRY>
-</VARIABLELIST>
-</REFSECT2>
-
-<REFSECT2 ID="R2-SPI-SPIEXECP-2">
-<REFSECT2INFO>
-<DATE>1997-12-24</DATE>
-</REFSECT2INFO>
-<TITLE>Outputs
-</TITLE>
-<VARIABLELIST>
-<VARLISTENTRY>
-<TERM>int
-</TERM>
-<LISTITEM>
-<PARA>
- Returns the same value as <Function>SPI_exec</Function> as well as
-<SimpleList>
-<Member>
- <ReturnValue>SPI_ERROR_ARGUMENT</ReturnValue>
- if <REPLACEABLE CLASS="PARAMETER">plan</REPLACEABLE>
- is NULL or <REPLACEABLE CLASS="PARAMETER">tcount</REPLACEABLE> < 0
-</Member>
-<Member>
- <ReturnValue>SPI_ERROR_PARAM</ReturnValue>
- if <REPLACEABLE CLASS="PARAMETER">values</REPLACEABLE>
- is NULL
- and <REPLACEABLE CLASS="PARAMETER">plan</REPLACEABLE>
- was prepared with some parameters.
-</Member>
-</SimpleList>
-</para>
-</LISTITEM>
-</VARLISTENTRY>
-<VARLISTENTRY>
-<TERM>SPI_tuptable
-</TERM>
-<LISTITEM>
-<PARA>
-initialized as in
- <Function>SPI_exec</Function> if successful
-</PARA>
-</LISTITEM>
-</VARLISTENTRY>
-<VARLISTENTRY>
-<TERM>SPI_processed
-</TERM>
-<LISTITEM>
-<PARA>
-initialized as in
- <Function>SPI_exec</Function> if successful
-</para>
-</listitem>
-</VARLISTENTRY>
-</VARIABLELIST>
-</REFSECT2>
-</REFSYNOPSISDIV>
-
-<REFSECT1 ID="R1-SPI-SPIEXECP-1">
-<REFSECT1INFO>
-<DATE>1997-12-24</DATE>
-</REFSECT1INFO>
-<TITLE>Description
-</TITLE>
-<PARA>
-<FUNCTION>SPI_execp</FUNCTION>
- stores a plan prepared by <Function>SPI_prepare</Function> in safe memory
- protected from freeing by <Function>SPI_finish</Function> or the transaction manager.
-</para>
-<Para>
- In the current version of <ProductName>Postgres</ProductName> there is no ability to
- store prepared plans in the system
- catalog and fetch them from there for execution. This will be implemented
- in future versions.
-
- As a work arround, there is the ability to reuse prepared plans in the
- consequent invocations of your procedure in the current session.
- Use <Function>SPI_execp</Function> to execute this saved plan.
-</PARA>
-</REFSECT1>
-<REFSECT1 ID="R1-SPI-SPIEXECP-2">
-<TITLE>Usage
-</TITLE>
-<Para>
- If <REPLACEABLE CLASS="PARAMETER">nulls</REPLACEABLE>
-is NULL then
- <Function>SPI_execp</Function>
-assumes that all values (if any) are NOT NULL.
-
-<Note>
-<Para>
- If one of the objects (a relation, function, etc.) referenced by the prepared
- plan is dropped during your session (by your backend or another process) then the
- results of <Function>SPI_execp</Function> for this plan will be unpredictable.
-</Para>
-</Note>
-
-</PARA>
-</REFSECT1>
-<!--
-<REFSECT1 ID="R1-SPI-SPIEXECP-3">
-<TITLE>Algorithm
-</TITLE>
-<PARA><FUNCTION>SPI_execp</FUNCTION> performs the following:
-</PARA>
-<VARIABLELIST>
-<VARLISTENTRY>
-<TERM>•
-</TERM>
-<LISTITEM>
-<PARA>
-TBD
-</PARA>
-</LISTITEM>
-</VARLISTENTRY>
-</VARIABLELIST>
-<PARA>
-</PARA>
-</REFSECT1>
--->
-<!--
-<REFSECT1 ID="R1-SPI-SPIEXECP-4">
-<TITLE>Structures
-</TITLE>
-<PARA>None
-</PARA>
-</REFSECT1>
--->
-</REFENTRY>
-
-</Sect1>
-
-<Sect1 id="spi-interface-support">
-<Title>Interface Support Functions</Title>
-
-<Para>
-All functions described below may be used by connected and unconnected
-procedures.
-</Para>
+ <refsect1>
+ <title>Description</title>
-<!-- *********************************************** -->
-<!-- *********************************************** -->
-<!-- *********************************************** -->
+ <para>
+ <function>SPI_pop</function> pops the previous environment from the
+ SPI call stack. See <function>SPI_push</function>.
+ </para>
+ </refsect1>
-<REFENTRY ID="SPI-SPICOPYTUPLE">
-<REFMETA>
-<REFENTRYTITLE>SPI_copytuple</REFENTRYTITLE>
-<REFMISCINFO>SPI - Tuple Copy</REFMISCINFO>
-</REFMETA>
-<REFNAMEDIV>
-<REFNAME>SPI_copytuple
-</REFNAME>
-<REFPURPOSE>
-Makes copy of tuple in upper Executor context
-</REFPURPOSE>
-<INDEXTERM ID="IX-SPI-SPICOPYTUPLE-1"><PRIMARY>SPI</PRIMARY><SECONDARY>copying tuples</SECONDARY></INDEXTERM>
-<INDEXTERM ID="IX-SPI-SPICOPYTUPLE-2"><PRIMARY>SPI_copytuple</PRIMARY></INDEXTERM>
-</REFNAMEDIV>
-<REFSYNOPSISDIV>
-<REFSYNOPSISDIVINFO>
-<DATE>1997-12-24</DATE>
-</REFSYNOPSISDIVINFO>
-<SYNOPSIS>
-SPI_copytuple(<REPLACEABLE CLASS="PARAMETER">tuple</REPLACEABLE>)
-</SYNOPSIS>
-
-<REFSECT2 ID="R2-SPI-SPICOPYTUPLE-1">
-<REFSECT2INFO>
-<DATE>1997-12-24</DATE>
-</REFSECT2INFO>
-<TITLE>Inputs
-</TITLE>
-<VARIABLELIST>
-<VARLISTENTRY>
-<TERM>
-HeapTuple <REPLACEABLE CLASS="PARAMETER">tuple</REPLACEABLE>
-</TERM>
-<LISTITEM>
-<PARA>
-Input tuple to be copied
-</PARA>
-</LISTITEM>
-</VARLISTENTRY>
-</VARIABLELIST>
-</REFSECT2>
-
-<REFSECT2 ID="R2-SPI-SPICOPYTUPLE-2">
-<REFSECT2INFO>
-<DATE>1997-12-24</DATE>
-</REFSECT2INFO>
-<TITLE>Outputs
-</TITLE>
-<VARIABLELIST>
-<VARLISTENTRY>
-<TERM>
-HeapTuple
-</TERM>
-<LISTITEM>
-<PARA>
-Copied tuple
-<SimpleList>
-<Member>
- <ReturnValue>non-NULL</ReturnValue>
- if <REPLACEABLE CLASS="PARAMETER">tuple</REPLACEABLE>
- is not NULL and the copy was successful
-</Member>
-<Member>
- <ReturnValue>NULL</ReturnValue>
- only if <REPLACEABLE CLASS="PARAMETER">tuple</REPLACEABLE>
- is NULL
-</Member>
-</SimpleList>
-</para>
-</LISTITEM>
-</VARLISTENTRY>
-</VARIABLELIST>
-</REFSECT2>
-</REFSYNOPSISDIV>
-
-<REFSECT1 ID="R1-SPI-SPICOPYTUPLE-1">
-<REFSECT1INFO>
-<DATE>1997-12-24</DATE>
-</REFSECT1INFO>
-<TITLE>Description
-</TITLE>
-<PARA>
-<FUNCTION>SPI_copytuple</FUNCTION>
- makes a copy of tuple in upper Executor context. See the section on Memory Management.
-</PARA>
-</REFSECT1>
-<REFSECT1 ID="R1-SPI-SPICOPYTUPLE-2">
-<TITLE>Usage
-</TITLE>
-<Para>
-TBD
-</PARA>
-</REFSECT1>
-<!--
-<REFSECT1 ID="R1-SPI-SPICOPYTUPLE-3">
-<TITLE>Algorithm
-</TITLE>
-<PARA>
-</PARA>
-</REFSECT1>
--->
-<!--
-<REFSECT1 ID="R1-SPI-SPICOPYTUPLE-4">
-<TITLE>Structures
-</TITLE>
-<PARA>None
-</PARA>
-</REFSECT1>
--->
-</REFENTRY>
+</refentry>
-<!-- *********************************************** -->
-<!-- *********************************************** -->
<!-- *********************************************** -->
-<REFENTRY ID="SPI-SPICOPYTUPLEDESC">
-<REFMETA>
-<REFENTRYTITLE>SPI_copytupledesc</REFENTRYTITLE>
-<REFMISCINFO>SPI - Tuple Descriptor Copy</REFMISCINFO>
-</REFMETA>
-<REFNAMEDIV>
-<REFNAME>SPI_copytupledesc
-</REFNAME>
-<REFPURPOSE>
-Makes copy of tuple descriptor in upper Executor context
-</REFPURPOSE>
-<INDEXTERM ID="IX-SPI-SPICOPYTUPLEDESC-1"><PRIMARY>SPI</PRIMARY><SECONDARY>copying tuple descriptors</SECONDARY></INDEXTERM>
-<INDEXTERM ID="IX-SPI-SPICOPYTUPLEDESC-2"><PRIMARY>SPI_copytupledesc</PRIMARY></INDEXTERM>
-</REFNAMEDIV>
-<REFSYNOPSISDIV>
-<REFSYNOPSISDIVINFO>
-<DATE>2001-08-02</DATE>
-</REFSYNOPSISDIVINFO>
-<SYNOPSIS>
-SPI_copytupledesc(<REPLACEABLE CLASS="PARAMETER">tupdesc</REPLACEABLE>)
-</SYNOPSIS>
-
-<REFSECT2 ID="R2-SPI-SPICOPYTUPLEDESC-1">
-<REFSECT2INFO>
-<DATE>2001-08-02</DATE>
-</REFSECT2INFO>
-<TITLE>Inputs
-</TITLE>
-<VARIABLELIST>
-<VARLISTENTRY>
-<TERM>
-TupleDesc <REPLACEABLE CLASS="PARAMETER">tupdesc</REPLACEABLE>
-</TERM>
-<LISTITEM>
-<PARA>
-Input tuple descriptor to be copied
-</PARA>
-</LISTITEM>
-</VARLISTENTRY>
-</VARIABLELIST>
-</REFSECT2>
-
-<REFSECT2 ID="R2-SPI-SPICOPYTUPLEDESC-2">
-<REFSECT2INFO>
-<DATE>2001-08-02</DATE>
-</REFSECT2INFO>
-<TITLE>Outputs
-</TITLE>
-<VARIABLELIST>
-<VARLISTENTRY>
-<TERM>
-TupleDesc
-</TERM>
-<LISTITEM>
-<PARA>
-Copied tuple descriptor
-<SimpleList>
-<Member>
- <ReturnValue>non-NULL</ReturnValue>
- if <REPLACEABLE CLASS="PARAMETER">tupdesc</REPLACEABLE>
- is not NULL and the copy was successful
-</Member>
-<Member>
- <ReturnValue>NULL</ReturnValue>
- only if <REPLACEABLE CLASS="PARAMETER">tupdesc</REPLACEABLE>
- is NULL
-</Member>
-</SimpleList>
-</para>
-</LISTITEM>
-</VARLISTENTRY>
-</VARIABLELIST>
-</REFSECT2>
-</REFSYNOPSISDIV>
-
-<REFSECT1 ID="R1-SPI-SPICOPYTUPLEDESC-1">
-<REFSECT1INFO>
-<DATE>2001-08-02</DATE>
-</REFSECT1INFO>
-<TITLE>Description
-</TITLE>
-<PARA>
-<FUNCTION>SPI_copytupledesc</FUNCTION>
- makes a copy of tupdesc in upper Executor context. See the section on Memory Management.
-</PARA>
-</REFSECT1>
-<REFSECT1 ID="R1-SPI-SPICOPYTUPLEDESC-2">
-<TITLE>Usage
-</TITLE>
-<Para>
-TBD
-</PARA>
-</REFSECT1>
-<!--
-<REFSECT1 ID="R1-SPI-SPICOPYTUPLEDESC-3">
-<TITLE>Algorithm
-</TITLE>
-<PARA>
-</PARA>
-</REFSECT1>
--->
-<!--
-<REFSECT1 ID="R1-SPI-SPICOPYTUPLEDESC-4">
-<TITLE>Structures
-</TITLE>
-<PARA>None
-</PARA>
-</REFSECT1>
--->
-</REFENTRY>
+<refentry id="spi-spi-execute">
+ <refmeta>
+ <refentrytitle>SPI_execute</refentrytitle>
+ </refmeta>
+
+ <refnamediv>
+ <refname>SPI_execute</refname>
+ <refpurpose>execute a command</refpurpose>
+ </refnamediv>
+
+ <indexterm><primary>SPI_execute</primary></indexterm>
+
+ <refsynopsisdiv>
+<synopsis>
+int SPI_execute(const char * <parameter>command</parameter>, bool <parameter>read_only</parameter>, long <parameter>count</parameter>)
+</synopsis>
+ </refsynopsisdiv>
+
+ <refsect1>
+ <title>Description</title>
+
+ <para>
+ <function>SPI_execute</function> executes the specified SQL command
+ for <parameter>count</parameter> rows. If <parameter>read_only</parameter>
+ is <literal>true</>, the command must be read-only, and execution overhead
+ is somewhat reduced.
+ </para>
+
+ <para>
+ This function can only be called from a connected procedure.
+ </para>
+
+ <para>
+ If <parameter>count</parameter> is zero then the command is executed
+ for all rows that it applies to. If <parameter>count</parameter>
+ is greater than 0, then the number of rows for which the command
+ will be executed is restricted (much like a
+ <literal>LIMIT</literal> clause). For example:
+<programlisting>
+SPI_execute("INSERT INTO foo SELECT * FROM bar", false, 5);
+</programlisting>
+ will allow at most 5 rows to be inserted into the table.
+ </para>
+
+ <para>
+ You can pass multiple commands in one string, but later commands cannot
+ depend on the creation of objects earlier in the string, because the
+ whole string will be parsed and planned before execution begins.
+ <function>SPI_execute</function> returns the
+ result for the command executed last. The <parameter>count</parameter>
+ limit applies to each command separately, but it is not applied to
+ hidden commands generated by rules.
+ </para>
+
+ <para>
+ When <parameter>read_only</parameter> is <literal>false</>,
+ <function>SPI_execute</function> increments the command
+ counter and computes a new <firstterm>snapshot</> before executing each
+ command in the string. The snapshot does not actually change if the
+ current transaction isolation level is <literal>SERIALIZABLE</>, but in
+ <literal>READ COMMITTED</> mode the snapshot update allows each command to
+ see the results of newly committed transactions from other sessions.
+ This is essential for consistent behavior when the commands are modifying
+ the database.
+ </para>
+
+ <para>
+ When <parameter>read_only</parameter> is <literal>true</>,
+ <function>SPI_execute</function> does not update either the snapshot
+ or the command counter, and it allows only plain <command>SELECT</>
+ commands to appear in the command string. The commands are executed
+ using the snapshot previously established for the surrounding query.
+ This execution mode is somewhat faster than the read/write mode due
+ to eliminating per-command overhead. It also allows genuinely
+ <firstterm>stable</> functions to be built: since successive executions
+ will all use the same snapshot, there will be no change in the results.
+ </para>
+
+ <para>
+ It is generally unwise to mix read-only and read-write commands within
+ a single function using SPI; that could result in very confusing behavior,
+ since the read-only queries would not see the results of any database
+ updates done by the read-write queries.
+ </para>
+
+ <para>
+ The actual number of rows for which the (last) command was executed
+ is returned in the global variable <varname>SPI_processed</varname>.
+ If the return value of the function is <symbol>SPI_OK_SELECT</symbol>,
+ <symbol>SPI_OK_INSERT_RETURNING</symbol>,
+ <symbol>SPI_OK_DELETE_RETURNING</symbol>, or
+ <symbol>SPI_OK_UPDATE_RETURNING</symbol>,
+ then you can use the
+ global pointer <literal>SPITupleTable *SPI_tuptable</literal> to
+ access the result rows. Some utility commands (such as
+ <command>EXPLAIN</>) also return row sets, and <literal>SPI_tuptable</>
+ will contain the result in these cases too.
+ </para>
+
+ <para>
+ The structure <structname>SPITupleTable</structname> is defined
+ thus:
+<programlisting>
+typedef struct
+{
+ MemoryContext tuptabcxt; /* memory context of result table */
+ uint32 alloced; /* number of alloced vals */
+ uint32 free; /* number of free vals */
+ TupleDesc tupdesc; /* row descriptor */
+ HeapTuple *vals; /* rows */
+} SPITupleTable;
+</programlisting>
+ <structfield>vals</> is an array of pointers to rows. (The number
+ of valid entries is given by <varname>SPI_processed</varname>.)
+ <structfield>tupdesc</> is a row descriptor which you can pass to
+ SPI functions dealing with rows. <structfield>tuptabcxt</>,
+ <structfield>alloced</>, and <structfield>free</> are internal
+ fields not intended for use by SPI callers.
+ </para>
+
+ <para>
+ <function>SPI_finish</function> frees all
+ <structname>SPITupleTable</>s allocated during the current
+ procedure. You can free a particular result table earlier, if you
+ are done with it, by calling <function>SPI_freetuptable</function>.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Arguments</title>
+
+ <variablelist>
+ <varlistentry>
+ <term><literal>const char * <parameter>command</parameter></literal></term>
+ <listitem>
+ <para>
+ string containing command to execute
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>bool <parameter>read_only</parameter></literal></term>
+ <listitem>
+ <para>
+ <literal>true</> for read-only execution
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>long <parameter>count</parameter></literal></term>
+ <listitem>
+ <para>
+ maximum number of rows to process or return
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </refsect1>
+
+ <refsect1>
+ <title>Return Value</title>
+
+ <para>
+ If the execution of the command was successful then one of the
+ following (nonnegative) values will be returned:
+
+ <variablelist>
+ <varlistentry>
+ <term><symbol>SPI_OK_SELECT</symbol></term>
+ <listitem>
+ <para>
+ if a <command>SELECT</command> (but not <command>SELECT
+ INTO</>) was executed
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><symbol>SPI_OK_SELINTO</symbol></term>
+ <listitem>
+ <para>
+ if a <command>SELECT INTO</command> was executed
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><symbol>SPI_OK_INSERT</symbol></term>
+ <listitem>
+ <para>
+ if an <command>INSERT</command> was executed
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><symbol>SPI_OK_DELETE</symbol></term>
+ <listitem>
+ <para>
+ if a <command>DELETE</command> was executed
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><symbol>SPI_OK_UPDATE</symbol></term>
+ <listitem>
+ <para>
+ if an <command>UPDATE</command> was executed
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><symbol>SPI_OK_INSERT_RETURNING</symbol></term>
+ <listitem>
+ <para>
+ if an <command>INSERT RETURNING</command> was executed
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><symbol>SPI_OK_DELETE_RETURNING</symbol></term>
+ <listitem>
+ <para>
+ if a <command>DELETE RETURNING</command> was executed
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><symbol>SPI_OK_UPDATE_RETURNING</symbol></term>
+ <listitem>
+ <para>
+ if an <command>UPDATE RETURNING</command> was executed
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><symbol>SPI_OK_UTILITY</symbol></term>
+ <listitem>
+ <para>
+ if a utility command (e.g., <command>CREATE TABLE</command>)
+ was executed
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </para>
+
+ <para>
+ On error, one of the following negative values is returned:
+
+ <variablelist>
+ <varlistentry>
+ <term><symbol>SPI_ERROR_ARGUMENT</symbol></term>
+ <listitem>
+ <para>
+ if <parameter>command</parameter> is <symbol>NULL</symbol> or
+ <parameter>count</parameter> is less than 0
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><symbol>SPI_ERROR_COPY</symbol></term>
+ <listitem>
+ <para>
+ if <command>COPY TO stdout</> or <command>COPY FROM stdin</>
+ was attempted
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><symbol>SPI_ERROR_TRANSACTION</symbol></term>
+ <listitem>
+ <para>
+ if a transaction manipulation command was attempted
+ (<command>BEGIN</>,
+ <command>COMMIT</>,
+ <command>ROLLBACK</>,
+ <command>SAVEPOINT</>,
+ <command>PREPARE TRANSACTION</>,
+ <command>COMMIT PREPARED</>,
+ <command>ROLLBACK PREPARED</>,
+ or any variant thereof)
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><symbol>SPI_ERROR_OPUNKNOWN</symbol></term>
+ <listitem>
+ <para>
+ if the command type is unknown (shouldn't happen)
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><symbol>SPI_ERROR_UNCONNECTED</symbol></term>
+ <listitem>
+ <para>
+ if called from an unconnected procedure
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Notes</title>
+
+ <para>
+ The functions <function>SPI_execute</function>,
+ <function>SPI_exec</function>,
+ <function>SPI_execute_plan</function>, and
+ <function>SPI_execp</function> change both
+ <varname>SPI_processed</varname> and
+ <varname>SPI_tuptable</varname> (just the pointer, not the contents
+ of the structure). Save these two global variables into local
+ procedure variables if you need to access the result table of
+ <function>SPI_execute</function> or a related function
+ across later calls.
+ </para>
+ </refsect1>
+</refentry>
-<!-- *********************************************** -->
-<!-- *********************************************** -->
<!-- *********************************************** -->
-<REFENTRY ID="SPI-SPIMODIFYTUPLE">
-<REFMETA>
-<REFENTRYTITLE>SPI_modifytuple</REFENTRYTITLE>
-<REFMISCINFO>SPI - Tuple Modify</REFMISCINFO>
-</REFMETA>
-<REFNAMEDIV>
-<REFNAME>SPI_modifytuple
-</REFNAME>
-<REFPURPOSE>
-Modifies tuple of relation
-</REFPURPOSE>
-<INDEXTERM ID="IX-SPI-SPIMODIFYTUPLE-1"><PRIMARY>SPI</PRIMARY><SECONDARY>modifying tuples</SECONDARY></INDEXTERM>
-<INDEXTERM ID="IX-SPI-SPIMODIFYTUPLE-2"><PRIMARY>SPI_modifytuple</PRIMARY></INDEXTERM>
-</REFNAMEDIV>
-<REFSYNOPSISDIV>
-<REFSYNOPSISDIVINFO>
-<DATE>1997-12-24</DATE>
-</REFSYNOPSISDIVINFO>
-<SYNOPSIS>
-SPI_modifytuple(<REPLACEABLE CLASS="PARAMETER">rel</REPLACEABLE>, <REPLACEABLE CLASS="PARAMETER">tuple</REPLACEABLE> , <REPLACEABLE CLASS="PARAMETER">nattrs</REPLACEABLE>
-, <REPLACEABLE CLASS="PARAMETER">attnum</REPLACEABLE> , <REPLACEABLE CLASS="PARAMETER">Values</REPLACEABLE> , <REPLACEABLE CLASS="PARAMETER">Nulls</REPLACEABLE>)
-</SYNOPSIS>
-
-<REFSECT2 ID="R2-SPI-SPIMODIFYTUPLE-1">
-<REFSECT2INFO>
-<DATE>1997-12-24</DATE>
-</REFSECT2INFO>
-<TITLE>Inputs
-</TITLE>
-<VARIABLELIST>
-<VARLISTENTRY>
-<TERM>
-Relation <REPLACEABLE CLASS="PARAMETER">rel</REPLACEABLE>
-</TERM>
-<LISTITEM>
-<PARA>
-</PARA>
-</LISTITEM>
-</VARLISTENTRY>
-<VARLISTENTRY>
-<TERM>
-HeapTuple <REPLACEABLE CLASS="PARAMETER">tuple</REPLACEABLE>
-</TERM>
-<LISTITEM>
-<PARA>
-Input tuple to be modified
-</PARA>
-</LISTITEM>
-</VARLISTENTRY>
-<VARLISTENTRY>
-<TERM>
-int <REPLACEABLE CLASS="PARAMETER">nattrs</REPLACEABLE>
-</TERM>
-<LISTITEM>
-<PARA>
-Number of attribute numbers in attnum
-</PARA>
-</LISTITEM>
-</VARLISTENTRY>
-<VARLISTENTRY>
-<TERM>
-int * <REPLACEABLE CLASS="PARAMETER">attnum</REPLACEABLE>
-</TERM>
-<LISTITEM>
-<PARA>
-Array of numbers of the attributes that are to be changed
-</PARA>
-</LISTITEM>
-</VARLISTENTRY>
-<VARLISTENTRY>
-<TERM>
-Datum * <REPLACEABLE CLASS="PARAMETER">Values</REPLACEABLE>
-</TERM>
-<LISTITEM>
-<PARA>
-New values for the attributes specified
-</PARA>
-</LISTITEM>
-</VARLISTENTRY>
-<VARLISTENTRY>
-<TERM>
-char * <REPLACEABLE CLASS="PARAMETER">Nulls</REPLACEABLE>
-</TERM>
-<LISTITEM>
-<PARA>
-Which attributes are NULL, if any
-</PARA>
-</LISTITEM>
-</VARLISTENTRY>
-</VARIABLELIST>
-</REFSECT2>
-
-<REFSECT2 ID="R2-SPI-SPIMODIFYTUPLE-2">
-<REFSECT2INFO>
-<DATE>1997-12-24</DATE>
-</REFSECT2INFO>
-<TITLE>Outputs
-</TITLE>
-<VARIABLELIST>
-<VARLISTENTRY>
-<TERM>
-HeapTuple
-</TERM>
-<LISTITEM>
-<PARA>
-New tuple with modifications
-<SimpleList>
-<Member>
- <ReturnValue>non-NULL</ReturnValue>
- if <REPLACEABLE CLASS="PARAMETER">tuple</REPLACEABLE>
- is not NULL and the modify was successful
-</Member>
-<Member>
- <ReturnValue>NULL</ReturnValue>
- only if <REPLACEABLE CLASS="PARAMETER">tuple</REPLACEABLE>
- is NULL
-</Member>
-</SimpleList>
-</para>
-</LISTITEM>
-</VARLISTENTRY>
-<VARLISTENTRY>
-<TERM>
-SPI_result
-</TERM>
-<LISTITEM>
-<PARA>
-<SimpleList>
-<Member>
- <ReturnValue>SPI_ERROR_ARGUMENT</ReturnValue> if rel is NULL or tuple is NULL or natts ≤ 0 or
- attnum is NULL or Values is NULL.
-</Member>
-<Member>
- <ReturnValue>SPI_ERROR_NOATTRIBUTE</ReturnValue> if there is an invalid
- attribute number in attnum (attnum ≤ 0 or > number of
- attributes in tuple)
-</Member>
-</SimpleList>
-</para>
-</LISTITEM>
-</VARLISTENTRY>
-</VARIABLELIST>
-</REFSECT2>
-</REFSYNOPSISDIV>
-
-<REFSECT1 ID="R1-SPI-SPIMODIFYTUPLE-1">
-<REFSECT1INFO>
-<DATE>1997-12-24</DATE>
-</REFSECT1INFO>
-<TITLE>Description
-</TITLE>
-<PARA>
-<FUNCTION>SPI_modifytuple</FUNCTION>
-Modifies a tuple in upper Executor context. See the section on Memory Management.
-</PARA>
-</REFSECT1>
-<REFSECT1 ID="R1-SPI-SPIMODIFYTUPLE-2">
-<TITLE>Usage
-</TITLE>
-<Para>
-If successful, a pointer to the new tuple is returned. The new tuple is
-allocated in upper Executor context (see Memory management). Passed tuple
-is not changed.
-</PARA>
-</REFSECT1>
-<!--
-<REFSECT1 ID="R1-SPI-SPIMODIFYTUPLE-3">
-<TITLE>Algorithm
-</TITLE>
-<PARA>
-</PARA>
-</REFSECT1>
--->
-<!--
-<REFSECT1 ID="R1-SPI-SPIMODIFYTUPLE-4">
-<TITLE>Structures
-</TITLE>
-<PARA>None
-</PARA>
-</REFSECT1>
--->
-</REFENTRY>
+<refentry id="spi-spi-exec">
+ <refmeta>
+ <refentrytitle>SPI_exec</refentrytitle>
+ </refmeta>
+
+ <refnamediv>
+ <refname>SPI_exec</refname>
+ <refpurpose>execute a read/write command</refpurpose>
+ </refnamediv>
+
+ <indexterm><primary>SPI_exec</primary></indexterm>
+
+ <refsynopsisdiv>
+<synopsis>
+int SPI_exec(const char * <parameter>command</parameter>, long <parameter>count</parameter>)
+</synopsis>
+ </refsynopsisdiv>
+
+ <refsect1>
+ <title>Description</title>
+
+ <para>
+ <function>SPI_exec</function> is the same as
+ <function>SPI_execute</function>, with the latter's
+ <parameter>read_only</parameter> parameter always taken as
+ <literal>false</>.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Arguments</title>
+
+ <variablelist>
+ <varlistentry>
+ <term><literal>const char * <parameter>command</parameter></literal></term>
+ <listitem>
+ <para>
+ string containing command to execute
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>long <parameter>count</parameter></literal></term>
+ <listitem>
+ <para>
+ maximum number of rows to process or return
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </refsect1>
+
+ <refsect1>
+ <title>Return Value</title>
+
+ <para>
+ See <function>SPI_execute</function>.
+ </para>
+ </refsect1>
+</refentry>
<!-- *********************************************** -->
-<!-- *********************************************** -->
+
+<refentry id="spi-spi-prepare">
+ <refmeta>
+ <refentrytitle>SPI_prepare</refentrytitle>
+ </refmeta>
+
+ <refnamediv>
+ <refname>SPI_prepare</refname>
+ <refpurpose>prepare a plan for a command, without executing it yet</refpurpose>
+ </refnamediv>
+
+ <indexterm><primary>SPI_prepare</primary></indexterm>
+
+ <refsynopsisdiv>
+<synopsis>
+SPIPlanPtr SPI_prepare(const char * <parameter>command</parameter>, int <parameter>nargs</parameter>, Oid * <parameter>argtypes</parameter>)
+</synopsis>
+ </refsynopsisdiv>
+
+ <refsect1>
+ <title>Description</title>
+
+ <para>
+ <function>SPI_prepare</function> creates and returns an execution
+ plan for the specified command but doesn't execute the command.
+ This function should only be called from a connected procedure.
+ </para>
+
+ <para>
+ When the same or a similar command is to be executed repeatedly, it
+ might be advantageous to perform the planning only once.
+ <function>SPI_prepare</function> converts a command string into an
+ execution plan that can be executed repeatedly using
+ <function>SPI_execute_plan</function>.
+ </para>
+
+ <para>
+ A prepared command can be generalized by writing parameters
+ (<literal>$1</>, <literal>$2</>, etc.) in place of what would be
+ constants in a normal command. The actual values of the parameters
+ are then specified when <function>SPI_execute_plan</function> is called.
+ This allows the prepared command to be used over a wider range of
+ situations than would be possible without parameters.
+ </para>
+
+ <para>
+ The plan returned by <function>SPI_prepare</function> can be used
+ only in the current invocation of the procedure, since
+ <function>SPI_finish</function> frees memory allocated for a plan.
+ But a plan can be saved for longer using the function
+ <function>SPI_saveplan</function>.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Arguments</title>
+
+ <variablelist>
+ <varlistentry>
+ <term><literal>const char * <parameter>command</parameter></literal></term>
+ <listitem>
+ <para>
+ command string
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>int <parameter>nargs</parameter></literal></term>
+ <listitem>
+ <para>
+ number of input parameters (<literal>$1</>, <literal>$2</>, etc.)
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>Oid * <parameter>argtypes</parameter></literal></term>
+ <listitem>
+ <para>
+ pointer to an array containing the <acronym>OID</acronym>s of
+ the data types of the parameters
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </refsect1>
+
+ <refsect1>
+ <title>Return Value</title>
+
+ <para>
+ <function>SPI_prepare</function> returns a non-null pointer to an
+ execution plan. On error, <symbol>NULL</symbol> will be returned,
+ and <varname>SPI_result</varname> will be set to one of the same
+ error codes used by <function>SPI_execute</function>, except that
+ it is set to <symbol>SPI_ERROR_ARGUMENT</symbol> if
+ <parameter>command</parameter> is <symbol>NULL</symbol>, or if
+ <parameter>nargs</> is less than 0, or if <parameter>nargs</> is
+ greater than 0 and <parameter>argtypes</> is <symbol>NULL</symbol>.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Notes</title>
+
+ <para>
+ <type>SPIPlanPtr</> is declared as a pointer to an opaque struct type in
+ <filename>spi.h</>. It is unwise to try to access its contents
+ directly, as that makes your code much more likely to break in
+ future revisions of <productname>PostgreSQL</productname>.
+ </para>
+
+ <para>
+ There is a disadvantage to using parameters: since the planner does
+ not know the values that will be supplied for the parameters, it
+ might make worse planning choices than it would make for a normal
+ command with all constants visible.
+ </para>
+ </refsect1>
+</refentry>
+
<!-- *********************************************** -->
-<REFENTRY ID="SPI-SPIFNUMBER">
-<REFMETA>
-<REFENTRYTITLE>SPI_fnumber</REFENTRYTITLE>
-<REFMISCINFO>SPI - Tuple Information</REFMISCINFO>
-</REFMETA>
-<REFNAMEDIV>
-<REFNAME>SPI_fnumber
-</REFNAME>
-<REFPURPOSE>
-Finds the attribute number for specified attribute
-</REFPURPOSE>
-<INDEXTERM ID="IX-SPI-SPIFNUMBER-1"><PRIMARY>SPI</PRIMARY><SECONDARY>decoding tuples</SECONDARY></INDEXTERM>
-<INDEXTERM ID="IX-SPI-SPIFNUMBER-2"><PRIMARY>SPI_fnumber</PRIMARY></INDEXTERM>
-</REFNAMEDIV>
-<REFSYNOPSISDIV>
-<REFSYNOPSISDIVINFO>
-<DATE>1997-12-24</DATE>
-</REFSYNOPSISDIVINFO>
-<SYNOPSIS>
-SPI_fnumber(<REPLACEABLE CLASS="PARAMETER">tupdesc</REPLACEABLE>, <REPLACEABLE CLASS="PARAMETER">fname</REPLACEABLE>)
-</SYNOPSIS>
-
-<REFSECT2 ID="R2-SPI-SPIFNUMBER-1">
-<REFSECT2INFO>
-<DATE>1997-12-24</DATE>
-</REFSECT2INFO>
-<TITLE>Inputs
-</TITLE>
-<VARIABLELIST>
-<VARLISTENTRY>
-<TERM>
-TupleDesc <REPLACEABLE CLASS="PARAMETER">tupdesc</REPLACEABLE>
-</TERM>
-<LISTITEM>
-<PARA>
-Input tuple description
-</PARA>
-</LISTITEM>
-</VARLISTENTRY>
-<VARLISTENTRY>
-<TERM>
-char * <REPLACEABLE CLASS="PARAMETER">fname</REPLACEABLE>
-</TERM>
-<LISTITEM>
-<PARA>
-Field name
-</PARA>
-</LISTITEM>
-</VARLISTENTRY>
-</VARIABLELIST>
-</REFSECT2>
-
-<REFSECT2 ID="R2-SPI-SPIFNUMBER-2">
-<REFSECT2INFO>
-<DATE>1997-12-24</DATE>
-</REFSECT2INFO>
-<TITLE>Outputs
-</TITLE>
-<VARIABLELIST>
-<VARLISTENTRY>
-<TERM>
-int
-</TERM>
-<LISTITEM>
-<PARA>
-Attribute number
-<SimpleList>
-<Member>
-Valid one-based index number of attribute
-</Member>
-<Member>
-<ReturnValue>SPI_ERROR_NOATTRIBUTE</ReturnValue> if the named attribute is not found
-</Member>
-</SimpleList>
-</para>
-</LISTITEM>
-</VARLISTENTRY>
-</VARIABLELIST>
-</REFSECT2>
-</REFSYNOPSISDIV>
-
-<REFSECT1 ID="R1-SPI-SPIFNUMBER-1">
-<REFSECT1INFO>
-<DATE>1997-12-24</DATE>
-</REFSECT1INFO>
-<TITLE>Description
-</TITLE>
-<PARA>
-<FUNCTION>SPI_fnumber</FUNCTION>
- returns the attribute number for the attribute with name in fname.
-</PARA>
-</REFSECT1>
-<REFSECT1 ID="R1-SPI-SPIFNUMBER-2">
-<TITLE>Usage
-</TITLE>
-<Para>
-Attribute numbers are 1 based.
-</PARA>
-</REFSECT1>
-<!--
-<REFSECT1 ID="R1-SPI-SPIFNUMBER-3">
-<TITLE>Algorithm
-</TITLE>
-<PARA>
-</PARA>
-</REFSECT1>
--->
-<!--
-<REFSECT1 ID="R1-SPI-SPIFNUMBER-4">
-<TITLE>Structures
-</TITLE>
-<PARA>None
-</PARA>
-</REFSECT1>
--->
-</REFENTRY>
+<refentry id="spi-spi-prepare-cursor">
+ <refmeta>
+ <refentrytitle>SPI_prepare_cursor</refentrytitle>
+ </refmeta>
+
+ <refnamediv>
+ <refname>SPI_prepare_cursor</refname>
+ <refpurpose>prepare a plan for a command, without executing it yet</refpurpose>
+ </refnamediv>
+
+ <indexterm><primary>SPI_prepare_cursor</primary></indexterm>
+
+ <refsynopsisdiv>
+<synopsis>
+SPIPlanPtr SPI_prepare_cursor(const char * <parameter>command</parameter>, int <parameter>nargs</parameter>, Oid * <parameter>argtypes</parameter>, int <parameter>cursorOptions</parameter>)
+</synopsis>
+ </refsynopsisdiv>
+
+ <refsect1>
+ <title>Description</title>
+
+ <para>
+ <function>SPI_prepare_cursor</function> is identical to
+ <function>SPI_prepare</function>, except that it also allows specification
+ of the planner's <quote>cursor options</> parameter. This is a bitmask
+ having the values shown in <filename>nodes/parsenodes.h</filename>
+ for the <structfield>options</> field of <structname>DeclareCursorStmt</>.
+ <function>SPI_prepare</function> always takes these options as zero.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Arguments</title>
+
+ <variablelist>
+ <varlistentry>
+ <term><literal>const char * <parameter>command</parameter></literal></term>
+ <listitem>
+ <para>
+ command string
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>int <parameter>nargs</parameter></literal></term>
+ <listitem>
+ <para>
+ number of input parameters (<literal>$1</>, <literal>$2</>, etc.)
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>Oid * <parameter>argtypes</parameter></literal></term>
+ <listitem>
+ <para>
+ pointer to an array containing the <acronym>OID</acronym>s of
+ the data types of the parameters
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>int <parameter>cursorOptions</parameter></literal></term>
+ <listitem>
+ <para>
+ integer bitmask of cursor options; zero produces default behavior
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </refsect1>
+
+ <refsect1>
+ <title>Return Value</title>
+
+ <para>
+ <function>SPI_prepare_cursor</function> has the same return conventions as
+ <function>SPI_prepare</function>.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Notes</title>
+
+ <para>
+ Useful bits to set in <parameter>cursorOptions</> include
+ <symbol>CURSOR_OPT_SCROLL</symbol>,
+ <symbol>CURSOR_OPT_NO_SCROLL</symbol>, and
+ <symbol>CURSOR_OPT_FAST_PLAN</symbol>. Note in particular that
+ <symbol>CURSOR_OPT_HOLD</symbol> is ignored.
+ </para>
+ </refsect1>
+</refentry>
<!-- *********************************************** -->
+
+<refentry id="spi-spi-getargcount">
+ <refmeta>
+ <refentrytitle>SPI_getargcount</refentrytitle>
+ </refmeta>
+
+ <refnamediv>
+ <refname>SPI_getargcount</refname>
+ <refpurpose>return the number of arguments needed by a plan
+ prepared by <function>SPI_prepare</function></refpurpose>
+ </refnamediv>
+
+ <indexterm><primary>SPI_getargcount</primary></indexterm>
+
+ <refsynopsisdiv>
+<synopsis>
+int SPI_getargcount(SPIPlanPtr <parameter>plan</parameter>)
+</synopsis>
+ </refsynopsisdiv>
+
+ <refsect1>
+ <title>Description</title>
+
+ <para>
+ <function>SPI_getargcount</function> returns the number of arguments needed
+ to execute a plan prepared by <function>SPI_prepare</function>.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Arguments</title>
+
+ <variablelist>
+ <varlistentry>
+ <term><literal>SPIPlanPtr <parameter>plan</parameter></literal></term>
+ <listitem>
+ <para>
+ execution plan (returned by <function>SPI_prepare</function>)
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </refsect1>
+
+ <refsect1>
+ <title>Return Value</title>
+ <para>
+ The count of expected arguments for the <parameter>plan</parameter>.
+ If the <parameter>plan</parameter> is <symbol>NULL</symbol> or invalid,
+ <varname>SPI_result</varname> is set to <symbol>SPI_ERROR_ARGUMENT</symbol>
+ and <literal>-1</literal> is returned.
+ </para>
+ </refsect1>
+</refentry>
+
<!-- *********************************************** -->
+
+<refentry id="spi-spi-getargtypeid">
+ <refmeta>
+ <refentrytitle>SPI_getargtypeid</refentrytitle>
+ </refmeta>
+
+ <refnamediv>
+ <refname>SPI_getargtypeid</refname>
+ <refpurpose>return the data type OID for an argument of
+ a plan prepared by <function>SPI_prepare</function></refpurpose>
+ </refnamediv>
+
+ <indexterm><primary>SPI_getargtypeid</primary></indexterm>
+
+ <refsynopsisdiv>
+<synopsis>
+Oid SPI_getargtypeid(SPIPlanPtr <parameter>plan</parameter>, int <parameter>argIndex</parameter>)
+</synopsis>
+ </refsynopsisdiv>
+
+ <refsect1>
+ <title>Description</title>
+
+ <para>
+ <function>SPI_getargtypeid</function> returns the OID representing the type
+ id for the <parameter>argIndex</parameter>'th argument of a plan prepared by
+ <function>SPI_prepare</function>. First argument is at index zero.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Arguments</title>
+
+ <variablelist>
+ <varlistentry>
+ <term><literal>SPIPlanPtr <parameter>plan</parameter></literal></term>
+ <listitem>
+ <para>
+ execution plan (returned by <function>SPI_prepare</function>)
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>int <parameter>argIndex</parameter></literal></term>
+ <listitem>
+ <para>
+ zero based index of the argument
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </refsect1>
+
+ <refsect1>
+ <title>Return Value</title>
+ <para>
+ The type id of the argument at the given index.
+ If the <parameter>plan</parameter> is <symbol>NULL</symbol> or invalid,
+ or <parameter>argIndex</parameter> is less than 0 or
+ not less than the number of arguments declared for the
+ <parameter>plan</parameter>,
+ <varname>SPI_result</varname> is set to <symbol>SPI_ERROR_ARGUMENT</symbol>
+ and <symbol>InvalidOid</symbol> is returned.
+ </para>
+ </refsect1>
+</refentry>
+
<!-- *********************************************** -->
-<REFENTRY ID="SPI-SPIFNAME">
-<REFMETA>
-<REFENTRYTITLE>SPI_fname</REFENTRYTITLE>
-<REFMISCINFO>SPI - Tuple Information</REFMISCINFO>
-</REFMETA>
-<REFNAMEDIV>
-<REFNAME>SPI_fname
-</REFNAME>
-<REFPURPOSE>
-Finds the attribute name for the specified attribute
-</REFPURPOSE>
-<INDEXTERM ID="IX-SPI-SPIFNAME-1"><PRIMARY>SPI</PRIMARY><SECONDARY>decoding tuples</SECONDARY></INDEXTERM>
-<INDEXTERM ID="IX-SPI-SPIFNAME-2"><PRIMARY>SPI_fname</PRIMARY></INDEXTERM>
-</REFNAMEDIV>
-<REFSYNOPSISDIV>
-<REFSYNOPSISDIVINFO>
-<DATE>1997-12-24</DATE>
-</REFSYNOPSISDIVINFO>
-<SYNOPSIS>
-SPI_fname(<REPLACEABLE CLASS="PARAMETER">tupdesc</REPLACEABLE>, <REPLACEABLE CLASS="PARAMETER">fname</REPLACEABLE>)
-</SYNOPSIS>
-
-<REFSECT2 ID="R2-SPI-SPIFNAME-1">
-<REFSECT2INFO>
-<DATE>1997-12-24</DATE>
-</REFSECT2INFO>
-<TITLE>Inputs
-</TITLE>
-<VARIABLELIST>
-<VARLISTENTRY>
-<TERM>
-TupleDesc <REPLACEABLE CLASS="PARAMETER">tupdesc</REPLACEABLE>
-</TERM>
-<LISTITEM>
-<PARA>
-Input tuple description
-</PARA>
-</LISTITEM>
-</VARLISTENTRY>
-<VARLISTENTRY>
-<TERM>
-int <REPLACEABLE CLASS="PARAMETER">fnumber</REPLACEABLE>
-</TERM>
-<LISTITEM>
-<PARA>
-Attribute number
-</PARA>
-</LISTITEM>
-</VARLISTENTRY>
-</VARIABLELIST>
-</REFSECT2>
-
-<REFSECT2 ID="R2-SPI-SPIFNAME-2">
-<REFSECT2INFO>
-<DATE>1997-12-24</DATE>
-</REFSECT2INFO>
-<TITLE>Outputs
-</TITLE>
-<VARIABLELIST>
-<VARLISTENTRY>
-<TERM>
-char *
-</TERM>
-<LISTITEM>
-<PARA>
-Attribute name
-<SimpleList>
-<Member>
-NULL if fnumber is out of range
-</Member>
-<Member>
-SPI_result set to
-<ReturnValue>SPI_ERROR_NOATTRIBUTE</ReturnValue> on error
-</Member>
-</SimpleList>
-</para>
-</LISTITEM>
-</VARLISTENTRY>
-</VARIABLELIST>
-</REFSECT2>
-</REFSYNOPSISDIV>
-
-<REFSECT1 ID="R1-SPI-SPIFNAME-1">
-<REFSECT1INFO>
-<DATE>1997-12-24</DATE>
-</REFSECT1INFO>
-<TITLE>Description
-</TITLE>
-<PARA>
-<FUNCTION>SPI_fname</FUNCTION>
- returns the attribute name for the specified attribute.
-</PARA>
-</REFSECT1>
-<REFSECT1 ID="R1-SPI-SPIFNAME-2">
-<TITLE>Usage
-</TITLE>
-<Para>
-Attribute numbers are 1 based.
-</PARA>
-</REFSECT1>
-<REFSECT1 ID="R1-SPI-SPIFNAME-3">
-<TITLE>Algorithm
-</TITLE>
-<PARA>
-Returns a newly-allocated copy of the attribute name.
-</PARA>
-</REFSECT1>
-<!--
-<REFSECT1 ID="R1-SPI-SPIFNAME-4">
-<TITLE>Structures
-</TITLE>
-<PARA>None
-</PARA>
-</REFSECT1>
--->
-</REFENTRY>
+<refentry id="spi-spi-is-cursor-plan">
+ <refmeta>
+ <refentrytitle>SPI_is_cursor_plan</refentrytitle>
+ </refmeta>
+
+ <refnamediv>
+ <refname>SPI_is_cursor_plan</refname>
+ <refpurpose>return <symbol>true</symbol> if a plan
+ prepared by <function>SPI_prepare</function> can be used with
+ <function>SPI_cursor_open</function></refpurpose>
+ </refnamediv>
+
+ <indexterm><primary>SPI_is_cursor_plan</primary></indexterm>
+
+ <refsynopsisdiv>
+<synopsis>
+bool SPI_is_cursor_plan(SPIPlanPtr <parameter>plan</parameter>)
+</synopsis>
+ </refsynopsisdiv>
+
+ <refsect1>
+ <title>Description</title>
+
+ <para>
+ <function>SPI_is_cursor_plan</function> returns <symbol>true</symbol>
+ if a plan prepared by <function>SPI_prepare</function> can be passed
+ as an argument to <function>SPI_cursor_open</function>, or
+ <symbol>false</symbol> if that is not the case. The criteria are that the
+ <parameter>plan</parameter> represents one single command and that this
+ command returns tuples to the caller; for example, <command>SELECT</>
+ is allowed unless it contains an <literal>INTO</> clause, and
+ <command>UPDATE</> is allowed only if it contains a <literal>RETURNING</>
+ clause.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Arguments</title>
+
+ <variablelist>
+ <varlistentry>
+ <term><literal>SPIPlanPtr <parameter>plan</parameter></literal></term>
+ <listitem>
+ <para>
+ execution plan (returned by <function>SPI_prepare</function>)
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </refsect1>
+
+ <refsect1>
+ <title>Return Value</title>
+ <para>
+ <symbol>true</symbol> or <symbol>false</symbol> to indicate if the
+ <parameter>plan</parameter> can produce a cursor or not.
+ If the <parameter>plan</parameter> is <symbol>NULL</symbol> or invalid,
+ <varname>SPI_result</varname> is set to <symbol>SPI_ERROR_ARGUMENT</symbol>
+ and <symbol>false</symbol> is returned.
+ </para>
+ </refsect1>
+</refentry>
<!-- *********************************************** -->
+
+<refentry id="spi-spi-execute-plan">
+ <refmeta>
+ <refentrytitle>SPI_execute_plan</refentrytitle>
+ </refmeta>
+
+ <refnamediv>
+ <refname>SPI_execute_plan</refname>
+ <refpurpose>execute a plan prepared by <function>SPI_prepare</function></refpurpose>
+ </refnamediv>
+
+ <indexterm><primary>SPI_execute_plan</primary></indexterm>
+
+ <refsynopsisdiv>
+<synopsis>
+int SPI_execute_plan(SPIPlanPtr <parameter>plan</parameter>, Datum * <parameter>values</parameter>, const char * <parameter>nulls</parameter>,
+ bool <parameter>read_only</parameter>, long <parameter>count</parameter>)
+</synopsis>
+ </refsynopsisdiv>
+
+ <refsect1>
+ <title>Description</title>
+
+ <para>
+ <function>SPI_execute_plan</function> executes a plan prepared by
+ <function>SPI_prepare</function>. <parameter>read_only</parameter> and
+ <parameter>count</parameter> have the same interpretation as in
+ <function>SPI_execute</function>.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Arguments</title>
+
+ <variablelist>
+ <varlistentry>
+ <term><literal>SPIPlanPtr <parameter>plan</parameter></literal></term>
+ <listitem>
+ <para>
+ execution plan (returned by <function>SPI_prepare</function>)
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>Datum * <parameter>values</parameter></literal></term>
+ <listitem>
+ <para>
+ An array of actual parameter values. Must have same length as the
+ plan's number of arguments.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>const char * <parameter>nulls</parameter></literal></term>
+ <listitem>
+ <para>
+ An array describing which parameters are null. Must have same length as
+ the plan's number of arguments.
+ <literal>n</literal> indicates a null value (entry in
+ <parameter>values</> will be ignored); a space indicates a
+ nonnull value (entry in <parameter>values</> is valid).
+ </para>
+
+ <para>
+ If <parameter>nulls</parameter> is <symbol>NULL</symbol> then
+ <function>SPI_execute_plan</function> assumes that no parameters are
+ null.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>bool <parameter>read_only</parameter></literal></term>
+ <listitem>
+ <para>
+ <literal>true</> for read-only execution
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>long <parameter>count</parameter></literal></term>
+ <listitem>
+ <para>
+ maximum number of rows to process or return
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </refsect1>
+
+ <refsect1>
+ <title>Return Value</title>
+
+ <para>
+ The return value is the same as for <function>SPI_execute</function>,
+ with the following additional possible error (negative) results:
+
+ <variablelist>
+ <varlistentry>
+ <term><symbol>SPI_ERROR_ARGUMENT</symbol></term>
+ <listitem>
+ <para>
+ if <parameter>plan</parameter> is <symbol>NULL</symbol> or invalid,
+ or <parameter>count</parameter> is less than 0
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><symbol>SPI_ERROR_PARAM</symbol></term>
+ <listitem>
+ <para>
+ if <parameter>values</parameter> is <symbol>NULL</symbol> and
+ <parameter>plan</parameter> was prepared with some parameters
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </para>
+
+ <para>
+ <varname>SPI_processed</varname> and
+ <varname>SPI_tuptable</varname> are set as in
+ <function>SPI_execute</function> if successful.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Notes</title>
+
+ <para>
+ If one of the objects (a table, function, etc.) referenced by the
+ prepared plan is dropped during the session then the result of
+ <function>SPI_execute_plan</function> for this plan will be unpredictable.
+ </para>
+ </refsect1>
+</refentry>
+
<!-- *********************************************** -->
+
+<refentry id="spi-spi-execp">
+ <refmeta>
+ <refentrytitle>SPI_execp</refentrytitle>
+ </refmeta>
+
+ <refnamediv>
+ <refname>SPI_execp</refname>
+ <refpurpose>execute a plan in read/write mode</refpurpose>
+ </refnamediv>
+
+ <indexterm><primary>SPI_execp</primary></indexterm>
+
+ <refsynopsisdiv>
+<synopsis>
+int SPI_execp(SPIPlanPtr <parameter>plan</parameter>, Datum * <parameter>values</parameter>, const char * <parameter>nulls</parameter>, long <parameter>count</parameter>)
+</synopsis>
+ </refsynopsisdiv>
+
+ <refsect1>
+ <title>Description</title>
+
+ <para>
+ <function>SPI_execp</function> is the same as
+ <function>SPI_execute_plan</function>, with the latter's
+ <parameter>read_only</parameter> parameter always taken as
+ <literal>false</>.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Arguments</title>
+
+ <variablelist>
+ <varlistentry>
+ <term><literal>SPIPlanPtr <parameter>plan</parameter></literal></term>
+ <listitem>
+ <para>
+ execution plan (returned by <function>SPI_prepare</function>)
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>Datum * <parameter>values</parameter></literal></term>
+ <listitem>
+ <para>
+ An array of actual parameter values. Must have same length as the
+ plan's number of arguments.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>const char * <parameter>nulls</parameter></literal></term>
+ <listitem>
+ <para>
+ An array describing which parameters are null. Must have same length as
+ the plan's number of arguments.
+ <literal>n</literal> indicates a null value (entry in
+ <parameter>values</> will be ignored); a space indicates a
+ nonnull value (entry in <parameter>values</> is valid).
+ </para>
+
+ <para>
+ If <parameter>nulls</parameter> is <symbol>NULL</symbol> then
+ <function>SPI_execp</function> assumes that no parameters are
+ null.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>long <parameter>count</parameter></literal></term>
+ <listitem>
+ <para>
+ maximum number of rows to process or return
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </refsect1>
+
+ <refsect1>
+ <title>Return Value</title>
+
+ <para>
+ See <function>SPI_execute_plan</function>.
+ </para>
+
+ <para>
+ <varname>SPI_processed</varname> and
+ <varname>SPI_tuptable</varname> are set as in
+ <function>SPI_execute</function> if successful.
+ </para>
+ </refsect1>
+</refentry>
+
<!-- *********************************************** -->
-<REFENTRY ID="SPI-SPIGETVALUE">
-<REFMETA>
-<REFENTRYTITLE>SPI_getvalue</REFENTRYTITLE>
-<REFMISCINFO>SPI - Tuple Information</REFMISCINFO>
-</REFMETA>
-<REFNAMEDIV>
-<REFNAME>SPI_getvalue
-</REFNAME>
-<REFPURPOSE>
-Returns the string value of the specified attribute
-</REFPURPOSE>
-<INDEXTERM ID="IX-SPI-SPIGETVALUE-1"><PRIMARY>SPI</PRIMARY><SECONDARY>decoding tuples</SECONDARY></INDEXTERM>
-<INDEXTERM ID="IX-SPI-SPIGETVALUE-2"><PRIMARY>SPI_getvalue</PRIMARY></INDEXTERM>
-</REFNAMEDIV>
-<REFSYNOPSISDIV>
-<REFSYNOPSISDIVINFO>
-<DATE>1997-12-24</DATE>
-</REFSYNOPSISDIVINFO>
-<SYNOPSIS>
-SPI_getvalue(<REPLACEABLE CLASS="PARAMETER">tuple</REPLACEABLE>, <REPLACEABLE CLASS="PARAMETER">tupdesc</REPLACEABLE>, <REPLACEABLE CLASS="PARAMETER">fnumber</REPLACEABLE>)
-</SYNOPSIS>
-
-<REFSECT2 ID="R2-SPI-SPIGETVALUE-1">
-<REFSECT2INFO>
-<DATE>1997-12-24</DATE>
-</REFSECT2INFO>
-<TITLE>Inputs
-</TITLE>
-<VARIABLELIST>
-<VARLISTENTRY>
-<TERM>
-HeapTuple <REPLACEABLE CLASS="PARAMETER">tuple</REPLACEABLE>
-</TERM>
-<LISTITEM>
-<PARA>
-Input tuple to be examined
-</PARA>
-</LISTITEM>
-</VARLISTENTRY>
-<VARLISTENTRY>
-<TERM>
-TupleDesc <REPLACEABLE CLASS="PARAMETER">tupdesc</REPLACEABLE>
-</TERM>
-<LISTITEM>
-<PARA>
-Input tuple description
-</PARA>
-</LISTITEM>
-</VARLISTENTRY>
-<VARLISTENTRY>
-<TERM>
-int <REPLACEABLE CLASS="PARAMETER">fnumber</REPLACEABLE>
-</TERM>
-<LISTITEM>
-<PARA>
-Attribute number
-</PARA>
-</LISTITEM>
-</VARLISTENTRY>
-</VARIABLELIST>
-</REFSECT2>
-
-<REFSECT2 ID="R2-SPI-SPIGETVALUE-2">
-<REFSECT2INFO>
-<DATE>1997-12-24</DATE>
-</REFSECT2INFO>
-<TITLE>Outputs
-</TITLE>
-<VARIABLELIST>
-<VARLISTENTRY>
-<TERM>
-char *
-</TERM>
-<LISTITEM>
-<PARA>
-Attribute value or NULL if
-<SimpleList>
-<Member>
-attribute is NULL
-</Member>
-<Member>
-fnumber is out of range
-(SPI_result set to
-<ReturnValue>SPI_ERROR_NOATTRIBUTE</ReturnValue>)
-</Member>
-<Member>
-no output function available
-(SPI_result set to
-<ReturnValue>SPI_ERROR_NOOUTFUNC</ReturnValue>)
-</Member>
-</SimpleList>
-</para>
-</LISTITEM>
-</VARLISTENTRY>
-</VARIABLELIST>
-</REFSECT2>
-</REFSYNOPSISDIV>
-
-<REFSECT1 ID="R1-SPI-SPIGETVALUE-1">
-<REFSECT1INFO>
-<DATE>1997-12-24</DATE>
-</REFSECT1INFO>
-<TITLE>Description
-</TITLE>
-<PARA>
-<FUNCTION>SPI_getvalue</FUNCTION>
- returns an external (string) representation of the value of the specified attribute.
-</PARA>
-</REFSECT1>
-<REFSECT1 ID="R1-SPI-SPIGETVALUE-2">
-<TITLE>Usage
-</TITLE>
-<Para>
-Attribute numbers are 1 based.
-</PARA>
-</REFSECT1>
-<REFSECT1 ID="R1-SPI-SPIGETVALUE-3">
-<TITLE>Algorithm
-</TITLE>
-<PARA>
-Allocates memory as required by the value.
-</PARA>
-</REFSECT1>
-<!--
-<REFSECT1 ID="R1-SPI-SPIGETVALUE-4">
-<TITLE>Structures
-</TITLE>
-<PARA>None
-</PARA>
-</REFSECT1>
--->
-</REFENTRY>
+<refentry id="spi-spi-cursor-open">
+ <refmeta>
+ <refentrytitle>SPI_cursor_open</refentrytitle>
+ </refmeta>
+
+ <refnamediv>
+ <refname>SPI_cursor_open</refname>
+ <refpurpose>set up a cursor using a plan created with <function>SPI_prepare</function></refpurpose>
+ </refnamediv>
+
+ <indexterm><primary>SPI_cursor_open</primary></indexterm>
+
+ <refsynopsisdiv>
+<synopsis>
+Portal SPI_cursor_open(const char * <parameter>name</parameter>, SPIPlanPtr <parameter>plan</parameter>,
+ Datum * <parameter>values</parameter>, const char * <parameter>nulls</parameter>,
+ bool <parameter>read_only</parameter>)
+</synopsis>
+ </refsynopsisdiv>
+
+ <refsect1>
+ <title>Description</title>
+
+ <para>
+ <function>SPI_cursor_open</function> sets up a cursor (internally,
+ a portal) that will execute a plan prepared by
+ <function>SPI_prepare</function>. The parameters have the same
+ meanings as the corresponding parameters to
+ <function>SPI_execute_plan</function>.
+ </para>
+
+ <para>
+ Using a cursor instead of executing the plan directly has two
+ benefits. First, the result rows can be retrieved a few at a time,
+ avoiding memory overrun for queries that return many rows. Second,
+ a portal can outlive the current procedure (it can, in fact, live
+ to the end of the current transaction). Returning the portal name
+ to the procedure's caller provides a way of returning a row set as
+ result.
+ </para>
+
+ <para>
+ The passed-in data will be copied into the cursor's portal, so it
+ can be freed while the cursor still exists.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Arguments</title>
+
+ <variablelist>
+ <varlistentry>
+ <term><literal>const char * <parameter>name</parameter></literal></term>
+ <listitem>
+ <para>
+ name for portal, or <symbol>NULL</symbol> to let the system
+ select a name
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>SPIPlanPtr <parameter>plan</parameter></literal></term>
+ <listitem>
+ <para>
+ execution plan (returned by <function>SPI_prepare</function>)
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>Datum * <parameter>values</parameter></literal></term>
+ <listitem>
+ <para>
+ An array of actual parameter values. Must have same length as the
+ plan's number of arguments.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>const char * <parameter>nulls</parameter></literal></term>
+ <listitem>
+ <para>
+ An array describing which parameters are null. Must have same length as
+ the plan's number of arguments.
+ <literal>n</literal> indicates a null value (entry in
+ <parameter>values</> will be ignored); a space indicates a
+ nonnull value (entry in <parameter>values</> is valid).
+ </para>
+
+ <para>
+ If <parameter>nulls</parameter> is <symbol>NULL</symbol> then
+ <function>SPI_cursor_open</function> assumes that no parameters are
+ null.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>bool <parameter>read_only</parameter></literal></term>
+ <listitem>
+ <para>
+ <literal>true</> for read-only execution
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </refsect1>
+
+ <refsect1>
+ <title>Return Value</title>
+
+ <para>
+ pointer to portal containing the cursor, or <symbol>NULL</symbol>
+ on error
+ </para>
+ </refsect1>
+</refentry>
<!-- *********************************************** -->
+
+<refentry id="spi-spi-cursor-find">
+ <refmeta>
+ <refentrytitle>SPI_cursor_find</refentrytitle>
+ </refmeta>
+
+ <refnamediv>
+ <refname>SPI_cursor_find</refname>
+ <refpurpose>find an existing cursor by name</refpurpose>
+ </refnamediv>
+
+ <indexterm><primary>SPI_cursor_find</primary></indexterm>
+
+ <refsynopsisdiv>
+<synopsis>
+Portal SPI_cursor_find(const char * <parameter>name</parameter>)
+</synopsis>
+ </refsynopsisdiv>
+
+ <refsect1>
+ <title>Description</title>
+
+ <para>
+ <function>SPI_cursor_find</function> finds an existing portal by
+ name. This is primarily useful to resolve a cursor name returned
+ as text by some other function.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Arguments</title>
+
+ <variablelist>
+ <varlistentry>
+ <term><literal>const char * <parameter>name</parameter></literal></term>
+ <listitem>
+ <para>
+ name of the portal
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </refsect1>
+
+ <refsect1>
+ <title>Return Value</title>
+
+ <para>
+ pointer to the portal with the specified name, or
+ <symbol>NULL</symbol> if none was found
+ </para>
+ </refsect1>
+</refentry>
+
<!-- *********************************************** -->
+
+<refentry id="spi-spi-cursor-fetch">
+ <refmeta>
+ <refentrytitle>SPI_cursor_fetch</refentrytitle>
+ </refmeta>
+
+ <refnamediv>
+ <refname>SPI_cursor_fetch</refname>
+ <refpurpose>fetch some rows from a cursor</refpurpose>
+ </refnamediv>
+
+ <indexterm><primary>SPI_cursor_fetch</primary></indexterm>
+
+ <refsynopsisdiv>
+<synopsis>
+void SPI_cursor_fetch(Portal <parameter>portal</parameter>, bool <parameter>forward</parameter>, long <parameter>count</parameter>)
+</synopsis>
+ </refsynopsisdiv>
+
+ <refsect1>
+ <title>Description</title>
+
+ <para>
+ <function>SPI_cursor_fetch</function> fetches some rows from a
+ cursor. This is equivalent to a subset of the SQL command
+ <command>FETCH</> (see <function>SPI_scroll_cursor_fetch</function>
+ for more functionality).
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Arguments</title>
+
+ <variablelist>
+ <varlistentry>
+ <term><literal>Portal <parameter>portal</parameter></literal></term>
+ <listitem>
+ <para>
+ portal containing the cursor
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>bool <parameter>forward</parameter></literal></term>
+ <listitem>
+ <para>
+ true for fetch forward, false for fetch backward
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>long <parameter>count</parameter></literal></term>
+ <listitem>
+ <para>
+ maximum number of rows to fetch
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </refsect1>
+
+ <refsect1>
+ <title>Return Value</title>
+
+ <para>
+ <varname>SPI_processed</varname> and
+ <varname>SPI_tuptable</varname> are set as in
+ <function>SPI_execute</function> if successful.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Notes</title>
+
+ <para>
+ Fetching backward may fail if the cursor's plan was not created
+ with the <symbol>CURSOR_OPT_SCROLL</symbol> option.
+ </para>
+ </refsect1>
+</refentry>
+
<!-- *********************************************** -->
-<REFENTRY ID="SPI-SPIGETBINVAL">
-<REFMETA>
-<REFENTRYTITLE>SPI_getbinval</REFENTRYTITLE>
-<REFMISCINFO>SPI - Tuple Information</REFMISCINFO>
-</REFMETA>
-<REFNAMEDIV>
-<REFNAME>SPI_getbinval
-</REFNAME>
-<REFPURPOSE>
-Returns the binary value of the specified attribute
-</REFPURPOSE>
-<INDEXTERM ID="IX-SPI-SPIGETBINVAL-1"><PRIMARY>SPI</PRIMARY><SECONDARY>decoding tuples</SECONDARY></INDEXTERM>
-<INDEXTERM ID="IX-SPI-SPIGETBINVAL-2"><PRIMARY>SPI_getbinval</PRIMARY></INDEXTERM>
-</REFNAMEDIV>
-<REFSYNOPSISDIV>
-<REFSYNOPSISDIVINFO>
-<DATE>1997-12-24</DATE>
-</REFSYNOPSISDIVINFO>
-<SYNOPSIS>
-SPI_getbinval(<REPLACEABLE CLASS="PARAMETER">tuple</REPLACEABLE>, <REPLACEABLE CLASS="PARAMETER">tupdesc</REPLACEABLE>, <REPLACEABLE CLASS="PARAMETER">fnumber</REPLACEABLE>, <REPLACEABLE CLASS="PARAMETER">isnull</REPLACEABLE>)
-</SYNOPSIS>
-
-<REFSECT2 ID="R2-SPI-SPIGETBINVAL-1">
-<REFSECT2INFO>
-<DATE>1997-12-24</DATE>
-</REFSECT2INFO>
-<TITLE>Inputs
-</TITLE>
-<VARIABLELIST>
-<VARLISTENTRY>
-<TERM>
-HeapTuple <REPLACEABLE CLASS="PARAMETER">tuple</REPLACEABLE>
-</TERM>
-<LISTITEM>
-<PARA>
-Input tuple to be examined
-</PARA>
-</LISTITEM>
-</VARLISTENTRY>
-<VARLISTENTRY>
-<TERM>
-TupleDesc <REPLACEABLE CLASS="PARAMETER">tupdesc</REPLACEABLE>
-</TERM>
-<LISTITEM>
-<PARA>
-Input tuple description
-</PARA>
-</LISTITEM>
-</VARLISTENTRY>
-<VARLISTENTRY>
-<TERM>
-int <REPLACEABLE CLASS="PARAMETER">fnumber</REPLACEABLE>
-</TERM>
-<LISTITEM>
-<PARA>
-Attribute number
-</PARA>
-</LISTITEM>
-</VARLISTENTRY>
-</VARIABLELIST>
-</REFSECT2>
-
-<REFSECT2 ID="R2-SPI-SPIGETBINVAL-2">
-<REFSECT2INFO>
-<DATE>1997-12-24</DATE>
-</REFSECT2INFO>
-<TITLE>Outputs
-</TITLE>
-<VARIABLELIST>
-<VARLISTENTRY>
-<TERM>
-Datum
-</TERM>
-<LISTITEM>
-<PARA>
-Attribute binary value
-</PARA>
-</LISTITEM>
-</VARLISTENTRY>
-<VARLISTENTRY>
-<TERM>
-bool * <REPLACEABLE CLASS="PARAMETER">isnull</REPLACEABLE>
-</TERM>
-<LISTITEM>
-<PARA>
-flag for null value in attribute
-</PARA>
-</LISTITEM>
-</VARLISTENTRY>
-<VARLISTENTRY>
-<TERM>
-SPI_result
-</TERM>
-<LISTITEM>
-<PARA>
-<SimpleList>
-<Member>
-<ReturnValue>SPI_ERROR_NOATTRIBUTE</ReturnValue>
-</Member>
-</SimpleList>
-</PARA>
-</LISTITEM>
-</VARLISTENTRY>
-</VARIABLELIST>
-</REFSECT2>
-</REFSYNOPSISDIV>
-
-<REFSECT1 ID="R1-SPI-SPIGETBINVAL-1">
-<REFSECT1INFO>
-<DATE>1997-12-24</DATE>
-</REFSECT1INFO>
-<TITLE>Description
-</TITLE>
-<PARA>
-<FUNCTION>SPI_getbinval</FUNCTION>
- returns the binary value of the specified attribute.
-</PARA>
-</REFSECT1>
-<REFSECT1 ID="R1-SPI-SPIGETBINVAL-2">
-<TITLE>Usage
-</TITLE>
-<Para>
-Attribute numbers are 1 based.
-</PARA>
-</REFSECT1>
-<REFSECT1 ID="R1-SPI-SPIGETBINVAL-3">
-<TITLE>Algorithm
-</TITLE>
-<PARA>
-Does not allocate new space for the binary value.
-</PARA>
-</REFSECT1>
-<!--
-<REFSECT1 ID="R1-SPI-SPIGETBINVAL-4">
-<TITLE>Structures
-</TITLE>
-<PARA>None
-</PARA>
-</REFSECT1>
--->
-</REFENTRY>
+<refentry id="spi-spi-cursor-move">
+ <refmeta>
+ <refentrytitle>SPI_cursor_move</refentrytitle>
+ </refmeta>
+
+ <refnamediv>
+ <refname>SPI_cursor_move</refname>
+ <refpurpose>move a cursor</refpurpose>
+ </refnamediv>
+
+ <indexterm><primary>SPI_cursor_move</primary></indexterm>
+
+ <refsynopsisdiv>
+<synopsis>
+void SPI_cursor_move(Portal <parameter>portal</parameter>, bool <parameter>forward</parameter>, long <parameter>count</parameter>)
+</synopsis>
+ </refsynopsisdiv>
+
+ <refsect1>
+ <title>Description</title>
+
+ <para>
+ <function>SPI_cursor_move</function> skips over some number of rows
+ in a cursor. This is equivalent to a subset of the SQL command
+ <command>MOVE</> (see <function>SPI_scroll_cursor_move</function>
+ for more functionality).
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Arguments</title>
+
+ <variablelist>
+ <varlistentry>
+ <term><literal>Portal <parameter>portal</parameter></literal></term>
+ <listitem>
+ <para>
+ portal containing the cursor
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>bool <parameter>forward</parameter></literal></term>
+ <listitem>
+ <para>
+ true for move forward, false for move backward
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>long <parameter>count</parameter></literal></term>
+ <listitem>
+ <para>
+ maximum number of rows to move
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </refsect1>
+
+ <refsect1>
+ <title>Notes</title>
+
+ <para>
+ Moving backward may fail if the cursor's plan was not created
+ with the <symbol>CURSOR_OPT_SCROLL</symbol> option.
+ </para>
+ </refsect1>
+</refentry>
<!-- *********************************************** -->
+
+<refentry id="spi-spi-scroll-cursor-fetch">
+ <refmeta>
+ <refentrytitle>SPI_scroll_cursor_fetch</refentrytitle>
+ </refmeta>
+
+ <refnamediv>
+ <refname>SPI_scroll_cursor_fetch</refname>
+ <refpurpose>fetch some rows from a cursor</refpurpose>
+ </refnamediv>
+
+ <indexterm><primary>SPI_scroll_cursor_fetch</primary></indexterm>
+
+ <refsynopsisdiv>
+<synopsis>
+void SPI_scroll_cursor_fetch(Portal <parameter>portal</parameter>, FetchDirection <parameter>direction</parameter>, long <parameter>count</parameter>)
+</synopsis>
+ </refsynopsisdiv>
+
+ <refsect1>
+ <title>Description</title>
+
+ <para>
+ <function>SPI_scroll_cursor_fetch</function> fetches some rows from a
+ cursor. This is equivalent to the SQL command <command>FETCH</>.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Arguments</title>
+
+ <variablelist>
+ <varlistentry>
+ <term><literal>Portal <parameter>portal</parameter></literal></term>
+ <listitem>
+ <para>
+ portal containing the cursor
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>FetchDirection <parameter>direction</parameter></literal></term>
+ <listitem>
+ <para>
+ one of <symbol>FETCH_FORWARD</symbol>,
+ <symbol>FETCH_BACKWARD</symbol>,
+ <symbol>FETCH_ABSOLUTE</symbol> or
+ <symbol>FETCH_RELATIVE</symbol>
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>long <parameter>count</parameter></literal></term>
+ <listitem>
+ <para>
+ number of rows to fetch for
+ <symbol>FETCH_FORWARD</symbol> or
+ <symbol>FETCH_BACKWARD</symbol>; absolute row number to fetch for
+ <symbol>FETCH_ABSOLUTE</symbol>; or relative row number to fetch for
+ <symbol>FETCH_RELATIVE</symbol>
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </refsect1>
+
+ <refsect1>
+ <title>Return Value</title>
+
+ <para>
+ <varname>SPI_processed</varname> and
+ <varname>SPI_tuptable</varname> are set as in
+ <function>SPI_execute</function> if successful.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Notes</title>
+
+ <para>
+ See the SQL <xref linkend="sql-fetch" endterm="sql-fetch-title"> command
+ for details of the interpretation of the
+ <parameter>direction</parameter> and
+ <parameter>count</parameter> parameters.
+ </para>
+
+ <para>
+ Direction values other than <symbol>FETCH_FORWARD</symbol>
+ may fail if the cursor's plan was not created
+ with the <symbol>CURSOR_OPT_SCROLL</symbol> option.
+ </para>
+ </refsect1>
+</refentry>
+
<!-- *********************************************** -->
+
+<refentry id="spi-spi-scroll-cursor-move">
+ <refmeta>
+ <refentrytitle>SPI_scroll_cursor_move</refentrytitle>
+ </refmeta>
+
+ <refnamediv>
+ <refname>SPI_scroll_cursor_move</refname>
+ <refpurpose>move a cursor</refpurpose>
+ </refnamediv>
+
+ <indexterm><primary>SPI_scroll_cursor_move</primary></indexterm>
+
+ <refsynopsisdiv>
+<synopsis>
+void SPI_scroll_cursor_move(Portal <parameter>portal</parameter>, FetchDirection <parameter>direction</parameter>, long <parameter>count</parameter>)
+</synopsis>
+ </refsynopsisdiv>
+
+ <refsect1>
+ <title>Description</title>
+
+ <para>
+ <function>SPI_scroll_cursor_move</function> skips over some number of rows
+ in a cursor. This is equivalent to the SQL command
+ <command>MOVE</>.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Arguments</title>
+
+ <variablelist>
+ <varlistentry>
+ <term><literal>Portal <parameter>portal</parameter></literal></term>
+ <listitem>
+ <para>
+ portal containing the cursor
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>FetchDirection <parameter>direction</parameter></literal></term>
+ <listitem>
+ <para>
+ one of <symbol>FETCH_FORWARD</symbol>,
+ <symbol>FETCH_BACKWARD</symbol>,
+ <symbol>FETCH_ABSOLUTE</symbol> or
+ <symbol>FETCH_RELATIVE</symbol>
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>long <parameter>count</parameter></literal></term>
+ <listitem>
+ <para>
+ number of rows to move for
+ <symbol>FETCH_FORWARD</symbol> or
+ <symbol>FETCH_BACKWARD</symbol>; absolute row number to move to for
+ <symbol>FETCH_ABSOLUTE</symbol>; or relative row number to move to for
+ <symbol>FETCH_RELATIVE</symbol>
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </refsect1>
+
+ <refsect1>
+ <title>Return Value</title>
+
+ <para>
+ <varname>SPI_processed</varname> and
+ <varname>SPI_tuptable</varname> are set as in
+ <function>SPI_execute</function> if successful.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Notes</title>
+
+ <para>
+ See the SQL <xref linkend="sql-fetch" endterm="sql-fetch-title"> command
+ for details of the interpretation of the
+ <parameter>direction</parameter> and
+ <parameter>count</parameter> parameters.
+ </para>
+
+ <para>
+ Direction values other than <symbol>FETCH_FORWARD</symbol>
+ may fail if the cursor's plan was not created
+ with the <symbol>CURSOR_OPT_SCROLL</symbol> option.
+ </para>
+ </refsect1>
+</refentry>
+
<!-- *********************************************** -->
-<REFENTRY ID="SPI-SPIGETTYPE">
-<REFMETA>
-<REFENTRYTITLE>SPI_gettype</REFENTRYTITLE>
-<REFMISCINFO>SPI - Tuple Information</REFMISCINFO>
-</REFMETA>
-<REFNAMEDIV>
-<REFNAME>SPI_gettype
-</REFNAME>
-<REFPURPOSE>
-Returns the type name of the specified attribute
-</REFPURPOSE>
-<INDEXTERM ID="IX-SPI-SPIGETTYPE-1"><PRIMARY>SPI</PRIMARY><SECONDARY>decoding tuples</SECONDARY></INDEXTERM>
-<INDEXTERM ID="IX-SPI-SPIGETTYPE-2"><PRIMARY>SPI_gettype</PRIMARY></INDEXTERM>
-</REFNAMEDIV>
-<REFSYNOPSISDIV>
-<REFSYNOPSISDIVINFO>
-<DATE>1997-12-24</DATE>
-</REFSYNOPSISDIVINFO>
-<SYNOPSIS>
-SPI_gettype(<REPLACEABLE CLASS="PARAMETER">tupdesc</REPLACEABLE>, <REPLACEABLE CLASS="PARAMETER">fnumber</REPLACEABLE>)
-</SYNOPSIS>
-
-<REFSECT2 ID="R2-SPI-SPIGETTYPE-1">
-<REFSECT2INFO>
-<DATE>1997-12-24</DATE>
-</REFSECT2INFO>
-<TITLE>Inputs
-</TITLE>
-<VARIABLELIST>
-<VARLISTENTRY>
-<TERM>
-TupleDesc <REPLACEABLE CLASS="PARAMETER">tupdesc</REPLACEABLE>
-</TERM>
-<LISTITEM>
-<PARA>
-Input tuple description
-</PARA>
-</LISTITEM>
-</VARLISTENTRY>
-<VARLISTENTRY>
-<TERM>
-int <REPLACEABLE CLASS="PARAMETER">fnumber</REPLACEABLE>
-</TERM>
-<LISTITEM>
-<PARA>
-Attribute number
-</PARA>
-</LISTITEM>
-</VARLISTENTRY>
-</VARIABLELIST>
-</REFSECT2>
-
-<REFSECT2 ID="R2-SPI-SPIGETTYPE-2">
-<REFSECT2INFO>
-<DATE>1997-12-24</DATE>
-</REFSECT2INFO>
-<TITLE>Outputs
-</TITLE>
-<VARIABLELIST>
-<VARLISTENTRY>
-<TERM>
-char *
-</TERM>
-<LISTITEM>
-<PARA>
-The type name for the specified attribute number
-</PARA>
-</LISTITEM>
-</VARLISTENTRY>
-<VARLISTENTRY>
-<TERM>
-SPI_result
-</TERM>
-<LISTITEM>
-<PARA>
-<SimpleList>
-<Member>
-<ReturnValue>SPI_ERROR_NOATTRIBUTE</ReturnValue>
-</Member>
-</SimpleList>
-</PARA>
-</LISTITEM>
-</VARLISTENTRY>
-</VARIABLELIST>
-</REFSECT2>
-</REFSYNOPSISDIV>
-
-<REFSECT1 ID="R1-SPI-SPIGETTYPE-1">
-<REFSECT1INFO>
-<DATE>1997-12-24</DATE>
-</REFSECT1INFO>
-<TITLE>Description
-</TITLE>
-<PARA>
-<FUNCTION>SPI_gettype</FUNCTION>
- returns a copy of the type name for the specified attribute.
-</PARA>
-</REFSECT1>
-<REFSECT1 ID="R1-SPI-SPIGETTYPE-2">
-<TITLE>Usage
-</TITLE>
-<Para>
-Attribute numbers are 1 based.
-</PARA>
-</REFSECT1>
-<REFSECT1 ID="R1-SPI-SPIGETTYPE-3">
-<TITLE>Algorithm
-</TITLE>
-<PARA>
-Does not allocate new space for the binary value.
-</PARA>
-</REFSECT1>
-<!--
-<REFSECT1 ID="R1-SPI-SPIGETTYPE-4">
-<TITLE>Structures
-</TITLE>
-<PARA>None
-</PARA>
-</REFSECT1>
--->
-</REFENTRY>
+<refentry id="spi-spi-cursor-close">
+ <refmeta>
+ <refentrytitle>SPI_cursor_close</refentrytitle>
+ </refmeta>
+
+ <refnamediv>
+ <refname>SPI_cursor_close</refname>
+ <refpurpose>close a cursor</refpurpose>
+ </refnamediv>
+
+ <indexterm><primary>SPI_cursor_close</primary></indexterm>
+
+ <refsynopsisdiv>
+<synopsis>
+void SPI_cursor_close(Portal <parameter>portal</parameter>)
+</synopsis>
+ </refsynopsisdiv>
+
+ <refsect1>
+ <title>Description</title>
+
+ <para>
+ <function>SPI_cursor_close</function> closes a previously created
+ cursor and releases its portal storage.
+ </para>
+
+ <para>
+ All open cursors are closed automatically at the end of a
+ transaction. <function>SPI_cursor_close</function> need only be
+ invoked if it is desirable to release resources sooner.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Arguments</title>
+
+ <variablelist>
+ <varlistentry>
+ <term><literal>Portal <parameter>portal</parameter></literal></term>
+ <listitem>
+ <para>
+ portal containing the cursor
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </refsect1>
+</refentry>
<!-- *********************************************** -->
+
+<refentry id="spi-spi-saveplan">
+ <refmeta>
+ <refentrytitle>SPI_saveplan</refentrytitle>
+ </refmeta>
+
+ <refnamediv>
+ <refname>SPI_saveplan</refname>
+ <refpurpose>save a plan</refpurpose>
+ </refnamediv>
+
+ <indexterm><primary>SPI_saveplan</primary></indexterm>
+
+ <refsynopsisdiv>
+<synopsis>
+SPIPlanPtr SPI_saveplan(SPIPlanPtr <parameter>plan</parameter>)
+</synopsis>
+ </refsynopsisdiv>
+
+ <refsect1>
+ <title>Description</title>
+
+ <para>
+ <function>SPI_saveplan</function> saves a passed plan (prepared by
+ <function>SPI_prepare</function>) in memory that will not be freed
+ by <function>SPI_finish</function> nor by the transaction manager,
+ and returns a pointer to the saved plan. This gives you the
+ ability to reuse prepared plans in the subsequent invocations of
+ your procedure in the current session.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Arguments</title>
+
+ <variablelist>
+ <varlistentry>
+ <term><literal>SPIPlanPtr <parameter>plan</parameter></literal></term>
+ <listitem>
+ <para>
+ the plan to be saved
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </refsect1>
+
+ <refsect1>
+ <title>Return Value</title>
+
+ <para>
+ Pointer to the saved plan; <symbol>NULL</symbol> if unsuccessful.
+ On error, <varname>SPI_result</varname> is set thus:
+
+ <variablelist>
+ <varlistentry>
+ <term><symbol>SPI_ERROR_ARGUMENT</symbol></term>
+ <listitem>
+ <para>
+ if <parameter>plan</parameter> is <symbol>NULL</symbol> or invalid
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><symbol>SPI_ERROR_UNCONNECTED</symbol></term>
+ <listitem>
+ <para>
+ if called from an unconnected procedure
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Notes</title>
+
+ <para>
+ The passed-in plan is not freed, so you might wish to do
+ <function>SPI_freeplan</function> on it to avoid leaking memory
+ until <function>SPI_finish</>.
+ </para>
+
+ <para>
+ If one of the objects (a table, function, etc.) referenced by the
+ prepared plan is dropped or redefined, then future executions of
+ <function>SPI_execute_plan</function> may fail or return different
+ results than the plan initially indicates.
+ </para>
+ </refsect1>
+</refentry>
+
+</sect1>
+
+<sect1 id="spi-interface-support">
+ <title>Interface Support Functions</title>
+
+ <para>
+ The functions described here provide an interface for extracting
+ information from result sets returned by <function>SPI_execute</> and
+ other SPI functions.
+ </para>
+
+ <para>
+ All functions described in this section can be used by both
+ connected and unconnected procedures.
+ </para>
+
<!-- *********************************************** -->
+
+<refentry id="spi-spi-fname">
+ <refmeta>
+ <refentrytitle>SPI_fname</refentrytitle>
+ </refmeta>
+
+ <refnamediv>
+ <refname>SPI_fname</refname>
+ <refpurpose>determine the column name for the specified column number</refpurpose>
+ </refnamediv>
+
+ <indexterm><primary>SPI_fname</primary></indexterm>
+
+ <refsynopsisdiv>
+<synopsis>
+char * SPI_fname(TupleDesc <parameter>rowdesc</parameter>, int <parameter>colnumber</parameter>)
+</synopsis>
+ </refsynopsisdiv>
+
+ <refsect1>
+ <title>Description</title>
+
+ <para>
+ <function>SPI_fname</function> returns a copy of the column name of the
+ specified column. (You can use <function>pfree</function> to
+ release the copy of the name when you don't need it anymore.)
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Arguments</title>
+
+ <variablelist>
+ <varlistentry>
+ <term><literal>TupleDesc <parameter>rowdesc</parameter></literal></term>
+ <listitem>
+ <para>
+ input row description
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>int <parameter>colnumber</parameter></literal></term>
+ <listitem>
+ <para>
+ column number (count starts at 1)
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </refsect1>
+
+ <refsect1>
+ <title>Return Value</title>
+
+ <para>
+ The column name; <symbol>NULL</symbol> if
+ <parameter>colnumber</parameter> is out of range.
+ <varname>SPI_result</varname> set to
+ <symbol>SPI_ERROR_NOATTRIBUTE</symbol> on error.
+ </para>
+ </refsect1>
+</refentry>
+
<!-- *********************************************** -->
-<REFENTRY ID="SPI-SPIGETTYPEID">
-<REFMETA>
-<REFENTRYTITLE>SPI_gettypeid</REFENTRYTITLE>
-<REFMISCINFO>SPI - Tuple Information</REFMISCINFO>
-</REFMETA>
-<REFNAMEDIV>
-<REFNAME>SPI_gettypeid
-</REFNAME>
-<REFPURPOSE>
-Returns the type <Acronym>OID</Acronym> of the specified attribute
-</REFPURPOSE>
-<INDEXTERM ID="IX-SPI-SPIGETTYPEID-1"><PRIMARY>SPI</PRIMARY><SECONDARY>decoding tuples</SECONDARY></INDEXTERM>
-<INDEXTERM ID="IX-SPI-SPIGETTYPEID-2"><PRIMARY>SPI_gettypeid</PRIMARY></INDEXTERM>
-</REFNAMEDIV>
-<REFSYNOPSISDIV>
-<REFSYNOPSISDIVINFO>
-<DATE>1997-12-24</DATE>
-</REFSYNOPSISDIVINFO>
-<SYNOPSIS>
-SPI_gettypeid(<REPLACEABLE CLASS="PARAMETER">tupdesc</REPLACEABLE>, <REPLACEABLE CLASS="PARAMETER">fnumber</REPLACEABLE>)
-</SYNOPSIS>
-
-<REFSECT2 ID="R2-SPI-SPIGETTYPEID-1">
-<REFSECT2INFO>
-<DATE>1997-12-24</DATE>
-</REFSECT2INFO>
-<TITLE>Inputs
-</TITLE>
-<VARIABLELIST>
-<VARLISTENTRY>
-<TERM>
-TupleDesc <REPLACEABLE CLASS="PARAMETER">tupdesc</REPLACEABLE>
-</TERM>
-<LISTITEM>
-<PARA>
-Input tuple description
-</PARA>
-</LISTITEM>
-</VARLISTENTRY>
-<VARLISTENTRY>
-<TERM>
-int <REPLACEABLE CLASS="PARAMETER">fnumber</REPLACEABLE>
-</TERM>
-<LISTITEM>
-<PARA>
-Attribute number
-</PARA>
-</LISTITEM>
-</VARLISTENTRY>
-</VARIABLELIST>
-</REFSECT2>
-
-<REFSECT2 ID="R2-SPI-SPIGETTYPEID-2">
-<REFSECT2INFO>
-<DATE>1997-12-24</DATE>
-</REFSECT2INFO>
-<TITLE>Outputs
-</TITLE>
-<VARIABLELIST>
-<VARLISTENTRY>
-<TERM>
-<Acronym>OID</Acronym>
-</TERM>
-<LISTITEM>
-<PARA>
-The type <Acronym>OID</Acronym> for the specified attribute number
-</PARA>
-</LISTITEM>
-</VARLISTENTRY>
-<VARLISTENTRY>
-<TERM>
-SPI_result
-</TERM>
-<LISTITEM>
-<PARA>
-<SimpleList>
-<Member>
-<ReturnValue>SPI_ERROR_NOATTRIBUTE</ReturnValue>
-</Member>
-</SimpleList>
-</PARA>
-</LISTITEM>
-</VARLISTENTRY>
-</VARIABLELIST>
-</REFSECT2>
-</REFSYNOPSISDIV>
-
-<REFSECT1 ID="R1-SPI-SPIGETTYPEID-1">
-<REFSECT1INFO>
-<DATE>1997-12-24</DATE>
-</REFSECT1INFO>
-<TITLE>Description
-</TITLE>
-<PARA>
-<FUNCTION>SPI_gettypeid</FUNCTION>
- returns the type <Acronym>OID</Acronym> for the specified attribute.
-</PARA>
-</REFSECT1>
-<REFSECT1 ID="R1-SPI-SPIGETTYPEID-2">
-<TITLE>Usage
-</TITLE>
-<Para>
-Attribute numbers are 1 based.
-</PARA>
-</REFSECT1>
-<REFSECT1 ID="R1-SPI-SPIGETTYPEID-3">
-<TITLE>Algorithm
-</TITLE>
-<PARA>
-TBD
-</PARA>
-</REFSECT1>
-<!--
-<REFSECT1 ID="R1-SPI-SPIGETTYPEID-4">
-<TITLE>Structures
-</TITLE>
-<PARA>None
-</PARA>
-</REFSECT1>
--->
-</REFENTRY>
+<refentry id="spi-spi-fnumber">
+ <refmeta>
+ <refentrytitle>SPI_fnumber</refentrytitle>
+ </refmeta>
+
+ <refnamediv>
+ <refname>SPI_fnumber</refname>
+ <refpurpose>determine the column number for the specified column name</refpurpose>
+ </refnamediv>
+
+ <indexterm><primary>SPI_fnumber</primary></indexterm>
+
+ <refsynopsisdiv>
+<synopsis>
+int SPI_fnumber(TupleDesc <parameter>rowdesc</parameter>, const char * <parameter>colname</parameter>)
+</synopsis>
+ </refsynopsisdiv>
+
+ <refsect1>
+ <title>Description</title>
+
+ <para>
+ <function>SPI_fnumber</function> returns the column number for the
+ column with the specified name.
+ </para>
+
+ <para>
+ If <parameter>colname</parameter> refers to a system column (e.g.,
+ <literal>oid</>) then the appropriate negative column number will
+ be returned. The caller should be careful to test the return value
+ for exact equality to <symbol>SPI_ERROR_NOATTRIBUTE</symbol> to
+ detect an error; testing the result for less than or equal to 0 is
+ not correct unless system columns should be rejected.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Arguments</title>
+
+ <variablelist>
+ <varlistentry>
+ <term><literal>TupleDesc <parameter>rowdesc</parameter></literal></term>
+ <listitem>
+ <para>
+ input row description
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>const char * <parameter>colname</parameter></literal></term>
+ <listitem>
+ <para>
+ column name
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </refsect1>
+
+ <refsect1>
+ <title>Return Value</title>
+
+ <para>
+ Column number (count starts at 1), or
+ <symbol>SPI_ERROR_NOATTRIBUTE</symbol> if the named column was not
+ found.
+ </para>
+ </refsect1>
+</refentry>
<!-- *********************************************** -->
+
+<refentry id="spi-spi-getvalue">
+ <refmeta>
+ <refentrytitle>SPI_getvalue</refentrytitle>
+ </refmeta>
+
+ <refnamediv>
+ <refname>SPI_getvalue</refname>
+ <refpurpose>return the string value of the specified column</refpurpose>
+ </refnamediv>
+
+ <indexterm><primary>SPI_getvalue</primary></indexterm>
+
+ <refsynopsisdiv>
+<synopsis>
+char * SPI_getvalue(HeapTuple <parameter>row</parameter>, TupleDesc <parameter>rowdesc</parameter>, int <parameter>colnumber</parameter>)
+</synopsis>
+ </refsynopsisdiv>
+
+ <refsect1>
+ <title>Description</title>
+
+ <para>
+ <function>SPI_getvalue</function> returns the string representation
+ of the value of the specified column.
+ </para>
+
+ <para>
+ The result is returned in memory allocated using
+ <function>palloc</function>. (You can use
+ <function>pfree</function> to release the memory when you don't
+ need it anymore.)
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Arguments</title>
+
+ <variablelist>
+ <varlistentry>
+ <term><literal>HeapTuple <parameter>row</parameter></literal></term>
+ <listitem>
+ <para>
+ input row to be examined
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>TupleDesc <parameter>rowdesc</parameter></literal></term>
+ <listitem>
+ <para>
+ input row description
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>int <parameter>colnumber</parameter></literal></term>
+ <listitem>
+ <para>
+ column number (count starts at 1)
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </refsect1>
+
+ <refsect1>
+ <title>Return Value</title>
+
+ <para>
+ Column value, or <symbol>NULL</symbol> if the column is null,
+ <parameter>colnumber</parameter> is out of range
+ (<varname>SPI_result</varname> is set to
+ <symbol>SPI_ERROR_NOATTRIBUTE</symbol>), or no no output function
+ available (<varname>SPI_result</varname> is set to
+ <symbol>SPI_ERROR_NOOUTFUNC</symbol>).
+ </para>
+ </refsect1>
+</refentry>
+
<!-- *********************************************** -->
+
+<refentry id="spi-spi-getbinval">
+ <refmeta>
+ <refentrytitle>SPI_getbinval</refentrytitle>
+ </refmeta>
+
+ <refnamediv>
+ <refname>SPI_getbinval</refname>
+ <refpurpose>return the binary value of the specified column</refpurpose>
+ </refnamediv>
+
+ <indexterm><primary>SPI_getbinval</primary></indexterm>
+
+ <refsynopsisdiv>
+<synopsis>
+Datum SPI_getbinval(HeapTuple <parameter>row</parameter>, TupleDesc <parameter>rowdesc</parameter>, int <parameter>colnumber</parameter>, bool * <parameter>isnull</parameter>)
+</synopsis>
+ </refsynopsisdiv>
+
+ <refsect1>
+ <title>Description</title>
+
+ <para>
+ <function>SPI_getbinval</function> returns the value of the
+ specified column in the internal form (as type <type>Datum</type>).
+ </para>
+
+ <para>
+ This function does not allocate new space for the datum. In the
+ case of a pass-by-reference data type, the return value will be a
+ pointer into the passed row.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Arguments</title>
+
+ <variablelist>
+ <varlistentry>
+ <term><literal>HeapTuple <parameter>row</parameter></literal></term>
+ <listitem>
+ <para>
+ input row to be examined
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>TupleDesc <parameter>rowdesc</parameter></literal></term>
+ <listitem>
+ <para>
+ input row description
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>int <parameter>colnumber</parameter></literal></term>
+ <listitem>
+ <para>
+ column number (count starts at 1)
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>bool * <parameter>isnull</parameter></literal></term>
+ <listitem>
+ <para>
+ flag for a null value in the column
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </refsect1>
+
+ <refsect1>
+ <title>Return Value</title>
+
+ <para>
+ The binary value of the column is returned. The variable pointed
+ to by <parameter>isnull</parameter> is set to true if the column is
+ null, else to false.
+ </para>
+
+ <para>
+ <varname>SPI_result</varname> is set to
+ <symbol>SPI_ERROR_NOATTRIBUTE</symbol> on error.
+ </para>
+ </refsect1>
+</refentry>
+
<!-- *********************************************** -->
-<REFENTRY ID="SPI-SPIGETRELNAME">
-<REFMETA>
-<REFENTRYTITLE>SPI_getrelname</REFENTRYTITLE>
-<REFMISCINFO>SPI - Tuple Information</REFMISCINFO>
-</REFMETA>
-<REFNAMEDIV>
-<REFNAME>SPI_getrelname
-</REFNAME>
-<REFPURPOSE>
-Returns the name of the specified relation
-</REFPURPOSE>
-<INDEXTERM ID="IX-SPI-SPIGETRELNAME-1"><PRIMARY>SPI</PRIMARY><SECONDARY>decoding tuples</SECONDARY></INDEXTERM>
-<INDEXTERM ID="IX-SPI-SPIGETRELNAME-2"><PRIMARY>SPI_getrelname</PRIMARY></INDEXTERM>
-</REFNAMEDIV>
-<REFSYNOPSISDIV>
-<REFSYNOPSISDIVINFO>
-<DATE>1997-12-24</DATE>
-</REFSYNOPSISDIVINFO>
-<SYNOPSIS>
-SPI_getrelname(<REPLACEABLE CLASS="PARAMETER">rel</REPLACEABLE>)
-</SYNOPSIS>
-
-<REFSECT2 ID="R2-SPI-SPIGETRELNAME-1">
-<REFSECT2INFO>
-<DATE>1997-12-24</DATE>
-</REFSECT2INFO>
-<TITLE>Inputs
-</TITLE>
-<VARIABLELIST>
-<VARLISTENTRY>
-<TERM>
-Relation <REPLACEABLE CLASS="PARAMETER">rel</REPLACEABLE>
-</TERM>
-<LISTITEM>
-<PARA>
-Input relation
-</PARA>
-</LISTITEM>
-</VARLISTENTRY>
-</VARIABLELIST>
-</REFSECT2>
-
-<REFSECT2 ID="R2-SPI-SPIGETRELNAME-2">
-<REFSECT2INFO>
-<DATE>1997-12-24</DATE>
-</REFSECT2INFO>
-<TITLE>Outputs
-</TITLE>
-<VARIABLELIST>
-<VARLISTENTRY>
-<TERM>
-char *
-</TERM>
-<LISTITEM>
-<PARA>
-The name of the specified relation
-</PARA>
-</LISTITEM>
-</VARLISTENTRY>
-</VARIABLELIST>
-</REFSECT2>
-</REFSYNOPSISDIV>
-
-<REFSECT1 ID="R1-SPI-SPIGETRELNAME-1">
-<REFSECT1INFO>
-<DATE>1997-12-24</DATE>
-</REFSECT1INFO>
-<TITLE>Description
-</TITLE>
-<PARA>
-<FUNCTION>SPI_getrelname</FUNCTION>
- returns the name of the specified relation.
-</PARA>
-</REFSECT1>
-<REFSECT1 ID="R1-SPI-SPIGETRELNAME-2">
-<TITLE>Usage
-</TITLE>
-<Para>
-TBD
-</PARA>
-</REFSECT1>
-<REFSECT1 ID="R1-SPI-SPIGETRELNAME-3">
-<TITLE>Algorithm
-</TITLE>
-<PARA>
-Copies the relation name into new storage.
-</PARA>
-</REFSECT1>
-<!--
-<REFSECT1 ID="R1-SPI-SPIGETRELNAME-4">
-<TITLE>Structures
-</TITLE>
-<PARA>None
-</PARA>
-</REFSECT1>
--->
-</REFENTRY>
+<refentry id="spi-spi-gettype">
+ <refmeta>
+ <refentrytitle>SPI_gettype</refentrytitle>
+ </refmeta>
+
+ <refnamediv>
+ <refname>SPI_gettype</refname>
+ <refpurpose>return the data type name of the specified column</refpurpose>
+ </refnamediv>
+
+ <indexterm><primary>SPI_gettype</primary></indexterm>
+
+ <refsynopsisdiv>
+<synopsis>
+char * SPI_gettype(TupleDesc <parameter>rowdesc</parameter>, int <parameter>colnumber</parameter>)
+</synopsis>
+ </refsynopsisdiv>
+
+ <refsect1>
+ <title>Description</title>
+
+ <para>
+ <function>SPI_gettype</function> returns a copy of the data type name of the
+ specified column. (You can use <function>pfree</function> to
+ release the copy of the name when you don't need it anymore.)
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Arguments</title>
+
+ <variablelist>
+ <varlistentry>
+ <term><literal>TupleDesc <parameter>rowdesc</parameter></literal></term>
+ <listitem>
+ <para>
+ input row description
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>int <parameter>colnumber</parameter></literal></term>
+ <listitem>
+ <para>
+ column number (count starts at 1)
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </refsect1>
+
+ <refsect1>
+ <title>Return Value</title>
+
+ <para>
+ The data type name of the specified column, or
+ <symbol>NULL</symbol> on error. <varname>SPI_result</varname> is
+ set to <symbol>SPI_ERROR_NOATTRIBUTE</symbol> on error.
+ </para>
+ </refsect1>
+</refentry>
<!-- *********************************************** -->
+
+<refentry id="spi-spi-gettypeid">
+ <refmeta>
+ <refentrytitle>SPI_gettypeid</refentrytitle>
+ </refmeta>
+
+ <refnamediv>
+ <refname>SPI_gettypeid</refname>
+ <refpurpose>return the data type <acronym>OID</acronym> of the specified column</refpurpose>
+ </refnamediv>
+
+ <indexterm><primary>SPI_gettypeid</primary></indexterm>
+
+ <refsynopsisdiv>
+<synopsis>
+Oid SPI_gettypeid(TupleDesc <parameter>rowdesc</parameter>, int <parameter>colnumber</parameter>)
+</synopsis>
+ </refsynopsisdiv>
+
+ <refsect1>
+ <title>Description</title>
+
+ <para>
+ <function>SPI_gettypeid</function> returns the
+ <acronym>OID</acronym> of the data type of the specified column.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Arguments</title>
+
+ <variablelist>
+ <varlistentry>
+ <term><literal>TupleDesc <parameter>rowdesc</parameter></literal></term>
+ <listitem>
+ <para>
+ input row description
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>int <parameter>colnumber</parameter></literal></term>
+ <listitem>
+ <para>
+ column number (count starts at 1)
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </refsect1>
+
+ <refsect1>
+ <title>Return Value</title>
+
+ <para>
+ The <acronym>OID</acronym> of the data type of the specified column
+ or <symbol>InvalidOid</symbol> on error. On error,
+ <varname>SPI_result</varname> is set to
+ <symbol>SPI_ERROR_NOATTRIBUTE</symbol>.
+ </para>
+ </refsect1>
+</refentry>
+
<!-- *********************************************** -->
+
+<refentry id="spi-spi-getrelname">
+ <refmeta>
+ <refentrytitle>SPI_getrelname</refentrytitle>
+ </refmeta>
+
+ <refnamediv>
+ <refname>SPI_getrelname</refname>
+ <refpurpose>return the name of the specified relation</refpurpose>
+ </refnamediv>
+
+ <indexterm><primary>SPI_getrelname</primary></indexterm>
+
+ <refsynopsisdiv>
+<synopsis>
+char * SPI_getrelname(Relation <parameter>rel</parameter>)
+</synopsis>
+ </refsynopsisdiv>
+
+ <refsect1>
+ <title>Description</title>
+
+ <para>
+ <function>SPI_getrelname</function> returns a copy of the name of the
+ specified relation. (You can use <function>pfree</function> to
+ release the copy of the name when you don't need it anymore.)
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Arguments</title>
+
+ <variablelist>
+ <varlistentry>
+ <term><literal>Relation <parameter>rel</parameter></literal></term>
+ <listitem>
+ <para>
+ input relation
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </refsect1>
+
+ <refsect1>
+ <title>Return Value</title>
+
+ <para>
+ The name of the specified relation.
+ </para>
+ </refsect1>
+</refentry>
+
+<refentry id="spi-spi-getnspname">
+ <refmeta>
+ <refentrytitle>SPI_getnspname</refentrytitle>
+ </refmeta>
+
+ <refnamediv>
+ <refname>SPI_getnspname</refname>
+ <refpurpose>return the namespace of the specified relation</refpurpose>
+ </refnamediv>
+
+ <indexterm><primary>SPI_getnspname</primary></indexterm>
+
+ <refsynopsisdiv>
+<synopsis>
+char * SPI_getnspname(Relation <parameter>rel</parameter>)
+</synopsis>
+ </refsynopsisdiv>
+
+ <refsect1>
+ <title>Description</title>
+
+ <para>
+ <function>SPI_getnspname</function> returns a copy of the name of
+ the namespace that the specified <structname>Relation</structname>
+ belongs to. This is equivalent to the relation's schema. You should
+ <function>pfree</function> the return value of this function when
+ you are finished with it.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Arguments</title>
+
+ <variablelist>
+ <varlistentry>
+ <term><literal>Relation <parameter>rel</parameter></literal></term>
+ <listitem>
+ <para>
+ input relation
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </refsect1>
+
+ <refsect1>
+ <title>Return Value</title>
+
+ <para>
+ The name of the specified relation's namespace.
+ </para>
+ </refsect1>
+</refentry>
+
+ </sect1>
+
+ <sect1 id="spi-memory">
+ <title>Memory Management</title>
+
+ <para>
+ <productname>PostgreSQL</productname> allocates memory within
+ <firstterm>memory contexts</firstterm><indexterm><primary>memory
+ context</primary><secondary>in SPI</secondary></indexterm>, which provide a convenient method of
+ managing allocations made in many different places that need to
+ live for differing amounts of time. Destroying a context releases
+ all the memory that was allocated in it. Thus, it is not necessary
+ to keep track of individual objects to avoid memory leaks; instead
+ only a relatively small number of contexts have to be managed.
+ <function>palloc</function> and related functions allocate memory
+ from the <quote>current</> context.
+ </para>
+
+ <para>
+ <function>SPI_connect</function> creates a new memory context and
+ makes it current. <function>SPI_finish</function> restores the
+ previous current memory context and destroys the context created by
+ <function>SPI_connect</function>. These actions ensure that
+ transient memory allocations made inside your procedure are
+ reclaimed at procedure exit, avoiding memory leakage.
+ </para>
+
+ <para>
+ However, if your procedure needs to return an object in allocated
+ memory (such as a value of a pass-by-reference data type), you
+ cannot allocate that memory using <function>palloc</function>, at
+ least not while you are connected to SPI. If you try, the object
+ will be deallocated by <function>SPI_finish</function>, and your
+ procedure will not work reliably. To solve this problem, use
+ <function>SPI_palloc</function> to allocate memory for your return
+ object. <function>SPI_palloc</function> allocates memory in the
+ <quote>upper executor context</quote>, that is, the memory context
+ that was current when <function>SPI_connect</function> was called,
+ which is precisely the right context for a value returned from your
+ procedure.
+ </para>
+
+ <para>
+ If <function>SPI_palloc</function> is called while the procedure is
+ not connected to SPI, then it acts the same as a normal
+ <function>palloc</function>. Before a procedure connects to the
+ SPI manager, the current memory context is the upper executor
+ context, so all allocations made by the procedure via
+ <function>palloc</function> or by SPI utility functions are made in
+ this context.
+ </para>
+
+ <para>
+ When <function>SPI_connect</function> is called, the private
+ context of the procedure, which is created by
+ <function>SPI_connect</function>, is made the current context. All
+ allocations made by <function>palloc</function>,
+ <function>repalloc</function>, or SPI utility functions (except for
+ <function>SPI_copytuple</function>,
+ <function>SPI_returntuple</function>,
+ <function>SPI_modifytuple</function>, and
+ <function>SPI_palloc</function>) are made in this context. When a
+ procedure disconnects from the SPI manager (via
+ <function>SPI_finish</function>) the current context is restored to
+ the upper executor context, and all allocations made in the
+ procedure memory context are freed and cannot be used any more.
+ </para>
+
+ <para>
+ All functions described in this section can be used by both
+ connected and unconnected procedures. In an unconnected procedure,
+ they act the same as the underlying ordinary server functions
+ (<function>palloc</>, etc.).
+ </para>
+
<!-- *********************************************** -->
-<REFENTRY ID="SPI-SPIPALLOC">
-<REFMETA>
-<REFENTRYTITLE>SPI_palloc</REFENTRYTITLE>
-<REFMISCINFO>SPI - Memory Management</REFMISCINFO>
-</REFMETA>
-<REFNAMEDIV>
-<REFNAME>SPI_palloc
-</REFNAME>
-<REFPURPOSE>
-Allocates memory in upper Executor context
-</REFPURPOSE>
-<INDEXTERM ID="IX-SPI-SPIPALLOC-1"><PRIMARY>SPI</PRIMARY><SECONDARY>allocating space</SECONDARY></INDEXTERM>
-<INDEXTERM ID="IX-SPI-SPIPALLOC-2"><PRIMARY>SPI_palloc</PRIMARY></INDEXTERM>
-</REFNAMEDIV>
-<REFSYNOPSISDIV>
-<REFSYNOPSISDIVINFO>
-<DATE>1997-12-24</DATE>
-</REFSYNOPSISDIVINFO>
-<SYNOPSIS>
-SPI_palloc(<REPLACEABLE CLASS="PARAMETER">size</REPLACEABLE>)
-</SYNOPSIS>
-
-<REFSECT2 ID="R2-SPI-SPIPALLOC-1">
-<REFSECT2INFO>
-<DATE>1997-12-24</DATE>
-</REFSECT2INFO>
-<TITLE>Inputs
-</TITLE>
-<VARIABLELIST>
-<VARLISTENTRY>
-<TERM>
-Size <REPLACEABLE CLASS="PARAMETER">size</REPLACEABLE>
-</TERM>
-<LISTITEM>
-<PARA>
-Octet size of storage to allocate
-</PARA>
-</LISTITEM>
-</VARLISTENTRY>
-</VARIABLELIST>
-</REFSECT2>
-
-<REFSECT2 ID="R2-SPI-SPIPALLOC-2">
-<REFSECT2INFO>
-<DATE>1997-12-24</DATE>
-</REFSECT2INFO>
-<TITLE>Outputs
-</TITLE>
-<VARIABLELIST>
-<VARLISTENTRY>
-<TERM>
-void *
-</TERM>
-<LISTITEM>
-<PARA>
-New storage space of specified size
-</PARA>
-</LISTITEM>
-</VARLISTENTRY>
-</VARIABLELIST>
-</REFSECT2>
-</REFSYNOPSISDIV>
-
-<REFSECT1 ID="R1-SPI-SPIPALLOC-1">
-<REFSECT1INFO>
-<DATE>1997-12-24</DATE>
-</REFSECT1INFO>
-<TITLE>Description
-</TITLE>
-<PARA>
-<FUNCTION>SPI_palloc</FUNCTION>
- allocates memory in upper Executor context. See section on memory management.
-</PARA>
-</REFSECT1>
-<REFSECT1 ID="R1-SPI-SPIPALLOC-2">
-<TITLE>Usage
-</TITLE>
-<Para>
-TBD
-</PARA>
-</REFSECT1>
-<!--
-<REFSECT1 ID="R1-SPI-SPIPALLOC-3">
-<TITLE>Algorithm
-</TITLE>
-<PARA>
-TBD
-</PARA>
-</REFSECT1>
--->
-<!--
-<REFSECT1 ID="R1-SPI-SPIPALLOC-4">
-<TITLE>Structures
-</TITLE>
-<PARA>None
-</PARA>
-</REFSECT1>
--->
-</REFENTRY>
+<refentry id="spi-spi-palloc">
+ <refmeta>
+ <refentrytitle>SPI_palloc</refentrytitle>
+ </refmeta>
+
+ <refnamediv>
+ <refname>SPI_palloc</refname>
+ <refpurpose>allocate memory in the upper executor context</refpurpose>
+ </refnamediv>
+
+ <indexterm><primary>SPI_palloc</primary></indexterm>
+
+ <refsynopsisdiv>
+<synopsis>
+void * SPI_palloc(Size <parameter>size</parameter>)
+</synopsis>
+ </refsynopsisdiv>
+
+ <refsect1>
+ <title>Description</title>
+
+ <para>
+ <function>SPI_palloc</function> allocates memory in the upper
+ executor context.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Arguments</title>
+
+ <variablelist>
+ <varlistentry>
+ <term><literal>Size <parameter>size</parameter></literal></term>
+ <listitem>
+ <para>
+ size in bytes of storage to allocate
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </refsect1>
+
+ <refsect1>
+ <title>Return Value</title>
+
+ <para>
+ pointer to new storage space of the specified size
+ </para>
+ </refsect1>
+</refentry>
<!-- *********************************************** -->
+
+<refentry id="spi-realloc">
+ <refmeta>
+ <refentrytitle>SPI_repalloc</refentrytitle>
+ </refmeta>
+
+ <refnamediv>
+ <refname>SPI_repalloc</refname>
+ <refpurpose>reallocate memory in the upper executor context</refpurpose>
+ </refnamediv>
+
+ <indexterm><primary>SPI_repalloc</primary></indexterm>
+
+ <refsynopsisdiv>
+<synopsis>
+void * SPI_repalloc(void * <parameter>pointer</parameter>, Size <parameter>size</parameter>)
+</synopsis>
+ </refsynopsisdiv>
+
+ <refsect1>
+ <title>Description</title>
+
+ <para>
+ <function>SPI_repalloc</function> changes the size of a memory
+ segment previously allocated using <function>SPI_palloc</function>.
+ </para>
+
+ <para>
+ This function is no longer different from plain
+ <function>repalloc</function>. It's kept just for backward
+ compatibility of existing code.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Arguments</title>
+
+ <variablelist>
+ <varlistentry>
+ <term><literal>void * <parameter>pointer</parameter></literal></term>
+ <listitem>
+ <para>
+ pointer to existing storage to change
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>Size <parameter>size</parameter></literal></term>
+ <listitem>
+ <para>
+ size in bytes of storage to allocate
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </refsect1>
+
+ <refsect1>
+ <title>Return Value</title>
+
+ <para>
+ pointer to new storage space of specified size with the contents
+ copied from the existing area
+ </para>
+ </refsect1>
+</refentry>
+
<!-- *********************************************** -->
+
+<refentry id="spi-spi-pfree">
+ <refmeta>
+ <refentrytitle>SPI_pfree</refentrytitle>
+ </refmeta>
+
+ <refnamediv>
+ <refname>SPI_pfree</refname>
+ <refpurpose>free memory in the upper executor context</refpurpose>
+ </refnamediv>
+
+ <indexterm><primary>SPI_pfree</primary></indexterm>
+
+ <refsynopsisdiv>
+<synopsis>
+void SPI_pfree(void * <parameter>pointer</parameter>)
+</synopsis>
+ </refsynopsisdiv>
+
+ <refsect1>
+ <title>Description</title>
+
+ <para>
+ <function>SPI_pfree</function> frees memory previously allocated
+ using <function>SPI_palloc</function> or
+ <function>SPI_repalloc</function>.
+ </para>
+
+ <para>
+ This function is no longer different from plain
+ <function>pfree</function>. It's kept just for backward
+ compatibility of existing code.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Arguments</title>
+
+ <variablelist>
+ <varlistentry>
+ <term><literal>void * <parameter>pointer</parameter></literal></term>
+ <listitem>
+ <para>
+ pointer to existing storage to free
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </refsect1>
+</refentry>
+
<!-- *********************************************** -->
-<REFENTRY ID="SPI-SPIREPALLOC">
-<REFMETA>
-<REFENTRYTITLE>SPI_repalloc</REFENTRYTITLE>
-<REFMISCINFO>SPI - Memory Management</REFMISCINFO>
-</REFMETA>
-<REFNAMEDIV>
-<REFNAME>SPI_repalloc
-</REFNAME>
-<REFPURPOSE>
-Re-allocates memory in upper Executor context
-</REFPURPOSE>
-<INDEXTERM ID="IX-SPI-SPIREPALLOC-1"><PRIMARY>SPI</PRIMARY><SECONDARY>allocating space</SECONDARY></INDEXTERM>
-<INDEXTERM ID="IX-SPI-SPIREPALLOC-2"><PRIMARY>SPI_repalloc</PRIMARY></INDEXTERM>
-</REFNAMEDIV>
-<REFSYNOPSISDIV>
-<REFSYNOPSISDIVINFO>
-<DATE>1997-12-24</DATE>
-</REFSYNOPSISDIVINFO>
-<SYNOPSIS>
-SPI_repalloc(<REPLACEABLE CLASS="PARAMETER">pointer</REPLACEABLE>, <REPLACEABLE CLASS="PARAMETER">size</REPLACEABLE>)
-</SYNOPSIS>
-
-<REFSECT2 ID="R2-SPI-SPIREPALLOC-1">
-<REFSECT2INFO>
-<DATE>1997-12-24</DATE>
-</REFSECT2INFO>
-<TITLE>Inputs
-</TITLE>
-<VARIABLELIST>
-<VARLISTENTRY>
-<TERM>
-void * <REPLACEABLE CLASS="PARAMETER">pointer</REPLACEABLE>
-</TERM>
-<LISTITEM>
-<PARA>
-Pointer to existing storage
-</PARA>
-</LISTITEM>
-</VARLISTENTRY>
-<VARLISTENTRY>
-<TERM>
-Size <REPLACEABLE CLASS="PARAMETER">size</REPLACEABLE>
-</TERM>
-<LISTITEM>
-<PARA>
-Octet size of storage to allocate
-</PARA>
-</LISTITEM>
-</VARLISTENTRY>
-</VARIABLELIST>
-</REFSECT2>
-
-<REFSECT2 ID="R2-SPI-SPIREPALLOC-2">
-<REFSECT2INFO>
-<DATE>1997-12-24</DATE>
-</REFSECT2INFO>
-<TITLE>Outputs
-</TITLE>
-<VARIABLELIST>
-<VARLISTENTRY>
-<TERM>
-void *
-</TERM>
-<LISTITEM>
-<PARA>
-New storage space of specified size with contents copied from existing area
-</PARA>
-</LISTITEM>
-</VARLISTENTRY>
-</VARIABLELIST>
-</REFSECT2>
-</REFSYNOPSISDIV>
-
-<REFSECT1 ID="R1-SPI-SPIREPALLOC-1">
-<REFSECT1INFO>
-<DATE>1997-12-24</DATE>
-</REFSECT1INFO>
-<TITLE>Description
-</TITLE>
-<PARA>
-<FUNCTION>SPI_repalloc</FUNCTION>
- re-allocates memory in upper Executor context. See section on memory management.
-</PARA>
-</REFSECT1>
-<REFSECT1 ID="R1-SPI-SPIREPALLOC-2">
-<TITLE>Usage
-</TITLE>
-<Para>
-TBD
-</PARA>
-</REFSECT1>
-<!--
-<REFSECT1 ID="R1-SPI-SPIREPALLOC-3">
-<TITLE>Algorithm
-</TITLE>
-<PARA>
-TBD
-</PARA>
-</REFSECT1>
--->
-<!--
-<REFSECT1 ID="R1-SPI-SPIREPALLOC-4">
-<TITLE>Structures
-</TITLE>
-<PARA>None
-</PARA>
-</REFSECT1>
--->
-</REFENTRY>
+<refentry id="spi-spi-copytuple">
+ <refmeta>
+ <refentrytitle>SPI_copytuple</refentrytitle>
+ </refmeta>
+
+ <refnamediv>
+ <refname>SPI_copytuple</refname>
+ <refpurpose>make a copy of a row in the upper executor context</refpurpose>
+ </refnamediv>
+
+ <indexterm><primary>SPI_copytuple</primary></indexterm>
+
+ <refsynopsisdiv>
+<synopsis>
+HeapTuple SPI_copytuple(HeapTuple <parameter>row</parameter>)
+</synopsis>
+ </refsynopsisdiv>
+
+ <refsect1>
+ <title>Description</title>
+
+ <para>
+ <function>SPI_copytuple</function> makes a copy of a row in the
+ upper executor context. This is normally used to return a modified
+ row from a trigger. In a function declared to return a composite
+ type, use <function>SPI_returntuple</function> instead.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Arguments</title>
+
+ <variablelist>
+ <varlistentry>
+ <term><literal>HeapTuple <parameter>row</parameter></literal></term>
+ <listitem>
+ <para>
+ row to be copied
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </refsect1>
+
+ <refsect1>
+ <title>Return Value</title>
+
+ <para>
+ the copied row; <symbol>NULL</symbol> only if
+ <parameter>tuple</parameter> is <symbol>NULL</symbol>
+ </para>
+ </refsect1>
+</refentry>
<!-- *********************************************** -->
+
+<refentry id="spi-spi-returntuple">
+ <refmeta>
+ <refentrytitle>SPI_returntuple</refentrytitle>
+ </refmeta>
+
+ <refnamediv>
+ <refname>SPI_returntuple</refname>
+ <refpurpose>prepare to return a tuple as a Datum</refpurpose>
+ </refnamediv>
+
+ <indexterm><primary>SPI_returntuple</primary></indexterm>
+
+ <refsynopsisdiv>
+<synopsis>
+HeapTupleHeader SPI_returntuple(HeapTuple <parameter>row</parameter>, TupleDesc <parameter>rowdesc</parameter>)
+</synopsis>
+ </refsynopsisdiv>
+
+ <refsect1>
+ <title>Description</title>
+
+ <para>
+ <function>SPI_returntuple</function> makes a copy of a row in
+ the upper executor context, returning it in the form of a row type <type>Datum</type>.
+ The returned pointer need only be converted to <type>Datum</type> via <function>PointerGetDatum</function>
+ before returning.
+ </para>
+
+ <para>
+ Note that this should be used for functions that are declared to return
+ composite types. It is not used for triggers; use
+ <function>SPI_copytuple</> for returning a modified row in a trigger.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Arguments</title>
+
+ <variablelist>
+ <varlistentry>
+ <term><literal>HeapTuple <parameter>row</parameter></literal></term>
+ <listitem>
+ <para>
+ row to be copied
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>TupleDesc <parameter>rowdesc</parameter></literal></term>
+ <listitem>
+ <para>
+ descriptor for row (pass the same descriptor each time for most
+ effective caching)
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </refsect1>
+
+ <refsect1>
+ <title>Return Value</title>
+
+ <para>
+ <type>HeapTupleHeader</type> pointing to copied row;
+ <symbol>NULL</symbol> only if
+ <parameter>row</parameter> or <parameter>rowdesc</parameter> is
+ <symbol>NULL</symbol>
+ </para>
+ </refsect1>
+</refentry>
+
<!-- *********************************************** -->
+
+<refentry id="spi-spi-modifytuple">
+ <refmeta>
+ <refentrytitle>SPI_modifytuple</refentrytitle>
+ </refmeta>
+
+ <refnamediv>
+ <refname>SPI_modifytuple</refname>
+ <refpurpose>create a row by replacing selected fields of a given row</refpurpose>
+ </refnamediv>
+
+ <indexterm><primary>SPI_modifytuple</primary></indexterm>
+
+ <refsynopsisdiv>
+<synopsis>
+HeapTuple SPI_modifytuple(Relation <parameter>rel</parameter>, HeapTuple <parameter>row</parameter>, <parameter>ncols</parameter>, <parameter>colnum</parameter>, Datum * <parameter>values</parameter>, const char * <parameter>nulls</parameter>)
+</synopsis>
+ </refsynopsisdiv>
+
+ <refsect1>
+ <title>Description</title>
+
+ <para>
+ <function>SPI_modifytuple</function> creates a new row by
+ substituting new values for selected columns, copying the original
+ row's columns at other positions. The input row is not modified.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Arguments</title>
+
+ <variablelist>
+ <varlistentry>
+ <term><literal>Relation <parameter>rel</parameter></literal></term>
+ <listitem>
+ <para>
+ Used only as the source of the row descriptor for the row.
+ (Passing a relation rather than a row descriptor is a
+ misfeature.)
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>HeapTuple <parameter>row</parameter></literal></term>
+ <listitem>
+ <para>
+ row to be modified
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>int <parameter>ncols</parameter></literal></term>
+ <listitem>
+ <para>
+ number of column numbers in the array
+ <parameter>colnum</parameter>
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>int * <parameter>colnum</parameter></literal></term>
+ <listitem>
+ <para>
+ array of the numbers of the columns that are to be changed
+ (column numbers start at 1)
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>Datum * <parameter>values</parameter></literal></term>
+ <listitem>
+ <para>
+ new values for the specified columns
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>const char * <parameter>Nulls</parameter></literal></term>
+ <listitem>
+ <para>
+ which new values are null, if any (see
+ <function>SPI_execute_plan</function> for the format)
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </refsect1>
+
+ <refsect1>
+ <title>Return Value</title>
+
+ <para>
+ new row with modifications, allocated in the upper executor
+ context; <symbol>NULL</symbol> only if <parameter>row</parameter>
+ is <symbol>NULL</symbol>
+ </para>
+
+ <para>
+ On error, <varname>SPI_result</varname> is set as follows:
+ <variablelist>
+ <varlistentry>
+ <term><symbol>SPI_ERROR_ARGUMENT</symbol></term>
+ <listitem>
+ <para>
+ if <parameter>rel</> is <symbol>NULL</>, or if
+ <parameter>row</> is <symbol>NULL</>, or if <parameter>ncols</>
+ is less than or equal to 0, or if <parameter>colnum</> is
+ <symbol>NULL</>, or if <parameter>values</> is <symbol>NULL</>.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><symbol>SPI_ERROR_NOATTRIBUTE</symbol></term>
+ <listitem>
+ <para>
+ if <parameter>colnum</> contains an invalid column number (less
+ than or equal to 0 or greater than the number of column in
+ <parameter>row</>)
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </para>
+ </refsect1>
+</refentry>
+
<!-- *********************************************** -->
-<REFENTRY ID="SPI-SPIPFREE">
-<REFMETA>
-<REFENTRYTITLE>SPI_pfree</REFENTRYTITLE>
-<REFMISCINFO>SPI - Memory Management</REFMISCINFO>
-</REFMETA>
-<REFNAMEDIV>
-<REFNAME>SPI_pfree
-</REFNAME>
-<REFPURPOSE>
-Frees memory from upper Executor context
-</REFPURPOSE>
-<INDEXTERM ID="IX-SPI-SPIPFREE-1"><PRIMARY>SPI</PRIMARY><SECONDARY>allocating space</SECONDARY></INDEXTERM>
-<INDEXTERM ID="IX-SPI-SPIPFREE-2"><PRIMARY>SPI_pfree</PRIMARY></INDEXTERM>
-</REFNAMEDIV>
-<REFSYNOPSISDIV>
-<REFSYNOPSISDIVINFO>
-<DATE>1997-12-24</DATE>
-</REFSYNOPSISDIVINFO>
-<SYNOPSIS>
-SPI_pfree(<REPLACEABLE CLASS="PARAMETER">pointer</REPLACEABLE>)
-</SYNOPSIS>
-
-<REFSECT2 ID="R2-SPI-SPIPFREE-1">
-<REFSECT2INFO>
-<DATE>1997-12-24</DATE>
-</REFSECT2INFO>
-<TITLE>Inputs
-</TITLE>
-<VARIABLELIST>
-<VARLISTENTRY>
-<TERM>
-void * <REPLACEABLE CLASS="PARAMETER">pointer</REPLACEABLE>
-</TERM>
-<LISTITEM>
-<PARA>
-Pointer to existing storage
-</PARA>
-</LISTITEM>
-</VARLISTENTRY>
-</VARIABLELIST>
-</REFSECT2>
-
-<REFSECT2 ID="R2-SPI-SPIPFREE-2">
-<REFSECT2INFO>
-<DATE>1997-12-24</DATE>
-</REFSECT2INFO>
-<TITLE>Outputs
-</TITLE>
-<VARIABLELIST>
-<VARLISTENTRY>
-<TERM>
-None
-</TERM>
-<LISTITEM>
-<PARA>
-</PARA>
-</LISTITEM>
-</VARLISTENTRY>
-</VARIABLELIST>
-</REFSECT2>
-</REFSYNOPSISDIV>
-
-<REFSECT1 ID="R1-SPI-SPIPFREE-1">
-<REFSECT1INFO>
-<DATE>1997-12-24</DATE>
-</REFSECT1INFO>
-<TITLE>Description
-</TITLE>
-<PARA>
-<FUNCTION>SPI_pfree</FUNCTION>
- frees memory in upper Executor context. See section on memory management.
-</PARA>
-</REFSECT1>
-<REFSECT1 ID="R1-SPI-SPIPFREE-2">
-<TITLE>Usage
-</TITLE>
-<Para>
-TBD
-</PARA>
-</REFSECT1>
-<!--
-<REFSECT1 ID="R1-SPI-SPIPFREE-3">
-<TITLE>Algorithm
-</TITLE>
-<PARA>
-TBD
-</PARA>
-</REFSECT1>
--->
-<!--
-<REFSECT1 ID="R1-SPI-SPIPFREE-4">
-<TITLE>Structures
-</TITLE>
-<PARA>None
-</PARA>
-</REFSECT1>
--->
-</REFENTRY>
-
-</Sect1>
-
-<Sect1 id="spi-memory">
-<Title>Memory Management</Title>
-
-<Para>
- Server allocates memory in memory contexts in such way that allocations
-made in one context may be freed by context destruction without affecting
-allocations made in other contexts. All allocations (via <Function>palloc</Function>, etc) are
-made in the context that is chosen as the current one. You'll get
-unpredictable results if you'll try to free (or reallocate) memory allocated
-not in current context.
-</Para>
-
-<Para>
- Creation and switching between memory contexts are subject of SPI manager
-memory management.
-</Para>
-
-<Para>
-
- SPI procedures deal with two memory contexts: upper Executor memory
-context and procedure memory context (if connected).
-</Para>
-
-<Para>
-
- Before a procedure is connected to the SPI manager, current memory context
-is upper Executor context so all allocation made by the procedure itself via
-<Function>palloc</Function>/<Function>repalloc</Function> or by SPI utility functions before connecting to SPI are
-made in this context.
-</Para>
-
-<Para>
-
- After <Function>SPI_connect</Function> is called current context is the
- procedure's one. All allocations made via
-<Function>palloc</Function>/<Function>repalloc</Function> or by SPI utility
-functions (except for <Function>SPI_copytuple</Function>,
-<Function>SPI_copytupledesc</Function>,
-<Function>SPI_modifytuple</Function>,
-<Function>SPI_palloc</Function> and <Function>SPI_repalloc</Function>) are
-made in this context.
-</Para>
-
-<Para>
-
- When a procedure disconnects from the SPI manager (via <Function>SPI_finish</Function>) the
-current context is restored to the upper Executor context and all allocations
-made in the procedure memory context are freed and can't be used any more!
-</Para>
-
-<Para>
-
- If you want to return something to the upper Executor then you have to
-allocate memory for this in the upper context!
-</Para>
-
-<Para>
-
- SPI has no ability to automatically free allocations in the upper Executor
-context!
-</Para>
-
-<Para>
-
- SPI automatically frees memory allocated during execution of a query when
-this query is done!
-</Para>
-
-</Sect1>
-
-<Sect1 id="spi-visibility">
-<Title>Visibility of Data Changes</Title>
-
-<Para>
-<ProductName>Postgres</ProductName> data changes visibility rule: during a query execution, data
-changes made by the query itself (via SQL-function, SPI-function, triggers)
-are invisible to the query scan. For example, in query
-
- INSERT INTO a SELECT * FROM a
-
- tuples inserted are invisible for SELECT's scan. In effect, this
-duplicates the database table within itself (subject to unique index
-rules, of course) without recursing.
-</Para>
-
-<Para>
- Changes made by query Q are visible to queries that are started after
-query Q, no matter whether they are started inside Q (during the execution
-of Q) or after Q is done.
-</Para>
-</Sect1>
+<refentry id="spi-spi-freetuple">
+ <refmeta>
+ <refentrytitle>SPI_freetuple</refentrytitle>
+ </refmeta>
+
+ <refnamediv>
+ <refname>SPI_freetuple</refname>
+ <refpurpose>free a row allocated in the upper executor context</refpurpose>
+ </refnamediv>
+
+ <indexterm><primary>SPI_freetuple</primary></indexterm>
+
+ <refsynopsisdiv>
+<synopsis>
+void SPI_freetuple(HeapTuple <parameter>row</parameter>)
+</synopsis>
+ </refsynopsisdiv>
+
+ <refsect1>
+ <title>Description</title>
+
+ <para>
+ <function>SPI_freetuple</function> frees a row previously allocated
+ in the upper executor context.
+ </para>
+
+ <para>
+ This function is no longer different from plain
+ <function>heap_freetuple</function>. It's kept just for backward
+ compatibility of existing code.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Arguments</title>
+
+ <variablelist>
+ <varlistentry>
+ <term><literal>HeapTuple <parameter>row</parameter></literal></term>
+ <listitem>
+ <para>
+ row to free
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </refsect1>
+</refentry>
-<Sect1 id="spi-examples">
-<Title>Examples</Title>
+<!-- *********************************************** -->
-<Para>
- This example of SPI usage demonstrates the visibility rule.
- There are more complex examples in src/test/regress/regress.c and
-in contrib/spi.
-</Para>
+<refentry id="spi-spi-freetupletable">
+ <refmeta>
+ <refentrytitle>SPI_freetuptable</refentrytitle>
+ </refmeta>
+
+ <refnamediv>
+ <refname>SPI_freetuptable</refname>
+ <refpurpose>free a row set created by <function>SPI_execute</> or a similar
+ function</refpurpose>
+ </refnamediv>
+
+ <indexterm><primary>SPI_freetuptable</primary></indexterm>
+
+ <refsynopsisdiv>
+<synopsis>
+void SPI_freetuptable(SPITupleTable * <parameter>tuptable</parameter>)
+</synopsis>
+ </refsynopsisdiv>
+
+ <refsect1>
+ <title>Description</title>
+
+ <para>
+ <function>SPI_freetuptable</function> frees a row set created by a
+ prior SPI command execution function, such as
+ <function>SPI_execute</>. Therefore, this function is usually called
+ with the global variable <varname>SPI_tupletable</varname> as
+ argument.
+ </para>
+
+ <para>
+ This function is useful if a SPI procedure needs to execute
+ multiple commands and does not want to keep the results of earlier
+ commands around until it ends. Note that any unfreed row sets will
+ be freed anyway at <function>SPI_finish</>.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Arguments</title>
+
+ <variablelist>
+ <varlistentry>
+ <term><literal>SPITupleTable * <parameter>tuptable</parameter></literal></term>
+ <listitem>
+ <para>
+ pointer to row set to free
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </refsect1>
+</refentry>
-<Para>
- This is a very simple example of SPI usage. The procedure execq accepts
-an SQL-query in its first argument and tcount in its second, executes the
-query using SPI_exec and returns the number of tuples for which the query
-executed:
+<!-- *********************************************** -->
-<ProgramListing>
-#include "executor/spi.h" /* this is what you need to work with SPI */
+<refentry id="spi-spi-freeplan">
+ <refmeta>
+ <refentrytitle>SPI_freeplan</refentrytitle>
+ </refmeta>
+
+ <refnamediv>
+ <refname>SPI_freeplan</refname>
+ <refpurpose>free a previously saved plan</refpurpose>
+ </refnamediv>
+
+ <indexterm><primary>SPI_freeplan</primary></indexterm>
+
+ <refsynopsisdiv>
+<synopsis>
+int SPI_freeplan(SPIPlanPtr <parameter>plan</parameter>)
+</synopsis>
+ </refsynopsisdiv>
+
+ <refsect1>
+ <title>Description</title>
+
+ <para>
+ <function>SPI_freeplan</function> releases a command execution plan
+ previously returned by <function>SPI_prepare</function> or saved by
+ <function>SPI_saveplan</function>.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Arguments</title>
+
+ <variablelist>
+ <varlistentry>
+ <term><literal>SPIPlanPtr <parameter>plan</parameter></literal></term>
+ <listitem>
+ <para>
+ pointer to plan to free
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </refsect1>
+
+ <refsect1>
+ <title>Return Value</title>
+
+ <para>
+ <symbol>SPI_ERROR_ARGUMENT</symbol> if <parameter>plan</parameter>
+ is <symbol>NULL</symbol> or invalid
+ </para>
+ </refsect1>
+</refentry>
+
+ </sect1>
+
+ <sect1 id="spi-visibility">
+ <title>Visibility of Data Changes</title>
+
+ <para>
+ The following rules govern the visibility of data changes in
+ functions that use SPI (or any other C function):
+
+ <itemizedlist>
+ <listitem>
+ <para>
+ During the execution of an SQL command, any data changes made by
+ the command are invisible to the command itself. For
+ example, in:
+<programlisting>
+INSERT INTO a SELECT * FROM a;
+</programlisting>
+ the inserted rows are invisible to the <command>SELECT</command>
+ part.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Changes made by a command C are visible to all commands that are
+ started after C, no matter whether they are started inside C
+ (during the execution of C) or after C is done.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Commands executed via SPI inside a function called by an SQL command
+ (either an ordinary function or a trigger) follow one or the
+ other of the above rules depending on the read/write flag passed
+ to SPI. Commands executed in read-only mode follow the first
+ rule: they cannot see changes of the calling command. Commands executed
+ in read-write mode follow the second rule: they can see all changes made
+ so far.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ All standard procedural languages set the SPI read-write mode
+ depending on the volatility attribute of the function. Commands of
+ <literal>STABLE</> and <literal>IMMUTABLE</> functions are done in
+ read-only mode, while commands of <literal>VOLATILE</> functions are
+ done in read-write mode. While authors of C functions are able to
+ violate this convention, it's unlikely to be a good idea to do so.
+ </para>
+ </listitem>
+ </itemizedlist>
+ </para>
+
+ <para>
+ The next section contains an example that illustrates the
+ application of these rules.
+ </para>
+ </sect1>
+
+ <sect1 id="spi-examples">
+ <title>Examples</title>
+
+ <para>
+ This section contains a very simple example of SPI usage. The
+ procedure <function>execq</function> takes an SQL command as its
+ first argument and a row count as its second, executes the command
+ using <function>SPI_exec</function> and returns the number of rows
+ that were processed by the command. You can find more complex
+ examples for SPI in the source tree in
+ <filename>src/test/regress/regress.c</filename> and in
+ <filename>contrib/spi</filename>.
+ </para>
+
+<programlisting>
+#include "executor/spi.h"
int execq(text *sql, int cnt);
int
execq(text *sql, int cnt)
{
- char *query;
+ char *command;
int ret;
int proc;
- /* Convert given TEXT object to a C string */
- query = DatumGetCString(DirectFunctionCall1(textout,
- PointerGetDatum(sql)));
+ /* Convert given text object to a C string */
+ command = DatumGetCString(DirectFunctionCall1(textout,
+ PointerGetDatum(sql)));
SPI_connect();
- ret = SPI_exec(query, cnt);
+ ret = SPI_exec(command, cnt);
proc = SPI_processed;
/*
- * If this is SELECT and some tuple(s) fetched -
- * returns tuples to the caller via elog (NOTICE).
+ * If some rows were fetched, print them via elog(INFO).
*/
- if ( ret == SPI_OK_SELECT && SPI_processed > 0 )
+ if (ret > 0 && SPI_tuptable != NULL)
{
- TupleDesc tupdesc = SPI_tuptable->tupdesc;
+ TupleDesc tupdesc = SPI_tuptable->tupdesc;
SPITupleTable *tuptable = SPI_tuptable;
char buf[8192];
- int i,j;
+ int i, j;
- for (j = 0; j < proc; j++)
+ for (j = 0; j < proc; j++)
{
- HeapTuple tuple = tuptable->vals[j];
+ HeapTuple tuple = tuptable->vals[j];
- for (i = 1, buf[0] = 0; i <= tupdesc->natts; i++)
- sprintf(buf + strlen (buf), " %s%s",
+ for (i = 1, buf[0] = 0; i <= tupdesc->natts; i++)
+ snprintf(buf + strlen (buf), sizeof(buf) - strlen(buf), " %s%s",
SPI_getvalue(tuple, tupdesc, i),
- (i == tupdesc->natts) ? " " : " |");
- elog (NOTICE, "EXECQ: %s", buf);
+ (i == tupdesc->natts) ? " " : " |");
+ elog(INFO, "EXECQ: %s", buf);
}
}
SPI_finish();
-
- pfree(query);
+ pfree(command);
return (proc);
}
-</ProgramListing>
-</Para>
-
-<Para>
- Now, compile and create the function:
-
-<ProgramListing>
-create function execq (text, int4) returns int4 as '...path_to_so' language 'c';
-</ProgramListing>
-
-<ProgramListing>
-vac=> select execq('create table a (x int4)', 0);
-execq
------
- 0
+</programlisting>
+
+ <para>
+ (This function uses call convention version 0, to make the example
+ easier to understand. In real applications you should use the new
+ version 1 interface.)
+ </para>
+
+ <para>
+ This is how you declare the function after having compiled it into
+ a shared library:
+
+<programlisting>
+CREATE FUNCTION execq(text, integer) RETURNS integer
+ AS '<replaceable>filename</replaceable>'
+ LANGUAGE C;
+</programlisting>
+ </para>
+
+ <para>
+ Here is a sample session:
+
+<programlisting>
+=> SELECT execq('CREATE TABLE a (x integer)', 0);
+ execq
+-------
+ 0
(1 row)
-vac=> insert into a values (execq('insert into a values (0)',0));
-INSERT 167631 1
-vac=> select execq('select * from a',0);
-NOTICE:EXECQ: 0 <<< inserted by execq
-
-NOTICE:EXECQ: 1 <<< value returned by execq and inserted by upper INSERT
+=> INSERT INTO a VALUES (execq('INSERT INTO a VALUES (0)', 0));
+INSERT 0 1
+=> SELECT execq('SELECT * FROM a', 0);
+INFO: EXECQ: 0 -- inserted by execq
+INFO: EXECQ: 1 -- returned by execq and inserted by upper INSERT
-execq
------
- 2
+ execq
+-------
+ 2
(1 row)
-vac=> select execq('insert into a select x + 2 from a',1);
-execq
------
- 1
+=> SELECT execq('INSERT INTO a SELECT x + 2 FROM a', 1);
+ execq
+-------
+ 1
(1 row)
-vac=> select execq('select * from a', 10);
-NOTICE:EXECQ: 0
-
-NOTICE:EXECQ: 1
-
-NOTICE:EXECQ: 2 <<< 0 + 2, only one tuple inserted - as specified
+=> SELECT execq('SELECT * FROM a', 10);
+INFO: EXECQ: 0
+INFO: EXECQ: 1
+INFO: EXECQ: 2 -- 0 + 2, only one row inserted - as specified
-execq
------
- 3 <<< 10 is max value only, 3 is real # of tuples
+ execq
+-------
+ 3 -- 10 is the max value only, 3 is the real number of rows
(1 row)
-vac=> delete from a;
+=> DELETE FROM a;
DELETE 3
-vac=> insert into a values (execq('select * from a', 0) + 1);
-INSERT 167712 1
-vac=> select * from a;
-x
--
-1 <<< no tuples in a (0) + 1
+=> INSERT INTO a VALUES (execq('SELECT * FROM a', 0) + 1);
+INSERT 0 1
+=> SELECT * FROM a;
+ x
+---
+ 1 -- no rows in a (0) + 1
(1 row)
-vac=> insert into a values (execq('select * from a', 0) + 1);
-NOTICE:EXECQ: 0
-INSERT 167713 1
-vac=> select * from a;
-x
--
-1
-2 <<< there was single tuple in a + 1
+=> INSERT INTO a VALUES (execq('SELECT * FROM a', 0) + 1);
+INFO: EXECQ: 1
+INSERT 0 1
+=> SELECT * FROM a;
+ x
+---
+ 1
+ 2 -- there was one row in a + 1
(2 rows)
--- This demonstrates data changes visibility rule:
+-- This demonstrates the data changes visibility rule:
-vac=> insert into a select execq('select * from a', 0) * x from a;
-NOTICE:EXECQ: 1
-NOTICE:EXECQ: 2
-NOTICE:EXECQ: 1
-NOTICE:EXECQ: 2
-NOTICE:EXECQ: 2
+=> INSERT INTO a SELECT execq('SELECT * FROM a', 0) * x FROM a;
+INFO: EXECQ: 1
+INFO: EXECQ: 2
+INFO: EXECQ: 1
+INFO: EXECQ: 2
+INFO: EXECQ: 2
INSERT 0 2
-vac=> select * from a;
-x
--
-1
-2
-2 <<< 2 tuples * 1 (x in first tuple)
-6 <<< 3 tuples (2 + 1 just inserted) * 2 (x in second tuple)
-(4 rows) ^^^^^^^^
- tuples visible to execq() in different invocations
-</ProgramListing>
-</Para>
-</Sect1>
-</Chapter>
+=> SELECT * FROM a;
+ x
+---
+ 1
+ 2
+ 2 -- 2 rows * 1 (x in first row)
+ 6 -- 3 rows (2 + 1 just inserted) * 2 (x in second row)
+(4 rows) ^^^^^^
+ rows visible to execq() in different invocations
+</programlisting>
+ </para>
+ </sect1>
+</chapter>