<!-- $PostgreSQL: pgsql/doc/src/sgml/spi.sgml,v 1.32 2004/03/05 01:00:45 momjian 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, optimizer, 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. Some of these are based on or modelled after 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 during the execution of a procedure the transaction is aborted because of an error in a command, 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. A related restriction is the inability to execute <command>BEGIN</command>, <command>COMMIT</command>, and <command>ROLLBACK</command> (transaction control statements) inside a procedure. Both of these restrictions will probably be changed in the future. </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> </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 may be called from unconnected procedures. </para> <para> If your procedure is already connected, <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. </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-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-spi-exec"> <refmeta> <refentrytitle>SPI_exec</refentrytitle> </refmeta> <refnamediv> <refname>SPI_exec</refname> <refpurpose>execute a command</refpurpose> </refnamediv> <indexterm><primary>SPI_exec</primary></indexterm> <refsynopsisdiv> <synopsis> int SPI_exec(const char * <parameter>command</parameter>, int <parameter>count</parameter>) </synopsis> </refsynopsisdiv> <refsect1> <title>Description</title> <para> <function>SPI_exec</function> executes the specified SQL command for <parameter>count</parameter> rows. </para> <para> This function should only be called from a connected procedure. If <parameter>count</parameter> is zero then it executes the command 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_exec("INSERT INTO tab SELECT * FROM tab", 5); </programlisting> will allow at most 5 rows to be inserted into the table. </para> <para> You may pass multiple commands in one string, and the command may be rewritten by rules. <function>SPI_exec</function> returns the result for the command executed last. </para> <para> The actual number of rows for which the (last) command was executed is returned in the global variable <varname>SPI_processed</varname> (unless the return value of the function is <symbol>SPI_OK_UTILITY</symbol>). If the return value of the function is <symbol>SPI_OK_SELECT</symbol> then you may the use global pointer <literal>SPITupleTable *SPI_tuptable</literal> to access the result rows. </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 may 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>int <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_DELETE</symbol></term> <listitem> <para> if a <command>DELETE</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_UPDATE</symbol></term> <listitem> <para> if an <command>UPDATE</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_CURSOR</symbol></term> <listitem> <para> if <command>DECLARE</>, <command>CLOSE</>, or <command>FETCH</> was attempted </para> </listitem> </varlistentry> <varlistentry> <term><symbol>SPI_ERROR_TRANSACTION</symbol></term> <listitem> <para> if <command>BEGIN</>, <command>COMMIT</>, or <command>ROLLBACK</> was attempted </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_exec</function>, <function>SPI_execp</function>, and <function>SPI_prepare</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 of <function>SPI_exec</function> or <function>SPI_execp</function> across later calls. </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> void * 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 may 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_execp</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_execp</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 non-null pointer to an execution plan. On error, <symbol>NULL</symbol> will be returned. In both cases, <varname>SPI_result</varname> will be set analogous to the value returned by <function>SPI_exec</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> There is a disadvantage to using parameters: since the planner does not know the values that will be supplied for the parameters, it may make worse planning choices than it would make for a normal command with all constants visible. </para> </refsect1> </refentry> <!-- *********************************************** --> <refentry id="spi-spi-getargcount"> <refmeta> <refentrytitle>SPI_getargcount</refentrytitle> </refmeta> <refnamediv> <refname>SPI_getargcount</refname> <refpurpose>returns the number of arguments needed when executing a plan prepared by <function>SPI_prepare</function></refpurpose> </refnamediv> <indexterm><primary>SPI_getargcount</primary></indexterm> <refsynopsisdiv> <synopsis> int SPI_getargcount(void * <parameter>plan</parameter>) </synopsis> </refsynopsisdiv> <refsect1> <title>Description</title> <para> <function>SPI_getargcount</function> returns the number of arguments needed when executing a plan prepared by <function>SPI_prepare</function>. </para> </refsect1> <refsect1> <title>Arguments</title> <variablelist> <varlistentry> <term><literal>void * <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 expected argument count for the <parameter>plan</parameter> or <symbol>SPI_ERROR_ARGUMENT</symbol> if the <parameter>plan </parameter> is <symbol>NULL</symbol> </para> </refsect1> </refentry> <!-- *********************************************** --> <refentry id="spi-spi-getargtypeid"> <refmeta> <refentrytitle>SPI_getargtypeid</refentrytitle> </refmeta> <refnamediv> <refname>SPI_getargtypeid</refname> <refpurpose>returns the expected typeid for the specified argument when executing a plan prepared by <function>SPI_prepare</function></refpurpose> </refnamediv> <indexterm><primary>SPI_getargtypeid</primary></indexterm> <refsynopsisdiv> <synopsis> Oid SPI_getargtypeid(void * <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 argument at <parameter>argIndex</parameter> in a plan prepared by <function>SPI_prepare</function>. First argument is at index zero. </para> </refsect1> <refsect1> <title>Arguments</title> <variablelist> <varlistentry> <term><literal>void * <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 or <symbol> SPI_ERROR_ARGUMENT</symbol> if the <parameter>plan</parameter> is <symbol>NULL</symbol> or <parameter>argIndex</parameter> is less than 0 or not less than the number of arguments declared for the <parameter>plan </parameter> </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>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></refpurpose> </refnamediv> <indexterm><primary>SPI_is_cursor_plan</primary></indexterm> <refsynopsisdiv> <synopsis> bool SPI_is_cursor_plan(void * <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> and <symbol> false</symbol> if that is not the case. The criteria is that the <parameter>plan</parameter> represents one single command and that this command is a <command>SELECT</command> without an <command>INTO</command> clause. </para> </refsect1> <refsect1> <title>Arguments</title> <variablelist> <varlistentry> <term><literal>void * <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, or <symbol>SPI_ERROR_ARGUMENT</symbol> if the <parameter>plan</parameter> is <symbol>NULL</symbol> </para> </refsect1> </refentry> <!-- *********************************************** --> <refentry id="spi-spi-execp"> <refmeta> <refentrytitle>SPI_execp</refentrytitle> </refmeta> <refnamediv> <refname>SPI_execp</refname> <refpurpose>executes a plan prepared by <function>SPI_prepare</function></refpurpose> </refnamediv> <indexterm><primary>SPI_execp</primary></indexterm> <refsynopsisdiv> <synopsis> int SPI_execp(void * <parameter>plan</parameter>, Datum * <parameter>values</parameter>, const char * <parameter>nulls</parameter>, int <parameter>count</parameter>) </synopsis> </refsynopsisdiv> <refsect1> <title>Description</title> <para> <function>SPI_execp</function> executes a plan prepared by <function>SPI_prepare</function>. <parameter>tcount</parameter> has the same interpretation as in <function>SPI_exec</function>. </para> </refsect1> <refsect1> <title>Arguments</title> <variablelist> <varlistentry> <term><literal>void * <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> actual parameter values </para> </listitem> </varlistentry> <varlistentry> <term><literal>const char * <parameter>nulls</parameter></literal></term> <listitem> <para> An array describing which parameters are null. <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>int <parameter>count</parameter></literal></term> <listitem> <para> number of row for which plan is to be executed </para> </listitem> </varlistentry> </variablelist> </refsect1> <refsect1> <title>Return Value</title> <para> The return value is the same as for <function>SPI_exec</function> or one of the following: <variablelist> <varlistentry> <term><symbol>SPI_ERROR_ARGUMENT</symbol></term> <listitem> <para> if <parameter>plan</parameter> is <symbol>NULL</symbol> 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_exec</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_execp</function> for this plan will be unpredictable. </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>, void * <parameter>plan</parameter>, Datum * <parameter>values</parameter>, const char * <parameter>nulls</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>. </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> </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>void * <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> actual parameter values </para> </listitem> </varlistentry> <varlistentry> <term><literal>const char *<parameter>nulls</parameter></literal></term> <listitem> <para> An array describing which parameters are null values. <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). If <parameter>nulls</parameter> is <symbol>NULL</> then <function>SPI_cursor_open</function> assumes that no parameters are null. </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>, int <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 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>bool <parameter>forward</parameter></literal></term> <listitem> <para> true for fetch forward, false for fetch backward </para> </listitem> </varlistentry> <varlistentry> <term><literal>int <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_exec</function> if successful. </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>, int <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 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>bool <parameter>forward</parameter></literal></term> <listitem> <para> true for move forward, false for move backward </para> </listitem> </varlistentry> <varlistentry> <term><literal>int <parameter>count</parameter></literal></term> <listitem> <para> maximum number of rows to move </para> </listitem> </varlistentry> </variablelist> </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> void * SPI_saveplan(void * <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 protected from freeing by <function>SPI_finish</function> and 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. You may save the pointer returned in a local variable. Always check if this pointer is <symbol>NULL</symbol> or not either when preparing a plan or using an already prepared plan in <function>SPI_execp</function>. </para> </refsect1> <refsect1> <title>Arguments</title> <variablelist> <varlistentry> <term><literal>void * <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> </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> If one of the objects (a table, function, etc.) referenced by the prepared plan is dropped during the session then the results of <function>SPI_execp</function> for this plan will be unpredictable. </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_exec</> and other SPI functions. </para> <para> All functions described in this section may 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 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-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>rownumber</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-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 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 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> </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 return a value 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_copytupledesc</function>, <function>SPI_copytupleintoslot</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 may 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-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-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. </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-copytupledesc"> <refmeta> <refentrytitle>SPI_copytupledesc</refentrytitle> </refmeta> <refnamediv> <refname>SPI_copytupledesc</refname> <refpurpose>make a copy of a row descriptor in the upper executor context</refpurpose> </refnamediv> <indexterm><primary>SPI_copytupledesc</primary></indexterm> <refsynopsisdiv> <synopsis> TupleDesc SPI_copytupledesc(TupleDesc <parameter>tupdesc</parameter>) </synopsis> </refsynopsisdiv> <refsect1> <title>Description</title> <para> <function>SPI_copytupledesc</function> makes a copy of a row descriptor in the upper executor context. </para> </refsect1> <refsect1> <title>Arguments</title> <variablelist> <varlistentry> <term><literal>TupleDesc <parameter>tupdesc</parameter></literal></term> <listitem> <para> row descriptor to be copied </para> </listitem> </varlistentry> </variablelist> </refsect1> <refsect1> <title>Return Value</title> <para> the copied row descriptor; <symbol>NULL</symbol> only if <parameter>tupdesc</parameter> is <symbol>NULL</symbol> </para> </refsect1> </refentry> <!-- *********************************************** --> <refentry id="spi-spi-copytupleintoslot"> <refmeta> <refentrytitle>SPI_copytupleintoslot</refentrytitle> </refmeta> <refnamediv> <refname>SPI_copytupleintoslot</refname> <refpurpose>make a copy of a row and descriptor in the upper executor context</refpurpose> </refnamediv> <indexterm><primary>SPI_copytupleintoslot</primary></indexterm> <refsynopsisdiv> <synopsis> TupleTableSlot * SPI_copytupleintoslot(HeapTuple <parameter>row</parameter>, TupleDesc <parameter>rowdesc</parameter>) </synopsis> </refsynopsisdiv> <refsect1> <title>Description</title> <para> <function>SPI_copytupleintoslot</function> makes a copy of a row in the upper executor context, returning it in the form of a filled-in <type>TupleTableSlot</type> structure. </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> row descriptor to be copied </para> </listitem> </varlistentry> </variablelist> </refsect1> <refsect1> <title>Return Value</title> <para> <type>TupleTableSlot</type> containing the copied row and descriptor; <symbol>NULL</symbol> only if <parameter>row</parameter> or <parameter>rowdesc</parameter> are <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 (count starts 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_execp</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-spi-freetuple"> <refmeta> <refentrytitle>SPI_freetuple</refentrytitle> </refmeta> <refnamediv> <refname>SPI_freetuple</refname> <refpurpose>frees 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> <!-- *********************************************** --> <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_exec</> 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_exec</>. 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> <!-- *********************************************** --> <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(void *<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>void * <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>. </para> </refsect1> </refentry> </sect1> <sect1 id="spi-visibility"> <title>Visibility of Data Changes</title> <para> The following two 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 (or by function called by the command, including trigger functions) are invisible to the command. For example, in command <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> </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 *command; int ret; int proc; /* Convert given text object to a C string */ command = DatumGetCString(DirectFunctionCall1(textout, PointerGetDatum(sql))); SPI_connect(); ret = SPI_exec(command, cnt); proc = SPI_processed; /* * If this is a SELECT and some rows were fetched, * then the rows are printed via elog(INFO). */ if (ret == SPI_OK_SELECT && SPI_processed > 0) { TupleDesc tupdesc = SPI_tuptable->tupdesc; SPITupleTable *tuptable = SPI_tuptable; char buf[8192]; int i, j; for (j = 0; j < proc; j++) { HeapTuple tuple = tuptable->vals[j]; 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 (INFO, "EXECQ: %s", buf); } } SPI_finish(); pfree(command); return (proc); } </programlisting> <para> (This function uses call convention version 0, to make the example easier to understand. In real applications you should user 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) => INSERT INTO a VALUES (execq('INSERT INTO a VALUES (0)', 0)); INSERT 167631 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 (1 row) => SELECT execq('INSERT INTO a SELECT x + 2 FROM a', 1); execq ------- 1 (1 row) => 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 the max value only, 3 is the real number of rows (1 row) => DELETE FROM a; DELETE 3 => INSERT INTO a VALUES (execq('SELECT * FROM a', 0) + 1); INSERT 167712 1 => SELECT * FROM a; x --- 1 -- no rows in a (0) + 1 (1 row) => INSERT INTO a VALUES (execq('SELECT * FROM a', 0) + 1); INFO: EXECQ: 0 INSERT 167713 1 => SELECT * FROM a; x --- 1 2 -- there was one row in a + 1 (2 rows) -- This demonstrates the data changes visibility rule: => 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 => 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>