1 <!-- $PostgreSQL: pgsql/doc/src/sgml/spi.sgml,v 1.48 2006/09/10 20:56:42 tgl Exp $ -->
4 <title>Server Programming Interface</title>
11 The <firstterm>Server Programming Interface</firstterm>
12 (<acronym>SPI</acronym>) gives writers of user-defined
13 <acronym>C</acronym> functions the ability to run
14 <acronym>SQL</acronym> commands inside their functions.
15 <acronym>SPI</acronym> is a set of
16 interface functions to simplify access to the parser, planner,
17 optimizer, and executor. <acronym>SPI</acronym> also does some
23 The available procedural languages provide various means to
24 execute SQL commands from procedures. Most of these facilities are
25 based on SPI, so this documentation might be of use for users
26 of those languages as well.
31 To avoid misunderstanding we'll use the term <quote>function</quote>
32 when we speak of <acronym>SPI</acronym> interface functions and
33 <quote>procedure</quote> for a user-defined C-function that is
34 using <acronym>SPI</acronym>.
38 Note that if a command invoked via SPI fails, then control will not be
39 returned to your procedure. Rather, the
40 transaction or subtransaction in which your procedure executes will be
41 rolled back. (This may seem surprising given that the SPI functions mostly
42 have documented error-return conventions. Those conventions only apply
43 for errors detected within the SPI functions themselves, however.)
44 It is possible to recover control after an error by establishing your own
45 subtransaction surrounding SPI calls that might fail. This is not currently
46 documented because the mechanisms required are still in flux.
50 <acronym>SPI</acronym> functions return a nonnegative result on
51 success (either via a returned integer value or in the global
52 variable <varname>SPI_result</varname>, as described below). On
53 error, a negative result or <symbol>NULL</symbol> will be returned.
57 Source code files that use SPI must include the header file
58 <filename>executor/spi.h</filename>.
62 <sect1 id="spi-interface">
63 <title>Interface Functions</title>
65 <refentry id="spi-spi-connect">
67 <refentrytitle>SPI_connect</refentrytitle>
71 <refname>SPI_connect</refname>
72 <refpurpose>connect a procedure to the SPI manager</refpurpose>
75 <indexterm><primary>SPI_connect</primary></indexterm>
84 <title>Description</title>
87 <function>SPI_connect</function> opens a connection from a
88 procedure invocation to the SPI manager. You must call this
89 function if you want to execute commands through SPI. Some utility
90 SPI functions may be called from unconnected procedures.
94 If your procedure is already connected,
95 <function>SPI_connect</function> will return the error code
96 <returnvalue>SPI_ERROR_CONNECT</returnvalue>. This could happen if
97 a procedure that has called <function>SPI_connect</function>
98 directly calls another procedure that calls
99 <function>SPI_connect</function>. While recursive calls to the
100 <acronym>SPI</acronym> manager are permitted when an SQL command
101 called through SPI invokes another function that uses
102 <acronym>SPI</acronym>, directly nested calls to
103 <function>SPI_connect</function> and
104 <function>SPI_finish</function> are forbidden.
105 (But see <function>SPI_push</function> and <function>SPI_pop</function>.)
110 <title>Return Value</title>
114 <term><symbol>SPI_OK_CONNECT</symbol></term>
123 <term><symbol>SPI_ERROR_CONNECT</symbol></term>
134 <!-- *********************************************** -->
136 <refentry id="spi-spi-finish">
138 <refentrytitle>SPI_finish</refentrytitle>
142 <refname>SPI_finish</refname>
143 <refpurpose>disconnect a procedure from the SPI manager</refpurpose>
146 <indexterm><primary>SPI_finish</primary></indexterm>
155 <title>Description</title>
158 <function>SPI_finish</function> closes an existing connection to
159 the SPI manager. You must call this function after completing the
160 SPI operations needed during your procedure's current invocation.
161 You do not need to worry about making this happen, however, if you
162 abort the transaction via <literal>elog(ERROR)</literal>. In that
163 case SPI will clean itself up automatically.
167 If <function>SPI_finish</function> is called without having a valid
168 connection, it will return <symbol>SPI_ERROR_UNCONNECTED</symbol>.
169 There is no fundamental problem with this; it means that the SPI
170 manager has nothing to do.
175 <title>Return Value</title>
179 <term><symbol>SPI_OK_FINISH</symbol></term>
182 if properly disconnected
188 <term><symbol>SPI_ERROR_UNCONNECTED</symbol></term>
191 if called from an unconnected procedure
199 <!-- *********************************************** -->
201 <refentry id="spi-spi-push">
203 <refentrytitle>SPI_push</refentrytitle>
207 <refname>SPI_push</refname>
208 <refpurpose>push SPI stack to allow recursive SPI usage</refpurpose>
211 <indexterm><primary>SPI_push</primary></indexterm>
220 <title>Description</title>
223 <function>SPI_push</function> should be called before executing another
224 procedure that might itself wish to use SPI.
225 After <function>SPI_push</function>, SPI is no longer in a
226 <quote>connected</> state, and SPI function calls will be rejected unless
227 a fresh <function>SPI_connect</function> is done. This ensures a clean
228 separation between your procedure's SPI state and that of another procedure
229 you call. After the other procedure returns, call
230 <function>SPI_pop</function> to restore access to your own SPI state.
234 Note that <function>SPI_execute</function> and related functions
235 automatically do the equivalent of <function>SPI_push</function> before
236 passing control back to the SQL execution engine, so it is not necessary
237 for you to worry about this when using those functions.
238 Only when you are directly calling arbitrary code that might contain
239 <function>SPI_connect</function> calls do you need to issue
240 <function>SPI_push</function> and <function>SPI_pop</function>.
246 <!-- *********************************************** -->
248 <refentry id="spi-spi-pop">
250 <refentrytitle>SPI_pop</refentrytitle>
254 <refname>SPI_pop</refname>
255 <refpurpose>pop SPI stack to return from recursive SPI usage</refpurpose>
258 <indexterm><primary>SPI_pop</primary></indexterm>
267 <title>Description</title>
270 <function>SPI_pop</function> pops the previous environment from the
271 SPI call stack. See <function>SPI_push</function>.
277 <!-- *********************************************** -->
279 <refentry id="spi-spi-execute">
281 <refentrytitle>SPI_execute</refentrytitle>
285 <refname>SPI_execute</refname>
286 <refpurpose>execute a command</refpurpose>
289 <indexterm><primary>SPI_execute</primary></indexterm>
293 int SPI_execute(const char * <parameter>command</parameter>, bool <parameter>read_only</parameter>, long <parameter>count</parameter>)
298 <title>Description</title>
301 <function>SPI_execute</function> executes the specified SQL command
302 for <parameter>count</parameter> rows. If <parameter>read_only</parameter>
303 is <literal>true</>, the command must be read-only, and execution overhead
308 This function may only be called from a connected procedure.
312 If <parameter>count</parameter> is zero then the command is executed
313 for all rows that it applies to. If <parameter>count</parameter>
314 is greater than 0, then the number of rows for which the command
315 will be executed is restricted (much like a
316 <literal>LIMIT</literal> clause). For example,
318 SPI_execute("INSERT INTO foo SELECT * FROM bar", false, 5);
320 will allow at most 5 rows to be inserted into the table.
324 You may pass multiple commands in one string.
325 <function>SPI_execute</function> returns the
326 result for the command executed last. The <parameter>count</parameter>
327 limit applies to each command separately, but it is not applied to
328 hidden commands generated by rules.
332 When <parameter>read_only</parameter> is <literal>false</>,
333 <function>SPI_execute</function> increments the command
334 counter and computes a new <firstterm>snapshot</> before executing each
335 command in the string. The snapshot does not actually change if the
336 current transaction isolation level is <literal>SERIALIZABLE</>, but in
337 <literal>READ COMMITTED</> mode the snapshot update allows each command to
338 see the results of newly committed transactions from other sessions.
339 This is essential for consistent behavior when the commands are modifying
344 When <parameter>read_only</parameter> is <literal>true</>,
345 <function>SPI_execute</function> does not update either the snapshot
346 or the command counter, and it allows only plain <command>SELECT</>
347 commands to appear in the command string. The commands are executed
348 using the snapshot previously established for the surrounding query.
349 This execution mode is somewhat faster than the read/write mode due
350 to eliminating per-command overhead. It also allows genuinely
351 <firstterm>stable</> functions to be built: since successive executions
352 will all use the same snapshot, there will be no change in the results.
356 It is generally unwise to mix read-only and read-write commands within
357 a single function using SPI; that could result in very confusing behavior,
358 since the read-only queries would not see the results of any database
359 updates done by the read-write queries.
363 The actual number of rows for which the (last) command was executed
364 is returned in the global variable <varname>SPI_processed</varname>.
365 If the return value of the function is <symbol>SPI_OK_SELECT</symbol>,
366 <symbol>SPI_OK_INSERT_RETURNING</symbol>,
367 <symbol>SPI_OK_DELETE_RETURNING</symbol>, or
368 <symbol>SPI_OK_UPDATE_RETURNING</symbol>,
370 global pointer <literal>SPITupleTable *SPI_tuptable</literal> to
371 access the result rows. Some utility commands (such as
372 <command>EXPLAIN</>) also return rowsets, and <literal>SPI_tuptable</>
373 will contain the result in these cases too.
377 The structure <structname>SPITupleTable</structname> is defined
382 MemoryContext tuptabcxt; /* memory context of result table */
383 uint32 alloced; /* number of alloced vals */
384 uint32 free; /* number of free vals */
385 TupleDesc tupdesc; /* row descriptor */
386 HeapTuple *vals; /* rows */
389 <structfield>vals</> is an array of pointers to rows. (The number
390 of valid entries is given by <varname>SPI_processed</varname>.)
391 <structfield>tupdesc</> is a row descriptor which you may pass to
392 SPI functions dealing with rows. <structfield>tuptabcxt</>,
393 <structfield>alloced</>, and <structfield>free</> are internal
394 fields not intended for use by SPI callers.
398 <function>SPI_finish</function> frees all
399 <structname>SPITupleTable</>s allocated during the current
400 procedure. You can free a particular result table earlier, if you
401 are done with it, by calling <function>SPI_freetuptable</function>.
406 <title>Arguments</title>
410 <term><literal>const char * <parameter>command</parameter></literal></term>
413 string containing command to execute
419 <term><literal>bool <parameter>read_only</parameter></literal></term>
422 <literal>true</> for read-only execution
428 <term><literal>long <parameter>count</parameter></literal></term>
431 maximum number of rows to process or return
439 <title>Return Value</title>
442 If the execution of the command was successful then one of the
443 following (nonnegative) values will be returned:
447 <term><symbol>SPI_OK_SELECT</symbol></term>
450 if a <command>SELECT</command> (but not <command>SELECT
451 INTO</>) was executed
457 <term><symbol>SPI_OK_SELINTO</symbol></term>
460 if a <command>SELECT INTO</command> was executed
466 <term><symbol>SPI_OK_INSERT</symbol></term>
469 if an <command>INSERT</command> was executed
475 <term><symbol>SPI_OK_DELETE</symbol></term>
478 if a <command>DELETE</command> was executed
484 <term><symbol>SPI_OK_UPDATE</symbol></term>
487 if an <command>UPDATE</command> was executed
493 <term><symbol>SPI_OK_INSERT_RETURNING</symbol></term>
496 if an <command>INSERT RETURNING</command> was executed
502 <term><symbol>SPI_OK_DELETE_RETURNING</symbol></term>
505 if a <command>DELETE RETURNING</command> was executed
511 <term><symbol>SPI_OK_UPDATE_RETURNING</symbol></term>
514 if an <command>UPDATE RETURNING</command> was executed
520 <term><symbol>SPI_OK_UTILITY</symbol></term>
523 if a utility command (e.g., <command>CREATE TABLE</command>)
532 On error, one of the following negative values is returned:
536 <term><symbol>SPI_ERROR_ARGUMENT</symbol></term>
539 if <parameter>command</parameter> is <symbol>NULL</symbol> or
540 <parameter>count</parameter> is less than 0
546 <term><symbol>SPI_ERROR_COPY</symbol></term>
549 if <command>COPY TO stdout</> or <command>COPY FROM stdin</>
556 <term><symbol>SPI_ERROR_CURSOR</symbol></term>
559 if <command>DECLARE</>, <command>CLOSE</>, or <command>FETCH</>
566 <term><symbol>SPI_ERROR_TRANSACTION</symbol></term>
569 if any command involving transaction manipulation was attempted
572 <command>ROLLBACK</>,
573 <command>SAVEPOINT</>,
574 <command>PREPARE TRANSACTION</>,
575 <command>COMMIT PREPARED</>,
576 <command>ROLLBACK PREPARED</>,
577 or any variant thereof)
583 <term><symbol>SPI_ERROR_OPUNKNOWN</symbol></term>
586 if the command type is unknown (shouldn't happen)
592 <term><symbol>SPI_ERROR_UNCONNECTED</symbol></term>
595 if called from an unconnected procedure
607 The functions <function>SPI_execute</function>,
608 <function>SPI_exec</function>,
609 <function>SPI_execute_plan</function>, and
610 <function>SPI_execp</function> change both
611 <varname>SPI_processed</varname> and
612 <varname>SPI_tuptable</varname> (just the pointer, not the contents
613 of the structure). Save these two global variables into local
614 procedure variables if you need to access the result table of
615 <function>SPI_execute</function> or a related function
621 <!-- *********************************************** -->
623 <refentry id="spi-spi-exec">
625 <refentrytitle>SPI_exec</refentrytitle>
629 <refname>SPI_exec</refname>
630 <refpurpose>execute a read/write command</refpurpose>
633 <indexterm><primary>SPI_exec</primary></indexterm>
637 int SPI_exec(const char * <parameter>command</parameter>, long <parameter>count</parameter>)
642 <title>Description</title>
645 <function>SPI_exec</function> is the same as
646 <function>SPI_execute</function>, with the latter's
647 <parameter>read_only</parameter> parameter always taken as
653 <title>Arguments</title>
657 <term><literal>const char * <parameter>command</parameter></literal></term>
660 string containing command to execute
666 <term><literal>long <parameter>count</parameter></literal></term>
669 maximum number of rows to process or return
677 <title>Return Value</title>
680 See <function>SPI_execute</function>.
685 <!-- *********************************************** -->
687 <refentry id="spi-spi-prepare">
689 <refentrytitle>SPI_prepare</refentrytitle>
693 <refname>SPI_prepare</refname>
694 <refpurpose>prepare a plan for a command, without executing it yet</refpurpose>
697 <indexterm><primary>SPI_prepare</primary></indexterm>
701 void * SPI_prepare(const char * <parameter>command</parameter>, int <parameter>nargs</parameter>, Oid * <parameter>argtypes</parameter>)
706 <title>Description</title>
709 <function>SPI_prepare</function> creates and returns an execution
710 plan for the specified command but doesn't execute the command.
711 This function should only be called from a connected procedure.
715 When the same or a similar command is to be executed repeatedly, it
716 may be advantageous to perform the planning only once.
717 <function>SPI_prepare</function> converts a command string into an
718 execution plan that can be executed repeatedly using
719 <function>SPI_execute_plan</function>.
723 A prepared command can be generalized by writing parameters
724 (<literal>$1</>, <literal>$2</>, etc.) in place of what would be
725 constants in a normal command. The actual values of the parameters
726 are then specified when <function>SPI_execute_plan</function> is called.
727 This allows the prepared command to be used over a wider range of
728 situations than would be possible without parameters.
732 The plan returned by <function>SPI_prepare</function> can be used
733 only in the current invocation of the procedure, since
734 <function>SPI_finish</function> frees memory allocated for a plan.
735 But a plan can be saved for longer using the function
736 <function>SPI_saveplan</function>.
741 <title>Arguments</title>
745 <term><literal>const char * <parameter>command</parameter></literal></term>
754 <term><literal>int <parameter>nargs</parameter></literal></term>
757 number of input parameters (<literal>$1</>, <literal>$2</>, etc.)
763 <term><literal>Oid * <parameter>argtypes</parameter></literal></term>
766 pointer to an array containing the <acronym>OID</acronym>s of
767 the data types of the parameters
775 <title>Return Value</title>
778 <function>SPI_prepare</function> returns a non-null pointer to an
779 execution plan. On error, <symbol>NULL</symbol> will be returned,
780 and <varname>SPI_result</varname> will be set to one of the same
781 error codes used by <function>SPI_execute</function>, except that
782 it is set to <symbol>SPI_ERROR_ARGUMENT</symbol> if
783 <parameter>command</parameter> is <symbol>NULL</symbol>, or if
784 <parameter>nargs</> is less than 0, or if <parameter>nargs</> is
785 greater than 0 and <parameter>argtypes</> is <symbol>NULL</symbol>.
793 There is a disadvantage to using parameters: since the planner does
794 not know the values that will be supplied for the parameters, it
795 may make worse planning choices than it would make for a normal
796 command with all constants visible.
801 <!-- *********************************************** -->
803 <refentry id="spi-spi-getargcount">
805 <refentrytitle>SPI_getargcount</refentrytitle>
809 <refname>SPI_getargcount</refname>
810 <refpurpose>return the number of arguments needed by a plan
811 prepared by <function>SPI_prepare</function></refpurpose>
814 <indexterm><primary>SPI_getargcount</primary></indexterm>
818 int SPI_getargcount(void * <parameter>plan</parameter>)
823 <title>Description</title>
826 <function>SPI_getargcount</function> returns the number of arguments needed
827 to execute a plan prepared by <function>SPI_prepare</function>.
832 <title>Arguments</title>
836 <term><literal>void * <parameter>plan</parameter></literal></term>
839 execution plan (returned by <function>SPI_prepare</function>)
847 <title>Return Value</title>
849 The expected argument count for the <parameter>plan</parameter>, or
850 <symbol>SPI_ERROR_ARGUMENT</symbol> if the <parameter>plan
851 </parameter> is <symbol>NULL</symbol>
856 <!-- *********************************************** -->
858 <refentry id="spi-spi-getargtypeid">
860 <refentrytitle>SPI_getargtypeid</refentrytitle>
864 <refname>SPI_getargtypeid</refname>
865 <refpurpose>return the data type OID for an argument of
866 a plan prepared by <function>SPI_prepare</function></refpurpose>
869 <indexterm><primary>SPI_getargtypeid</primary></indexterm>
873 Oid SPI_getargtypeid(void * <parameter>plan</parameter>, int <parameter>argIndex</parameter>)
878 <title>Description</title>
881 <function>SPI_getargtypeid</function> returns the OID representing the type
882 id for the <parameter>argIndex</parameter>'th argument of a plan prepared by
883 <function>SPI_prepare</function>. First argument is at index zero.
888 <title>Arguments</title>
892 <term><literal>void * <parameter>plan</parameter></literal></term>
895 execution plan (returned by <function>SPI_prepare</function>)
901 <term><literal>int <parameter>argIndex</parameter></literal></term>
904 zero based index of the argument
912 <title>Return Value</title>
914 The type id of the argument at the given index, or
915 <symbol>SPI_ERROR_ARGUMENT</symbol> if the <parameter>plan</parameter> is
916 <symbol>NULL</symbol> or <parameter>argIndex</parameter> is less than 0 or
917 not less than the number of arguments declared for the
918 <parameter>plan</parameter>
923 <!-- *********************************************** -->
925 <refentry id="spi-spi-is-cursor-plan">
927 <refentrytitle>SPI_is_cursor_plan</refentrytitle>
931 <refname>SPI_is_cursor_plan</refname>
932 <refpurpose>return <symbol>true</symbol> if a plan
933 prepared by <function>SPI_prepare</function> can be used with
934 <function>SPI_cursor_open</function></refpurpose>
937 <indexterm><primary>SPI_is_cursor_plan</primary></indexterm>
941 bool SPI_is_cursor_plan(void * <parameter>plan</parameter>)
946 <title>Description</title>
949 <function>SPI_is_cursor_plan</function> returns <symbol>true</symbol>
950 if a plan prepared by <function>SPI_prepare</function> can be passed
951 as an argument to <function>SPI_cursor_open</function>, or
952 <symbol>false</symbol> if that is not the case. The criteria are that the
953 <parameter>plan</parameter> represents one single command and that this
954 command returns tuples to the caller; for example, <command>SELECT</>
955 is allowed unless it contains an <literal>INTO</> clause, and
956 <command>UPDATE</> is allowed only if it contains a <literal>RETURNING</>
962 <title>Arguments</title>
966 <term><literal>void * <parameter>plan</parameter></literal></term>
969 execution plan (returned by <function>SPI_prepare</function>)
977 <title>Return Value</title>
979 <symbol>true</symbol> or <symbol>false</symbol> to indicate if the
980 <parameter>plan</parameter> can produce a cursor or not, or
981 <symbol>SPI_ERROR_ARGUMENT</symbol> if the <parameter>plan</parameter>
982 is <symbol>NULL</symbol>
987 <!-- *********************************************** -->
989 <refentry id="spi-spi-execute-plan">
991 <refentrytitle>SPI_execute_plan</refentrytitle>
995 <refname>SPI_execute_plan</refname>
996 <refpurpose>execute a plan prepared by <function>SPI_prepare</function></refpurpose>
999 <indexterm><primary>SPI_execute_plan</primary></indexterm>
1003 int SPI_execute_plan(void * <parameter>plan</parameter>, Datum * <parameter>values</parameter>, const char * <parameter>nulls</parameter>,
1004 bool <parameter>read_only</parameter>, long <parameter>count</parameter>)
1009 <title>Description</title>
1012 <function>SPI_execute_plan</function> executes a plan prepared by
1013 <function>SPI_prepare</function>. <parameter>read_only</parameter> and
1014 <parameter>count</parameter> have the same interpretation as in
1015 <function>SPI_execute</function>.
1020 <title>Arguments</title>
1024 <term><literal>void * <parameter>plan</parameter></literal></term>
1027 execution plan (returned by <function>SPI_prepare</function>)
1033 <term><literal>Datum * <parameter>values</parameter></literal></term>
1036 An array of actual parameter values. Must have same length as the
1037 plan's number of arguments.
1043 <term><literal>const char * <parameter>nulls</parameter></literal></term>
1046 An array describing which parameters are null. Must have same length as
1047 the plan's number of arguments.
1048 <literal>n</literal> indicates a null value (entry in
1049 <parameter>values</> will be ignored); a space indicates a
1050 nonnull value (entry in <parameter>values</> is valid).
1054 If <parameter>nulls</parameter> is <symbol>NULL</symbol> then
1055 <function>SPI_execute_plan</function> assumes that no parameters are
1062 <term><literal>bool <parameter>read_only</parameter></literal></term>
1065 <literal>true</> for read-only execution
1071 <term><literal>long <parameter>count</parameter></literal></term>
1074 maximum number of rows to process or return
1082 <title>Return Value</title>
1085 The return value is the same as for <function>SPI_execute</function>,
1086 with the following additional possible error (negative) results:
1090 <term><symbol>SPI_ERROR_ARGUMENT</symbol></term>
1093 if <parameter>plan</parameter> is <symbol>NULL</symbol> or
1094 <parameter>count</parameter> is less than 0
1100 <term><symbol>SPI_ERROR_PARAM</symbol></term>
1103 if <parameter>values</parameter> is <symbol>NULL</symbol> and
1104 <parameter>plan</parameter> was prepared with some parameters
1112 <varname>SPI_processed</varname> and
1113 <varname>SPI_tuptable</varname> are set as in
1114 <function>SPI_execute</function> if successful.
1119 <title>Notes</title>
1122 If one of the objects (a table, function, etc.) referenced by the
1123 prepared plan is dropped during the session then the result of
1124 <function>SPI_execute_plan</function> for this plan will be unpredictable.
1129 <!-- *********************************************** -->
1131 <refentry id="spi-spi-execp">
1133 <refentrytitle>SPI_execp</refentrytitle>
1137 <refname>SPI_execp</refname>
1138 <refpurpose>execute a plan in read/write mode</refpurpose>
1141 <indexterm><primary>SPI_execp</primary></indexterm>
1145 int SPI_execp(void * <parameter>plan</parameter>, Datum * <parameter>values</parameter>, const char * <parameter>nulls</parameter>, long <parameter>count</parameter>)
1150 <title>Description</title>
1153 <function>SPI_execp</function> is the same as
1154 <function>SPI_execute_plan</function>, with the latter's
1155 <parameter>read_only</parameter> parameter always taken as
1161 <title>Arguments</title>
1165 <term><literal>void * <parameter>plan</parameter></literal></term>
1168 execution plan (returned by <function>SPI_prepare</function>)
1174 <term><literal>Datum * <parameter>values</parameter></literal></term>
1177 An array of actual parameter values. Must have same length as the
1178 plan's number of arguments.
1184 <term><literal>const char * <parameter>nulls</parameter></literal></term>
1187 An array describing which parameters are null. Must have same length as
1188 the plan's number of arguments.
1189 <literal>n</literal> indicates a null value (entry in
1190 <parameter>values</> will be ignored); a space indicates a
1191 nonnull value (entry in <parameter>values</> is valid).
1195 If <parameter>nulls</parameter> is <symbol>NULL</symbol> then
1196 <function>SPI_execp</function> assumes that no parameters are
1203 <term><literal>long <parameter>count</parameter></literal></term>
1206 maximum number of rows to process or return
1214 <title>Return Value</title>
1217 See <function>SPI_execute_plan</function>.
1221 <varname>SPI_processed</varname> and
1222 <varname>SPI_tuptable</varname> are set as in
1223 <function>SPI_execute</function> if successful.
1228 <!-- *********************************************** -->
1230 <refentry id="spi-spi-cursor-open">
1232 <refentrytitle>SPI_cursor_open</refentrytitle>
1236 <refname>SPI_cursor_open</refname>
1237 <refpurpose>set up a cursor using a plan created with <function>SPI_prepare</function></refpurpose>
1240 <indexterm><primary>SPI_cursor_open</primary></indexterm>
1244 Portal SPI_cursor_open(const char * <parameter>name</parameter>, void * <parameter>plan</parameter>,
1245 Datum * <parameter>values</parameter>, const char * <parameter>nulls</parameter>,
1246 bool <parameter>read_only</parameter>)
1251 <title>Description</title>
1254 <function>SPI_cursor_open</function> sets up a cursor (internally,
1255 a portal) that will execute a plan prepared by
1256 <function>SPI_prepare</function>. The parameters have the same
1257 meanings as the corresponding parameters to
1258 <function>SPI_execute_plan</function>.
1262 Using a cursor instead of executing the plan directly has two
1263 benefits. First, the result rows can be retrieved a few at a time,
1264 avoiding memory overrun for queries that return many rows. Second,
1265 a portal can outlive the current procedure (it can, in fact, live
1266 to the end of the current transaction). Returning the portal name
1267 to the procedure's caller provides a way of returning a row set as
1273 <title>Arguments</title>
1277 <term><literal>const char * <parameter>name</parameter></literal></term>
1280 name for portal, or <symbol>NULL</symbol> to let the system
1287 <term><literal>void * <parameter>plan</parameter></literal></term>
1290 execution plan (returned by <function>SPI_prepare</function>)
1296 <term><literal>Datum * <parameter>values</parameter></literal></term>
1299 An array of actual parameter values. Must have same length as the
1300 plan's number of arguments.
1306 <term><literal>const char * <parameter>nulls</parameter></literal></term>
1309 An array describing which parameters are null. Must have same length as
1310 the plan's number of arguments.
1311 <literal>n</literal> indicates a null value (entry in
1312 <parameter>values</> will be ignored); a space indicates a
1313 nonnull value (entry in <parameter>values</> is valid).
1317 If <parameter>nulls</parameter> is <symbol>NULL</symbol> then
1318 <function>SPI_cursor_open</function> assumes that no parameters are
1325 <term><literal>bool <parameter>read_only</parameter></literal></term>
1328 <literal>true</> for read-only execution
1336 <title>Return Value</title>
1339 pointer to portal containing the cursor, or <symbol>NULL</symbol>
1345 <!-- *********************************************** -->
1347 <refentry id="spi-spi-cursor-find">
1349 <refentrytitle>SPI_cursor_find</refentrytitle>
1353 <refname>SPI_cursor_find</refname>
1354 <refpurpose>find an existing cursor by name</refpurpose>
1357 <indexterm><primary>SPI_cursor_find</primary></indexterm>
1361 Portal SPI_cursor_find(const char * <parameter>name</parameter>)
1366 <title>Description</title>
1369 <function>SPI_cursor_find</function> finds an existing portal by
1370 name. This is primarily useful to resolve a cursor name returned
1371 as text by some other function.
1376 <title>Arguments</title>
1380 <term><literal>const char * <parameter>name</parameter></literal></term>
1391 <title>Return Value</title>
1394 pointer to the portal with the specified name, or
1395 <symbol>NULL</symbol> if none was found
1400 <!-- *********************************************** -->
1402 <refentry id="spi-spi-cursor-fetch">
1404 <refentrytitle>SPI_cursor_fetch</refentrytitle>
1408 <refname>SPI_cursor_fetch</refname>
1409 <refpurpose>fetch some rows from a cursor</refpurpose>
1412 <indexterm><primary>SPI_cursor_fetch</primary></indexterm>
1416 void SPI_cursor_fetch(Portal <parameter>portal</parameter>, bool <parameter>forward</parameter>, long <parameter>count</parameter>)
1421 <title>Description</title>
1424 <function>SPI_cursor_fetch</function> fetches some rows from a
1425 cursor. This is equivalent to the SQL command <command>FETCH</>.
1430 <title>Arguments</title>
1434 <term><literal>Portal <parameter>portal</parameter></literal></term>
1437 portal containing the cursor
1443 <term><literal>bool <parameter>forward</parameter></literal></term>
1446 true for fetch forward, false for fetch backward
1452 <term><literal>long <parameter>count</parameter></literal></term>
1455 maximum number of rows to fetch
1463 <title>Return Value</title>
1466 <varname>SPI_processed</varname> and
1467 <varname>SPI_tuptable</varname> are set as in
1468 <function>SPI_execute</function> if successful.
1473 <!-- *********************************************** -->
1475 <refentry id="spi-spi-cursor-move">
1477 <refentrytitle>SPI_cursor_move</refentrytitle>
1481 <refname>SPI_cursor_move</refname>
1482 <refpurpose>move a cursor</refpurpose>
1485 <indexterm><primary>SPI_cursor_move</primary></indexterm>
1489 void SPI_cursor_move(Portal <parameter>portal</parameter>, bool <parameter>forward</parameter>, long <parameter>count</parameter>)
1494 <title>Description</title>
1497 <function>SPI_cursor_move</function> skips over some number of rows
1498 in a cursor. This is equivalent to the SQL command
1504 <title>Arguments</title>
1508 <term><literal>Portal <parameter>portal</parameter></literal></term>
1511 portal containing the cursor
1517 <term><literal>bool <parameter>forward</parameter></literal></term>
1520 true for move forward, false for move backward
1526 <term><literal>long <parameter>count</parameter></literal></term>
1529 maximum number of rows to move
1537 <!-- *********************************************** -->
1539 <refentry id="spi-spi-cursor-close">
1541 <refentrytitle>SPI_cursor_close</refentrytitle>
1545 <refname>SPI_cursor_close</refname>
1546 <refpurpose>close a cursor</refpurpose>
1549 <indexterm><primary>SPI_cursor_close</primary></indexterm>
1553 void SPI_cursor_close(Portal <parameter>portal</parameter>)
1558 <title>Description</title>
1561 <function>SPI_cursor_close</function> closes a previously created
1562 cursor and releases its portal storage.
1566 All open cursors are closed automatically at the end of a
1567 transaction. <function>SPI_cursor_close</function> need only be
1568 invoked if it is desirable to release resources sooner.
1573 <title>Arguments</title>
1577 <term><literal>Portal <parameter>portal</parameter></literal></term>
1580 portal containing the cursor
1588 <!-- *********************************************** -->
1590 <refentry id="spi-spi-saveplan">
1592 <refentrytitle>SPI_saveplan</refentrytitle>
1596 <refname>SPI_saveplan</refname>
1597 <refpurpose>save a plan</refpurpose>
1600 <indexterm><primary>SPI_saveplan</primary></indexterm>
1604 void * SPI_saveplan(void * <parameter>plan</parameter>)
1609 <title>Description</title>
1612 <function>SPI_saveplan</function> saves a passed plan (prepared by
1613 <function>SPI_prepare</function>) in memory protected from freeing
1614 by <function>SPI_finish</function> and by the transaction manager
1615 and returns a pointer to the saved plan. This gives you the
1616 ability to reuse prepared plans in the subsequent invocations of
1617 your procedure in the current session.
1622 <title>Arguments</title>
1626 <term><literal>void * <parameter>plan</parameter></literal></term>
1629 the plan to be saved
1637 <title>Return Value</title>
1640 Pointer to the saved plan; <symbol>NULL</symbol> if unsuccessful.
1641 On error, <varname>SPI_result</varname> is set thus:
1645 <term><symbol>SPI_ERROR_ARGUMENT</symbol></term>
1648 if <parameter>plan</parameter> is <symbol>NULL</symbol>
1654 <term><symbol>SPI_ERROR_UNCONNECTED</symbol></term>
1657 if called from an unconnected procedure
1666 <title>Notes</title>
1669 If one of the objects (a table, function, etc.) referenced by the
1670 prepared plan is dropped during the session then the results of
1671 <function>SPI_execute_plan</function> for this plan will be unpredictable.
1678 <sect1 id="spi-interface-support">
1679 <title>Interface Support Functions</title>
1682 The functions described here provide an interface for extracting
1683 information from result sets returned by <function>SPI_execute</> and
1684 other SPI functions.
1688 All functions described in this section may be used by both
1689 connected and unconnected procedures.
1692 <!-- *********************************************** -->
1694 <refentry id="spi-spi-fname">
1696 <refentrytitle>SPI_fname</refentrytitle>
1700 <refname>SPI_fname</refname>
1701 <refpurpose>determine the column name for the specified column number</refpurpose>
1704 <indexterm><primary>SPI_fname</primary></indexterm>
1708 char * SPI_fname(TupleDesc <parameter>rowdesc</parameter>, int <parameter>colnumber</parameter>)
1713 <title>Description</title>
1716 <function>SPI_fname</function> returns a copy of the column name of the
1717 specified column. (You can use <function>pfree</function> to
1718 release the copy of the name when you don't need it anymore.)
1723 <title>Arguments</title>
1727 <term><literal>TupleDesc <parameter>rowdesc</parameter></literal></term>
1730 input row description
1736 <term><literal>int <parameter>colnumber</parameter></literal></term>
1739 column number (count starts at 1)
1747 <title>Return Value</title>
1750 The column name; <symbol>NULL</symbol> if
1751 <parameter>colnumber</parameter> is out of range.
1752 <varname>SPI_result</varname> set to
1753 <symbol>SPI_ERROR_NOATTRIBUTE</symbol> on error.
1758 <!-- *********************************************** -->
1760 <refentry id="spi-spi-fnumber">
1762 <refentrytitle>SPI_fnumber</refentrytitle>
1766 <refname>SPI_fnumber</refname>
1767 <refpurpose>determine the column number for the specified column name</refpurpose>
1770 <indexterm><primary>SPI_fnumber</primary></indexterm>
1774 int SPI_fnumber(TupleDesc <parameter>rowdesc</parameter>, const char * <parameter>colname</parameter>)
1779 <title>Description</title>
1782 <function>SPI_fnumber</function> returns the column number for the
1783 column with the specified name.
1787 If <parameter>colname</parameter> refers to a system column (e.g.,
1788 <literal>oid</>) then the appropriate negative column number will
1789 be returned. The caller should be careful to test the return value
1790 for exact equality to <symbol>SPI_ERROR_NOATTRIBUTE</symbol> to
1791 detect an error; testing the result for less than or equal to 0 is
1792 not correct unless system columns should be rejected.
1797 <title>Arguments</title>
1801 <term><literal>TupleDesc <parameter>rowdesc</parameter></literal></term>
1804 input row description
1810 <term><literal>const char * <parameter>colname</parameter></literal></term>
1821 <title>Return Value</title>
1824 Column number (count starts at 1), or
1825 <symbol>SPI_ERROR_NOATTRIBUTE</symbol> if the named column was not
1831 <!-- *********************************************** -->
1833 <refentry id="spi-spi-getvalue">
1835 <refentrytitle>SPI_getvalue</refentrytitle>
1839 <refname>SPI_getvalue</refname>
1840 <refpurpose>return the string value of the specified column</refpurpose>
1843 <indexterm><primary>SPI_getvalue</primary></indexterm>
1847 char * SPI_getvalue(HeapTuple <parameter>row</parameter>, TupleDesc <parameter>rowdesc</parameter>, int <parameter>colnumber</parameter>)
1852 <title>Description</title>
1855 <function>SPI_getvalue</function> returns the string representation
1856 of the value of the specified column.
1860 The result is returned in memory allocated using
1861 <function>palloc</function>. (You can use
1862 <function>pfree</function> to release the memory when you don't
1868 <title>Arguments</title>
1872 <term><literal>HeapTuple <parameter>row</parameter></literal></term>
1875 input row to be examined
1881 <term><literal>TupleDesc <parameter>rowdesc</parameter></literal></term>
1884 input row description
1890 <term><literal>int <parameter>colnumber</parameter></literal></term>
1893 column number (count starts at 1)
1901 <title>Return Value</title>
1904 Column value, or <symbol>NULL</symbol> if the column is null,
1905 <parameter>colnumber</parameter> is out of range
1906 (<varname>SPI_result</varname> is set to
1907 <symbol>SPI_ERROR_NOATTRIBUTE</symbol>), or no no output function
1908 available (<varname>SPI_result</varname> is set to
1909 <symbol>SPI_ERROR_NOOUTFUNC</symbol>).
1914 <!-- *********************************************** -->
1916 <refentry id="spi-spi-getbinval">
1918 <refentrytitle>SPI_getbinval</refentrytitle>
1922 <refname>SPI_getbinval</refname>
1923 <refpurpose>return the binary value of the specified column</refpurpose>
1926 <indexterm><primary>SPI_getbinval</primary></indexterm>
1930 Datum SPI_getbinval(HeapTuple <parameter>row</parameter>, TupleDesc <parameter>rowdesc</parameter>, int <parameter>colnumber</parameter>, bool * <parameter>isnull</parameter>)
1935 <title>Description</title>
1938 <function>SPI_getbinval</function> returns the value of the
1939 specified column in the internal form (as type <type>Datum</type>).
1943 This function does not allocate new space for the datum. In the
1944 case of a pass-by-reference data type, the return value will be a
1945 pointer into the passed row.
1950 <title>Arguments</title>
1954 <term><literal>HeapTuple <parameter>row</parameter></literal></term>
1957 input row to be examined
1963 <term><literal>TupleDesc <parameter>rowdesc</parameter></literal></term>
1966 input row description
1972 <term><literal>int <parameter>colnumber</parameter></literal></term>
1975 column number (count starts at 1)
1981 <term><literal>bool * <parameter>isnull</parameter></literal></term>
1984 flag for a null value in the column
1992 <title>Return Value</title>
1995 The binary value of the column is returned. The variable pointed
1996 to by <parameter>isnull</parameter> is set to true if the column is
1997 null, else to false.
2001 <varname>SPI_result</varname> is set to
2002 <symbol>SPI_ERROR_NOATTRIBUTE</symbol> on error.
2007 <!-- *********************************************** -->
2009 <refentry id="spi-spi-gettype">
2011 <refentrytitle>SPI_gettype</refentrytitle>
2015 <refname>SPI_gettype</refname>
2016 <refpurpose>return the data type name of the specified column</refpurpose>
2019 <indexterm><primary>SPI_gettype</primary></indexterm>
2023 char * SPI_gettype(TupleDesc <parameter>rowdesc</parameter>, int <parameter>colnumber</parameter>)
2028 <title>Description</title>
2031 <function>SPI_gettype</function> returns a copy of the data type name of the
2032 specified column. (You can use <function>pfree</function> to
2033 release the copy of the name when you don't need it anymore.)
2038 <title>Arguments</title>
2042 <term><literal>TupleDesc <parameter>rowdesc</parameter></literal></term>
2045 input row description
2051 <term><literal>int <parameter>colnumber</parameter></literal></term>
2054 column number (count starts at 1)
2062 <title>Return Value</title>
2065 The data type name of the specified column, or
2066 <symbol>NULL</symbol> on error. <varname>SPI_result</varname> is
2067 set to <symbol>SPI_ERROR_NOATTRIBUTE</symbol> on error.
2072 <!-- *********************************************** -->
2074 <refentry id="spi-spi-gettypeid">
2076 <refentrytitle>SPI_gettypeid</refentrytitle>
2080 <refname>SPI_gettypeid</refname>
2081 <refpurpose>return the data type <acronym>OID</acronym> of the specified column</refpurpose>
2084 <indexterm><primary>SPI_gettypeid</primary></indexterm>
2088 Oid SPI_gettypeid(TupleDesc <parameter>rowdesc</parameter>, int <parameter>colnumber</parameter>)
2093 <title>Description</title>
2096 <function>SPI_gettypeid</function> returns the
2097 <acronym>OID</acronym> of the data type of the specified column.
2102 <title>Arguments</title>
2106 <term><literal>TupleDesc <parameter>rowdesc</parameter></literal></term>
2109 input row description
2115 <term><literal>int <parameter>colnumber</parameter></literal></term>
2118 column number (count starts at 1)
2126 <title>Return Value</title>
2129 The <acronym>OID</acronym> of the data type of the specified column
2130 or <symbol>InvalidOid</symbol> on error. On error,
2131 <varname>SPI_result</varname> is set to
2132 <symbol>SPI_ERROR_NOATTRIBUTE</symbol>.
2137 <!-- *********************************************** -->
2139 <refentry id="spi-spi-getrelname">
2141 <refentrytitle>SPI_getrelname</refentrytitle>
2145 <refname>SPI_getrelname</refname>
2146 <refpurpose>return the name of the specified relation</refpurpose>
2149 <indexterm><primary>SPI_getrelname</primary></indexterm>
2153 char * SPI_getrelname(Relation <parameter>rel</parameter>)
2158 <title>Description</title>
2161 <function>SPI_getrelname</function> returns a copy of the name of the
2162 specified relation. (You can use <function>pfree</function> to
2163 release the copy of the name when you don't need it anymore.)
2168 <title>Arguments</title>
2172 <term><literal>Relation <parameter>rel</parameter></literal></term>
2183 <title>Return Value</title>
2186 The name of the specified relation.
2191 <refentry id="spi-spi-getnspname">
2193 <refentrytitle>SPI_getnspname</refentrytitle>
2197 <refname>SPI_getnspname</refname>
2198 <refpurpose>return the namespace of the specified relation</refpurpose>
2201 <indexterm><primary>SPI_getnspname</primary></indexterm>
2205 char * SPI_getnspname(Relation <parameter>rel</parameter>)
2210 <title>Description</title>
2213 <function>SPI_getnspname</function> returns a copy of the name of
2214 the namespace that the specified <structname>Relation</structname>
2215 belongs to. This is equivalent to the relation's schema. You should
2216 <function>pfree</function> the return value of this function when
2217 you are finished with it.
2222 <title>Arguments</title>
2226 <term><literal>Relation <parameter>rel</parameter></literal></term>
2237 <title>Return Value</title>
2240 The name of the specified relation's namespace.
2247 <sect1 id="spi-memory">
2248 <title>Memory Management</title>
2251 <productname>PostgreSQL</productname> allocates memory within
2252 <firstterm>memory contexts</firstterm><indexterm><primary>memory
2253 context</primary><secondary>in SPI</secondary></indexterm>, which provide a convenient method of
2254 managing allocations made in many different places that need to
2255 live for differing amounts of time. Destroying a context releases
2256 all the memory that was allocated in it. Thus, it is not necessary
2257 to keep track of individual objects to avoid memory leaks; instead
2258 only a relatively small number of contexts have to be managed.
2259 <function>palloc</function> and related functions allocate memory
2260 from the <quote>current</> context.
2264 <function>SPI_connect</function> creates a new memory context and
2265 makes it current. <function>SPI_finish</function> restores the
2266 previous current memory context and destroys the context created by
2267 <function>SPI_connect</function>. These actions ensure that
2268 transient memory allocations made inside your procedure are
2269 reclaimed at procedure exit, avoiding memory leakage.
2273 However, if your procedure needs to return an object in allocated
2274 memory (such as a value of a pass-by-reference data type), you
2275 cannot allocate that memory using <function>palloc</function>, at
2276 least not while you are connected to SPI. If you try, the object
2277 will be deallocated by <function>SPI_finish</function>, and your
2278 procedure will not work reliably. To solve this problem, use
2279 <function>SPI_palloc</function> to allocate memory for your return
2280 object. <function>SPI_palloc</function> allocates memory in the
2281 <quote>upper executor context</quote>, that is, the memory context
2282 that was current when <function>SPI_connect</function> was called,
2283 which is precisely the right context for a value returned from your
2288 If <function>SPI_palloc</function> is called while the procedure is
2289 not connected to SPI, then it acts the same as a normal
2290 <function>palloc</function>. Before a procedure connects to the
2291 SPI manager, the current memory context is the upper executor
2292 context, so all allocations made by the procedure via
2293 <function>palloc</function> or by SPI utility functions are made in
2298 When <function>SPI_connect</function> is called, the private
2299 context of the procedure, which is created by
2300 <function>SPI_connect</function>, is made the current context. All
2301 allocations made by <function>palloc</function>,
2302 <function>repalloc</function>, or SPI utility functions (except for
2303 <function>SPI_copytuple</function>,
2304 <function>SPI_returntuple</function>,
2305 <function>SPI_modifytuple</function>, and
2306 <function>SPI_palloc</function>) are made in this context. When a
2307 procedure disconnects from the SPI manager (via
2308 <function>SPI_finish</function>) the current context is restored to
2309 the upper executor context, and all allocations made in the
2310 procedure memory context are freed and cannot be used any more.
2314 All functions described in this section may be used by both
2315 connected and unconnected procedures. In an unconnected procedure,
2316 they act the same as the underlying ordinary server functions
2317 (<function>palloc</>, etc.).
2320 <!-- *********************************************** -->
2322 <refentry id="spi-spi-palloc">
2324 <refentrytitle>SPI_palloc</refentrytitle>
2328 <refname>SPI_palloc</refname>
2329 <refpurpose>allocate memory in the upper executor context</refpurpose>
2332 <indexterm><primary>SPI_palloc</primary></indexterm>
2336 void * SPI_palloc(Size <parameter>size</parameter>)
2341 <title>Description</title>
2344 <function>SPI_palloc</function> allocates memory in the upper
2350 <title>Arguments</title>
2354 <term><literal>Size <parameter>size</parameter></literal></term>
2357 size in bytes of storage to allocate
2365 <title>Return Value</title>
2368 pointer to new storage space of the specified size
2373 <!-- *********************************************** -->
2375 <refentry id="spi-realloc">
2377 <refentrytitle>SPI_repalloc</refentrytitle>
2381 <refname>SPI_repalloc</refname>
2382 <refpurpose>reallocate memory in the upper executor context</refpurpose>
2385 <indexterm><primary>SPI_repalloc</primary></indexterm>
2389 void * SPI_repalloc(void * <parameter>pointer</parameter>, Size <parameter>size</parameter>)
2394 <title>Description</title>
2397 <function>SPI_repalloc</function> changes the size of a memory
2398 segment previously allocated using <function>SPI_palloc</function>.
2402 This function is no longer different from plain
2403 <function>repalloc</function>. It's kept just for backward
2404 compatibility of existing code.
2409 <title>Arguments</title>
2413 <term><literal>void * <parameter>pointer</parameter></literal></term>
2416 pointer to existing storage to change
2422 <term><literal>Size <parameter>size</parameter></literal></term>
2425 size in bytes of storage to allocate
2433 <title>Return Value</title>
2436 pointer to new storage space of specified size with the contents
2437 copied from the existing area
2442 <!-- *********************************************** -->
2444 <refentry id="spi-spi-pfree">
2446 <refentrytitle>SPI_pfree</refentrytitle>
2450 <refname>SPI_pfree</refname>
2451 <refpurpose>free memory in the upper executor context</refpurpose>
2454 <indexterm><primary>SPI_pfree</primary></indexterm>
2458 void SPI_pfree(void * <parameter>pointer</parameter>)
2463 <title>Description</title>
2466 <function>SPI_pfree</function> frees memory previously allocated
2467 using <function>SPI_palloc</function> or
2468 <function>SPI_repalloc</function>.
2472 This function is no longer different from plain
2473 <function>pfree</function>. It's kept just for backward
2474 compatibility of existing code.
2479 <title>Arguments</title>
2483 <term><literal>void * <parameter>pointer</parameter></literal></term>
2486 pointer to existing storage to free
2494 <!-- *********************************************** -->
2496 <refentry id="spi-spi-copytuple">
2498 <refentrytitle>SPI_copytuple</refentrytitle>
2502 <refname>SPI_copytuple</refname>
2503 <refpurpose>make a copy of a row in the upper executor context</refpurpose>
2506 <indexterm><primary>SPI_copytuple</primary></indexterm>
2510 HeapTuple SPI_copytuple(HeapTuple <parameter>row</parameter>)
2515 <title>Description</title>
2518 <function>SPI_copytuple</function> makes a copy of a row in the
2519 upper executor context. This is normally used to return a modified
2520 row from a trigger. In a function declared to return a composite
2521 type, use <function>SPI_returntuple</function> instead.
2526 <title>Arguments</title>
2530 <term><literal>HeapTuple <parameter>row</parameter></literal></term>
2541 <title>Return Value</title>
2544 the copied row; <symbol>NULL</symbol> only if
2545 <parameter>tuple</parameter> is <symbol>NULL</symbol>
2550 <!-- *********************************************** -->
2552 <refentry id="spi-spi-returntuple">
2554 <refentrytitle>SPI_returntuple</refentrytitle>
2558 <refname>SPI_returntuple</refname>
2559 <refpurpose>prepare to return a tuple as a Datum</refpurpose>
2562 <indexterm><primary>SPI_returntuple</primary></indexterm>
2566 HeapTupleHeader SPI_returntuple(HeapTuple <parameter>row</parameter>, TupleDesc <parameter>rowdesc</parameter>)
2571 <title>Description</title>
2574 <function>SPI_returntuple</function> makes a copy of a row in
2575 the upper executor context, returning it in the form of a row type <type>Datum</type>.
2576 The returned pointer need only be converted to <type>Datum</type> via <function>PointerGetDatum</function>
2581 Note that this should be used for functions that are declared to return
2582 composite types. It is not used for triggers; use
2583 <function>SPI_copytuple</> for returning a modified row in a trigger.
2588 <title>Arguments</title>
2592 <term><literal>HeapTuple <parameter>row</parameter></literal></term>
2601 <term><literal>TupleDesc <parameter>rowdesc</parameter></literal></term>
2604 descriptor for row (pass the same descriptor each time for most
2613 <title>Return Value</title>
2616 <type>HeapTupleHeader</type> pointing to copied row;
2617 <symbol>NULL</symbol> only if
2618 <parameter>row</parameter> or <parameter>rowdesc</parameter> is
2619 <symbol>NULL</symbol>
2624 <!-- *********************************************** -->
2626 <refentry id="spi-spi-modifytuple">
2628 <refentrytitle>SPI_modifytuple</refentrytitle>
2632 <refname>SPI_modifytuple</refname>
2633 <refpurpose>create a row by replacing selected fields of a given row</refpurpose>
2636 <indexterm><primary>SPI_modifytuple</primary></indexterm>
2640 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>)
2645 <title>Description</title>
2648 <function>SPI_modifytuple</function> creates a new row by
2649 substituting new values for selected columns, copying the original
2650 row's columns at other positions. The input row is not modified.
2655 <title>Arguments</title>
2659 <term><literal>Relation <parameter>rel</parameter></literal></term>
2662 Used only as the source of the row descriptor for the row.
2663 (Passing a relation rather than a row descriptor is a
2670 <term><literal>HeapTuple <parameter>row</parameter></literal></term>
2679 <term><literal>int <parameter>ncols</parameter></literal></term>
2682 number of column numbers in the array
2683 <parameter>colnum</parameter>
2689 <term><literal>int * <parameter>colnum</parameter></literal></term>
2692 array of the numbers of the columns that are to be changed
2693 (column numbers start at 1)
2699 <term><literal>Datum * <parameter>values</parameter></literal></term>
2702 new values for the specified columns
2708 <term><literal>const char * <parameter>Nulls</parameter></literal></term>
2711 which new values are null, if any (see
2712 <function>SPI_execute_plan</function> for the format)
2720 <title>Return Value</title>
2723 new row with modifications, allocated in the upper executor
2724 context; <symbol>NULL</symbol> only if <parameter>row</parameter>
2725 is <symbol>NULL</symbol>
2729 On error, <varname>SPI_result</varname> is set as follows:
2732 <term><symbol>SPI_ERROR_ARGUMENT</symbol></term>
2735 if <parameter>rel</> is <symbol>NULL</>, or if
2736 <parameter>row</> is <symbol>NULL</>, or if <parameter>ncols</>
2737 is less than or equal to 0, or if <parameter>colnum</> is
2738 <symbol>NULL</>, or if <parameter>values</> is <symbol>NULL</>.
2744 <term><symbol>SPI_ERROR_NOATTRIBUTE</symbol></term>
2747 if <parameter>colnum</> contains an invalid column number (less
2748 than or equal to 0 or greater than the number of column in
2758 <!-- *********************************************** -->
2760 <refentry id="spi-spi-freetuple">
2762 <refentrytitle>SPI_freetuple</refentrytitle>
2766 <refname>SPI_freetuple</refname>
2767 <refpurpose>free a row allocated in the upper executor context</refpurpose>
2770 <indexterm><primary>SPI_freetuple</primary></indexterm>
2774 void SPI_freetuple(HeapTuple <parameter>row</parameter>)
2779 <title>Description</title>
2782 <function>SPI_freetuple</function> frees a row previously allocated
2783 in the upper executor context.
2787 This function is no longer different from plain
2788 <function>heap_freetuple</function>. It's kept just for backward
2789 compatibility of existing code.
2794 <title>Arguments</title>
2798 <term><literal>HeapTuple <parameter>row</parameter></literal></term>
2809 <!-- *********************************************** -->
2811 <refentry id="spi-spi-freetupletable">
2813 <refentrytitle>SPI_freetuptable</refentrytitle>
2817 <refname>SPI_freetuptable</refname>
2818 <refpurpose>free a row set created by <function>SPI_execute</> or a similar
2819 function</refpurpose>
2822 <indexterm><primary>SPI_freetuptable</primary></indexterm>
2826 void SPI_freetuptable(SPITupleTable * <parameter>tuptable</parameter>)
2831 <title>Description</title>
2834 <function>SPI_freetuptable</function> frees a row set created by a
2835 prior SPI command execution function, such as
2836 <function>SPI_execute</>. Therefore, this function is usually called
2837 with the global variable <varname>SPI_tupletable</varname> as
2842 This function is useful if a SPI procedure needs to execute
2843 multiple commands and does not want to keep the results of earlier
2844 commands around until it ends. Note that any unfreed row sets will
2845 be freed anyway at <function>SPI_finish</>.
2850 <title>Arguments</title>
2854 <term><literal>SPITupleTable * <parameter>tuptable</parameter></literal></term>
2857 pointer to row set to free
2865 <!-- *********************************************** -->
2867 <refentry id="spi-spi-freeplan">
2869 <refentrytitle>SPI_freeplan</refentrytitle>
2873 <refname>SPI_freeplan</refname>
2874 <refpurpose>free a previously saved plan</refpurpose>
2877 <indexterm><primary>SPI_freeplan</primary></indexterm>
2881 int SPI_freeplan(void *<parameter>plan</parameter>)
2886 <title>Description</title>
2889 <function>SPI_freeplan</function> releases a command execution plan
2890 previously returned by <function>SPI_prepare</function> or saved by
2891 <function>SPI_saveplan</function>.
2896 <title>Arguments</title>
2900 <term><literal>void * <parameter>plan</parameter></literal></term>
2903 pointer to plan to free
2911 <title>Return Value</title>
2914 <symbol>SPI_ERROR_ARGUMENT</symbol> if <parameter>plan</parameter>
2915 is <symbol>NULL</symbol>.
2922 <sect1 id="spi-visibility">
2923 <title>Visibility of Data Changes</title>
2926 The following rules govern the visibility of data changes in
2927 functions that use SPI (or any other C function):
2932 During the execution of an SQL command, any data changes made by
2933 the command are invisible to the command itself. For
2936 INSERT INTO a SELECT * FROM a;
2938 the inserted rows are invisible to the <command>SELECT</command>
2945 Changes made by a command C are visible to all commands that are
2946 started after C, no matter whether they are started inside C
2947 (during the execution of C) or after C is done.
2953 Commands executed via SPI inside a function called by an SQL command
2954 (either an ordinary function or a trigger) follow one or the
2955 other of the above rules depending on the read/write flag passed
2956 to SPI. Commands executed in read-only mode follow the first
2957 rule: they can't see changes of the calling command. Commands executed
2958 in read-write mode follow the second rule: they can see all changes made
2965 All standard procedural languages set the SPI read-write mode
2966 depending on the volatility attribute of the function. Commands of
2967 <literal>STABLE</> and <literal>IMMUTABLE</> functions are done in
2968 read-only mode, while commands of <literal>VOLATILE</> functions are
2969 done in read-write mode. While authors of C functions are able to
2970 violate this convention, it's unlikely to be a good idea to do so.
2977 The next section contains an example that illustrates the
2978 application of these rules.
2982 <sect1 id="spi-examples">
2983 <title>Examples</title>
2986 This section contains a very simple example of SPI usage. The
2987 procedure <function>execq</function> takes an SQL command as its
2988 first argument and a row count as its second, executes the command
2989 using <function>SPI_exec</function> and returns the number of rows
2990 that were processed by the command. You can find more complex
2991 examples for SPI in the source tree in
2992 <filename>src/test/regress/regress.c</filename> and in
2993 <filename>contrib/spi</filename>.
2997 #include "executor/spi.h"
2999 int execq(text *sql, int cnt);
3002 execq(text *sql, int cnt)
3008 /* Convert given text object to a C string */
3009 command = DatumGetCString(DirectFunctionCall1(textout,
3010 PointerGetDatum(sql)));
3014 ret = SPI_exec(command, cnt);
3016 proc = SPI_processed;
3018 * If some rows were fetched, print them via elog(INFO).
3020 if (ret > 0 && SPI_tuptable != NULL)
3022 TupleDesc tupdesc = SPI_tuptable->tupdesc;
3023 SPITupleTable *tuptable = SPI_tuptable;
3027 for (j = 0; j < proc; j++)
3029 HeapTuple tuple = tuptable->vals[j];
3031 for (i = 1, buf[0] = 0; i <= tupdesc->natts; i++)
3032 snprintf(buf + strlen (buf), sizeof(buf) - strlen(buf), " %s%s",
3033 SPI_getvalue(tuple, tupdesc, i),
3034 (i == tupdesc->natts) ? " " : " |");
3035 elog(INFO, "EXECQ: %s", buf);
3047 (This function uses call convention version 0, to make the example
3048 easier to understand. In real applications you should use the new
3049 version 1 interface.)
3053 This is how you declare the function after having compiled it into
3057 CREATE FUNCTION execq(text, integer) RETURNS integer
3058 AS '<replaceable>filename</replaceable>'
3064 Here is a sample session:
3067 => SELECT execq('CREATE TABLE a (x integer)', 0);
3073 => INSERT INTO a VALUES (execq('INSERT INTO a VALUES (0)', 0));
3075 => SELECT execq('SELECT * FROM a', 0);
3076 INFO: EXECQ: 0 -- inserted by execq
3077 INFO: EXECQ: 1 -- returned by execq and inserted by upper INSERT
3084 => SELECT execq('INSERT INTO a SELECT x + 2 FROM a', 1);
3090 => SELECT execq('SELECT * FROM a', 10);
3093 INFO: EXECQ: 2 -- 0 + 2, only one row inserted - as specified
3097 3 -- 10 is the max value only, 3 is the real number of rows
3100 => DELETE FROM a;
3102 => INSERT INTO a VALUES (execq('SELECT * FROM a', 0) + 1);
3104 => SELECT * FROM a;
3107 1 -- no rows in a (0) + 1
3110 => INSERT INTO a VALUES (execq('SELECT * FROM a', 0) + 1);
3113 => SELECT * FROM a;
3117 2 -- there was one row in a + 1
3120 -- This demonstrates the data changes visibility rule:
3122 => INSERT INTO a SELECT execq('SELECT * FROM a', 0) * x FROM a;
3129 => SELECT * FROM a;
3134 2 -- 2 rows * 1 (x in first row)
3135 6 -- 3 rows (2 + 1 just inserted) * 2 (x in second row)
3137 rows visible to execq() in different invocations