2 $PostgreSQL: pgsql/doc/src/sgml/spi.sgml,v 1.33 2004/03/17 01:05:10 momjian Exp $
6 <title>Server Programming Interface</title>
13 The <firstterm>Server Programming Interface</firstterm>
14 (<acronym>SPI</acronym>) gives writers of user-defined
15 <acronym>C</acronym> functions the ability to run
16 <acronym>SQL</acronym> commands inside their functions.
17 <acronym>SPI</acronym> is a set of
18 interface functions to simplify access to the parser, planner,
19 optimizer, and executor. <acronym>SPI</acronym> also does some
25 The available procedural languages provide various means to
26 execute SQL commands from procedures. Some of these are based on or
27 modelled after SPI, so this documentation might be of use for users
28 of those languages as well.
33 To avoid misunderstanding we'll use the term <quote>function</quote>
34 when we speak of <acronym>SPI</acronym> interface functions and
35 <quote>procedure</quote> for a user-defined C-function that is
36 using <acronym>SPI</acronym>.
40 Note that if during the execution of a procedure the transaction is
41 aborted because of an error in a command, then control will not be
42 returned to your procedure. Rather, all work will be rolled back
43 and the server will wait for the next command from the client. A
44 related restriction is the inability to execute
45 <command>BEGIN</command>, <command>COMMIT</command>, and
46 <command>ROLLBACK</command> (transaction control statements) inside
47 a procedure. Both of these restrictions will probably be changed in
52 <acronym>SPI</acronym> functions return a nonnegative result on
53 success (either via a returned integer value or in the global
54 variable <varname>SPI_result</varname>, as described below). On
55 error, a negative result or <symbol>NULL</symbol> will be returned.
59 Source code files that use SPI must include the header file
60 <filename>executor/spi.h</filename>.
64 <sect1 id="spi-interface">
65 <title>Interface Functions</title>
67 <refentry id="spi-spi-connect">
69 <refentrytitle>SPI_connect</refentrytitle>
73 <refname>SPI_connect</refname>
74 <refpurpose>connect a procedure to the SPI manager</refpurpose>
77 <indexterm><primary>SPI_connect</primary></indexterm>
86 <title>Description</title>
89 <function>SPI_connect</function> opens a connection from a
90 procedure invocation to the SPI manager. You must call this
91 function if you want to execute commands through SPI. Some utility
92 SPI functions may be called from unconnected procedures.
96 If your procedure is already connected,
97 <function>SPI_connect</function> will return the error code
98 <returnvalue>SPI_ERROR_CONNECT</returnvalue>. This could happen if
99 a procedure that has called <function>SPI_connect</function>
100 directly calls another procedure that calls
101 <function>SPI_connect</function>. While recursive calls to the
102 <acronym>SPI</acronym> manager are permitted when an SQL command
103 called through SPI invokes another function that uses
104 <acronym>SPI</acronym>, directly nested calls to
105 <function>SPI_connect</function> and
106 <function>SPI_finish</function> are forbidden.
111 <title>Return Value</title>
115 <term><symbol>SPI_OK_CONNECT</symbol></term>
124 <term><symbol>SPI_ERROR_CONNECT</symbol></term>
135 <!-- *********************************************** -->
137 <refentry id="spi-spi-finish">
139 <refentrytitle>SPI_finish</refentrytitle>
143 <refname>SPI_finish</refname>
144 <refpurpose>disconnect a procedure from the SPI manager</refpurpose>
147 <indexterm><primary>SPI_finish</primary></indexterm>
156 <title>Description</title>
159 <function>SPI_finish</function> closes an existing connection to
160 the SPI manager. You must call this function after completing the
161 SPI operations needed during your procedure's current invocation.
162 You do not need to worry about making this happen, however, if you
163 abort the transaction via <literal>elog(ERROR)</literal>. In that
164 case SPI will clean itself up automatically.
168 If <function>SPI_finish</function> is called without having a valid
169 connection, it will return <symbol>SPI_ERROR_UNCONNECTED</symbol>.
170 There is no fundamental problem with this; it means that the SPI
171 manager has nothing to do.
176 <title>Return Value</title>
180 <term><symbol>SPI_OK_FINISH</symbol></term>
183 if properly disconnected
189 <term><symbol>SPI_ERROR_UNCONNECTED</symbol></term>
192 if called from an unconnected procedure
200 <!-- *********************************************** -->
202 <refentry id="spi-spi-push">
204 <refentrytitle>SPI_push</refentrytitle>
208 <refname>SPI_push</refname>
209 <refpurpose>pushes SPI stack to allow recursive SPI calls</refpurpose>
212 <indexterm><primary>SPI_push</primary></indexterm>
221 <title>Description</title>
224 <function>SPI_push</function> pushes a new environment on to the
225 SPI call stack, allowing recursive calls to use a new environment.
231 <!-- *********************************************** -->
233 <refentry id="spi-spi-pop">
235 <refentrytitle>SPI_pop</refentrytitle>
239 <refname>SPI_pop</refname>
240 <refpurpose>pops SPI stack to allow recursive SPI calls</refpurpose>
243 <indexterm><primary>SPI_pop</primary></indexterm>
252 <title>Description</title>
255 <function>SPI_pop</function> pops the previous environment from the
256 SPI call stack. For use when returning from recursive SPI calls.
262 <!-- *********************************************** -->
264 <refentry id="spi-spi-exec">
266 <refentrytitle>SPI_exec</refentrytitle>
270 <refname>SPI_exec</refname>
271 <refpurpose>execute a command</refpurpose>
274 <indexterm><primary>SPI_exec</primary></indexterm>
278 int SPI_exec(const char * <parameter>command</parameter>, int <parameter>count</parameter>)
283 <title>Description</title>
286 <function>SPI_exec</function> executes the specified SQL command
287 for <parameter>count</parameter> rows.
291 This function should only be called from a connected procedure. If
292 <parameter>count</parameter> is zero then it executes the command
293 for all rows that it applies to. If <parameter>count</parameter>
294 is greater than 0, then the number of rows for which the command
295 will be executed is restricted (much like a
296 <literal>LIMIT</literal> clause). For example,
298 SPI_exec("INSERT INTO tab SELECT * FROM tab", 5);
300 will allow at most 5 rows to be inserted into the table.
304 You may pass multiple commands in one string, and the command may
305 be rewritten by rules. <function>SPI_exec</function> returns the
306 result for the command executed last.
310 The actual number of rows for which the (last) command was executed
311 is returned in the global variable <varname>SPI_processed</varname>
312 (unless the return value of the function is
313 <symbol>SPI_OK_UTILITY</symbol>). If the return value of the
314 function is <symbol>SPI_OK_SELECT</symbol> then you may the use
315 global pointer <literal>SPITupleTable *SPI_tuptable</literal> to
316 access the result rows.
320 The structure <structname>SPITupleTable</structname> is defined
325 MemoryContext tuptabcxt; /* memory context of result table */
326 uint32 alloced; /* number of alloced vals */
327 uint32 free; /* number of free vals */
328 TupleDesc tupdesc; /* row descriptor */
329 HeapTuple *vals; /* rows */
332 <structfield>vals</> is an array of pointers to rows. (The number
333 of valid entries is given by <varname>SPI_processed</varname>).
334 <structfield>tupdesc</> is a row descriptor which you may pass to
335 SPI functions dealing with rows. <structfield>tuptabcxt</>,
336 <structfield>alloced</>, and <structfield>free</> are internal
337 fields not intended for use by SPI callers.
341 <function>SPI_finish</function> frees all
342 <structname>SPITupleTable</>s allocated during the current
343 procedure. You can free a particular result table earlier, if you
344 are done with it, by calling <function>SPI_freetuptable</function>.
349 <title>Arguments</title>
353 <term><literal>const char * <parameter>command</parameter></literal></term>
356 string containing command to execute
362 <term><literal>int <parameter>count</parameter></literal></term>
365 maximum number of rows to process or return
373 <title>Return Value</title>
376 If the execution of the command was successful then one of the
377 following (nonnegative) values will be returned:
381 <term><symbol>SPI_OK_SELECT</symbol></term>
384 if a <command>SELECT</command> (but not <command>SELECT
385 INTO</>) was executed
391 <term><symbol>SPI_OK_SELINTO</symbol></term>
394 if a <command>SELECT INTO</command> was executed
400 <term><symbol>SPI_OK_DELETE</symbol></term>
403 if a <command>DELETE</command> was executed
409 <term><symbol>SPI_OK_INSERT</symbol></term>
412 if an <command>INSERT</command> was executed
418 <term><symbol>SPI_OK_UPDATE</symbol></term>
421 if an <command>UPDATE</command> was executed
427 <term><symbol>SPI_OK_UTILITY</symbol></term>
430 if a utility command (e.g., <command>CREATE TABLE</command>)
439 On error, one of the following negative values is returned:
443 <term><symbol>SPI_ERROR_ARGUMENT</symbol></term>
446 if <parameter>command</parameter> is <symbol>NULL</symbol> or
447 <parameter>count</parameter> is less than 0
453 <term><symbol>SPI_ERROR_COPY</symbol></term>
456 if <command>COPY TO stdout</> or <command>COPY FROM stdin</>
463 <term><symbol>SPI_ERROR_CURSOR</symbol></term>
466 if <command>DECLARE</>, <command>CLOSE</>, or <command>FETCH</>
473 <term><symbol>SPI_ERROR_TRANSACTION</symbol></term>
476 if <command>BEGIN</>, <command>COMMIT</>, or
477 <command>ROLLBACK</> was attempted
483 <term><symbol>SPI_ERROR_OPUNKNOWN</symbol></term>
486 if the command type is unknown (shouldn't happen)
492 <term><symbol>SPI_ERROR_UNCONNECTED</symbol></term>
495 if called from an unconnected procedure
507 The functions <function>SPI_exec</function>,
508 <function>SPI_execp</function>, and
509 <function>SPI_prepare</function> change both
510 <varname>SPI_processed</varname> and
511 <varname>SPI_tuptable</varname> (just the pointer, not the contents
512 of the structure). Save these two global variables into local
513 procedure variables if you need to access the result of
514 <function>SPI_exec</function> or <function>SPI_execp</function>
520 <!-- *********************************************** -->
522 <refentry id="spi-spi-prepare">
524 <refentrytitle>SPI_prepare</refentrytitle>
528 <refname>SPI_prepare</refname>
529 <refpurpose>prepare a plan for a command, without executing it yet</refpurpose>
532 <indexterm><primary>SPI_prepare</primary></indexterm>
536 void * SPI_prepare(const char * <parameter>command</parameter>, int <parameter>nargs</parameter>, Oid * <parameter>argtypes</parameter>)
541 <title>Description</title>
544 <function>SPI_prepare</function> creates and returns an execution
545 plan for the specified command but doesn't execute the command.
546 This function should only be called from a connected procedure.
550 When the same or a similar command is to be executed repeatedly, it
551 may be advantageous to perform the planning only once.
552 <function>SPI_prepare</function> converts a command string into an
553 execution plan that can be executed repeatedly using
554 <function>SPI_execp</function>.
558 A prepared command can be generalized by writing parameters
559 (<literal>$1</>, <literal>$2</>, etc.) in place of what would be
560 constants in a normal command. The actual values of the parameters
561 are then specified when <function>SPI_execp</function> is called.
562 This allows the prepared command to be used over a wider range of
563 situations than would be possible without parameters.
567 The plan returned by <function>SPI_prepare</function> can be used
568 only in the current invocation of the procedure since
569 <function>SPI_finish</function> frees memory allocated for a plan.
570 But a plan can be saved for longer using the function
571 <function>SPI_saveplan</function>.
576 <title>Arguments</title>
580 <term><literal>const char * <parameter>command</parameter></literal></term>
589 <term><literal>int <parameter>nargs</parameter></literal></term>
592 number of input parameters (<literal>$1</>, <literal>$2</>, etc.)
598 <term><literal>Oid * <parameter>argtypes</parameter></literal></term>
601 pointer to an array containing the <acronym>OID</acronym>s of
602 the data types of the parameters
610 <title>Return Value</title>
613 <function>SPI_prepare</function> returns non-null pointer to an
614 execution plan. On error, <symbol>NULL</symbol> will be returned.
615 In both cases, <varname>SPI_result</varname> will be set analogous
616 to the value returned by <function>SPI_exec</function>, except that
617 it is set to <symbol>SPI_ERROR_ARGUMENT</symbol> if
618 <parameter>command</parameter> is <symbol>NULL</symbol>, or if
619 <parameter>nargs</> is less than 0, or if <parameter>nargs</> is
620 greater than 0 and <parameter>argtypes</> is <symbol>NULL</symbol>.
628 There is a disadvantage to using parameters: since the planner does
629 not know the values that will be supplied for the parameters, it
630 may make worse planning choices than it would make for a normal
631 command with all constants visible.
636 <!-- *********************************************** -->
638 <refentry id="spi-spi-getargcount">
640 <refentrytitle>SPI_getargcount</refentrytitle>
644 <refname>SPI_getargcount</refname>
645 <refpurpose>returns the number of arguments needed when executing a plan
646 prepared by <function>SPI_prepare</function></refpurpose>
649 <indexterm><primary>SPI_getargcount</primary></indexterm>
653 int SPI_getargcount(void * <parameter>plan</parameter>)
658 <title>Description</title>
661 <function>SPI_getargcount</function> returns the number of arguments needed
662 when executing a plan prepared by <function>SPI_prepare</function>.
667 <title>Arguments</title>
671 <term><literal>void * <parameter>plan</parameter></literal></term>
674 execution plan (returned by <function>SPI_prepare</function>)
682 <title>Return Value</title>
684 The expected argument count for the <parameter>plan</parameter> or
685 <symbol>SPI_ERROR_ARGUMENT</symbol> if the <parameter>plan
686 </parameter> is <symbol>NULL</symbol>
691 <!-- *********************************************** -->
693 <refentry id="spi-spi-getargtypeid">
695 <refentrytitle>SPI_getargtypeid</refentrytitle>
699 <refname>SPI_getargtypeid</refname>
700 <refpurpose>returns the expected typeid for the specified argument when
701 executing a plan prepared by <function>SPI_prepare</function></refpurpose>
704 <indexterm><primary>SPI_getargtypeid</primary></indexterm>
708 Oid SPI_getargtypeid(void * <parameter>plan</parameter>, int <parameter>argIndex</parameter>)
713 <title>Description</title>
716 <function>SPI_getargtypeid</function> returns the Oid representing the type
717 id for argument at <parameter>argIndex</parameter> in a plan prepared by
718 <function>SPI_prepare</function>. First argument is at index zero.
723 <title>Arguments</title>
727 <term><literal>void * <parameter>plan</parameter></literal></term>
730 execution plan (returned by <function>SPI_prepare</function>)
736 <term><literal>int <parameter>argIndex</parameter></literal></term>
739 zero based index of the argument
747 <title>Return Value</title>
749 The type id of the argument at the given index or <symbol>
750 SPI_ERROR_ARGUMENT</symbol> if the <parameter>plan</parameter> is
751 <symbol>NULL</symbol> or <parameter>argIndex</parameter> is less than 0 or
752 not less than the number of arguments declared for the <parameter>plan
758 <!-- *********************************************** -->
760 <refentry id="spi-spi-is-cursor-plan">
762 <refentrytitle>SPI_is_cursor_plan</refentrytitle>
766 <refname>SPI_is_cursor_plan</refname>
767 <refpurpose>returns <symbol>true</symbol> if a plan
768 prepared by <function>SPI_prepare</function> can be passed
769 as an argument to <function>SPI_cursor_open</function></refpurpose>
772 <indexterm><primary>SPI_is_cursor_plan</primary></indexterm>
776 bool SPI_is_cursor_plan(void * <parameter>plan</parameter>)
781 <title>Description</title>
784 <function>SPI_is_cursor_plan</function> returns <symbol>true</symbol>
785 if a plan prepared by <function>SPI_prepare</function> can be passed
786 as an argument to <function>SPI_cursor_open</function> and <symbol>
787 false</symbol> if that is not the case. The criteria is that the
788 <parameter>plan</parameter> represents one single command and that this
789 command is a <command>SELECT</command> without an <command>INTO</command>
795 <title>Arguments</title>
799 <term><literal>void * <parameter>plan</parameter></literal></term>
802 execution plan (returned by <function>SPI_prepare</function>)
810 <title>Return Value</title>
812 <symbol>true</symbol> or <symbol>false</symbol> to indicate if the
813 <parameter>plan</parameter> can produce a cursor or not, or
814 <symbol>SPI_ERROR_ARGUMENT</symbol> if the <parameter>plan</parameter>
815 is <symbol>NULL</symbol>
820 <!-- *********************************************** -->
822 <refentry id="spi-spi-execp">
824 <refentrytitle>SPI_execp</refentrytitle>
828 <refname>SPI_execp</refname>
829 <refpurpose>executes a plan prepared by <function>SPI_prepare</function></refpurpose>
832 <indexterm><primary>SPI_execp</primary></indexterm>
836 int SPI_execp(void * <parameter>plan</parameter>, Datum * <parameter>values</parameter>, const char * <parameter>nulls</parameter>, int <parameter>count</parameter>)
841 <title>Description</title>
844 <function>SPI_execp</function> executes a plan prepared by
845 <function>SPI_prepare</function>. <parameter>tcount</parameter>
846 has the same interpretation as in <function>SPI_exec</function>.
851 <title>Arguments</title>
855 <term><literal>void * <parameter>plan</parameter></literal></term>
858 execution plan (returned by <function>SPI_prepare</function>)
864 <term><literal>Datum *<parameter>values</parameter></literal></term>
867 actual parameter values
873 <term><literal>const char * <parameter>nulls</parameter></literal></term>
876 An array describing which parameters are null.
877 <literal>n</literal> indicates a null value (entry in
878 <parameter>values</> will be ignored); a space indicates a
879 nonnull value (entry in <parameter>values</> is valid).
883 If <parameter>nulls</parameter> is <symbol>NULL</symbol> then
884 <function>SPI_execp</function> assumes that no parameters are
891 <term><literal>int <parameter>count</parameter></literal></term>
894 number of row for which plan is to be executed
902 <title>Return Value</title>
905 The return value is the same as for <function>SPI_exec</function>
906 or one of the following:
910 <term><symbol>SPI_ERROR_ARGUMENT</symbol></term>
913 if <parameter>plan</parameter> is <symbol>NULL</symbol> or
914 <parameter>count</parameter> is less than 0
920 <term><symbol>SPI_ERROR_PARAM</symbol></term>
923 if <parameter>values</parameter> is <symbol>NULL</symbol> and
924 <parameter>plan</parameter> was prepared with some parameters
932 <varname>SPI_processed</varname> and
933 <varname>SPI_tuptable</varname> are set as in
934 <function>SPI_exec</function> if successful.
942 If one of the objects (a table, function, etc.) referenced by the
943 prepared plan is dropped during the session then the result of
944 <function>SPI_execp</function> for this plan will be unpredictable.
949 <!-- *********************************************** -->
951 <refentry id="spi-spi-cursor-open">
953 <refentrytitle>SPI_cursor_open</refentrytitle>
957 <refname>SPI_cursor_open</refname>
958 <refpurpose>set up a cursor using a plan created with <function>SPI_prepare</function></refpurpose>
961 <indexterm><primary>SPI_cursor_open</primary></indexterm>
965 Portal SPI_cursor_open(const char * <parameter>name</parameter>, void * <parameter>plan</parameter>, Datum * <parameter>values</parameter>, const char * <parameter>nulls</parameter>)
970 <title>Description</title>
973 <function>SPI_cursor_open</function> sets up a cursor (internally,
974 a portal) that will execute a plan prepared by
975 <function>SPI_prepare</function>.
979 Using a cursor instead of executing the plan directly has two
980 benefits. First, the result rows can be retrieved a few at a time,
981 avoiding memory overrun for queries that return many rows. Second,
982 a portal can outlive the current procedure (it can, in fact, live
983 to the end of the current transaction). Returning the portal name
984 to the procedure's caller provides a way of returning a row set as
990 <title>Arguments</title>
994 <term><literal>const char * <parameter>name</parameter></literal></term>
997 name for portal, or <symbol>NULL</symbol> to let the system
1004 <term><literal>void * <parameter>plan</parameter></literal></term>
1007 execution plan (returned by <function>SPI_prepare</function>)
1013 <term><literal>Datum * <parameter>values</parameter></literal></term>
1016 actual parameter values
1022 <term><literal>const char *<parameter>nulls</parameter></literal></term>
1025 An array describing which parameters are null values.
1026 <literal>n</literal> indicates a null value (entry in
1027 <parameter>values</> will be ignored); a space indicates a
1028 nonnull value (entry in <parameter>values</> is valid). If
1029 <parameter>nulls</parameter> is <symbol>NULL</> then
1030 <function>SPI_cursor_open</function> assumes that no parameters
1039 <title>Return Value</title>
1042 pointer to portal containing the cursor, or <symbol>NULL</symbol>
1048 <!-- *********************************************** -->
1050 <refentry id="spi-spi-cursor-find">
1052 <refentrytitle>SPI_cursor_find</refentrytitle>
1056 <refname>SPI_cursor_find</refname>
1057 <refpurpose>find an existing cursor by name</refpurpose>
1060 <indexterm><primary>SPI_cursor_find</primary></indexterm>
1064 Portal SPI_cursor_find(const char * <parameter>name</parameter>)
1069 <title>Description</title>
1072 <function>SPI_cursor_find</function> finds an existing portal by
1073 name. This is primarily useful to resolve a cursor name returned
1074 as text by some other function.
1079 <title>Arguments</title>
1083 <term><literal>const char * <parameter>name</parameter></literal></term>
1094 <title>Return Value</title>
1097 pointer to the portal with the specified name, or
1098 <symbol>NULL</symbol> if none was found
1103 <!-- *********************************************** -->
1105 <refentry id="spi-spi-cursor-fetch">
1107 <refentrytitle>SPI_cursor_fetch</refentrytitle>
1111 <refname>SPI_cursor_fetch</refname>
1112 <refpurpose>fetch some rows from a cursor</refpurpose>
1115 <indexterm><primary>SPI_cursor_fetch</primary></indexterm>
1119 void SPI_cursor_fetch(Portal <parameter>portal</parameter>, bool <parameter>forward</parameter>, int <parameter>count</parameter>)
1124 <title>Description</title>
1127 <function>SPI_cursor_fetch</function> fetches some rows from a
1128 cursor. This is equivalent to the SQL command <command>FETCH</>.
1133 <title>Arguments</title>
1137 <term><literal>Portal <parameter>portal</parameter></literal></term>
1140 portal containing the cursor
1146 <term><literal>bool <parameter>forward</parameter></literal></term>
1149 true for fetch forward, false for fetch backward
1155 <term><literal>int <parameter>count</parameter></literal></term>
1158 maximum number of rows to fetch
1166 <title>Return Value</title>
1169 <varname>SPI_processed</varname> and
1170 <varname>SPI_tuptable</varname> are set as in
1171 <function>SPI_exec</function> if successful.
1176 <!-- *********************************************** -->
1178 <refentry id="spi-spi-cursor-move">
1180 <refentrytitle>SPI_cursor_move</refentrytitle>
1184 <refname>SPI_cursor_move</refname>
1185 <refpurpose>move a cursor</refpurpose>
1188 <indexterm><primary>SPI_cursor_move</primary></indexterm>
1192 void SPI_cursor_move(Portal <parameter>portal</parameter>, bool <parameter>forward</parameter>, int <parameter>count</parameter>)
1197 <title>Description</title>
1200 <function>SPI_cursor_move</function> skips over some number of rows
1201 in a cursor. This is equivalent to the SQL command
1207 <title>Arguments</title>
1211 <term><literal>Portal <parameter>portal</parameter></literal></term>
1214 portal containing the cursor
1220 <term><literal>bool <parameter>forward</parameter></literal></term>
1223 true for move forward, false for move backward
1229 <term><literal>int <parameter>count</parameter></literal></term>
1232 maximum number of rows to move
1240 <!-- *********************************************** -->
1242 <refentry id="spi-spi-cursor-close">
1244 <refentrytitle>SPI_cursor_close</refentrytitle>
1248 <refname>SPI_cursor_close</refname>
1249 <refpurpose>close a cursor</refpurpose>
1252 <indexterm><primary>SPI_cursor_close</primary></indexterm>
1256 void SPI_cursor_close(Portal <parameter>portal</parameter>)
1261 <title>Description</title>
1264 <function>SPI_cursor_close</function> closes a previously created
1265 cursor and releases its portal storage.
1269 All open cursors are closed automatically at the end of a
1270 transaction. <function>SPI_cursor_close</function> need only be
1271 invoked if it is desirable to release resources sooner.
1276 <title>Arguments</title>
1280 <term><literal>Portal <parameter>portal</parameter></literal></term>
1283 portal containing the cursor
1291 <!-- *********************************************** -->
1293 <refentry id="spi-spi-saveplan">
1295 <refentrytitle>SPI_saveplan</refentrytitle>
1299 <refname>SPI_saveplan</refname>
1300 <refpurpose>save a plan</refpurpose>
1303 <indexterm><primary>SPI_saveplan</primary></indexterm>
1307 void * SPI_saveplan(void * <parameter>plan</parameter>)
1312 <title>Description</title>
1315 <function>SPI_saveplan</function> saves a passed plan (prepared by
1316 <function>SPI_prepare</function>) in memory protected from freeing
1317 by <function>SPI_finish</function> and by the transaction manager
1318 and returns a pointer to the saved plan. This gives you the
1319 ability to reuse prepared plans in the subsequent invocations of
1320 your procedure in the current session. You may save the pointer
1321 returned in a local variable. Always check if this pointer is
1322 <symbol>NULL</symbol> or not either when preparing a plan or using
1323 an already prepared plan in <function>SPI_execp</function>.
1328 <title>Arguments</title>
1332 <term><literal>void * <parameter>plan</parameter></literal></term>
1335 the plan to be saved
1343 <title>Return Value</title>
1346 Pointer to the saved plan; <symbol>NULL</symbol> if unsuccessful.
1347 On error, <varname>SPI_result</varname> is set thus:
1351 <term><symbol>SPI_ERROR_ARGUMENT</symbol></term>
1354 if <parameter>plan</parameter> is <symbol>NULL</symbol>
1360 <term><symbol>SPI_ERROR_UNCONNECTED</symbol></term>
1363 if called from an unconnected procedure
1372 <title>Notes</title>
1375 If one of the objects (a table, function, etc.) referenced by the
1376 prepared plan is dropped during the session then the results of
1377 <function>SPI_execp</function> for this plan will be unpredictable.
1384 <sect1 id="spi-interface-support">
1385 <title>Interface Support Functions</title>
1388 The functions described here provide an interface for extracting
1389 information from result sets returned by <function>SPI_exec</> and
1390 other SPI functions.
1394 All functions described in this section may be used by both
1395 connected and unconnected procedures.
1398 <!-- *********************************************** -->
1400 <refentry id="spi-spi-fname">
1402 <refentrytitle>SPI_fname</refentrytitle>
1406 <refname>SPI_fname</refname>
1407 <refpurpose>determine the column name for the specified column number</refpurpose>
1410 <indexterm><primary>SPI_fname</primary></indexterm>
1414 char * SPI_fname(TupleDesc <parameter>rowdesc</parameter>, int <parameter>colnumber</parameter>)
1419 <title>Description</title>
1422 <function>SPI_fname</function> returns the column name of the
1423 specified column. (You can use <function>pfree</function> to
1424 release the copy of the name when you don't need it anymore.)
1429 <title>Arguments</title>
1433 <term><literal>TupleDesc <parameter>rowdesc</parameter></literal></term>
1436 input row description
1442 <term><literal>int <parameter>colnumber</parameter></literal></term>
1445 column number (count starts at 1)
1453 <title>Return Value</title>
1456 The column name; <symbol>NULL</symbol> if
1457 <parameter>colnumber</parameter> is out of range.
1458 <varname>SPI_result</varname> set to
1459 <symbol>SPI_ERROR_NOATTRIBUTE</symbol> on error.
1464 <!-- *********************************************** -->
1466 <refentry id="spi-spi-fnumber">
1468 <refentrytitle>SPI_fnumber</refentrytitle>
1472 <refname>SPI_fnumber</refname>
1473 <refpurpose>determine the column number for the specified column name</refpurpose>
1476 <indexterm><primary>SPI_fnumber</primary></indexterm>
1480 int SPI_fnumber(TupleDesc <parameter>rowdesc</parameter>, const char * <parameter>colname</parameter>)
1485 <title>Description</title>
1488 <function>SPI_fnumber</function> returns the column number for the
1489 column with the specified name.
1493 If <parameter>colname</parameter> refers to a system column (e.g.,
1494 <literal>oid</>) then the appropriate negative column number will
1495 be returned. The caller should be careful to test the return value
1496 for exact equality to <symbol>SPI_ERROR_NOATTRIBUTE</symbol> to
1497 detect an error; testing the result for less than or equal to 0 is
1498 not correct unless system columns should be rejected.
1503 <title>Arguments</title>
1507 <term><literal>TupleDesc <parameter>rowdesc</parameter></literal></term>
1510 input row description
1516 <term><literal>const char * <parameter>colname</parameter></literal></term>
1527 <title>Return Value</title>
1530 Column number (count starts at 1), or
1531 <symbol>SPI_ERROR_NOATTRIBUTE</symbol> if the named column was not
1537 <!-- *********************************************** -->
1539 <refentry id="spi-spi-getvalue">
1541 <refentrytitle>SPI_getvalue</refentrytitle>
1545 <refname>SPI_getvalue</refname>
1546 <refpurpose>return the string value of the specified column</refpurpose>
1549 <indexterm><primary>SPI_getvalue</primary></indexterm>
1553 char * SPI_getvalue(HeapTuple <parameter>row</parameter>, TupleDesc <parameter>rowdesc</parameter>, int <parameter>colnumber</parameter>)
1558 <title>Description</title>
1561 <function>SPI_getvalue</function> returns the string representation
1562 of the value of the specified column.
1566 The result is returned in memory allocated using
1567 <function>palloc</function>. (You can use
1568 <function>pfree</function> to release the memory when you don't
1574 <title>Arguments</title>
1578 <term><literal>HeapTuple <parameter>row</parameter></literal></term>
1581 input row to be examined
1587 <term><literal>TupleDesc <parameter>rowdesc</parameter></literal></term>
1590 input row description
1596 <term><literal>int <parameter>colnumber</parameter></literal></term>
1599 column number (count starts at 1)
1607 <title>Return Value</title>
1610 Column value, or <symbol>NULL</symbol> if the column is null,
1611 <parameter>colnumber</parameter> is out of range
1612 (<varname>SPI_result</varname> is set to
1613 <symbol>SPI_ERROR_NOATTRIBUTE</symbol>), or no no output function
1614 available (<varname>SPI_result</varname> is set to
1615 <symbol>SPI_ERROR_NOOUTFUNC</symbol>).
1620 <!-- *********************************************** -->
1622 <refentry id="spi-spi-getbinval">
1624 <refentrytitle>SPI_getbinval</refentrytitle>
1628 <refname>SPI_getbinval</refname>
1629 <refpurpose>return the binary value of the specified column</refpurpose>
1632 <indexterm><primary>SPI_getbinval</primary></indexterm>
1636 Datum SPI_getbinval(HeapTuple <parameter>row</parameter>, TupleDesc <parameter>rowdesc</parameter>, int <parameter>colnumber</parameter>, bool * <parameter>isnull</parameter>)
1641 <title>Description</title>
1644 <function>SPI_getbinval</function> returns the value of the
1645 specified column in the internal form (as type <type>Datum</type>).
1649 This function does not allocate new space for the datum. In the
1650 case of a pass-by-reference data type, the return value will be a
1651 pointer into the passed row.
1656 <title>Arguments</title>
1660 <term><literal>HeapTuple <parameter>row</parameter></literal></term>
1663 input row to be examined
1669 <term><literal>TupleDesc <parameter>rowdesc</parameter></literal></term>
1672 input row description
1678 <term><literal>int <parameter>rownumber</parameter></literal></term>
1681 column number (count starts at 1)
1687 <term><literal>bool * <parameter>isnull</parameter></literal></term>
1690 flag for a null value in the column
1698 <title>Return Value</title>
1701 The binary value of the column is returned. The variable pointed
1702 to by <parameter>isnull</parameter> is set to true if the column is
1703 null, else to false.
1707 <varname>SPI_result</varname> is set to
1708 <symbol>SPI_ERROR_NOATTRIBUTE</symbol> on error.
1713 <!-- *********************************************** -->
1715 <refentry id="spi-spi-gettype">
1717 <refentrytitle>SPI_gettype</refentrytitle>
1721 <refname>SPI_gettype</refname>
1722 <refpurpose>return the data type name of the specified column</refpurpose>
1725 <indexterm><primary>SPI_gettype</primary></indexterm>
1729 char * SPI_gettype(TupleDesc <parameter>rowdesc</parameter>, int <parameter>colnumber</parameter>)
1734 <title>Description</title>
1737 <function>SPI_gettype</function> returns the data type name of the
1738 specified column. (You can use <function>pfree</function> to
1739 release the copy of the name when you don't need it anymore.)
1744 <title>Arguments</title>
1748 <term><literal>TupleDesc <parameter>rowdesc</parameter></literal></term>
1751 input row description
1757 <term><literal>int <parameter>colnumber</parameter></literal></term>
1760 column number (count starts at 1)
1768 <title>Return Value</title>
1771 The data type name of the specified column, or
1772 <symbol>NULL</symbol> on error. <varname>SPI_result</varname> is
1773 set to <symbol>SPI_ERROR_NOATTRIBUTE</symbol> on error.
1778 <!-- *********************************************** -->
1780 <refentry id="spi-spi-gettypeid">
1782 <refentrytitle>SPI_gettypeid</refentrytitle>
1786 <refname>SPI_gettypeid</refname>
1787 <refpurpose>return the data type <acronym>OID</acronym> of the specified column</refpurpose>
1790 <indexterm><primary>SPI_gettypeid</primary></indexterm>
1794 Oid SPI_gettypeid(TupleDesc <parameter>rowdesc</parameter>, int <parameter>colnumber</parameter>)
1799 <title>Description</title>
1802 <function>SPI_gettypeid</function> returns the
1803 <acronym>OID</acronym> of the data type of the specified column.
1808 <title>Arguments</title>
1812 <term><literal>TupleDesc <parameter>rowdesc</parameter></literal></term>
1815 input row description
1821 <term><literal>int <parameter>colnumber</parameter></literal></term>
1824 column number (count starts at 1)
1832 <title>Return Value</title>
1835 The <acronym>OID</acronym> of the data type of the specified column
1836 or <symbol>InvalidOid</symbol> on error. On error,
1837 <varname>SPI_result</varname> is set to
1838 <symbol>SPI_ERROR_NOATTRIBUTE</symbol>.
1843 <!-- *********************************************** -->
1845 <refentry id="spi-spi-getrelname">
1847 <refentrytitle>SPI_getrelname</refentrytitle>
1851 <refname>SPI_getrelname</refname>
1852 <refpurpose>return the name of the specified relation</refpurpose>
1855 <indexterm><primary>SPI_getrelname</primary></indexterm>
1859 char * SPI_getrelname(Relation <parameter>rel</parameter>)
1864 <title>Description</title>
1867 <function>SPI_getrelname</function> returns the name of the
1868 specified relation. (You can use <function>pfree</function> to
1869 release the copy of the name when you don't need it anymore.)
1874 <title>Arguments</title>
1878 <term><literal>Relation <parameter>rel</parameter></literal></term>
1889 <title>Return Value</title>
1892 The name of the specified relation.
1899 <sect1 id="spi-memory">
1900 <title>Memory Management</title>
1903 <productname>PostgreSQL</productname> allocates memory within
1904 <firstterm>memory contexts</firstterm><indexterm><primary>memory
1905 context</primary><secondary>in SPI</secondary></indexterm>, which provide a convenient method of
1906 managing allocations made in many different places that need to
1907 live for differing amounts of time. Destroying a context releases
1908 all the memory that was allocated in it. Thus, it is not necessary
1909 to keep track of individual objects to avoid memory leaks; instead
1910 only a relatively small number of contexts have to be managed.
1911 <function>palloc</function> and related functions allocate memory
1912 from the <quote>current</> context.
1916 <function>SPI_connect</function> creates a new memory context and
1917 makes it current. <function>SPI_finish</function> restores the
1918 previous current memory context and destroys the context created by
1919 <function>SPI_connect</function>. These actions ensure that
1920 transient memory allocations made inside your procedure are
1921 reclaimed at procedure exit, avoiding memory leakage.
1925 However, if your procedure needs to return an object in allocated
1926 memory (such as a value of a pass-by-reference data type), you
1927 cannot allocate that memory using <function>palloc</function>, at
1928 least not while you are connected to SPI. If you try, the object
1929 will be deallocated by <function>SPI_finish</function>, and your
1930 procedure will not work reliably. To solve this problem, use
1931 <function>SPI_palloc</function> to allocate memory for your return
1932 object. <function>SPI_palloc</function> allocates memory in the
1933 <quote>upper executor context</quote>, that is, the memory context
1934 that was current when <function>SPI_connect</function> was called,
1935 which is precisely the right context for return a value from your
1940 If <function>SPI_palloc</function> is called while the procedure is
1941 not connected to SPI, then it acts the same as a normal
1942 <function>palloc</function>. Before a procedure connects to the
1943 SPI manager, the current memory context is the upper executor
1944 context, so all allocations made by the procedure via
1945 <function>palloc</function> or by SPI utility functions are made in
1950 When <function>SPI_connect</function> is called, the private
1951 context of the procedure, which is created by
1952 <function>SPI_connect</function>, is made the current context. All
1953 allocations made by <function>palloc</function>,
1954 <function>repalloc</function>, or SPI utility functions (except for
1955 <function>SPI_copytuple</function>,
1956 <function>SPI_copytupledesc</function>,
1957 <function>SPI_copytupleintoslot</function>,
1958 <function>SPI_modifytuple</function>, and
1959 <function>SPI_palloc</function>) are made in this context. When a
1960 procedure disconnects from the SPI manager (via
1961 <function>SPI_finish</function>) the current context is restored to
1962 the upper executor context, and all allocations made in the
1963 procedure memory context are freed and cannot be used any more.
1967 All functions described in this section may be used by both
1968 connected and unconnected procedures. In an unconnected procedure,
1969 they act the same as the underlying ordinary server functions
1970 (<function>palloc</>, etc.).
1973 <!-- *********************************************** -->
1975 <refentry id="spi-spi-palloc">
1977 <refentrytitle>SPI_palloc</refentrytitle>
1981 <refname>SPI_palloc</refname>
1982 <refpurpose>allocate memory in the upper executor context</refpurpose>
1985 <indexterm><primary>SPI_palloc</primary></indexterm>
1989 void * SPI_palloc(Size <parameter>size</parameter>)
1994 <title>Description</title>
1997 <function>SPI_palloc</function> allocates memory in the upper
2003 <title>Arguments</title>
2007 <term><literal>Size <parameter>size</parameter></literal></term>
2010 size in bytes of storage to allocate
2018 <title>Return Value</title>
2021 pointer to new storage space of the specified size
2026 <!-- *********************************************** -->
2028 <refentry id="spi-realloc">
2030 <refentrytitle>SPI_repalloc</refentrytitle>
2034 <refname>SPI_repalloc</refname>
2035 <refpurpose>reallocate memory in the upper executor context</refpurpose>
2038 <indexterm><primary>SPI_repalloc</primary></indexterm>
2042 void * SPI_repalloc(void * <parameter>pointer</parameter>, Size <parameter>size</parameter>)
2047 <title>Description</title>
2050 <function>SPI_repalloc</function> changes the size of a memory
2051 segment previously allocated using <function>SPI_palloc</function>.
2055 This function is no longer different from plain
2056 <function>repalloc</function>. It's kept just for backward
2057 compatibility of existing code.
2062 <title>Arguments</title>
2066 <term><literal>void * <parameter>pointer</parameter></literal></term>
2069 pointer to existing storage to change
2075 <term><literal>Size <parameter>size</parameter></literal></term>
2078 size in bytes of storage to allocate
2086 <title>Return Value</title>
2089 pointer to new storage space of specified size with the contents
2090 copied from the existing area
2095 <!-- *********************************************** -->
2097 <refentry id="spi-spi-pfree">
2099 <refentrytitle>SPI_pfree</refentrytitle>
2103 <refname>SPI_pfree</refname>
2104 <refpurpose>free memory in the upper executor context</refpurpose>
2107 <indexterm><primary>SPI_pfree</primary></indexterm>
2111 void SPI_pfree(void * <parameter>pointer</parameter>)
2116 <title>Description</title>
2119 <function>SPI_pfree</function> frees memory previously allocated
2120 using <function>SPI_palloc</function> or
2121 <function>SPI_repalloc</function>.
2125 This function is no longer different from plain
2126 <function>pfree</function>. It's kept just for backward
2127 compatibility of existing code.
2132 <title>Arguments</title>
2136 <term><literal>void * <parameter>pointer</parameter></literal></term>
2139 pointer to existing storage to free
2147 <!-- *********************************************** -->
2149 <refentry id="spi-spi-copytuple">
2151 <refentrytitle>SPI_copytuple</refentrytitle>
2155 <refname>SPI_copytuple</refname>
2156 <refpurpose>make a copy of a row in the upper executor context</refpurpose>
2159 <indexterm><primary>SPI_copytuple</primary></indexterm>
2163 HeapTuple SPI_copytuple(HeapTuple <parameter>row</parameter>)
2168 <title>Description</title>
2171 <function>SPI_copytuple</function> makes a copy of a row in the
2172 upper executor context.
2177 <title>Arguments</title>
2181 <term><literal>HeapTuple <parameter>row</parameter></literal></term>
2192 <title>Return Value</title>
2195 the copied row; <symbol>NULL</symbol> only if
2196 <parameter>tuple</parameter> is <symbol>NULL</symbol>
2201 <!-- *********************************************** -->
2203 <refentry id="spi-spi-copytupledesc">
2205 <refentrytitle>SPI_copytupledesc</refentrytitle>
2209 <refname>SPI_copytupledesc</refname>
2210 <refpurpose>make a copy of a row descriptor in the upper executor context</refpurpose>
2213 <indexterm><primary>SPI_copytupledesc</primary></indexterm>
2217 TupleDesc SPI_copytupledesc(TupleDesc <parameter>tupdesc</parameter>)
2222 <title>Description</title>
2225 <function>SPI_copytupledesc</function> makes a copy of a row
2226 descriptor in the upper executor context.
2231 <title>Arguments</title>
2235 <term><literal>TupleDesc <parameter>tupdesc</parameter></literal></term>
2238 row descriptor to be copied
2246 <title>Return Value</title>
2249 the copied row descriptor; <symbol>NULL</symbol> only if
2250 <parameter>tupdesc</parameter> is <symbol>NULL</symbol>
2255 <!-- *********************************************** -->
2257 <refentry id="spi-spi-copytupleintoslot">
2259 <refentrytitle>SPI_copytupleintoslot</refentrytitle>
2263 <refname>SPI_copytupleintoslot</refname>
2264 <refpurpose>make a copy of a row and descriptor in the upper executor context</refpurpose>
2267 <indexterm><primary>SPI_copytupleintoslot</primary></indexterm>
2271 TupleTableSlot * SPI_copytupleintoslot(HeapTuple <parameter>row</parameter>, TupleDesc <parameter>rowdesc</parameter>)
2276 <title>Description</title>
2279 <function>SPI_copytupleintoslot</function> makes a copy of a row in
2280 the upper executor context, returning it in the form of a filled-in
2281 <type>TupleTableSlot</type> structure.
2286 <title>Arguments</title>
2290 <term><literal>HeapTuple <parameter>row</parameter></literal></term>
2299 <term><literal>TupleDesc <parameter>rowdesc</parameter></literal></term>
2302 row descriptor to be copied
2310 <title>Return Value</title>
2313 <type>TupleTableSlot</type> containing the copied row and
2314 descriptor; <symbol>NULL</symbol> only if
2315 <parameter>row</parameter> or <parameter>rowdesc</parameter> are
2316 <symbol>NULL</symbol>
2321 <!-- *********************************************** -->
2323 <refentry id="spi-spi-modifytuple">
2325 <refentrytitle>SPI_modifytuple</refentrytitle>
2329 <refname>SPI_modifytuple</refname>
2330 <refpurpose>create a row by replacing selected fields of a given row</refpurpose>
2333 <indexterm><primary>SPI_modifytuple</primary></indexterm>
2337 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>)
2342 <title>Description</title>
2345 <function>SPI_modifytuple</function> creates a new row by
2346 substituting new values for selected columns, copying the original
2347 row's columns at other positions. The input row is not modified.
2352 <title>Arguments</title>
2356 <term><literal>Relation <parameter>rel</parameter></literal></term>
2359 Used only as the source of the row descriptor for the row.
2360 (Passing a relation rather than a row descriptor is a
2367 <term><literal>HeapTuple <parameter>row</parameter></literal></term>
2376 <term><literal>int <parameter>ncols</parameter></literal></term>
2379 number of column numbers in the array
2380 <parameter>colnum</parameter>
2386 <term><literal>int * <parameter>colnum</parameter></literal></term>
2389 array of the numbers of the columns that are to be changed
2396 <term><literal>Datum * <parameter>values</parameter></literal></term>
2399 new values for the specified columns
2405 <term><literal>const char * <parameter>Nulls</parameter></literal></term>
2408 which new values are null, if any (see <function>SPI_execp</function> for the format)
2416 <title>Return Value</title>
2419 new row with modifications, allocated in the upper executor
2420 context; <symbol>NULL</symbol> only if <parameter>row</parameter>
2421 is <symbol>NULL</symbol>
2425 On error, <varname>SPI_result</varname> is set as follows:
2428 <term><symbol>SPI_ERROR_ARGUMENT</symbol></term>
2431 if <parameter>rel</> is <symbol>NULL</>, or if
2432 <parameter>row</> is <symbol>NULL</>, or if <parameter>ncols</>
2433 is less than or equal to 0, or if <parameter>colnum</> is
2434 <symbol>NULL</>, or if <parameter>values</> is <symbol>NULL</>.
2440 <term><symbol>SPI_ERROR_NOATTRIBUTE</symbol></term>
2443 if <parameter>colnum</> contains an invalid column number (less
2444 than or equal to 0 or greater than the number of column in
2454 <!-- *********************************************** -->
2456 <refentry id="spi-spi-freetuple">
2458 <refentrytitle>SPI_freetuple</refentrytitle>
2462 <refname>SPI_freetuple</refname>
2463 <refpurpose>frees a row allocated in the upper executor context</refpurpose>
2466 <indexterm><primary>SPI_freetuple</primary></indexterm>
2470 void SPI_freetuple(HeapTuple <parameter>row</parameter>)
2475 <title>Description</title>
2478 <function>SPI_freetuple</function> frees a row previously allocated
2479 in the upper executor context.
2483 This function is no longer different from plain
2484 <function>heap_freetuple</function>. It's kept just for backward
2485 compatibility of existing code.
2490 <title>Arguments</title>
2494 <term><literal>HeapTuple <parameter>row</parameter></literal></term>
2505 <!-- *********************************************** -->
2507 <refentry id="spi-spi-freetupletable">
2509 <refentrytitle>SPI_freetuptable</refentrytitle>
2513 <refname>SPI_freetuptable</refname>
2514 <refpurpose>free a row set created by <function>SPI_exec</> or a similar function</refpurpose>
2517 <indexterm><primary>SPI_freetuptable</primary></indexterm>
2521 void SPI_freetuptable(SPITupleTable * <parameter>tuptable</parameter>)
2526 <title>Description</title>
2529 <function>SPI_freetuptable</function> frees a row set created by a
2530 prior SPI command execution function, such as
2531 <function>SPI_exec</>. Therefore, this function is usually called
2532 with the global variable <varname>SPI_tupletable</varname> as
2537 This function is useful if a SPI procedure needs to execute
2538 multiple commands and does not want to keep the results of earlier
2539 commands around until it ends. Note that any unfreed row sets will
2540 be freed anyway at <function>SPI_finish</>.
2545 <title>Arguments</title>
2549 <term><literal>SPITupleTable * <parameter>tuptable</parameter></literal></term>
2552 pointer to row set to free
2560 <!-- *********************************************** -->
2562 <refentry id="spi-spi-freeplan">
2564 <refentrytitle>SPI_freeplan</refentrytitle>
2568 <refname>SPI_freeplan</refname>
2569 <refpurpose>free a previously saved plan</refpurpose>
2572 <indexterm><primary>SPI_freeplan</primary></indexterm>
2576 int SPI_freeplan(void *<parameter>plan</parameter>)
2581 <title>Description</title>
2584 <function>SPI_freeplan</function> releases a command execution plan
2585 previously returned by <function>SPI_prepare</function> or saved by
2586 <function>SPI_saveplan</function>.
2591 <title>Arguments</title>
2595 <term><literal>void * <parameter>plan</parameter></literal></term>
2598 pointer to plan to free
2606 <title>Return Value</title>
2609 <symbol>SPI_ERROR_ARGUMENT</symbol> if <parameter>plan</parameter>
2610 is <symbol>NULL</symbol>.
2617 <sect1 id="spi-visibility">
2618 <title>Visibility of Data Changes</title>
2621 The following two rules govern the visibility of data changes in
2622 functions that use SPI (or any other C function):
2627 During the execution of an SQL command, any data changes made by
2628 the command (or by function called by the command, including
2629 trigger functions) are invisible to the command. For
2632 INSERT INTO a SELECT * FROM a;
2634 the inserted rows are invisible to the <command>SELECT</command>
2641 Changes made by a command C are visible to all commands that are
2642 started after C, no matter whether they are started inside C
2643 (during the execution of C) or after C is done.
2650 The next section contains an example that illustrates the
2651 application of these rules.
2655 <sect1 id="spi-examples">
2656 <title>Examples</title>
2659 This section contains a very simple example of SPI usage. The
2660 procedure <function>execq</function> takes an SQL command as its
2661 first argument and a row count as its second, executes the command
2662 using <function>SPI_exec</function> and returns the number of rows
2663 that were processed by the command. You can find more complex
2664 examples for SPI in the source tree in
2665 <filename>src/test/regress/regress.c</filename> and in
2666 <filename>contrib/spi</filename>.
2670 #include "executor/spi.h"
2672 int execq(text *sql, int cnt);
2675 execq(text *sql, int cnt)
2681 /* Convert given text object to a C string */
2682 command = DatumGetCString(DirectFunctionCall1(textout,
2683 PointerGetDatum(sql)));
2687 ret = SPI_exec(command, cnt);
2689 proc = SPI_processed;
2691 * If this is a SELECT and some rows were fetched,
2692 * then the rows are printed via elog(INFO).
2694 if (ret == SPI_OK_SELECT && SPI_processed > 0)
2696 TupleDesc tupdesc = SPI_tuptable->tupdesc;
2697 SPITupleTable *tuptable = SPI_tuptable;
2701 for (j = 0; j < proc; j++)
2703 HeapTuple tuple = tuptable->vals[j];
2705 for (i = 1, buf[0] = 0; i <= tupdesc->natts; i++)
2706 snprintf(buf + strlen (buf), sizeof(buf) - strlen(buf), " %s%s",
2707 SPI_getvalue(tuple, tupdesc, i),
2708 (i == tupdesc->natts) ? " " : " |");
2709 elog (INFO, "EXECQ: %s", buf);
2721 (This function uses call convention version 0, to make the example
2722 easier to understand. In real applications you should user the new
2723 version 1 interface.)
2727 This is how you declare the function after having compiled it into
2731 CREATE FUNCTION execq(text, integer) RETURNS integer
2732 AS '<replaceable>filename</replaceable>'
2738 Here is a sample session:
2741 => SELECT execq('CREATE TABLE a (x integer)', 0);
2747 => INSERT INTO a VALUES (execq('INSERT INTO a VALUES (0)', 0));
2749 => SELECT execq('SELECT * FROM a', 0);
2750 INFO: EXECQ: 0 -- inserted by execq
2751 INFO: EXECQ: 1 -- returned by execq and inserted by upper INSERT
2758 => SELECT execq('INSERT INTO a SELECT x + 2 FROM a', 1);
2764 => SELECT execq('SELECT * FROM a', 10);
2767 INFO: EXECQ: 2 -- 0 + 2, only one row inserted - as specified
2771 3 -- 10 is the max value only, 3 is the real number of rows
2776 => INSERT INTO a VALUES (execq('SELECT * FROM a', 0) + 1);
2781 1 -- no rows in a (0) + 1
2784 => INSERT INTO a VALUES (execq('SELECT * FROM a', 0) + 1);
2791 2 -- there was one row in a + 1
2794 -- This demonstrates the data changes visibility rule:
2796 => INSERT INTO a SELECT execq('SELECT * FROM a', 0) * x FROM a;
2808 2 -- 2 rows * 1 (x in first row)
2809 6 -- 3 rows (2 + 1 just inserted) * 2 (x in second row)
2811 rows visible to execq() in different invocations