spi.sgml

关系型数据库 Postgresql 6.5.2

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

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

<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>'n' indicates NULL allowed</Member>
<Member>' ' 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> &lt; 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>&bull;
</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>
<Title>Interface Support Functions</Title>

<Para>
All functions described below may be used by connected and unconnected
procedures.
</Para>

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

<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>