1 <!-- $PostgreSQL: pgsql/doc/src/sgml/spi.sgml,v 1.62 2008/04/01 03:09:30 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-execute-with-args">
681 <refentrytitle>SPI_execute_with_args</refentrytitle>
685 <refname>SPI_execute_with_args</refname>
686 <refpurpose>execute a command with out-of-line parameters</refpurpose>
689 <indexterm><primary>SPI_execute_with_args</primary></indexterm>
693 int SPI_execute_with_args(const char *<parameter>command</parameter>,
694 int <parameter>nargs</parameter>, Oid *<parameter>argtypes</parameter>,
695 Datum *<parameter>values</parameter>, const char *<parameter>nulls</parameter>,
696 bool <parameter>read_only</parameter>, long <parameter>count</parameter>)
701 <title>Description</title>
704 <function>SPI_execute_with_args</function> executes a command that might
705 include references to externally supplied parameters. The command text
706 refers to a parameter as <literal>$<replaceable>n</></literal>, and
707 the call specifies data types and values for each such symbol.
708 <parameter>read_only</parameter> and <parameter>count</parameter> have
709 the same interpretation as in <function>SPI_execute</function>.
713 The main advantage of this routine compared to
714 <function>SPI_execute</function> is that data values can be inserted
715 into the command without tedious quoting/escaping, and thus with much
716 less risk of SQL-injection attacks.
720 Similar results can be achieved with <function>SPI_prepare</> followed by
721 <function>SPI_execute_plan</function>; however, when using this function
722 the query plan is customized to the specific parameter values provided.
723 For one-time query execution, this function should be preferred.
724 If the same command is to be executed with many different parameters,
725 either method might be faster, depending on the cost of re-planning
726 versus the benefit of custom plans.
731 <title>Arguments</title>
735 <term><literal>const char * <parameter>command</parameter></literal></term>
744 <term><literal>int <parameter>nargs</parameter></literal></term>
747 number of input parameters (<literal>$1</>, <literal>$2</>, etc.)
753 <term><literal>Oid * <parameter>argtypes</parameter></literal></term>
756 an array containing the <acronym>OID</acronym>s of
757 the data types of the parameters
763 <term><literal>Datum * <parameter>values</parameter></literal></term>
766 an array of actual parameter values
772 <term><literal>const char * <parameter>nulls</parameter></literal></term>
775 an array describing which parameters are null
779 If <parameter>nulls</parameter> is <symbol>NULL</symbol> then
780 <function>SPI_execute_with_args</function> assumes that no parameters are
787 <term><literal>bool <parameter>read_only</parameter></literal></term>
790 <literal>true</> for read-only execution
796 <term><literal>long <parameter>count</parameter></literal></term>
799 maximum number of rows to process or return
807 <title>Return Value</title>
810 The return value is the same as for <function>SPI_execute</function>.
814 <varname>SPI_processed</varname> and
815 <varname>SPI_tuptable</varname> are set as in
816 <function>SPI_execute</function> if successful.
821 <!-- *********************************************** -->
823 <refentry id="spi-spi-prepare">
825 <refentrytitle>SPI_prepare</refentrytitle>
829 <refname>SPI_prepare</refname>
830 <refpurpose>prepare a plan for a command, without executing it yet</refpurpose>
833 <indexterm><primary>SPI_prepare</primary></indexterm>
837 SPIPlanPtr SPI_prepare(const char * <parameter>command</parameter>, int <parameter>nargs</parameter>, Oid * <parameter>argtypes</parameter>)
842 <title>Description</title>
845 <function>SPI_prepare</function> creates and returns an execution
846 plan for the specified command but doesn't execute the command.
847 This function should only be called from a connected procedure.
851 When the same or a similar command is to be executed repeatedly, it
852 might be advantageous to perform the planning only once.
853 <function>SPI_prepare</function> converts a command string into an
854 execution plan that can be executed repeatedly using
855 <function>SPI_execute_plan</function>.
859 A prepared command can be generalized by writing parameters
860 (<literal>$1</>, <literal>$2</>, etc.) in place of what would be
861 constants in a normal command. The actual values of the parameters
862 are then specified when <function>SPI_execute_plan</function> is called.
863 This allows the prepared command to be used over a wider range of
864 situations than would be possible without parameters.
868 The plan returned by <function>SPI_prepare</function> can be used
869 only in the current invocation of the procedure, since
870 <function>SPI_finish</function> frees memory allocated for a plan.
871 But a plan can be saved for longer using the function
872 <function>SPI_saveplan</function>.
877 <title>Arguments</title>
881 <term><literal>const char * <parameter>command</parameter></literal></term>
890 <term><literal>int <parameter>nargs</parameter></literal></term>
893 number of input parameters (<literal>$1</>, <literal>$2</>, etc.)
899 <term><literal>Oid * <parameter>argtypes</parameter></literal></term>
902 pointer to an array containing the <acronym>OID</acronym>s of
903 the data types of the parameters
911 <title>Return Value</title>
914 <function>SPI_prepare</function> returns a non-null pointer to an
915 execution plan. On error, <symbol>NULL</symbol> will be returned,
916 and <varname>SPI_result</varname> will be set to one of the same
917 error codes used by <function>SPI_execute</function>, except that
918 it is set to <symbol>SPI_ERROR_ARGUMENT</symbol> if
919 <parameter>command</parameter> is <symbol>NULL</symbol>, or if
920 <parameter>nargs</> is less than 0, or if <parameter>nargs</> is
921 greater than 0 and <parameter>argtypes</> is <symbol>NULL</symbol>.
929 <type>SPIPlanPtr</> is declared as a pointer to an opaque struct type in
930 <filename>spi.h</>. It is unwise to try to access its contents
931 directly, as that makes your code much more likely to break in
932 future revisions of <productname>PostgreSQL</productname>.
936 There is a disadvantage to using parameters: since the planner does
937 not know the values that will be supplied for the parameters, it
938 might make worse planning choices than it would make for a normal
939 command with all constants visible.
944 <!-- *********************************************** -->
946 <refentry id="spi-spi-prepare-cursor">
948 <refentrytitle>SPI_prepare_cursor</refentrytitle>
952 <refname>SPI_prepare_cursor</refname>
953 <refpurpose>prepare a plan for a command, without executing it yet</refpurpose>
956 <indexterm><primary>SPI_prepare_cursor</primary></indexterm>
960 SPIPlanPtr SPI_prepare_cursor(const char * <parameter>command</parameter>, int <parameter>nargs</parameter>, Oid * <parameter>argtypes</parameter>, int <parameter>cursorOptions</parameter>)
965 <title>Description</title>
968 <function>SPI_prepare_cursor</function> is identical to
969 <function>SPI_prepare</function>, except that it also allows specification
970 of the planner's <quote>cursor options</> parameter. This is a bitmask
971 having the values shown in <filename>nodes/parsenodes.h</filename>
972 for the <structfield>options</> field of <structname>DeclareCursorStmt</>.
973 <function>SPI_prepare</function> always takes these options as zero.
978 <title>Arguments</title>
982 <term><literal>const char * <parameter>command</parameter></literal></term>
991 <term><literal>int <parameter>nargs</parameter></literal></term>
994 number of input parameters (<literal>$1</>, <literal>$2</>, etc.)
1000 <term><literal>Oid * <parameter>argtypes</parameter></literal></term>
1003 pointer to an array containing the <acronym>OID</acronym>s of
1004 the data types of the parameters
1010 <term><literal>int <parameter>cursorOptions</parameter></literal></term>
1013 integer bitmask of cursor options; zero produces default behavior
1021 <title>Return Value</title>
1024 <function>SPI_prepare_cursor</function> has the same return conventions as
1025 <function>SPI_prepare</function>.
1030 <title>Notes</title>
1033 Useful bits to set in <parameter>cursorOptions</> include
1034 <symbol>CURSOR_OPT_SCROLL</symbol>,
1035 <symbol>CURSOR_OPT_NO_SCROLL</symbol>, and
1036 <symbol>CURSOR_OPT_FAST_PLAN</symbol>. Note in particular that
1037 <symbol>CURSOR_OPT_HOLD</symbol> is ignored.
1042 <!-- *********************************************** -->
1044 <refentry id="spi-spi-getargcount">
1046 <refentrytitle>SPI_getargcount</refentrytitle>
1050 <refname>SPI_getargcount</refname>
1051 <refpurpose>return the number of arguments needed by a plan
1052 prepared by <function>SPI_prepare</function></refpurpose>
1055 <indexterm><primary>SPI_getargcount</primary></indexterm>
1059 int SPI_getargcount(SPIPlanPtr <parameter>plan</parameter>)
1064 <title>Description</title>
1067 <function>SPI_getargcount</function> returns the number of arguments needed
1068 to execute a plan prepared by <function>SPI_prepare</function>.
1073 <title>Arguments</title>
1077 <term><literal>SPIPlanPtr <parameter>plan</parameter></literal></term>
1080 execution plan (returned by <function>SPI_prepare</function>)
1088 <title>Return Value</title>
1090 The count of expected arguments for the <parameter>plan</parameter>.
1091 If the <parameter>plan</parameter> is <symbol>NULL</symbol> or invalid,
1092 <varname>SPI_result</varname> is set to <symbol>SPI_ERROR_ARGUMENT</symbol>
1093 and <literal>-1</literal> is returned.
1098 <!-- *********************************************** -->
1100 <refentry id="spi-spi-getargtypeid">
1102 <refentrytitle>SPI_getargtypeid</refentrytitle>
1106 <refname>SPI_getargtypeid</refname>
1107 <refpurpose>return the data type OID for an argument of
1108 a plan prepared by <function>SPI_prepare</function></refpurpose>
1111 <indexterm><primary>SPI_getargtypeid</primary></indexterm>
1115 Oid SPI_getargtypeid(SPIPlanPtr <parameter>plan</parameter>, int <parameter>argIndex</parameter>)
1120 <title>Description</title>
1123 <function>SPI_getargtypeid</function> returns the OID representing the type
1124 id for the <parameter>argIndex</parameter>'th argument of a plan prepared by
1125 <function>SPI_prepare</function>. First argument is at index zero.
1130 <title>Arguments</title>
1134 <term><literal>SPIPlanPtr <parameter>plan</parameter></literal></term>
1137 execution plan (returned by <function>SPI_prepare</function>)
1143 <term><literal>int <parameter>argIndex</parameter></literal></term>
1146 zero based index of the argument
1154 <title>Return Value</title>
1156 The type id of the argument at the given index.
1157 If the <parameter>plan</parameter> is <symbol>NULL</symbol> or invalid,
1158 or <parameter>argIndex</parameter> is less than 0 or
1159 not less than the number of arguments declared for the
1160 <parameter>plan</parameter>,
1161 <varname>SPI_result</varname> is set to <symbol>SPI_ERROR_ARGUMENT</symbol>
1162 and <symbol>InvalidOid</symbol> is returned.
1167 <!-- *********************************************** -->
1169 <refentry id="spi-spi-is-cursor-plan">
1171 <refentrytitle>SPI_is_cursor_plan</refentrytitle>
1175 <refname>SPI_is_cursor_plan</refname>
1176 <refpurpose>return <symbol>true</symbol> if a plan
1177 prepared by <function>SPI_prepare</function> can be used with
1178 <function>SPI_cursor_open</function></refpurpose>
1181 <indexterm><primary>SPI_is_cursor_plan</primary></indexterm>
1185 bool SPI_is_cursor_plan(SPIPlanPtr <parameter>plan</parameter>)
1190 <title>Description</title>
1193 <function>SPI_is_cursor_plan</function> returns <symbol>true</symbol>
1194 if a plan prepared by <function>SPI_prepare</function> can be passed
1195 as an argument to <function>SPI_cursor_open</function>, or
1196 <symbol>false</symbol> if that is not the case. The criteria are that the
1197 <parameter>plan</parameter> represents one single command and that this
1198 command returns tuples to the caller; for example, <command>SELECT</>
1199 is allowed unless it contains an <literal>INTO</> clause, and
1200 <command>UPDATE</> is allowed only if it contains a <literal>RETURNING</>
1206 <title>Arguments</title>
1210 <term><literal>SPIPlanPtr <parameter>plan</parameter></literal></term>
1213 execution plan (returned by <function>SPI_prepare</function>)
1221 <title>Return Value</title>
1223 <symbol>true</symbol> or <symbol>false</symbol> to indicate if the
1224 <parameter>plan</parameter> can produce a cursor or not, with
1225 <varname>SPI_result</varname> set to zero.
1226 If it is not possible to determine the answer (for example,
1227 if the <parameter>plan</parameter> is <symbol>NULL</symbol> or invalid,
1228 or if called when not connected to SPI), then
1229 <varname>SPI_result</varname> is set to a suitable error code
1230 and <symbol>false</symbol> is returned.
1235 <!-- *********************************************** -->
1237 <refentry id="spi-spi-execute-plan">
1239 <refentrytitle>SPI_execute_plan</refentrytitle>
1243 <refname>SPI_execute_plan</refname>
1244 <refpurpose>execute a plan prepared by <function>SPI_prepare</function></refpurpose>
1247 <indexterm><primary>SPI_execute_plan</primary></indexterm>
1251 int SPI_execute_plan(SPIPlanPtr <parameter>plan</parameter>, Datum * <parameter>values</parameter>, const char * <parameter>nulls</parameter>,
1252 bool <parameter>read_only</parameter>, long <parameter>count</parameter>)
1257 <title>Description</title>
1260 <function>SPI_execute_plan</function> executes a plan prepared by
1261 <function>SPI_prepare</function>. <parameter>read_only</parameter> and
1262 <parameter>count</parameter> have the same interpretation as in
1263 <function>SPI_execute</function>.
1268 <title>Arguments</title>
1272 <term><literal>SPIPlanPtr <parameter>plan</parameter></literal></term>
1275 execution plan (returned by <function>SPI_prepare</function>)
1281 <term><literal>Datum * <parameter>values</parameter></literal></term>
1284 An array of actual parameter values. Must have same length as the
1285 plan's number of arguments.
1291 <term><literal>const char * <parameter>nulls</parameter></literal></term>
1294 An array describing which parameters are null. Must have same length as
1295 the plan's number of arguments.
1296 <literal>n</literal> indicates a null value (entry in
1297 <parameter>values</> will be ignored); a space indicates a
1298 nonnull value (entry in <parameter>values</> is valid).
1302 If <parameter>nulls</parameter> is <symbol>NULL</symbol> then
1303 <function>SPI_execute_plan</function> assumes that no parameters are
1310 <term><literal>bool <parameter>read_only</parameter></literal></term>
1313 <literal>true</> for read-only execution
1319 <term><literal>long <parameter>count</parameter></literal></term>
1322 maximum number of rows to process or return
1330 <title>Return Value</title>
1333 The return value is the same as for <function>SPI_execute</function>,
1334 with the following additional possible error (negative) results:
1338 <term><symbol>SPI_ERROR_ARGUMENT</symbol></term>
1341 if <parameter>plan</parameter> is <symbol>NULL</symbol> or invalid,
1342 or <parameter>count</parameter> is less than 0
1348 <term><symbol>SPI_ERROR_PARAM</symbol></term>
1351 if <parameter>values</parameter> is <symbol>NULL</symbol> and
1352 <parameter>plan</parameter> was prepared with some parameters
1360 <varname>SPI_processed</varname> and
1361 <varname>SPI_tuptable</varname> are set as in
1362 <function>SPI_execute</function> if successful.
1367 <title>Notes</title>
1370 If one of the objects (a table, function, etc.) referenced by the
1371 prepared plan is dropped during the session then the result of
1372 <function>SPI_execute_plan</function> for this plan will be unpredictable.
1377 <!-- *********************************************** -->
1379 <refentry id="spi-spi-execp">
1381 <refentrytitle>SPI_execp</refentrytitle>
1385 <refname>SPI_execp</refname>
1386 <refpurpose>execute a plan in read/write mode</refpurpose>
1389 <indexterm><primary>SPI_execp</primary></indexterm>
1393 int SPI_execp(SPIPlanPtr <parameter>plan</parameter>, Datum * <parameter>values</parameter>, const char * <parameter>nulls</parameter>, long <parameter>count</parameter>)
1398 <title>Description</title>
1401 <function>SPI_execp</function> is the same as
1402 <function>SPI_execute_plan</function>, with the latter's
1403 <parameter>read_only</parameter> parameter always taken as
1409 <title>Arguments</title>
1413 <term><literal>SPIPlanPtr <parameter>plan</parameter></literal></term>
1416 execution plan (returned by <function>SPI_prepare</function>)
1422 <term><literal>Datum * <parameter>values</parameter></literal></term>
1425 An array of actual parameter values. Must have same length as the
1426 plan's number of arguments.
1432 <term><literal>const char * <parameter>nulls</parameter></literal></term>
1435 An array describing which parameters are null. Must have same length as
1436 the plan's number of arguments.
1437 <literal>n</literal> indicates a null value (entry in
1438 <parameter>values</> will be ignored); a space indicates a
1439 nonnull value (entry in <parameter>values</> is valid).
1443 If <parameter>nulls</parameter> is <symbol>NULL</symbol> then
1444 <function>SPI_execp</function> assumes that no parameters are
1451 <term><literal>long <parameter>count</parameter></literal></term>
1454 maximum number of rows to process or return
1462 <title>Return Value</title>
1465 See <function>SPI_execute_plan</function>.
1469 <varname>SPI_processed</varname> and
1470 <varname>SPI_tuptable</varname> are set as in
1471 <function>SPI_execute</function> if successful.
1476 <!-- *********************************************** -->
1478 <refentry id="spi-spi-cursor-open">
1480 <refentrytitle>SPI_cursor_open</refentrytitle>
1484 <refname>SPI_cursor_open</refname>
1485 <refpurpose>set up a cursor using a plan created with <function>SPI_prepare</function></refpurpose>
1488 <indexterm><primary>SPI_cursor_open</primary></indexterm>
1492 Portal SPI_cursor_open(const char * <parameter>name</parameter>, SPIPlanPtr <parameter>plan</parameter>,
1493 Datum * <parameter>values</parameter>, const char * <parameter>nulls</parameter>,
1494 bool <parameter>read_only</parameter>)
1499 <title>Description</title>
1502 <function>SPI_cursor_open</function> sets up a cursor (internally,
1503 a portal) that will execute a plan prepared by
1504 <function>SPI_prepare</function>. The parameters have the same
1505 meanings as the corresponding parameters to
1506 <function>SPI_execute_plan</function>.
1510 Using a cursor instead of executing the plan directly has two
1511 benefits. First, the result rows can be retrieved a few at a time,
1512 avoiding memory overrun for queries that return many rows. Second,
1513 a portal can outlive the current procedure (it can, in fact, live
1514 to the end of the current transaction). Returning the portal name
1515 to the procedure's caller provides a way of returning a row set as
1520 The passed-in data will be copied into the cursor's portal, so it
1521 can be freed while the cursor still exists.
1526 <title>Arguments</title>
1530 <term><literal>const char * <parameter>name</parameter></literal></term>
1533 name for portal, or <symbol>NULL</symbol> to let the system
1540 <term><literal>SPIPlanPtr <parameter>plan</parameter></literal></term>
1543 execution plan (returned by <function>SPI_prepare</function>)
1549 <term><literal>Datum * <parameter>values</parameter></literal></term>
1552 An array of actual parameter values. Must have same length as the
1553 plan's number of arguments.
1559 <term><literal>const char * <parameter>nulls</parameter></literal></term>
1562 An array describing which parameters are null. Must have same length as
1563 the plan's number of arguments.
1564 <literal>n</literal> indicates a null value (entry in
1565 <parameter>values</> will be ignored); a space indicates a
1566 nonnull value (entry in <parameter>values</> is valid).
1570 If <parameter>nulls</parameter> is <symbol>NULL</symbol> then
1571 <function>SPI_cursor_open</function> assumes that no parameters are
1578 <term><literal>bool <parameter>read_only</parameter></literal></term>
1581 <literal>true</> for read-only execution
1589 <title>Return Value</title>
1592 Pointer to portal containing the cursor. Note there is no error
1593 return convention; any error will be reported via <function>elog</>.
1598 <!-- *********************************************** -->
1600 <refentry id="spi-spi-cursor-open-with-args">
1602 <refentrytitle>SPI_cursor_open_with_args</refentrytitle>
1606 <refname>SPI_cursor_open_with_args</refname>
1607 <refpurpose>set up a cursor using a query and parameters</refpurpose>
1610 <indexterm><primary>SPI_cursor_open_with_args</primary></indexterm>
1614 Portal SPI_cursor_open_with_args(const char *<parameter>name</parameter>,
1615 const char *<parameter>command</parameter>,
1616 int <parameter>nargs</parameter>, Oid *<parameter>argtypes</parameter>,
1617 Datum *<parameter>values</parameter>, const char *<parameter>nulls</parameter>,
1618 bool <parameter>read_only</parameter>, int <parameter>cursorOptions</parameter>)
1623 <title>Description</title>
1626 <function>SPI_cursor_open_with_args</function> sets up a cursor
1627 (internally, a portal) that will execute the specified query.
1628 Most of the parameters have the same meanings as the corresponding
1629 parameters to <function>SPI_prepare_cursor</function>
1630 and <function>SPI_cursor_open</function>.
1634 For one-time query execution, this function should be preferred
1635 over <function>SPI_prepare_cursor</function> followed by
1636 <function>SPI_cursor_open</function>.
1637 If the same command is to be executed with many different parameters,
1638 either method might be faster, depending on the cost of re-planning
1639 versus the benefit of custom plans.
1643 The passed-in data will be copied into the cursor's portal, so it
1644 can be freed while the cursor still exists.
1649 <title>Arguments</title>
1653 <term><literal>const char * <parameter>name</parameter></literal></term>
1656 name for portal, or <symbol>NULL</symbol> to let the system
1663 <term><literal>const char * <parameter>command</parameter></literal></term>
1672 <term><literal>int <parameter>nargs</parameter></literal></term>
1675 number of input parameters (<literal>$1</>, <literal>$2</>, etc.)
1681 <term><literal>Oid * <parameter>argtypes</parameter></literal></term>
1684 an array containing the <acronym>OID</acronym>s of
1685 the data types of the parameters
1691 <term><literal>Datum * <parameter>values</parameter></literal></term>
1694 an array of actual parameter values
1700 <term><literal>const char * <parameter>nulls</parameter></literal></term>
1703 an array describing which parameters are null
1707 If <parameter>nulls</parameter> is <symbol>NULL</symbol> then
1708 <function>SPI_cursor_open_with_args</function> assumes that no
1709 parameters are null.
1715 <term><literal>bool <parameter>read_only</parameter></literal></term>
1718 <literal>true</> for read-only execution
1724 <term><literal>int <parameter>cursorOptions</parameter></literal></term>
1727 integer bitmask of cursor options; zero produces default behavior
1735 <title>Return Value</title>
1738 Pointer to portal containing the cursor. Note there is no error
1739 return convention; any error will be reported via <function>elog</>.
1744 <!-- *********************************************** -->
1746 <refentry id="spi-spi-cursor-find">
1748 <refentrytitle>SPI_cursor_find</refentrytitle>
1752 <refname>SPI_cursor_find</refname>
1753 <refpurpose>find an existing cursor by name</refpurpose>
1756 <indexterm><primary>SPI_cursor_find</primary></indexterm>
1760 Portal SPI_cursor_find(const char * <parameter>name</parameter>)
1765 <title>Description</title>
1768 <function>SPI_cursor_find</function> finds an existing portal by
1769 name. This is primarily useful to resolve a cursor name returned
1770 as text by some other function.
1775 <title>Arguments</title>
1779 <term><literal>const char * <parameter>name</parameter></literal></term>
1790 <title>Return Value</title>
1793 pointer to the portal with the specified name, or
1794 <symbol>NULL</symbol> if none was found
1799 <!-- *********************************************** -->
1801 <refentry id="spi-spi-cursor-fetch">
1803 <refentrytitle>SPI_cursor_fetch</refentrytitle>
1807 <refname>SPI_cursor_fetch</refname>
1808 <refpurpose>fetch some rows from a cursor</refpurpose>
1811 <indexterm><primary>SPI_cursor_fetch</primary></indexterm>
1815 void SPI_cursor_fetch(Portal <parameter>portal</parameter>, bool <parameter>forward</parameter>, long <parameter>count</parameter>)
1820 <title>Description</title>
1823 <function>SPI_cursor_fetch</function> fetches some rows from a
1824 cursor. This is equivalent to a subset of the SQL command
1825 <command>FETCH</> (see <function>SPI_scroll_cursor_fetch</function>
1826 for more functionality).
1831 <title>Arguments</title>
1835 <term><literal>Portal <parameter>portal</parameter></literal></term>
1838 portal containing the cursor
1844 <term><literal>bool <parameter>forward</parameter></literal></term>
1847 true for fetch forward, false for fetch backward
1853 <term><literal>long <parameter>count</parameter></literal></term>
1856 maximum number of rows to fetch
1864 <title>Return Value</title>
1867 <varname>SPI_processed</varname> and
1868 <varname>SPI_tuptable</varname> are set as in
1869 <function>SPI_execute</function> if successful.
1874 <title>Notes</title>
1877 Fetching backward may fail if the cursor's plan was not created
1878 with the <symbol>CURSOR_OPT_SCROLL</symbol> option.
1883 <!-- *********************************************** -->
1885 <refentry id="spi-spi-cursor-move">
1887 <refentrytitle>SPI_cursor_move</refentrytitle>
1891 <refname>SPI_cursor_move</refname>
1892 <refpurpose>move a cursor</refpurpose>
1895 <indexterm><primary>SPI_cursor_move</primary></indexterm>
1899 void SPI_cursor_move(Portal <parameter>portal</parameter>, bool <parameter>forward</parameter>, long <parameter>count</parameter>)
1904 <title>Description</title>
1907 <function>SPI_cursor_move</function> skips over some number of rows
1908 in a cursor. This is equivalent to a subset of the SQL command
1909 <command>MOVE</> (see <function>SPI_scroll_cursor_move</function>
1910 for more functionality).
1915 <title>Arguments</title>
1919 <term><literal>Portal <parameter>portal</parameter></literal></term>
1922 portal containing the cursor
1928 <term><literal>bool <parameter>forward</parameter></literal></term>
1931 true for move forward, false for move backward
1937 <term><literal>long <parameter>count</parameter></literal></term>
1940 maximum number of rows to move
1948 <title>Notes</title>
1951 Moving backward may fail if the cursor's plan was not created
1952 with the <symbol>CURSOR_OPT_SCROLL</symbol> option.
1957 <!-- *********************************************** -->
1959 <refentry id="spi-spi-scroll-cursor-fetch">
1961 <refentrytitle>SPI_scroll_cursor_fetch</refentrytitle>
1965 <refname>SPI_scroll_cursor_fetch</refname>
1966 <refpurpose>fetch some rows from a cursor</refpurpose>
1969 <indexterm><primary>SPI_scroll_cursor_fetch</primary></indexterm>
1973 void SPI_scroll_cursor_fetch(Portal <parameter>portal</parameter>, FetchDirection <parameter>direction</parameter>, long <parameter>count</parameter>)
1978 <title>Description</title>
1981 <function>SPI_scroll_cursor_fetch</function> fetches some rows from a
1982 cursor. This is equivalent to the SQL command <command>FETCH</>.
1987 <title>Arguments</title>
1991 <term><literal>Portal <parameter>portal</parameter></literal></term>
1994 portal containing the cursor
2000 <term><literal>FetchDirection <parameter>direction</parameter></literal></term>
2003 one of <symbol>FETCH_FORWARD</symbol>,
2004 <symbol>FETCH_BACKWARD</symbol>,
2005 <symbol>FETCH_ABSOLUTE</symbol> or
2006 <symbol>FETCH_RELATIVE</symbol>
2012 <term><literal>long <parameter>count</parameter></literal></term>
2015 number of rows to fetch for
2016 <symbol>FETCH_FORWARD</symbol> or
2017 <symbol>FETCH_BACKWARD</symbol>; absolute row number to fetch for
2018 <symbol>FETCH_ABSOLUTE</symbol>; or relative row number to fetch for
2019 <symbol>FETCH_RELATIVE</symbol>
2027 <title>Return Value</title>
2030 <varname>SPI_processed</varname> and
2031 <varname>SPI_tuptable</varname> are set as in
2032 <function>SPI_execute</function> if successful.
2037 <title>Notes</title>
2040 See the SQL <xref linkend="sql-fetch" endterm="sql-fetch-title"> command
2041 for details of the interpretation of the
2042 <parameter>direction</parameter> and
2043 <parameter>count</parameter> parameters.
2047 Direction values other than <symbol>FETCH_FORWARD</symbol>
2048 may fail if the cursor's plan was not created
2049 with the <symbol>CURSOR_OPT_SCROLL</symbol> option.
2054 <!-- *********************************************** -->
2056 <refentry id="spi-spi-scroll-cursor-move">
2058 <refentrytitle>SPI_scroll_cursor_move</refentrytitle>
2062 <refname>SPI_scroll_cursor_move</refname>
2063 <refpurpose>move a cursor</refpurpose>
2066 <indexterm><primary>SPI_scroll_cursor_move</primary></indexterm>
2070 void SPI_scroll_cursor_move(Portal <parameter>portal</parameter>, FetchDirection <parameter>direction</parameter>, long <parameter>count</parameter>)
2075 <title>Description</title>
2078 <function>SPI_scroll_cursor_move</function> skips over some number of rows
2079 in a cursor. This is equivalent to the SQL command
2085 <title>Arguments</title>
2089 <term><literal>Portal <parameter>portal</parameter></literal></term>
2092 portal containing the cursor
2098 <term><literal>FetchDirection <parameter>direction</parameter></literal></term>
2101 one of <symbol>FETCH_FORWARD</symbol>,
2102 <symbol>FETCH_BACKWARD</symbol>,
2103 <symbol>FETCH_ABSOLUTE</symbol> or
2104 <symbol>FETCH_RELATIVE</symbol>
2110 <term><literal>long <parameter>count</parameter></literal></term>
2113 number of rows to move for
2114 <symbol>FETCH_FORWARD</symbol> or
2115 <symbol>FETCH_BACKWARD</symbol>; absolute row number to move to for
2116 <symbol>FETCH_ABSOLUTE</symbol>; or relative row number to move to for
2117 <symbol>FETCH_RELATIVE</symbol>
2125 <title>Return Value</title>
2128 <varname>SPI_processed</varname> is set as in
2129 <function>SPI_execute</function> if successful.
2130 <varname>SPI_tuptable</varname> is set to <symbol>NULL</>, since
2131 no rows are returned by this function.
2136 <title>Notes</title>
2139 See the SQL <xref linkend="sql-fetch" endterm="sql-fetch-title"> command
2140 for details of the interpretation of the
2141 <parameter>direction</parameter> and
2142 <parameter>count</parameter> parameters.
2146 Direction values other than <symbol>FETCH_FORWARD</symbol>
2147 may fail if the cursor's plan was not created
2148 with the <symbol>CURSOR_OPT_SCROLL</symbol> option.
2153 <!-- *********************************************** -->
2155 <refentry id="spi-spi-cursor-close">
2157 <refentrytitle>SPI_cursor_close</refentrytitle>
2161 <refname>SPI_cursor_close</refname>
2162 <refpurpose>close a cursor</refpurpose>
2165 <indexterm><primary>SPI_cursor_close</primary></indexterm>
2169 void SPI_cursor_close(Portal <parameter>portal</parameter>)
2174 <title>Description</title>
2177 <function>SPI_cursor_close</function> closes a previously created
2178 cursor and releases its portal storage.
2182 All open cursors are closed automatically at the end of a
2183 transaction. <function>SPI_cursor_close</function> need only be
2184 invoked if it is desirable to release resources sooner.
2189 <title>Arguments</title>
2193 <term><literal>Portal <parameter>portal</parameter></literal></term>
2196 portal containing the cursor
2204 <!-- *********************************************** -->
2206 <refentry id="spi-spi-saveplan">
2208 <refentrytitle>SPI_saveplan</refentrytitle>
2212 <refname>SPI_saveplan</refname>
2213 <refpurpose>save a plan</refpurpose>
2216 <indexterm><primary>SPI_saveplan</primary></indexterm>
2220 SPIPlanPtr SPI_saveplan(SPIPlanPtr <parameter>plan</parameter>)
2225 <title>Description</title>
2228 <function>SPI_saveplan</function> saves a passed plan (prepared by
2229 <function>SPI_prepare</function>) in memory that will not be freed
2230 by <function>SPI_finish</function> nor by the transaction manager,
2231 and returns a pointer to the saved plan. This gives you the
2232 ability to reuse prepared plans in the subsequent invocations of
2233 your procedure in the current session.
2238 <title>Arguments</title>
2242 <term><literal>SPIPlanPtr <parameter>plan</parameter></literal></term>
2245 the plan to be saved
2253 <title>Return Value</title>
2256 Pointer to the saved plan; <symbol>NULL</symbol> if unsuccessful.
2257 On error, <varname>SPI_result</varname> is set thus:
2261 <term><symbol>SPI_ERROR_ARGUMENT</symbol></term>
2264 if <parameter>plan</parameter> is <symbol>NULL</symbol> or invalid
2270 <term><symbol>SPI_ERROR_UNCONNECTED</symbol></term>
2273 if called from an unconnected procedure
2282 <title>Notes</title>
2285 The passed-in plan is not freed, so you might wish to do
2286 <function>SPI_freeplan</function> on it to avoid leaking memory
2287 until <function>SPI_finish</>.
2291 If one of the objects (a table, function, etc.) referenced by the
2292 prepared plan is dropped or redefined, then future executions of
2293 <function>SPI_execute_plan</function> may fail or return different
2294 results than the plan initially indicates.
2301 <sect1 id="spi-interface-support">
2302 <title>Interface Support Functions</title>
2305 The functions described here provide an interface for extracting
2306 information from result sets returned by <function>SPI_execute</> and
2307 other SPI functions.
2311 All functions described in this section can be used by both
2312 connected and unconnected procedures.
2315 <!-- *********************************************** -->
2317 <refentry id="spi-spi-fname">
2319 <refentrytitle>SPI_fname</refentrytitle>
2323 <refname>SPI_fname</refname>
2324 <refpurpose>determine the column name for the specified column number</refpurpose>
2327 <indexterm><primary>SPI_fname</primary></indexterm>
2331 char * SPI_fname(TupleDesc <parameter>rowdesc</parameter>, int <parameter>colnumber</parameter>)
2336 <title>Description</title>
2339 <function>SPI_fname</function> returns a copy of the column name of the
2340 specified column. (You can use <function>pfree</function> to
2341 release the copy of the name when you don't need it anymore.)
2346 <title>Arguments</title>
2350 <term><literal>TupleDesc <parameter>rowdesc</parameter></literal></term>
2353 input row description
2359 <term><literal>int <parameter>colnumber</parameter></literal></term>
2362 column number (count starts at 1)
2370 <title>Return Value</title>
2373 The column name; <symbol>NULL</symbol> if
2374 <parameter>colnumber</parameter> is out of range.
2375 <varname>SPI_result</varname> set to
2376 <symbol>SPI_ERROR_NOATTRIBUTE</symbol> on error.
2381 <!-- *********************************************** -->
2383 <refentry id="spi-spi-fnumber">
2385 <refentrytitle>SPI_fnumber</refentrytitle>
2389 <refname>SPI_fnumber</refname>
2390 <refpurpose>determine the column number for the specified column name</refpurpose>
2393 <indexterm><primary>SPI_fnumber</primary></indexterm>
2397 int SPI_fnumber(TupleDesc <parameter>rowdesc</parameter>, const char * <parameter>colname</parameter>)
2402 <title>Description</title>
2405 <function>SPI_fnumber</function> returns the column number for the
2406 column with the specified name.
2410 If <parameter>colname</parameter> refers to a system column (e.g.,
2411 <literal>oid</>) then the appropriate negative column number will
2412 be returned. The caller should be careful to test the return value
2413 for exact equality to <symbol>SPI_ERROR_NOATTRIBUTE</symbol> to
2414 detect an error; testing the result for less than or equal to 0 is
2415 not correct unless system columns should be rejected.
2420 <title>Arguments</title>
2424 <term><literal>TupleDesc <parameter>rowdesc</parameter></literal></term>
2427 input row description
2433 <term><literal>const char * <parameter>colname</parameter></literal></term>
2444 <title>Return Value</title>
2447 Column number (count starts at 1), or
2448 <symbol>SPI_ERROR_NOATTRIBUTE</symbol> if the named column was not
2454 <!-- *********************************************** -->
2456 <refentry id="spi-spi-getvalue">
2458 <refentrytitle>SPI_getvalue</refentrytitle>
2462 <refname>SPI_getvalue</refname>
2463 <refpurpose>return the string value of the specified column</refpurpose>
2466 <indexterm><primary>SPI_getvalue</primary></indexterm>
2470 char * SPI_getvalue(HeapTuple <parameter>row</parameter>, TupleDesc <parameter>rowdesc</parameter>, int <parameter>colnumber</parameter>)
2475 <title>Description</title>
2478 <function>SPI_getvalue</function> returns the string representation
2479 of the value of the specified column.
2483 The result is returned in memory allocated using
2484 <function>palloc</function>. (You can use
2485 <function>pfree</function> to release the memory when you don't
2491 <title>Arguments</title>
2495 <term><literal>HeapTuple <parameter>row</parameter></literal></term>
2498 input row to be examined
2504 <term><literal>TupleDesc <parameter>rowdesc</parameter></literal></term>
2507 input row description
2513 <term><literal>int <parameter>colnumber</parameter></literal></term>
2516 column number (count starts at 1)
2524 <title>Return Value</title>
2527 Column value, or <symbol>NULL</symbol> if the column is null,
2528 <parameter>colnumber</parameter> is out of range
2529 (<varname>SPI_result</varname> is set to
2530 <symbol>SPI_ERROR_NOATTRIBUTE</symbol>), or no output function is
2531 available (<varname>SPI_result</varname> is set to
2532 <symbol>SPI_ERROR_NOOUTFUNC</symbol>).
2537 <!-- *********************************************** -->
2539 <refentry id="spi-spi-getbinval">
2541 <refentrytitle>SPI_getbinval</refentrytitle>
2545 <refname>SPI_getbinval</refname>
2546 <refpurpose>return the binary value of the specified column</refpurpose>
2549 <indexterm><primary>SPI_getbinval</primary></indexterm>
2553 Datum SPI_getbinval(HeapTuple <parameter>row</parameter>, TupleDesc <parameter>rowdesc</parameter>, int <parameter>colnumber</parameter>, bool * <parameter>isnull</parameter>)
2558 <title>Description</title>
2561 <function>SPI_getbinval</function> returns the value of the
2562 specified column in the internal form (as type <type>Datum</type>).
2566 This function does not allocate new space for the datum. In the
2567 case of a pass-by-reference data type, the return value will be a
2568 pointer into the passed row.
2573 <title>Arguments</title>
2577 <term><literal>HeapTuple <parameter>row</parameter></literal></term>
2580 input row to be examined
2586 <term><literal>TupleDesc <parameter>rowdesc</parameter></literal></term>
2589 input row description
2595 <term><literal>int <parameter>colnumber</parameter></literal></term>
2598 column number (count starts at 1)
2604 <term><literal>bool * <parameter>isnull</parameter></literal></term>
2607 flag for a null value in the column
2615 <title>Return Value</title>
2618 The binary value of the column is returned. The variable pointed
2619 to by <parameter>isnull</parameter> is set to true if the column is
2620 null, else to false.
2624 <varname>SPI_result</varname> is set to
2625 <symbol>SPI_ERROR_NOATTRIBUTE</symbol> on error.
2630 <!-- *********************************************** -->
2632 <refentry id="spi-spi-gettype">
2634 <refentrytitle>SPI_gettype</refentrytitle>
2638 <refname>SPI_gettype</refname>
2639 <refpurpose>return the data type name of the specified column</refpurpose>
2642 <indexterm><primary>SPI_gettype</primary></indexterm>
2646 char * SPI_gettype(TupleDesc <parameter>rowdesc</parameter>, int <parameter>colnumber</parameter>)
2651 <title>Description</title>
2654 <function>SPI_gettype</function> returns a copy of the data type name of the
2655 specified column. (You can use <function>pfree</function> to
2656 release the copy of the name when you don't need it anymore.)
2661 <title>Arguments</title>
2665 <term><literal>TupleDesc <parameter>rowdesc</parameter></literal></term>
2668 input row description
2674 <term><literal>int <parameter>colnumber</parameter></literal></term>
2677 column number (count starts at 1)
2685 <title>Return Value</title>
2688 The data type name of the specified column, or
2689 <symbol>NULL</symbol> on error. <varname>SPI_result</varname> is
2690 set to <symbol>SPI_ERROR_NOATTRIBUTE</symbol> on error.
2695 <!-- *********************************************** -->
2697 <refentry id="spi-spi-gettypeid">
2699 <refentrytitle>SPI_gettypeid</refentrytitle>
2703 <refname>SPI_gettypeid</refname>
2704 <refpurpose>return the data type <acronym>OID</acronym> of the specified column</refpurpose>
2707 <indexterm><primary>SPI_gettypeid</primary></indexterm>
2711 Oid SPI_gettypeid(TupleDesc <parameter>rowdesc</parameter>, int <parameter>colnumber</parameter>)
2716 <title>Description</title>
2719 <function>SPI_gettypeid</function> returns the
2720 <acronym>OID</acronym> of the data type of the specified column.
2725 <title>Arguments</title>
2729 <term><literal>TupleDesc <parameter>rowdesc</parameter></literal></term>
2732 input row description
2738 <term><literal>int <parameter>colnumber</parameter></literal></term>
2741 column number (count starts at 1)
2749 <title>Return Value</title>
2752 The <acronym>OID</acronym> of the data type of the specified column
2753 or <symbol>InvalidOid</symbol> on error. On error,
2754 <varname>SPI_result</varname> is set to
2755 <symbol>SPI_ERROR_NOATTRIBUTE</symbol>.
2760 <!-- *********************************************** -->
2762 <refentry id="spi-spi-getrelname">
2764 <refentrytitle>SPI_getrelname</refentrytitle>
2768 <refname>SPI_getrelname</refname>
2769 <refpurpose>return the name of the specified relation</refpurpose>
2772 <indexterm><primary>SPI_getrelname</primary></indexterm>
2776 char * SPI_getrelname(Relation <parameter>rel</parameter>)
2781 <title>Description</title>
2784 <function>SPI_getrelname</function> returns a copy of the name of the
2785 specified relation. (You can use <function>pfree</function> to
2786 release the copy of the name when you don't need it anymore.)
2791 <title>Arguments</title>
2795 <term><literal>Relation <parameter>rel</parameter></literal></term>
2806 <title>Return Value</title>
2809 The name of the specified relation.
2814 <refentry id="spi-spi-getnspname">
2816 <refentrytitle>SPI_getnspname</refentrytitle>
2820 <refname>SPI_getnspname</refname>
2821 <refpurpose>return the namespace of the specified relation</refpurpose>
2824 <indexterm><primary>SPI_getnspname</primary></indexterm>
2828 char * SPI_getnspname(Relation <parameter>rel</parameter>)
2833 <title>Description</title>
2836 <function>SPI_getnspname</function> returns a copy of the name of
2837 the namespace that the specified <structname>Relation</structname>
2838 belongs to. This is equivalent to the relation's schema. You should
2839 <function>pfree</function> the return value of this function when
2840 you are finished with it.
2845 <title>Arguments</title>
2849 <term><literal>Relation <parameter>rel</parameter></literal></term>
2860 <title>Return Value</title>
2863 The name of the specified relation's namespace.
2870 <sect1 id="spi-memory">
2871 <title>Memory Management</title>
2874 <productname>PostgreSQL</productname> allocates memory within
2875 <firstterm>memory contexts</firstterm><indexterm><primary>memory
2876 context</primary><secondary>in SPI</secondary></indexterm>, which provide a convenient method of
2877 managing allocations made in many different places that need to
2878 live for differing amounts of time. Destroying a context releases
2879 all the memory that was allocated in it. Thus, it is not necessary
2880 to keep track of individual objects to avoid memory leaks; instead
2881 only a relatively small number of contexts have to be managed.
2882 <function>palloc</function> and related functions allocate memory
2883 from the <quote>current</> context.
2887 <function>SPI_connect</function> creates a new memory context and
2888 makes it current. <function>SPI_finish</function> restores the
2889 previous current memory context and destroys the context created by
2890 <function>SPI_connect</function>. These actions ensure that
2891 transient memory allocations made inside your procedure are
2892 reclaimed at procedure exit, avoiding memory leakage.
2896 However, if your procedure needs to return an object in allocated
2897 memory (such as a value of a pass-by-reference data type), you
2898 cannot allocate that memory using <function>palloc</function>, at
2899 least not while you are connected to SPI. If you try, the object
2900 will be deallocated by <function>SPI_finish</function>, and your
2901 procedure will not work reliably. To solve this problem, use
2902 <function>SPI_palloc</function> to allocate memory for your return
2903 object. <function>SPI_palloc</function> allocates memory in the
2904 <quote>upper executor context</quote>, that is, the memory context
2905 that was current when <function>SPI_connect</function> was called,
2906 which is precisely the right context for a value returned from your
2911 If <function>SPI_palloc</function> is called while the procedure is
2912 not connected to SPI, then it acts the same as a normal
2913 <function>palloc</function>. Before a procedure connects to the
2914 SPI manager, the current memory context is the upper executor
2915 context, so all allocations made by the procedure via
2916 <function>palloc</function> or by SPI utility functions are made in
2921 When <function>SPI_connect</function> is called, the private
2922 context of the procedure, which is created by
2923 <function>SPI_connect</function>, is made the current context. All
2924 allocations made by <function>palloc</function>,
2925 <function>repalloc</function>, or SPI utility functions (except for
2926 <function>SPI_copytuple</function>,
2927 <function>SPI_returntuple</function>,
2928 <function>SPI_modifytuple</function>, and
2929 <function>SPI_palloc</function>) are made in this context. When a
2930 procedure disconnects from the SPI manager (via
2931 <function>SPI_finish</function>) the current context is restored to
2932 the upper executor context, and all allocations made in the
2933 procedure memory context are freed and cannot be used any more.
2937 All functions described in this section can be used by both
2938 connected and unconnected procedures. In an unconnected procedure,
2939 they act the same as the underlying ordinary server functions
2940 (<function>palloc</>, etc.).
2943 <!-- *********************************************** -->
2945 <refentry id="spi-spi-palloc">
2947 <refentrytitle>SPI_palloc</refentrytitle>
2951 <refname>SPI_palloc</refname>
2952 <refpurpose>allocate memory in the upper executor context</refpurpose>
2955 <indexterm><primary>SPI_palloc</primary></indexterm>
2959 void * SPI_palloc(Size <parameter>size</parameter>)
2964 <title>Description</title>
2967 <function>SPI_palloc</function> allocates memory in the upper
2973 <title>Arguments</title>
2977 <term><literal>Size <parameter>size</parameter></literal></term>
2980 size in bytes of storage to allocate
2988 <title>Return Value</title>
2991 pointer to new storage space of the specified size
2996 <!-- *********************************************** -->
2998 <refentry id="spi-realloc">
3000 <refentrytitle>SPI_repalloc</refentrytitle>
3004 <refname>SPI_repalloc</refname>
3005 <refpurpose>reallocate memory in the upper executor context</refpurpose>
3008 <indexterm><primary>SPI_repalloc</primary></indexterm>
3012 void * SPI_repalloc(void * <parameter>pointer</parameter>, Size <parameter>size</parameter>)
3017 <title>Description</title>
3020 <function>SPI_repalloc</function> changes the size of a memory
3021 segment previously allocated using <function>SPI_palloc</function>.
3025 This function is no longer different from plain
3026 <function>repalloc</function>. It's kept just for backward
3027 compatibility of existing code.
3032 <title>Arguments</title>
3036 <term><literal>void * <parameter>pointer</parameter></literal></term>
3039 pointer to existing storage to change
3045 <term><literal>Size <parameter>size</parameter></literal></term>
3048 size in bytes of storage to allocate
3056 <title>Return Value</title>
3059 pointer to new storage space of specified size with the contents
3060 copied from the existing area
3065 <!-- *********************************************** -->
3067 <refentry id="spi-spi-pfree">
3069 <refentrytitle>SPI_pfree</refentrytitle>
3073 <refname>SPI_pfree</refname>
3074 <refpurpose>free memory in the upper executor context</refpurpose>
3077 <indexterm><primary>SPI_pfree</primary></indexterm>
3081 void SPI_pfree(void * <parameter>pointer</parameter>)
3086 <title>Description</title>
3089 <function>SPI_pfree</function> frees memory previously allocated
3090 using <function>SPI_palloc</function> or
3091 <function>SPI_repalloc</function>.
3095 This function is no longer different from plain
3096 <function>pfree</function>. It's kept just for backward
3097 compatibility of existing code.
3102 <title>Arguments</title>
3106 <term><literal>void * <parameter>pointer</parameter></literal></term>
3109 pointer to existing storage to free
3117 <!-- *********************************************** -->
3119 <refentry id="spi-spi-copytuple">
3121 <refentrytitle>SPI_copytuple</refentrytitle>
3125 <refname>SPI_copytuple</refname>
3126 <refpurpose>make a copy of a row in the upper executor context</refpurpose>
3129 <indexterm><primary>SPI_copytuple</primary></indexterm>
3133 HeapTuple SPI_copytuple(HeapTuple <parameter>row</parameter>)
3138 <title>Description</title>
3141 <function>SPI_copytuple</function> makes a copy of a row in the
3142 upper executor context. This is normally used to return a modified
3143 row from a trigger. In a function declared to return a composite
3144 type, use <function>SPI_returntuple</function> instead.
3149 <title>Arguments</title>
3153 <term><literal>HeapTuple <parameter>row</parameter></literal></term>
3164 <title>Return Value</title>
3167 the copied row; <symbol>NULL</symbol> only if
3168 <parameter>tuple</parameter> is <symbol>NULL</symbol>
3173 <!-- *********************************************** -->
3175 <refentry id="spi-spi-returntuple">
3177 <refentrytitle>SPI_returntuple</refentrytitle>
3181 <refname>SPI_returntuple</refname>
3182 <refpurpose>prepare to return a tuple as a Datum</refpurpose>
3185 <indexterm><primary>SPI_returntuple</primary></indexterm>
3189 HeapTupleHeader SPI_returntuple(HeapTuple <parameter>row</parameter>, TupleDesc <parameter>rowdesc</parameter>)
3194 <title>Description</title>
3197 <function>SPI_returntuple</function> makes a copy of a row in
3198 the upper executor context, returning it in the form of a row type <type>Datum</type>.
3199 The returned pointer need only be converted to <type>Datum</type> via <function>PointerGetDatum</function>
3204 Note that this should be used for functions that are declared to return
3205 composite types. It is not used for triggers; use
3206 <function>SPI_copytuple</> for returning a modified row in a trigger.
3211 <title>Arguments</title>
3215 <term><literal>HeapTuple <parameter>row</parameter></literal></term>
3224 <term><literal>TupleDesc <parameter>rowdesc</parameter></literal></term>
3227 descriptor for row (pass the same descriptor each time for most
3236 <title>Return Value</title>
3239 <type>HeapTupleHeader</type> pointing to copied row;
3240 <symbol>NULL</symbol> only if
3241 <parameter>row</parameter> or <parameter>rowdesc</parameter> is
3242 <symbol>NULL</symbol>
3247 <!-- *********************************************** -->
3249 <refentry id="spi-spi-modifytuple">
3251 <refentrytitle>SPI_modifytuple</refentrytitle>
3255 <refname>SPI_modifytuple</refname>
3256 <refpurpose>create a row by replacing selected fields of a given row</refpurpose>
3259 <indexterm><primary>SPI_modifytuple</primary></indexterm>
3263 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>)
3268 <title>Description</title>
3271 <function>SPI_modifytuple</function> creates a new row by
3272 substituting new values for selected columns, copying the original
3273 row's columns at other positions. The input row is not modified.
3278 <title>Arguments</title>
3282 <term><literal>Relation <parameter>rel</parameter></literal></term>
3285 Used only as the source of the row descriptor for the row.
3286 (Passing a relation rather than a row descriptor is a
3293 <term><literal>HeapTuple <parameter>row</parameter></literal></term>
3302 <term><literal>int <parameter>ncols</parameter></literal></term>
3305 number of column numbers in the array
3306 <parameter>colnum</parameter>
3312 <term><literal>int * <parameter>colnum</parameter></literal></term>
3315 array of the numbers of the columns that are to be changed
3316 (column numbers start at 1)
3322 <term><literal>Datum * <parameter>values</parameter></literal></term>
3325 new values for the specified columns
3331 <term><literal>const char * <parameter>Nulls</parameter></literal></term>
3334 which new values are null, if any (see
3335 <function>SPI_execute_plan</function> for the format)
3343 <title>Return Value</title>
3346 new row with modifications, allocated in the upper executor
3347 context; <symbol>NULL</symbol> only if <parameter>row</parameter>
3348 is <symbol>NULL</symbol>
3352 On error, <varname>SPI_result</varname> is set as follows:
3355 <term><symbol>SPI_ERROR_ARGUMENT</symbol></term>
3358 if <parameter>rel</> is <symbol>NULL</>, or if
3359 <parameter>row</> is <symbol>NULL</>, or if <parameter>ncols</>
3360 is less than or equal to 0, or if <parameter>colnum</> is
3361 <symbol>NULL</>, or if <parameter>values</> is <symbol>NULL</>.
3367 <term><symbol>SPI_ERROR_NOATTRIBUTE</symbol></term>
3370 if <parameter>colnum</> contains an invalid column number (less
3371 than or equal to 0 or greater than the number of column in
3381 <!-- *********************************************** -->
3383 <refentry id="spi-spi-freetuple">
3385 <refentrytitle>SPI_freetuple</refentrytitle>
3389 <refname>SPI_freetuple</refname>
3390 <refpurpose>free a row allocated in the upper executor context</refpurpose>
3393 <indexterm><primary>SPI_freetuple</primary></indexterm>
3397 void SPI_freetuple(HeapTuple <parameter>row</parameter>)
3402 <title>Description</title>
3405 <function>SPI_freetuple</function> frees a row previously allocated
3406 in the upper executor context.
3410 This function is no longer different from plain
3411 <function>heap_freetuple</function>. It's kept just for backward
3412 compatibility of existing code.
3417 <title>Arguments</title>
3421 <term><literal>HeapTuple <parameter>row</parameter></literal></term>
3432 <!-- *********************************************** -->
3434 <refentry id="spi-spi-freetupletable">
3436 <refentrytitle>SPI_freetuptable</refentrytitle>
3440 <refname>SPI_freetuptable</refname>
3441 <refpurpose>free a row set created by <function>SPI_execute</> or a similar
3442 function</refpurpose>
3445 <indexterm><primary>SPI_freetuptable</primary></indexterm>
3449 void SPI_freetuptable(SPITupleTable * <parameter>tuptable</parameter>)
3454 <title>Description</title>
3457 <function>SPI_freetuptable</function> frees a row set created by a
3458 prior SPI command execution function, such as
3459 <function>SPI_execute</>. Therefore, this function is usually called
3460 with the global variable <varname>SPI_tupletable</varname> as
3465 This function is useful if a SPI procedure needs to execute
3466 multiple commands and does not want to keep the results of earlier
3467 commands around until it ends. Note that any unfreed row sets will
3468 be freed anyway at <function>SPI_finish</>.
3473 <title>Arguments</title>
3477 <term><literal>SPITupleTable * <parameter>tuptable</parameter></literal></term>
3480 pointer to row set to free
3488 <!-- *********************************************** -->
3490 <refentry id="spi-spi-freeplan">
3492 <refentrytitle>SPI_freeplan</refentrytitle>
3496 <refname>SPI_freeplan</refname>
3497 <refpurpose>free a previously saved plan</refpurpose>
3500 <indexterm><primary>SPI_freeplan</primary></indexterm>
3504 int SPI_freeplan(SPIPlanPtr <parameter>plan</parameter>)
3509 <title>Description</title>
3512 <function>SPI_freeplan</function> releases a command execution plan
3513 previously returned by <function>SPI_prepare</function> or saved by
3514 <function>SPI_saveplan</function>.
3519 <title>Arguments</title>
3523 <term><literal>SPIPlanPtr <parameter>plan</parameter></literal></term>
3526 pointer to plan to free
3534 <title>Return Value</title>
3537 <symbol>SPI_ERROR_ARGUMENT</symbol> if <parameter>plan</parameter>
3538 is <symbol>NULL</symbol> or invalid
3545 <sect1 id="spi-visibility">
3546 <title>Visibility of Data Changes</title>
3549 The following rules govern the visibility of data changes in
3550 functions that use SPI (or any other C function):
3555 During the execution of an SQL command, any data changes made by
3556 the command are invisible to the command itself. For
3559 INSERT INTO a SELECT * FROM a;
3561 the inserted rows are invisible to the <command>SELECT</command>
3568 Changes made by a command C are visible to all commands that are
3569 started after C, no matter whether they are started inside C
3570 (during the execution of C) or after C is done.
3576 Commands executed via SPI inside a function called by an SQL command
3577 (either an ordinary function or a trigger) follow one or the
3578 other of the above rules depending on the read/write flag passed
3579 to SPI. Commands executed in read-only mode follow the first
3580 rule: they cannot see changes of the calling command. Commands executed
3581 in read-write mode follow the second rule: they can see all changes made
3588 All standard procedural languages set the SPI read-write mode
3589 depending on the volatility attribute of the function. Commands of
3590 <literal>STABLE</> and <literal>IMMUTABLE</> functions are done in
3591 read-only mode, while commands of <literal>VOLATILE</> functions are
3592 done in read-write mode. While authors of C functions are able to
3593 violate this convention, it's unlikely to be a good idea to do so.
3600 The next section contains an example that illustrates the
3601 application of these rules.
3605 <sect1 id="spi-examples">
3606 <title>Examples</title>
3609 This section contains a very simple example of SPI usage. The
3610 procedure <function>execq</function> takes an SQL command as its
3611 first argument and a row count as its second, executes the command
3612 using <function>SPI_exec</function> and returns the number of rows
3613 that were processed by the command. You can find more complex
3614 examples for SPI in the source tree in
3615 <filename>src/test/regress/regress.c</filename> and in
3616 <filename>contrib/spi</filename>.
3620 #include "executor/spi.h"
3622 #ifdef PG_MODULE_MAGIC
3626 int execq(text *sql, int cnt);
3629 execq(text *sql, int cnt)
3635 /* Convert given text object to a C string */
3636 command = text_to_cstring(sql);
3640 ret = SPI_exec(command, cnt);
3642 proc = SPI_processed;
3644 * If some rows were fetched, print them via elog(INFO).
3646 if (ret > 0 && SPI_tuptable != NULL)
3648 TupleDesc tupdesc = SPI_tuptable->tupdesc;
3649 SPITupleTable *tuptable = SPI_tuptable;
3653 for (j = 0; j < proc; j++)
3655 HeapTuple tuple = tuptable->vals[j];
3657 for (i = 1, buf[0] = 0; i <= tupdesc->natts; i++)
3658 snprintf(buf + strlen (buf), sizeof(buf) - strlen(buf), " %s%s",
3659 SPI_getvalue(tuple, tupdesc, i),
3660 (i == tupdesc->natts) ? " " : " |");
3661 elog(INFO, "EXECQ: %s", buf);
3673 (This function uses call convention version 0, to make the example
3674 easier to understand. In real applications you should use the new
3675 version 1 interface.)
3679 This is how you declare the function after having compiled it into
3680 a shared library (details are in <xref linkend="dfunc">.):
3683 CREATE FUNCTION execq(text, integer) RETURNS integer
3684 AS '<replaceable>filename</replaceable>'
3690 Here is a sample session:
3693 => SELECT execq('CREATE TABLE a (x integer)', 0);
3699 => INSERT INTO a VALUES (execq('INSERT INTO a VALUES (0)', 0));
3701 => SELECT execq('SELECT * FROM a', 0);
3702 INFO: EXECQ: 0 -- inserted by execq
3703 INFO: EXECQ: 1 -- returned by execq and inserted by upper INSERT
3710 => SELECT execq('INSERT INTO a SELECT x + 2 FROM a', 1);
3716 => SELECT execq('SELECT * FROM a', 10);
3719 INFO: EXECQ: 2 -- 0 + 2, only one row inserted - as specified
3723 3 -- 10 is the max value only, 3 is the real number of rows
3726 => DELETE FROM a;
3728 => INSERT INTO a VALUES (execq('SELECT * FROM a', 0) + 1);
3730 => SELECT * FROM a;
3733 1 -- no rows in a (0) + 1
3736 => INSERT INTO a VALUES (execq('SELECT * FROM a', 0) + 1);
3739 => SELECT * FROM a;
3743 2 -- there was one row in a + 1
3746 -- This demonstrates the data changes visibility rule:
3748 => INSERT INTO a SELECT execq('SELECT * FROM a', 0) * x FROM a;
3755 => SELECT * FROM a;
3760 2 -- 2 rows * 1 (x in first row)
3761 6 -- 3 rows (2 + 1 just inserted) * 2 (x in second row)
3763 rows visible to execq() in different invocations