spi.sgml

关系型数据库 Postgresql 6.5.2

<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>
<Acronym>SPI</Acronym> procedures are always called by some (upper) 
Executor and the <Acronym>SPI</Acronym>
manager uses the Executor to run your queries. Other procedures may be
called by the Executor running queries from your procedure.
</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>
<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>
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>
   You may get <ReturnValue>SPI_ERROR_CONNECT</ReturnValue> error if <Function>SPI_connect</Function> is
   called from an already connected procedure - e.g. if you directly call one
   procedure from another connected one.  Actually, while the child procedure
   will be able to use SPI, your parent procedure will not be able to continue
   to use SPI after the child returns (if <Function>SPI_finish</Function> is called by the child).
   It's bad practice.
</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>&bull;
</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>

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

<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>&bull;
</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-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 &gt; 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>&bull;
</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).