1 <!-- $PostgreSQL: pgsql/doc/src/sgml/spi.sgml,v 1.56 2007/04/16 01:14:55 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 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 might 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 can 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 can 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 can pass multiple commands in one string, but later commands cannot
325 depend on the creation of objects earlier in the string, because the
326 whole string will be parsed and planned before execution begins.
327 <function>SPI_execute</function> returns the
328 result for the command executed last. The <parameter>count</parameter>
329 limit applies to each command separately, but it is not applied to
330 hidden commands generated by rules.
334 When <parameter>read_only</parameter> is <literal>false</>,
335 <function>SPI_execute</function> increments the command
336 counter and computes a new <firstterm>snapshot</> before executing each
337 command in the string. The snapshot does not actually change if the
338 current transaction isolation level is <literal>SERIALIZABLE</>, but in
339 <literal>READ COMMITTED</> mode the snapshot update allows each command to
340 see the results of newly committed transactions from other sessions.
341 This is essential for consistent behavior when the commands are modifying
346 When <parameter>read_only</parameter> is <literal>true</>,
347 <function>SPI_execute</function> does not update either the snapshot
348 or the command counter, and it allows only plain <command>SELECT</>
349 commands to appear in the command string. The commands are executed
350 using the snapshot previously established for the surrounding query.
351 This execution mode is somewhat faster than the read/write mode due
352 to eliminating per-command overhead. It also allows genuinely
353 <firstterm>stable</> functions to be built: since successive executions
354 will all use the same snapshot, there will be no change in the results.
358 It is generally unwise to mix read-only and read-write commands within
359 a single function using SPI; that could result in very confusing behavior,
360 since the read-only queries would not see the results of any database
361 updates done by the read-write queries.
365 The actual number of rows for which the (last) command was executed
366 is returned in the global variable <varname>SPI_processed</varname>.
367 If the return value of the function is <symbol>SPI_OK_SELECT</symbol>,
368 <symbol>SPI_OK_INSERT_RETURNING</symbol>,
369 <symbol>SPI_OK_DELETE_RETURNING</symbol>, or
370 <symbol>SPI_OK_UPDATE_RETURNING</symbol>,
372 global pointer <literal>SPITupleTable *SPI_tuptable</literal> to
373 access the result rows. Some utility commands (such as
374 <command>EXPLAIN</>) also return row sets, and <literal>SPI_tuptable</>
375 will contain the result in these cases too.
379 The structure <structname>SPITupleTable</structname> is defined
384 MemoryContext tuptabcxt; /* memory context of result table */
385 uint32 alloced; /* number of alloced vals */
386 uint32 free; /* number of free vals */
387 TupleDesc tupdesc; /* row descriptor */
388 HeapTuple *vals; /* rows */
391 <structfield>vals</> is an array of pointers to rows. (The number
392 of valid entries is given by <varname>SPI_processed</varname>.)
393 <structfield>tupdesc</> is a row descriptor which you can pass to
394 SPI functions dealing with rows. <structfield>tuptabcxt</>,
395 <structfield>alloced</>, and <structfield>free</> are internal
396 fields not intended for use by SPI callers.
400 <function>SPI_finish</function> frees all
401 <structname>SPITupleTable</>s allocated during the current
402 procedure. You can free a particular result table earlier, if you
403 are done with it, by calling <function>SPI_freetuptable</function>.
408 <title>Arguments</title>
412 <term><literal>const char * <parameter>command</parameter></literal></term>
415 string containing command to execute
421 <term><literal>bool <parameter>read_only</parameter></literal></term>
424 <literal>true</> for read-only execution
430 <term><literal>long <parameter>count</parameter></literal></term>
433 maximum number of rows to process or return
441 <title>Return Value</title>
444 If the execution of the command was successful then one of the
445 following (nonnegative) values will be returned:
449 <term><symbol>SPI_OK_SELECT</symbol></term>
452 if a <command>SELECT</command> (but not <command>SELECT
453 INTO</>) was executed
459 <term><symbol>SPI_OK_SELINTO</symbol></term>
462 if a <command>SELECT INTO</command> was executed
468 <term><symbol>SPI_OK_INSERT</symbol></term>
471 if an <command>INSERT</command> was executed
477 <term><symbol>SPI_OK_DELETE</symbol></term>
480 if a <command>DELETE</command> was executed
486 <term><symbol>SPI_OK_UPDATE</symbol></term>
489 if an <command>UPDATE</command> was executed
495 <term><symbol>SPI_OK_INSERT_RETURNING</symbol></term>
498 if an <command>INSERT RETURNING</command> was executed
504 <term><symbol>SPI_OK_DELETE_RETURNING</symbol></term>
507 if a <command>DELETE RETURNING</command> was executed
513 <term><symbol>SPI_OK_UPDATE_RETURNING</symbol></term>
516 if an <command>UPDATE RETURNING</command> was executed
522 <term><symbol>SPI_OK_UTILITY</symbol></term>
525 if a utility command (e.g., <command>CREATE TABLE</command>)
534 On error, one of the following negative values is returned:
538 <term><symbol>SPI_ERROR_ARGUMENT</symbol></term>
541 if <parameter>command</parameter> is <symbol>NULL</symbol> or
542 <parameter>count</parameter> is less than 0
548 <term><symbol>SPI_ERROR_COPY</symbol></term>
551 if <command>COPY TO stdout</> or <command>COPY FROM stdin</>
558 <term><symbol>SPI_ERROR_TRANSACTION</symbol></term>
561 if a transaction manipulation command was attempted
564 <command>ROLLBACK</>,
565 <command>SAVEPOINT</>,
566 <command>PREPARE TRANSACTION</>,
567 <command>COMMIT PREPARED</>,
568 <command>ROLLBACK PREPARED</>,
569 or any variant thereof)
575 <term><symbol>SPI_ERROR_OPUNKNOWN</symbol></term>
578 if the command type is unknown (shouldn't happen)
584 <term><symbol>SPI_ERROR_UNCONNECTED</symbol></term>
587 if called from an unconnected procedure
599 The functions <function>SPI_execute</function>,
600 <function>SPI_exec</function>,
601 <function>SPI_execute_plan</function>, and
602 <function>SPI_execp</function> change both
603 <varname>SPI_processed</varname> and
604 <varname>SPI_tuptable</varname> (just the pointer, not the contents
605 of the structure). Save these two global variables into local
606 procedure variables if you need to access the result table of
607 <function>SPI_execute</function> or a related function
613 <!-- *********************************************** -->
615 <refentry id="spi-spi-exec">
617 <refentrytitle>SPI_exec</refentrytitle>
621 <refname>SPI_exec</refname>
622 <refpurpose>execute a read/write command</refpurpose>
625 <indexterm><primary>SPI_exec</primary></indexterm>
629 int SPI_exec(const char * <parameter>command</parameter>, long <parameter>count</parameter>)
634 <title>Description</title>
637 <function>SPI_exec</function> is the same as
638 <function>SPI_execute</function>, with the latter's
639 <parameter>read_only</parameter> parameter always taken as
645 <title>Arguments</title>
649 <term><literal>const char * <parameter>command</parameter></literal></term>
652 string containing command to execute
658 <term><literal>long <parameter>count</parameter></literal></term>
661 maximum number of rows to process or return
669 <title>Return Value</title>
672 See <function>SPI_execute</function>.
677 <!-- *********************************************** -->
679 <refentry id="spi-spi-prepare">
681 <refentrytitle>SPI_prepare</refentrytitle>
685 <refname>SPI_prepare</refname>
686 <refpurpose>prepare a plan for a command, without executing it yet</refpurpose>
689 <indexterm><primary>SPI_prepare</primary></indexterm>
693 SPIPlanPtr SPI_prepare(const char * <parameter>command</parameter>, int <parameter>nargs</parameter>, Oid * <parameter>argtypes</parameter>)
698 <title>Description</title>
701 <function>SPI_prepare</function> creates and returns an execution
702 plan for the specified command but doesn't execute the command.
703 This function should only be called from a connected procedure.
707 When the same or a similar command is to be executed repeatedly, it
708 might be advantageous to perform the planning only once.
709 <function>SPI_prepare</function> converts a command string into an
710 execution plan that can be executed repeatedly using
711 <function>SPI_execute_plan</function>.
715 A prepared command can be generalized by writing parameters
716 (<literal>$1</>, <literal>$2</>, etc.) in place of what would be
717 constants in a normal command. The actual values of the parameters
718 are then specified when <function>SPI_execute_plan</function> is called.
719 This allows the prepared command to be used over a wider range of
720 situations than would be possible without parameters.
724 The plan returned by <function>SPI_prepare</function> can be used
725 only in the current invocation of the procedure, since
726 <function>SPI_finish</function> frees memory allocated for a plan.
727 But a plan can be saved for longer using the function
728 <function>SPI_saveplan</function>.
733 <title>Arguments</title>
737 <term><literal>const char * <parameter>command</parameter></literal></term>
746 <term><literal>int <parameter>nargs</parameter></literal></term>
749 number of input parameters (<literal>$1</>, <literal>$2</>, etc.)
755 <term><literal>Oid * <parameter>argtypes</parameter></literal></term>
758 pointer to an array containing the <acronym>OID</acronym>s of
759 the data types of the parameters
767 <title>Return Value</title>
770 <function>SPI_prepare</function> returns a non-null pointer to an
771 execution plan. On error, <symbol>NULL</symbol> will be returned,
772 and <varname>SPI_result</varname> will be set to one of the same
773 error codes used by <function>SPI_execute</function>, except that
774 it is set to <symbol>SPI_ERROR_ARGUMENT</symbol> if
775 <parameter>command</parameter> is <symbol>NULL</symbol>, or if
776 <parameter>nargs</> is less than 0, or if <parameter>nargs</> is
777 greater than 0 and <parameter>argtypes</> is <symbol>NULL</symbol>.
785 <type>SPIPlanPtr</> is declared as a pointer to an opaque struct type in
786 <filename>spi.h</>. It is unwise to try to access its contents
787 directly, as that makes your code much more likely to break in
788 future revisions of <productname>PostgreSQL</productname>.
792 There is a disadvantage to using parameters: since the planner does
793 not know the values that will be supplied for the parameters, it
794 might make worse planning choices than it would make for a normal
795 command with all constants visible.
800 <!-- *********************************************** -->
802 <refentry id="spi-spi-prepare-cursor">
804 <refentrytitle>SPI_prepare_cursor</refentrytitle>
808 <refname>SPI_prepare_cursor</refname>
809 <refpurpose>prepare a plan for a command, without executing it yet</refpurpose>
812 <indexterm><primary>SPI_prepare_cursor</primary></indexterm>
816 SPIPlanPtr SPI_prepare_cursor(const char * <parameter>command</parameter>, int <parameter>nargs</parameter>, Oid * <parameter>argtypes</parameter>, int <parameter>cursorOptions</parameter>)
821 <title>Description</title>
824 <function>SPI_prepare_cursor</function> is identical to
825 <function>SPI_prepare</function>, except that it also allows specification
826 of the planner's <quote>cursor options</> parameter. This is a bitmask
827 having the values shown in <filename>nodes/parsenodes.h</filename>
828 for the <structfield>options</> field of <structname>DeclareCursorStmt</>.
829 <function>SPI_prepare</function> always takes these options as zero.
834 <title>Arguments</title>
838 <term><literal>const char * <parameter>command</parameter></literal></term>
847 <term><literal>int <parameter>nargs</parameter></literal></term>
850 number of input parameters (<literal>$1</>, <literal>$2</>, etc.)
856 <term><literal>Oid * <parameter>argtypes</parameter></literal></term>
859 pointer to an array containing the <acronym>OID</acronym>s of
860 the data types of the parameters
866 <term><literal>int <parameter>cursorOptions</parameter></literal></term>
869 integer bitmask of cursor options; zero produces default behavior
877 <title>Return Value</title>
880 <function>SPI_prepare_cursor</function> has the same return conventions as
881 <function>SPI_prepare</function>.
889 Useful bits to set in <parameter>cursorOptions</> include
890 <symbol>CURSOR_OPT_SCROLL</symbol>,
891 <symbol>CURSOR_OPT_NO_SCROLL</symbol>, and
892 <symbol>CURSOR_OPT_FAST_PLAN</symbol>. Note in particular that
893 <symbol>CURSOR_OPT_HOLD</symbol> is ignored.
898 <!-- *********************************************** -->
900 <refentry id="spi-spi-getargcount">
902 <refentrytitle>SPI_getargcount</refentrytitle>
906 <refname>SPI_getargcount</refname>
907 <refpurpose>return the number of arguments needed by a plan
908 prepared by <function>SPI_prepare</function></refpurpose>
911 <indexterm><primary>SPI_getargcount</primary></indexterm>
915 int SPI_getargcount(SPIPlanPtr <parameter>plan</parameter>)
920 <title>Description</title>
923 <function>SPI_getargcount</function> returns the number of arguments needed
924 to execute a plan prepared by <function>SPI_prepare</function>.
929 <title>Arguments</title>
933 <term><literal>SPIPlanPtr <parameter>plan</parameter></literal></term>
936 execution plan (returned by <function>SPI_prepare</function>)
944 <title>Return Value</title>
946 The count of expected arguments for the <parameter>plan</parameter>.
947 If the <parameter>plan</parameter> is <symbol>NULL</symbol> or invalid,
948 <varname>SPI_result</varname> is set to <symbol>SPI_ERROR_ARGUMENT</symbol>
949 and <literal>-1</literal> is returned.
954 <!-- *********************************************** -->
956 <refentry id="spi-spi-getargtypeid">
958 <refentrytitle>SPI_getargtypeid</refentrytitle>
962 <refname>SPI_getargtypeid</refname>
963 <refpurpose>return the data type OID for an argument of
964 a plan prepared by <function>SPI_prepare</function></refpurpose>
967 <indexterm><primary>SPI_getargtypeid</primary></indexterm>
971 Oid SPI_getargtypeid(SPIPlanPtr <parameter>plan</parameter>, int <parameter>argIndex</parameter>)
976 <title>Description</title>
979 <function>SPI_getargtypeid</function> returns the OID representing the type
980 id for the <parameter>argIndex</parameter>'th argument of a plan prepared by
981 <function>SPI_prepare</function>. First argument is at index zero.
986 <title>Arguments</title>
990 <term><literal>SPIPlanPtr <parameter>plan</parameter></literal></term>
993 execution plan (returned by <function>SPI_prepare</function>)
999 <term><literal>int <parameter>argIndex</parameter></literal></term>
1002 zero based index of the argument
1010 <title>Return Value</title>
1012 The type id of the argument at the given index.
1013 If the <parameter>plan</parameter> is <symbol>NULL</symbol> or invalid,
1014 or <parameter>argIndex</parameter> is less than 0 or
1015 not less than the number of arguments declared for the
1016 <parameter>plan</parameter>,
1017 <varname>SPI_result</varname> is set to <symbol>SPI_ERROR_ARGUMENT</symbol>
1018 and <symbol>InvalidOid</symbol> is returned.
1023 <!-- *********************************************** -->
1025 <refentry id="spi-spi-is-cursor-plan">
1027 <refentrytitle>SPI_is_cursor_plan</refentrytitle>
1031 <refname>SPI_is_cursor_plan</refname>
1032 <refpurpose>return <symbol>true</symbol> if a plan
1033 prepared by <function>SPI_prepare</function> can be used with
1034 <function>SPI_cursor_open</function></refpurpose>
1037 <indexterm><primary>SPI_is_cursor_plan</primary></indexterm>
1041 bool SPI_is_cursor_plan(SPIPlanPtr <parameter>plan</parameter>)
1046 <title>Description</title>
1049 <function>SPI_is_cursor_plan</function> returns <symbol>true</symbol>
1050 if a plan prepared by <function>SPI_prepare</function> can be passed
1051 as an argument to <function>SPI_cursor_open</function>, or
1052 <symbol>false</symbol> if that is not the case. The criteria are that the
1053 <parameter>plan</parameter> represents one single command and that this
1054 command returns tuples to the caller; for example, <command>SELECT</>
1055 is allowed unless it contains an <literal>INTO</> clause, and
1056 <command>UPDATE</> is allowed only if it contains a <literal>RETURNING</>
1062 <title>Arguments</title>
1066 <term><literal>SPIPlanPtr <parameter>plan</parameter></literal></term>
1069 execution plan (returned by <function>SPI_prepare</function>)
1077 <title>Return Value</title>
1079 <symbol>true</symbol> or <symbol>false</symbol> to indicate if the
1080 <parameter>plan</parameter> can produce a cursor or not.
1081 If the <parameter>plan</parameter> is <symbol>NULL</symbol> or invalid,
1082 <varname>SPI_result</varname> is set to <symbol>SPI_ERROR_ARGUMENT</symbol>
1083 and <symbol>false</symbol> is returned.
1088 <!-- *********************************************** -->
1090 <refentry id="spi-spi-execute-plan">
1092 <refentrytitle>SPI_execute_plan</refentrytitle>
1096 <refname>SPI_execute_plan</refname>
1097 <refpurpose>execute a plan prepared by <function>SPI_prepare</function></refpurpose>
1100 <indexterm><primary>SPI_execute_plan</primary></indexterm>
1104 int SPI_execute_plan(SPIPlanPtr <parameter>plan</parameter>, Datum * <parameter>values</parameter>, const char * <parameter>nulls</parameter>,
1105 bool <parameter>read_only</parameter>, long <parameter>count</parameter>)
1110 <title>Description</title>
1113 <function>SPI_execute_plan</function> executes a plan prepared by
1114 <function>SPI_prepare</function>. <parameter>read_only</parameter> and
1115 <parameter>count</parameter> have the same interpretation as in
1116 <function>SPI_execute</function>.
1121 <title>Arguments</title>
1125 <term><literal>SPIPlanPtr <parameter>plan</parameter></literal></term>
1128 execution plan (returned by <function>SPI_prepare</function>)
1134 <term><literal>Datum * <parameter>values</parameter></literal></term>
1137 An array of actual parameter values. Must have same length as the
1138 plan's number of arguments.
1144 <term><literal>const char * <parameter>nulls</parameter></literal></term>
1147 An array describing which parameters are null. Must have same length as
1148 the plan's number of arguments.
1149 <literal>n</literal> indicates a null value (entry in
1150 <parameter>values</> will be ignored); a space indicates a
1151 nonnull value (entry in <parameter>values</> is valid).
1155 If <parameter>nulls</parameter> is <symbol>NULL</symbol> then
1156 <function>SPI_execute_plan</function> assumes that no parameters are
1163 <term><literal>bool <parameter>read_only</parameter></literal></term>
1166 <literal>true</> for read-only execution
1172 <term><literal>long <parameter>count</parameter></literal></term>
1175 maximum number of rows to process or return
1183 <title>Return Value</title>
1186 The return value is the same as for <function>SPI_execute</function>,
1187 with the following additional possible error (negative) results:
1191 <term><symbol>SPI_ERROR_ARGUMENT</symbol></term>
1194 if <parameter>plan</parameter> is <symbol>NULL</symbol> or invalid,
1195 or <parameter>count</parameter> is less than 0
1201 <term><symbol>SPI_ERROR_PARAM</symbol></term>
1204 if <parameter>values</parameter> is <symbol>NULL</symbol> and
1205 <parameter>plan</parameter> was prepared with some parameters
1213 <varname>SPI_processed</varname> and
1214 <varname>SPI_tuptable</varname> are set as in
1215 <function>SPI_execute</function> if successful.
1220 <title>Notes</title>
1223 If one of the objects (a table, function, etc.) referenced by the
1224 prepared plan is dropped during the session then the result of
1225 <function>SPI_execute_plan</function> for this plan will be unpredictable.
1230 <!-- *********************************************** -->
1232 <refentry id="spi-spi-execp">
1234 <refentrytitle>SPI_execp</refentrytitle>
1238 <refname>SPI_execp</refname>
1239 <refpurpose>execute a plan in read/write mode</refpurpose>
1242 <indexterm><primary>SPI_execp</primary></indexterm>
1246 int SPI_execp(SPIPlanPtr <parameter>plan</parameter>, Datum * <parameter>values</parameter>, const char * <parameter>nulls</parameter>, long <parameter>count</parameter>)
1251 <title>Description</title>
1254 <function>SPI_execp</function> is the same as
1255 <function>SPI_execute_plan</function>, with the latter's
1256 <parameter>read_only</parameter> parameter always taken as
1262 <title>Arguments</title>
1266 <term><literal>SPIPlanPtr <parameter>plan</parameter></literal></term>
1269 execution plan (returned by <function>SPI_prepare</function>)
1275 <term><literal>Datum * <parameter>values</parameter></literal></term>
1278 An array of actual parameter values. Must have same length as the
1279 plan's number of arguments.
1285 <term><literal>const char * <parameter>nulls</parameter></literal></term>
1288 An array describing which parameters are null. Must have same length as
1289 the plan's number of arguments.
1290 <literal>n</literal> indicates a null value (entry in
1291 <parameter>values</> will be ignored); a space indicates a
1292 nonnull value (entry in <parameter>values</> is valid).
1296 If <parameter>nulls</parameter> is <symbol>NULL</symbol> then
1297 <function>SPI_execp</function> assumes that no parameters are
1304 <term><literal>long <parameter>count</parameter></literal></term>
1307 maximum number of rows to process or return
1315 <title>Return Value</title>
1318 See <function>SPI_execute_plan</function>.
1322 <varname>SPI_processed</varname> and
1323 <varname>SPI_tuptable</varname> are set as in
1324 <function>SPI_execute</function> if successful.
1329 <!-- *********************************************** -->
1331 <refentry id="spi-spi-cursor-open">
1333 <refentrytitle>SPI_cursor_open</refentrytitle>
1337 <refname>SPI_cursor_open</refname>
1338 <refpurpose>set up a cursor using a plan created with <function>SPI_prepare</function></refpurpose>
1341 <indexterm><primary>SPI_cursor_open</primary></indexterm>
1345 Portal SPI_cursor_open(const char * <parameter>name</parameter>, SPIPlanPtr <parameter>plan</parameter>,
1346 Datum * <parameter>values</parameter>, const char * <parameter>nulls</parameter>,
1347 bool <parameter>read_only</parameter>)
1352 <title>Description</title>
1355 <function>SPI_cursor_open</function> sets up a cursor (internally,
1356 a portal) that will execute a plan prepared by
1357 <function>SPI_prepare</function>. The parameters have the same
1358 meanings as the corresponding parameters to
1359 <function>SPI_execute_plan</function>.
1363 Using a cursor instead of executing the plan directly has two
1364 benefits. First, the result rows can be retrieved a few at a time,
1365 avoiding memory overrun for queries that return many rows. Second,
1366 a portal can outlive the current procedure (it can, in fact, live
1367 to the end of the current transaction). Returning the portal name
1368 to the procedure's caller provides a way of returning a row set as
1373 The passed-in data will be copied into the cursor's portal, so it
1374 can be freed while the cursor still exists.
1379 <title>Arguments</title>
1383 <term><literal>const char * <parameter>name</parameter></literal></term>
1386 name for portal, or <symbol>NULL</symbol> to let the system
1393 <term><literal>SPIPlanPtr <parameter>plan</parameter></literal></term>
1396 execution plan (returned by <function>SPI_prepare</function>)
1402 <term><literal>Datum * <parameter>values</parameter></literal></term>
1405 An array of actual parameter values. Must have same length as the
1406 plan's number of arguments.
1412 <term><literal>const char * <parameter>nulls</parameter></literal></term>
1415 An array describing which parameters are null. Must have same length as
1416 the plan's number of arguments.
1417 <literal>n</literal> indicates a null value (entry in
1418 <parameter>values</> will be ignored); a space indicates a
1419 nonnull value (entry in <parameter>values</> is valid).
1423 If <parameter>nulls</parameter> is <symbol>NULL</symbol> then
1424 <function>SPI_cursor_open</function> assumes that no parameters are
1431 <term><literal>bool <parameter>read_only</parameter></literal></term>
1434 <literal>true</> for read-only execution
1442 <title>Return Value</title>
1445 pointer to portal containing the cursor, or <symbol>NULL</symbol>
1451 <!-- *********************************************** -->
1453 <refentry id="spi-spi-cursor-find">
1455 <refentrytitle>SPI_cursor_find</refentrytitle>
1459 <refname>SPI_cursor_find</refname>
1460 <refpurpose>find an existing cursor by name</refpurpose>
1463 <indexterm><primary>SPI_cursor_find</primary></indexterm>
1467 Portal SPI_cursor_find(const char * <parameter>name</parameter>)
1472 <title>Description</title>
1475 <function>SPI_cursor_find</function> finds an existing portal by
1476 name. This is primarily useful to resolve a cursor name returned
1477 as text by some other function.
1482 <title>Arguments</title>
1486 <term><literal>const char * <parameter>name</parameter></literal></term>
1497 <title>Return Value</title>
1500 pointer to the portal with the specified name, or
1501 <symbol>NULL</symbol> if none was found
1506 <!-- *********************************************** -->
1508 <refentry id="spi-spi-cursor-fetch">
1510 <refentrytitle>SPI_cursor_fetch</refentrytitle>
1514 <refname>SPI_cursor_fetch</refname>
1515 <refpurpose>fetch some rows from a cursor</refpurpose>
1518 <indexterm><primary>SPI_cursor_fetch</primary></indexterm>
1522 void SPI_cursor_fetch(Portal <parameter>portal</parameter>, bool <parameter>forward</parameter>, long <parameter>count</parameter>)
1527 <title>Description</title>
1530 <function>SPI_cursor_fetch</function> fetches some rows from a
1531 cursor. This is equivalent to a subset of the SQL command
1532 <command>FETCH</> (see <function>SPI_scroll_cursor_fetch</function>
1533 for more functionality).
1538 <title>Arguments</title>
1542 <term><literal>Portal <parameter>portal</parameter></literal></term>
1545 portal containing the cursor
1551 <term><literal>bool <parameter>forward</parameter></literal></term>
1554 true for fetch forward, false for fetch backward
1560 <term><literal>long <parameter>count</parameter></literal></term>
1563 maximum number of rows to fetch
1571 <title>Return Value</title>
1574 <varname>SPI_processed</varname> and
1575 <varname>SPI_tuptable</varname> are set as in
1576 <function>SPI_execute</function> if successful.
1581 <title>Notes</title>
1584 Fetching backward may fail if the cursor's plan was not created
1585 with the <symbol>CURSOR_OPT_SCROLL</symbol> option.
1590 <!-- *********************************************** -->
1592 <refentry id="spi-spi-cursor-move">
1594 <refentrytitle>SPI_cursor_move</refentrytitle>
1598 <refname>SPI_cursor_move</refname>
1599 <refpurpose>move a cursor</refpurpose>
1602 <indexterm><primary>SPI_cursor_move</primary></indexterm>
1606 void SPI_cursor_move(Portal <parameter>portal</parameter>, bool <parameter>forward</parameter>, long <parameter>count</parameter>)
1611 <title>Description</title>
1614 <function>SPI_cursor_move</function> skips over some number of rows
1615 in a cursor. This is equivalent to a subset of the SQL command
1616 <command>MOVE</> (see <function>SPI_scroll_cursor_move</function>
1617 for more functionality).
1622 <title>Arguments</title>
1626 <term><literal>Portal <parameter>portal</parameter></literal></term>
1629 portal containing the cursor
1635 <term><literal>bool <parameter>forward</parameter></literal></term>
1638 true for move forward, false for move backward
1644 <term><literal>long <parameter>count</parameter></literal></term>
1647 maximum number of rows to move
1655 <title>Notes</title>
1658 Moving backward may fail if the cursor's plan was not created
1659 with the <symbol>CURSOR_OPT_SCROLL</symbol> option.
1664 <!-- *********************************************** -->
1666 <refentry id="spi-spi-scroll-cursor-fetch">
1668 <refentrytitle>SPI_scroll_cursor_fetch</refentrytitle>
1672 <refname>SPI_scroll_cursor_fetch</refname>
1673 <refpurpose>fetch some rows from a cursor</refpurpose>
1676 <indexterm><primary>SPI_scroll_cursor_fetch</primary></indexterm>
1680 void SPI_scroll_cursor_fetch(Portal <parameter>portal</parameter>, FetchDirection <parameter>direction</parameter>, long <parameter>count</parameter>)
1685 <title>Description</title>
1688 <function>SPI_scroll_cursor_fetch</function> fetches some rows from a
1689 cursor. This is equivalent to the SQL command <command>FETCH</>.
1694 <title>Arguments</title>
1698 <term><literal>Portal <parameter>portal</parameter></literal></term>
1701 portal containing the cursor
1707 <term><literal>FetchDirection <parameter>direction</parameter></literal></term>
1710 one of <symbol>FETCH_FORWARD</symbol>,
1711 <symbol>FETCH_BACKWARD</symbol>,
1712 <symbol>FETCH_ABSOLUTE</symbol> or
1713 <symbol>FETCH_RELATIVE</symbol>
1719 <term><literal>long <parameter>count</parameter></literal></term>
1722 number of rows to fetch for
1723 <symbol>FETCH_FORWARD</symbol> or
1724 <symbol>FETCH_BACKWARD</symbol>; absolute row number to fetch for
1725 <symbol>FETCH_ABSOLUTE</symbol>; or relative row number to fetch for
1726 <symbol>FETCH_RELATIVE</symbol>
1734 <title>Return Value</title>
1737 <varname>SPI_processed</varname> and
1738 <varname>SPI_tuptable</varname> are set as in
1739 <function>SPI_execute</function> if successful.
1744 <title>Notes</title>
1747 See the SQL <xref linkend="sql-fetch" endterm="sql-fetch-title"> command
1748 for details of the interpretation of the
1749 <parameter>direction</parameter> and
1750 <parameter>count</parameter> parameters.
1754 Direction values other than <symbol>FETCH_FORWARD</symbol>
1755 may fail if the cursor's plan was not created
1756 with the <symbol>CURSOR_OPT_SCROLL</symbol> option.
1761 <!-- *********************************************** -->
1763 <refentry id="spi-spi-scroll-cursor-move">
1765 <refentrytitle>SPI_scroll_cursor_move</refentrytitle>
1769 <refname>SPI_scroll_cursor_move</refname>
1770 <refpurpose>move a cursor</refpurpose>
1773 <indexterm><primary>SPI_scroll_cursor_move</primary></indexterm>
1777 void SPI_scroll_cursor_move(Portal <parameter>portal</parameter>, FetchDirection <parameter>direction</parameter>, long <parameter>count</parameter>)
1782 <title>Description</title>
1785 <function>SPI_scroll_cursor_move</function> skips over some number of rows
1786 in a cursor. This is equivalent to the SQL command
1792 <title>Arguments</title>
1796 <term><literal>Portal <parameter>portal</parameter></literal></term>
1799 portal containing the cursor
1805 <term><literal>FetchDirection <parameter>direction</parameter></literal></term>
1808 one of <symbol>FETCH_FORWARD</symbol>,
1809 <symbol>FETCH_BACKWARD</symbol>,
1810 <symbol>FETCH_ABSOLUTE</symbol> or
1811 <symbol>FETCH_RELATIVE</symbol>
1817 <term><literal>long <parameter>count</parameter></literal></term>
1820 number of rows to move for
1821 <symbol>FETCH_FORWARD</symbol> or
1822 <symbol>FETCH_BACKWARD</symbol>; absolute row number to move to for
1823 <symbol>FETCH_ABSOLUTE</symbol>; or relative row number to move to for
1824 <symbol>FETCH_RELATIVE</symbol>
1832 <title>Return Value</title>
1835 <varname>SPI_processed</varname> and
1836 <varname>SPI_tuptable</varname> are set as in
1837 <function>SPI_execute</function> if successful.
1842 <title>Notes</title>
1845 See the SQL <xref linkend="sql-fetch" endterm="sql-fetch-title"> command
1846 for details of the interpretation of the
1847 <parameter>direction</parameter> and
1848 <parameter>count</parameter> parameters.
1852 Direction values other than <symbol>FETCH_FORWARD</symbol>
1853 may fail if the cursor's plan was not created
1854 with the <symbol>CURSOR_OPT_SCROLL</symbol> option.
1859 <!-- *********************************************** -->
1861 <refentry id="spi-spi-cursor-close">
1863 <refentrytitle>SPI_cursor_close</refentrytitle>
1867 <refname>SPI_cursor_close</refname>
1868 <refpurpose>close a cursor</refpurpose>
1871 <indexterm><primary>SPI_cursor_close</primary></indexterm>
1875 void SPI_cursor_close(Portal <parameter>portal</parameter>)
1880 <title>Description</title>
1883 <function>SPI_cursor_close</function> closes a previously created
1884 cursor and releases its portal storage.
1888 All open cursors are closed automatically at the end of a
1889 transaction. <function>SPI_cursor_close</function> need only be
1890 invoked if it is desirable to release resources sooner.
1895 <title>Arguments</title>
1899 <term><literal>Portal <parameter>portal</parameter></literal></term>
1902 portal containing the cursor
1910 <!-- *********************************************** -->
1912 <refentry id="spi-spi-saveplan">
1914 <refentrytitle>SPI_saveplan</refentrytitle>
1918 <refname>SPI_saveplan</refname>
1919 <refpurpose>save a plan</refpurpose>
1922 <indexterm><primary>SPI_saveplan</primary></indexterm>
1926 SPIPlanPtr SPI_saveplan(SPIPlanPtr <parameter>plan</parameter>)
1931 <title>Description</title>
1934 <function>SPI_saveplan</function> saves a passed plan (prepared by
1935 <function>SPI_prepare</function>) in memory that will not be freed
1936 by <function>SPI_finish</function> nor by the transaction manager,
1937 and returns a pointer to the saved plan. This gives you the
1938 ability to reuse prepared plans in the subsequent invocations of
1939 your procedure in the current session.
1944 <title>Arguments</title>
1948 <term><literal>SPIPlanPtr <parameter>plan</parameter></literal></term>
1951 the plan to be saved
1959 <title>Return Value</title>
1962 Pointer to the saved plan; <symbol>NULL</symbol> if unsuccessful.
1963 On error, <varname>SPI_result</varname> is set thus:
1967 <term><symbol>SPI_ERROR_ARGUMENT</symbol></term>
1970 if <parameter>plan</parameter> is <symbol>NULL</symbol> or invalid
1976 <term><symbol>SPI_ERROR_UNCONNECTED</symbol></term>
1979 if called from an unconnected procedure
1988 <title>Notes</title>
1991 The passed-in plan is not freed, so you might wish to do
1992 <function>SPI_freeplan</function> on it to avoid leaking memory
1993 until <function>SPI_finish</>.
1997 If one of the objects (a table, function, etc.) referenced by the
1998 prepared plan is dropped or redefined, then future executions of
1999 <function>SPI_execute_plan</function> may fail or return different
2000 results than the plan initially indicates.
2007 <sect1 id="spi-interface-support">
2008 <title>Interface Support Functions</title>
2011 The functions described here provide an interface for extracting
2012 information from result sets returned by <function>SPI_execute</> and
2013 other SPI functions.
2017 All functions described in this section can be used by both
2018 connected and unconnected procedures.
2021 <!-- *********************************************** -->
2023 <refentry id="spi-spi-fname">
2025 <refentrytitle>SPI_fname</refentrytitle>
2029 <refname>SPI_fname</refname>
2030 <refpurpose>determine the column name for the specified column number</refpurpose>
2033 <indexterm><primary>SPI_fname</primary></indexterm>
2037 char * SPI_fname(TupleDesc <parameter>rowdesc</parameter>, int <parameter>colnumber</parameter>)
2042 <title>Description</title>
2045 <function>SPI_fname</function> returns a copy of the column name of the
2046 specified column. (You can use <function>pfree</function> to
2047 release the copy of the name when you don't need it anymore.)
2052 <title>Arguments</title>
2056 <term><literal>TupleDesc <parameter>rowdesc</parameter></literal></term>
2059 input row description
2065 <term><literal>int <parameter>colnumber</parameter></literal></term>
2068 column number (count starts at 1)
2076 <title>Return Value</title>
2079 The column name; <symbol>NULL</symbol> if
2080 <parameter>colnumber</parameter> is out of range.
2081 <varname>SPI_result</varname> set to
2082 <symbol>SPI_ERROR_NOATTRIBUTE</symbol> on error.
2087 <!-- *********************************************** -->
2089 <refentry id="spi-spi-fnumber">
2091 <refentrytitle>SPI_fnumber</refentrytitle>
2095 <refname>SPI_fnumber</refname>
2096 <refpurpose>determine the column number for the specified column name</refpurpose>
2099 <indexterm><primary>SPI_fnumber</primary></indexterm>
2103 int SPI_fnumber(TupleDesc <parameter>rowdesc</parameter>, const char * <parameter>colname</parameter>)
2108 <title>Description</title>
2111 <function>SPI_fnumber</function> returns the column number for the
2112 column with the specified name.
2116 If <parameter>colname</parameter> refers to a system column (e.g.,
2117 <literal>oid</>) then the appropriate negative column number will
2118 be returned. The caller should be careful to test the return value
2119 for exact equality to <symbol>SPI_ERROR_NOATTRIBUTE</symbol> to
2120 detect an error; testing the result for less than or equal to 0 is
2121 not correct unless system columns should be rejected.
2126 <title>Arguments</title>
2130 <term><literal>TupleDesc <parameter>rowdesc</parameter></literal></term>
2133 input row description
2139 <term><literal>const char * <parameter>colname</parameter></literal></term>
2150 <title>Return Value</title>
2153 Column number (count starts at 1), or
2154 <symbol>SPI_ERROR_NOATTRIBUTE</symbol> if the named column was not
2160 <!-- *********************************************** -->
2162 <refentry id="spi-spi-getvalue">
2164 <refentrytitle>SPI_getvalue</refentrytitle>
2168 <refname>SPI_getvalue</refname>
2169 <refpurpose>return the string value of the specified column</refpurpose>
2172 <indexterm><primary>SPI_getvalue</primary></indexterm>
2176 char * SPI_getvalue(HeapTuple <parameter>row</parameter>, TupleDesc <parameter>rowdesc</parameter>, int <parameter>colnumber</parameter>)
2181 <title>Description</title>
2184 <function>SPI_getvalue</function> returns the string representation
2185 of the value of the specified column.
2189 The result is returned in memory allocated using
2190 <function>palloc</function>. (You can use
2191 <function>pfree</function> to release the memory when you don't
2197 <title>Arguments</title>
2201 <term><literal>HeapTuple <parameter>row</parameter></literal></term>
2204 input row to be examined
2210 <term><literal>TupleDesc <parameter>rowdesc</parameter></literal></term>
2213 input row description
2219 <term><literal>int <parameter>colnumber</parameter></literal></term>
2222 column number (count starts at 1)
2230 <title>Return Value</title>
2233 Column value, or <symbol>NULL</symbol> if the column is null,
2234 <parameter>colnumber</parameter> is out of range
2235 (<varname>SPI_result</varname> is set to
2236 <symbol>SPI_ERROR_NOATTRIBUTE</symbol>), or no no output function
2237 available (<varname>SPI_result</varname> is set to
2238 <symbol>SPI_ERROR_NOOUTFUNC</symbol>).
2243 <!-- *********************************************** -->
2245 <refentry id="spi-spi-getbinval">
2247 <refentrytitle>SPI_getbinval</refentrytitle>
2251 <refname>SPI_getbinval</refname>
2252 <refpurpose>return the binary value of the specified column</refpurpose>
2255 <indexterm><primary>SPI_getbinval</primary></indexterm>
2259 Datum SPI_getbinval(HeapTuple <parameter>row</parameter>, TupleDesc <parameter>rowdesc</parameter>, int <parameter>colnumber</parameter>, bool * <parameter>isnull</parameter>)
2264 <title>Description</title>
2267 <function>SPI_getbinval</function> returns the value of the
2268 specified column in the internal form (as type <type>Datum</type>).
2272 This function does not allocate new space for the datum. In the
2273 case of a pass-by-reference data type, the return value will be a
2274 pointer into the passed row.
2279 <title>Arguments</title>
2283 <term><literal>HeapTuple <parameter>row</parameter></literal></term>
2286 input row to be examined
2292 <term><literal>TupleDesc <parameter>rowdesc</parameter></literal></term>
2295 input row description
2301 <term><literal>int <parameter>colnumber</parameter></literal></term>
2304 column number (count starts at 1)
2310 <term><literal>bool * <parameter>isnull</parameter></literal></term>
2313 flag for a null value in the column
2321 <title>Return Value</title>
2324 The binary value of the column is returned. The variable pointed
2325 to by <parameter>isnull</parameter> is set to true if the column is
2326 null, else to false.
2330 <varname>SPI_result</varname> is set to
2331 <symbol>SPI_ERROR_NOATTRIBUTE</symbol> on error.
2336 <!-- *********************************************** -->
2338 <refentry id="spi-spi-gettype">
2340 <refentrytitle>SPI_gettype</refentrytitle>
2344 <refname>SPI_gettype</refname>
2345 <refpurpose>return the data type name of the specified column</refpurpose>
2348 <indexterm><primary>SPI_gettype</primary></indexterm>
2352 char * SPI_gettype(TupleDesc <parameter>rowdesc</parameter>, int <parameter>colnumber</parameter>)
2357 <title>Description</title>
2360 <function>SPI_gettype</function> returns a copy of the data type name of the
2361 specified column. (You can use <function>pfree</function> to
2362 release the copy of the name when you don't need it anymore.)
2367 <title>Arguments</title>
2371 <term><literal>TupleDesc <parameter>rowdesc</parameter></literal></term>
2374 input row description
2380 <term><literal>int <parameter>colnumber</parameter></literal></term>
2383 column number (count starts at 1)
2391 <title>Return Value</title>
2394 The data type name of the specified column, or
2395 <symbol>NULL</symbol> on error. <varname>SPI_result</varname> is
2396 set to <symbol>SPI_ERROR_NOATTRIBUTE</symbol> on error.
2401 <!-- *********************************************** -->
2403 <refentry id="spi-spi-gettypeid">
2405 <refentrytitle>SPI_gettypeid</refentrytitle>
2409 <refname>SPI_gettypeid</refname>
2410 <refpurpose>return the data type <acronym>OID</acronym> of the specified column</refpurpose>
2413 <indexterm><primary>SPI_gettypeid</primary></indexterm>
2417 Oid SPI_gettypeid(TupleDesc <parameter>rowdesc</parameter>, int <parameter>colnumber</parameter>)
2422 <title>Description</title>
2425 <function>SPI_gettypeid</function> returns the
2426 <acronym>OID</acronym> of the data type of the specified column.
2431 <title>Arguments</title>
2435 <term><literal>TupleDesc <parameter>rowdesc</parameter></literal></term>
2438 input row description
2444 <term><literal>int <parameter>colnumber</parameter></literal></term>
2447 column number (count starts at 1)
2455 <title>Return Value</title>
2458 The <acronym>OID</acronym> of the data type of the specified column
2459 or <symbol>InvalidOid</symbol> on error. On error,
2460 <varname>SPI_result</varname> is set to
2461 <symbol>SPI_ERROR_NOATTRIBUTE</symbol>.
2466 <!-- *********************************************** -->
2468 <refentry id="spi-spi-getrelname">
2470 <refentrytitle>SPI_getrelname</refentrytitle>
2474 <refname>SPI_getrelname</refname>
2475 <refpurpose>return the name of the specified relation</refpurpose>
2478 <indexterm><primary>SPI_getrelname</primary></indexterm>
2482 char * SPI_getrelname(Relation <parameter>rel</parameter>)
2487 <title>Description</title>
2490 <function>SPI_getrelname</function> returns a copy of the name of the
2491 specified relation. (You can use <function>pfree</function> to
2492 release the copy of the name when you don't need it anymore.)
2497 <title>Arguments</title>
2501 <term><literal>Relation <parameter>rel</parameter></literal></term>
2512 <title>Return Value</title>
2515 The name of the specified relation.
2520 <refentry id="spi-spi-getnspname">
2522 <refentrytitle>SPI_getnspname</refentrytitle>
2526 <refname>SPI_getnspname</refname>
2527 <refpurpose>return the namespace of the specified relation</refpurpose>
2530 <indexterm><primary>SPI_getnspname</primary></indexterm>
2534 char * SPI_getnspname(Relation <parameter>rel</parameter>)
2539 <title>Description</title>
2542 <function>SPI_getnspname</function> returns a copy of the name of
2543 the namespace that the specified <structname>Relation</structname>
2544 belongs to. This is equivalent to the relation's schema. You should
2545 <function>pfree</function> the return value of this function when
2546 you are finished with it.
2551 <title>Arguments</title>
2555 <term><literal>Relation <parameter>rel</parameter></literal></term>
2566 <title>Return Value</title>
2569 The name of the specified relation's namespace.
2576 <sect1 id="spi-memory">
2577 <title>Memory Management</title>
2580 <productname>PostgreSQL</productname> allocates memory within
2581 <firstterm>memory contexts</firstterm><indexterm><primary>memory
2582 context</primary><secondary>in SPI</secondary></indexterm>, which provide a convenient method of
2583 managing allocations made in many different places that need to
2584 live for differing amounts of time. Destroying a context releases
2585 all the memory that was allocated in it. Thus, it is not necessary
2586 to keep track of individual objects to avoid memory leaks; instead
2587 only a relatively small number of contexts have to be managed.
2588 <function>palloc</function> and related functions allocate memory
2589 from the <quote>current</> context.
2593 <function>SPI_connect</function> creates a new memory context and
2594 makes it current. <function>SPI_finish</function> restores the
2595 previous current memory context and destroys the context created by
2596 <function>SPI_connect</function>. These actions ensure that
2597 transient memory allocations made inside your procedure are
2598 reclaimed at procedure exit, avoiding memory leakage.
2602 However, if your procedure needs to return an object in allocated
2603 memory (such as a value of a pass-by-reference data type), you
2604 cannot allocate that memory using <function>palloc</function>, at
2605 least not while you are connected to SPI. If you try, the object
2606 will be deallocated by <function>SPI_finish</function>, and your
2607 procedure will not work reliably. To solve this problem, use
2608 <function>SPI_palloc</function> to allocate memory for your return
2609 object. <function>SPI_palloc</function> allocates memory in the
2610 <quote>upper executor context</quote>, that is, the memory context
2611 that was current when <function>SPI_connect</function> was called,
2612 which is precisely the right context for a value returned from your
2617 If <function>SPI_palloc</function> is called while the procedure is
2618 not connected to SPI, then it acts the same as a normal
2619 <function>palloc</function>. Before a procedure connects to the
2620 SPI manager, the current memory context is the upper executor
2621 context, so all allocations made by the procedure via
2622 <function>palloc</function> or by SPI utility functions are made in
2627 When <function>SPI_connect</function> is called, the private
2628 context of the procedure, which is created by
2629 <function>SPI_connect</function>, is made the current context. All
2630 allocations made by <function>palloc</function>,
2631 <function>repalloc</function>, or SPI utility functions (except for
2632 <function>SPI_copytuple</function>,
2633 <function>SPI_returntuple</function>,
2634 <function>SPI_modifytuple</function>, and
2635 <function>SPI_palloc</function>) are made in this context. When a
2636 procedure disconnects from the SPI manager (via
2637 <function>SPI_finish</function>) the current context is restored to
2638 the upper executor context, and all allocations made in the
2639 procedure memory context are freed and cannot be used any more.
2643 All functions described in this section can be used by both
2644 connected and unconnected procedures. In an unconnected procedure,
2645 they act the same as the underlying ordinary server functions
2646 (<function>palloc</>, etc.).
2649 <!-- *********************************************** -->
2651 <refentry id="spi-spi-palloc">
2653 <refentrytitle>SPI_palloc</refentrytitle>
2657 <refname>SPI_palloc</refname>
2658 <refpurpose>allocate memory in the upper executor context</refpurpose>
2661 <indexterm><primary>SPI_palloc</primary></indexterm>
2665 void * SPI_palloc(Size <parameter>size</parameter>)
2670 <title>Description</title>
2673 <function>SPI_palloc</function> allocates memory in the upper
2679 <title>Arguments</title>
2683 <term><literal>Size <parameter>size</parameter></literal></term>
2686 size in bytes of storage to allocate
2694 <title>Return Value</title>
2697 pointer to new storage space of the specified size
2702 <!-- *********************************************** -->
2704 <refentry id="spi-realloc">
2706 <refentrytitle>SPI_repalloc</refentrytitle>
2710 <refname>SPI_repalloc</refname>
2711 <refpurpose>reallocate memory in the upper executor context</refpurpose>
2714 <indexterm><primary>SPI_repalloc</primary></indexterm>
2718 void * SPI_repalloc(void * <parameter>pointer</parameter>, Size <parameter>size</parameter>)
2723 <title>Description</title>
2726 <function>SPI_repalloc</function> changes the size of a memory
2727 segment previously allocated using <function>SPI_palloc</function>.
2731 This function is no longer different from plain
2732 <function>repalloc</function>. It's kept just for backward
2733 compatibility of existing code.
2738 <title>Arguments</title>
2742 <term><literal>void * <parameter>pointer</parameter></literal></term>
2745 pointer to existing storage to change
2751 <term><literal>Size <parameter>size</parameter></literal></term>
2754 size in bytes of storage to allocate
2762 <title>Return Value</title>
2765 pointer to new storage space of specified size with the contents
2766 copied from the existing area
2771 <!-- *********************************************** -->
2773 <refentry id="spi-spi-pfree">
2775 <refentrytitle>SPI_pfree</refentrytitle>
2779 <refname>SPI_pfree</refname>
2780 <refpurpose>free memory in the upper executor context</refpurpose>
2783 <indexterm><primary>SPI_pfree</primary></indexterm>
2787 void SPI_pfree(void * <parameter>pointer</parameter>)
2792 <title>Description</title>
2795 <function>SPI_pfree</function> frees memory previously allocated
2796 using <function>SPI_palloc</function> or
2797 <function>SPI_repalloc</function>.
2801 This function is no longer different from plain
2802 <function>pfree</function>. It's kept just for backward
2803 compatibility of existing code.
2808 <title>Arguments</title>
2812 <term><literal>void * <parameter>pointer</parameter></literal></term>
2815 pointer to existing storage to free
2823 <!-- *********************************************** -->
2825 <refentry id="spi-spi-copytuple">
2827 <refentrytitle>SPI_copytuple</refentrytitle>
2831 <refname>SPI_copytuple</refname>
2832 <refpurpose>make a copy of a row in the upper executor context</refpurpose>
2835 <indexterm><primary>SPI_copytuple</primary></indexterm>
2839 HeapTuple SPI_copytuple(HeapTuple <parameter>row</parameter>)
2844 <title>Description</title>
2847 <function>SPI_copytuple</function> makes a copy of a row in the
2848 upper executor context. This is normally used to return a modified
2849 row from a trigger. In a function declared to return a composite
2850 type, use <function>SPI_returntuple</function> instead.
2855 <title>Arguments</title>
2859 <term><literal>HeapTuple <parameter>row</parameter></literal></term>
2870 <title>Return Value</title>
2873 the copied row; <symbol>NULL</symbol> only if
2874 <parameter>tuple</parameter> is <symbol>NULL</symbol>
2879 <!-- *********************************************** -->
2881 <refentry id="spi-spi-returntuple">
2883 <refentrytitle>SPI_returntuple</refentrytitle>
2887 <refname>SPI_returntuple</refname>
2888 <refpurpose>prepare to return a tuple as a Datum</refpurpose>
2891 <indexterm><primary>SPI_returntuple</primary></indexterm>
2895 HeapTupleHeader SPI_returntuple(HeapTuple <parameter>row</parameter>, TupleDesc <parameter>rowdesc</parameter>)
2900 <title>Description</title>
2903 <function>SPI_returntuple</function> makes a copy of a row in
2904 the upper executor context, returning it in the form of a row type <type>Datum</type>.
2905 The returned pointer need only be converted to <type>Datum</type> via <function>PointerGetDatum</function>
2910 Note that this should be used for functions that are declared to return
2911 composite types. It is not used for triggers; use
2912 <function>SPI_copytuple</> for returning a modified row in a trigger.
2917 <title>Arguments</title>
2921 <term><literal>HeapTuple <parameter>row</parameter></literal></term>
2930 <term><literal>TupleDesc <parameter>rowdesc</parameter></literal></term>
2933 descriptor for row (pass the same descriptor each time for most
2942 <title>Return Value</title>
2945 <type>HeapTupleHeader</type> pointing to copied row;
2946 <symbol>NULL</symbol> only if
2947 <parameter>row</parameter> or <parameter>rowdesc</parameter> is
2948 <symbol>NULL</symbol>
2953 <!-- *********************************************** -->
2955 <refentry id="spi-spi-modifytuple">
2957 <refentrytitle>SPI_modifytuple</refentrytitle>
2961 <refname>SPI_modifytuple</refname>
2962 <refpurpose>create a row by replacing selected fields of a given row</refpurpose>
2965 <indexterm><primary>SPI_modifytuple</primary></indexterm>
2969 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>)
2974 <title>Description</title>
2977 <function>SPI_modifytuple</function> creates a new row by
2978 substituting new values for selected columns, copying the original
2979 row's columns at other positions. The input row is not modified.
2984 <title>Arguments</title>
2988 <term><literal>Relation <parameter>rel</parameter></literal></term>
2991 Used only as the source of the row descriptor for the row.
2992 (Passing a relation rather than a row descriptor is a
2999 <term><literal>HeapTuple <parameter>row</parameter></literal></term>
3008 <term><literal>int <parameter>ncols</parameter></literal></term>
3011 number of column numbers in the array
3012 <parameter>colnum</parameter>
3018 <term><literal>int * <parameter>colnum</parameter></literal></term>
3021 array of the numbers of the columns that are to be changed
3022 (column numbers start at 1)
3028 <term><literal>Datum * <parameter>values</parameter></literal></term>
3031 new values for the specified columns
3037 <term><literal>const char * <parameter>Nulls</parameter></literal></term>
3040 which new values are null, if any (see
3041 <function>SPI_execute_plan</function> for the format)
3049 <title>Return Value</title>
3052 new row with modifications, allocated in the upper executor
3053 context; <symbol>NULL</symbol> only if <parameter>row</parameter>
3054 is <symbol>NULL</symbol>
3058 On error, <varname>SPI_result</varname> is set as follows:
3061 <term><symbol>SPI_ERROR_ARGUMENT</symbol></term>
3064 if <parameter>rel</> is <symbol>NULL</>, or if
3065 <parameter>row</> is <symbol>NULL</>, or if <parameter>ncols</>
3066 is less than or equal to 0, or if <parameter>colnum</> is
3067 <symbol>NULL</>, or if <parameter>values</> is <symbol>NULL</>.
3073 <term><symbol>SPI_ERROR_NOATTRIBUTE</symbol></term>
3076 if <parameter>colnum</> contains an invalid column number (less
3077 than or equal to 0 or greater than the number of column in
3087 <!-- *********************************************** -->
3089 <refentry id="spi-spi-freetuple">
3091 <refentrytitle>SPI_freetuple</refentrytitle>
3095 <refname>SPI_freetuple</refname>
3096 <refpurpose>free a row allocated in the upper executor context</refpurpose>
3099 <indexterm><primary>SPI_freetuple</primary></indexterm>
3103 void SPI_freetuple(HeapTuple <parameter>row</parameter>)
3108 <title>Description</title>
3111 <function>SPI_freetuple</function> frees a row previously allocated
3112 in the upper executor context.
3116 This function is no longer different from plain
3117 <function>heap_freetuple</function>. It's kept just for backward
3118 compatibility of existing code.
3123 <title>Arguments</title>
3127 <term><literal>HeapTuple <parameter>row</parameter></literal></term>
3138 <!-- *********************************************** -->
3140 <refentry id="spi-spi-freetupletable">
3142 <refentrytitle>SPI_freetuptable</refentrytitle>
3146 <refname>SPI_freetuptable</refname>
3147 <refpurpose>free a row set created by <function>SPI_execute</> or a similar
3148 function</refpurpose>
3151 <indexterm><primary>SPI_freetuptable</primary></indexterm>
3155 void SPI_freetuptable(SPITupleTable * <parameter>tuptable</parameter>)
3160 <title>Description</title>
3163 <function>SPI_freetuptable</function> frees a row set created by a
3164 prior SPI command execution function, such as
3165 <function>SPI_execute</>. Therefore, this function is usually called
3166 with the global variable <varname>SPI_tupletable</varname> as
3171 This function is useful if a SPI procedure needs to execute
3172 multiple commands and does not want to keep the results of earlier
3173 commands around until it ends. Note that any unfreed row sets will
3174 be freed anyway at <function>SPI_finish</>.
3179 <title>Arguments</title>
3183 <term><literal>SPITupleTable * <parameter>tuptable</parameter></literal></term>
3186 pointer to row set to free
3194 <!-- *********************************************** -->
3196 <refentry id="spi-spi-freeplan">
3198 <refentrytitle>SPI_freeplan</refentrytitle>
3202 <refname>SPI_freeplan</refname>
3203 <refpurpose>free a previously saved plan</refpurpose>
3206 <indexterm><primary>SPI_freeplan</primary></indexterm>
3210 int SPI_freeplan(SPIPlanPtr <parameter>plan</parameter>)
3215 <title>Description</title>
3218 <function>SPI_freeplan</function> releases a command execution plan
3219 previously returned by <function>SPI_prepare</function> or saved by
3220 <function>SPI_saveplan</function>.
3225 <title>Arguments</title>
3229 <term><literal>SPIPlanPtr <parameter>plan</parameter></literal></term>
3232 pointer to plan to free
3240 <title>Return Value</title>
3243 <symbol>SPI_ERROR_ARGUMENT</symbol> if <parameter>plan</parameter>
3244 is <symbol>NULL</symbol> or invalid
3251 <sect1 id="spi-visibility">
3252 <title>Visibility of Data Changes</title>
3255 The following rules govern the visibility of data changes in
3256 functions that use SPI (or any other C function):
3261 During the execution of an SQL command, any data changes made by
3262 the command are invisible to the command itself. For
3265 INSERT INTO a SELECT * FROM a;
3267 the inserted rows are invisible to the <command>SELECT</command>
3274 Changes made by a command C are visible to all commands that are
3275 started after C, no matter whether they are started inside C
3276 (during the execution of C) or after C is done.
3282 Commands executed via SPI inside a function called by an SQL command
3283 (either an ordinary function or a trigger) follow one or the
3284 other of the above rules depending on the read/write flag passed
3285 to SPI. Commands executed in read-only mode follow the first
3286 rule: they cannot see changes of the calling command. Commands executed
3287 in read-write mode follow the second rule: they can see all changes made
3294 All standard procedural languages set the SPI read-write mode
3295 depending on the volatility attribute of the function. Commands of
3296 <literal>STABLE</> and <literal>IMMUTABLE</> functions are done in
3297 read-only mode, while commands of <literal>VOLATILE</> functions are
3298 done in read-write mode. While authors of C functions are able to
3299 violate this convention, it's unlikely to be a good idea to do so.
3306 The next section contains an example that illustrates the
3307 application of these rules.
3311 <sect1 id="spi-examples">
3312 <title>Examples</title>
3315 This section contains a very simple example of SPI usage. The
3316 procedure <function>execq</function> takes an SQL command as its
3317 first argument and a row count as its second, executes the command
3318 using <function>SPI_exec</function> and returns the number of rows
3319 that were processed by the command. You can find more complex
3320 examples for SPI in the source tree in
3321 <filename>src/test/regress/regress.c</filename> and in
3322 <filename>contrib/spi</filename>.
3326 #include "executor/spi.h"
3328 int execq(text *sql, int cnt);
3331 execq(text *sql, int cnt)
3337 /* Convert given text object to a C string */
3338 command = DatumGetCString(DirectFunctionCall1(textout,
3339 PointerGetDatum(sql)));
3343 ret = SPI_exec(command, cnt);
3345 proc = SPI_processed;
3347 * If some rows were fetched, print them via elog(INFO).
3349 if (ret > 0 && SPI_tuptable != NULL)
3351 TupleDesc tupdesc = SPI_tuptable->tupdesc;
3352 SPITupleTable *tuptable = SPI_tuptable;
3356 for (j = 0; j < proc; j++)
3358 HeapTuple tuple = tuptable->vals[j];
3360 for (i = 1, buf[0] = 0; i <= tupdesc->natts; i++)
3361 snprintf(buf + strlen (buf), sizeof(buf) - strlen(buf), " %s%s",
3362 SPI_getvalue(tuple, tupdesc, i),
3363 (i == tupdesc->natts) ? " " : " |");
3364 elog(INFO, "EXECQ: %s", buf);
3376 (This function uses call convention version 0, to make the example
3377 easier to understand. In real applications you should use the new
3378 version 1 interface.)
3382 This is how you declare the function after having compiled it into
3386 CREATE FUNCTION execq(text, integer) RETURNS integer
3387 AS '<replaceable>filename</replaceable>'
3393 Here is a sample session:
3396 => SELECT execq('CREATE TABLE a (x integer)', 0);
3402 => INSERT INTO a VALUES (execq('INSERT INTO a VALUES (0)', 0));
3404 => SELECT execq('SELECT * FROM a', 0);
3405 INFO: EXECQ: 0 -- inserted by execq
3406 INFO: EXECQ: 1 -- returned by execq and inserted by upper INSERT
3413 => SELECT execq('INSERT INTO a SELECT x + 2 FROM a', 1);
3419 => SELECT execq('SELECT * FROM a', 10);
3422 INFO: EXECQ: 2 -- 0 + 2, only one row inserted - as specified
3426 3 -- 10 is the max value only, 3 is the real number of rows
3429 => DELETE FROM a;
3431 => INSERT INTO a VALUES (execq('SELECT * FROM a', 0) + 1);
3433 => SELECT * FROM a;
3436 1 -- no rows in a (0) + 1
3439 => INSERT INTO a VALUES (execq('SELECT * FROM a', 0) + 1);
3442 => SELECT * FROM a;
3446 2 -- there was one row in a + 1
3449 -- This demonstrates the data changes visibility rule:
3451 => INSERT INTO a SELECT execq('SELECT * FROM a', 0) * x FROM a;
3458 => SELECT * FROM a;
3463 2 -- 2 rows * 1 (x in first row)
3464 6 -- 3 rows (2 + 1 just inserted) * 2 (x in second row)
3466 rows visible to execq() in different invocations