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>)
532 <term><symbol>SPI_OK_REWRITTEN
</symbol></term>
535 if the command was rewritten into another kind of command (e.g.,
536 <command>UPDATE
</command> became an
<command>INSERT
</command>) by a
<link linkend=
"rules">rule
</link>.
544 On error, one of the following negative values is returned:
548 <term><symbol>SPI_ERROR_ARGUMENT
</symbol></term>
551 if
<parameter>command
</parameter> is
<symbol>NULL
</symbol> or
552 <parameter>count
</parameter> is less than
0
558 <term><symbol>SPI_ERROR_COPY
</symbol></term>
561 if
<command>COPY TO stdout<
/> or
<command>COPY FROM stdin<
/>
568 <term><symbol>SPI_ERROR_TRANSACTION
</symbol></term>
571 if a transaction manipulation command was attempted
574 <command>ROLLBACK<
/>,
575 <command>SAVEPOINT<
/>,
576 <command>PREPARE TRANSACTION<
/>,
577 <command>COMMIT PREPARED<
/>,
578 <command>ROLLBACK PREPARED<
/>,
579 or any variant thereof)
585 <term><symbol>SPI_ERROR_OPUNKNOWN
</symbol></term>
588 if the command type is unknown (shouldn't happen)
594 <term><symbol>SPI_ERROR_UNCONNECTED
</symbol></term>
597 if called from an unconnected procedure
609 The functions
<function>SPI_execute
</function>,
610 <function>SPI_exec
</function>,
611 <function>SPI_execute_plan
</function>, and
612 <function>SPI_execp
</function> change both
613 <varname>SPI_processed
</varname> and
614 <varname>SPI_tuptable
</varname> (just the pointer, not the contents
615 of the structure). Save these two global variables into local
616 procedure variables if you need to access the result table of
617 <function>SPI_execute
</function> or a related function
623 <!-- *********************************************** -->
625 <refentry id=
"spi-spi-exec">
627 <refentrytitle>SPI_exec
</refentrytitle>
631 <refname>SPI_exec
</refname>
632 <refpurpose>execute a read/write command
</refpurpose>
635 <indexterm><primary>SPI_exec
</primary></indexterm>
639 int SPI_exec(const char *
<parameter>command
</parameter>, long
<parameter>count
</parameter>)
644 <title>Description
</title>
647 <function>SPI_exec
</function> is the same as
648 <function>SPI_execute
</function>, with the latter's
649 <parameter>read_only
</parameter> parameter always taken as
655 <title>Arguments
</title>
659 <term><literal>const char *
<parameter>command
</parameter></literal></term>
662 string containing command to execute
668 <term><literal>long
<parameter>count
</parameter></literal></term>
671 maximum number of rows to process or return
679 <title>Return Value
</title>
682 See
<function>SPI_execute
</function>.
687 <!-- *********************************************** -->
689 <refentry id=
"spi-spi-execute-with-args">
691 <refentrytitle>SPI_execute_with_args
</refentrytitle>
695 <refname>SPI_execute_with_args
</refname>
696 <refpurpose>execute a command with out-of-line parameters
</refpurpose>
699 <indexterm><primary>SPI_execute_with_args
</primary></indexterm>
703 int SPI_execute_with_args(const char *
<parameter>command
</parameter>,
704 int
<parameter>nargs
</parameter>, Oid *
<parameter>argtypes
</parameter>,
705 Datum *
<parameter>values
</parameter>, const char *
<parameter>nulls
</parameter>,
706 bool
<parameter>read_only
</parameter>, long
<parameter>count
</parameter>)
711 <title>Description
</title>
714 <function>SPI_execute_with_args
</function> executes a command that might
715 include references to externally supplied parameters. The command text
716 refers to a parameter as
<literal>$
<replaceable>n<
/></literal>, and
717 the call specifies data types and values for each such symbol.
718 <parameter>read_only
</parameter> and
<parameter>count
</parameter> have
719 the same interpretation as in
<function>SPI_execute
</function>.
723 The main advantage of this routine compared to
724 <function>SPI_execute
</function> is that data values can be inserted
725 into the command without tedious quoting/escaping, and thus with much
726 less risk of SQL-injection attacks.
730 Similar results can be achieved with
<function>SPI_prepare<
/> followed by
731 <function>SPI_execute_plan
</function>; however, when using this function
732 the query plan is customized to the specific parameter values provided.
733 For one-time query execution, this function should be preferred.
734 If the same command is to be executed with many different parameters,
735 either method might be faster, depending on the cost of re-planning
736 versus the benefit of custom plans.
741 <title>Arguments
</title>
745 <term><literal>const char *
<parameter>command
</parameter></literal></term>
754 <term><literal>int
<parameter>nargs
</parameter></literal></term>
757 number of input parameters (
<literal>$
1<
/>,
<literal>$
2<
/>, etc.)
763 <term><literal>Oid *
<parameter>argtypes
</parameter></literal></term>
766 an array containing the
<acronym>OID
</acronym>s of
767 the data types of the parameters
773 <term><literal>Datum *
<parameter>values
</parameter></literal></term>
776 an array of actual parameter values
782 <term><literal>const char *
<parameter>nulls
</parameter></literal></term>
785 an array describing which parameters are null
789 If
<parameter>nulls
</parameter> is
<symbol>NULL
</symbol> then
790 <function>SPI_execute_with_args
</function> assumes that no parameters are
797 <term><literal>bool
<parameter>read_only
</parameter></literal></term>
800 <literal>true<
/> for read-only execution
806 <term><literal>long
<parameter>count
</parameter></literal></term>
809 maximum number of rows to process or return
817 <title>Return Value
</title>
820 The return value is the same as for
<function>SPI_execute
</function>.
824 <varname>SPI_processed
</varname> and
825 <varname>SPI_tuptable
</varname> are set as in
826 <function>SPI_execute
</function> if successful.
831 <!-- *********************************************** -->
833 <refentry id=
"spi-spi-prepare">
835 <refentrytitle>SPI_prepare
</refentrytitle>
839 <refname>SPI_prepare
</refname>
840 <refpurpose>prepare a plan for a command, without executing it yet
</refpurpose>
843 <indexterm><primary>SPI_prepare
</primary></indexterm>
847 SPIPlanPtr SPI_prepare(const char *
<parameter>command
</parameter>, int
<parameter>nargs
</parameter>, Oid *
<parameter>argtypes
</parameter>)
852 <title>Description
</title>
855 <function>SPI_prepare
</function> creates and returns an execution
856 plan for the specified command but doesn't execute the command.
857 This function should only be called from a connected procedure.
861 When the same or a similar command is to be executed repeatedly, it
862 might be advantageous to perform the planning only once.
863 <function>SPI_prepare
</function> converts a command string into an
864 execution plan that can be executed repeatedly using
865 <function>SPI_execute_plan
</function>.
869 A prepared command can be generalized by writing parameters
870 (
<literal>$
1<
/>,
<literal>$
2<
/>, etc.) in place of what would be
871 constants in a normal command. The actual values of the parameters
872 are then specified when
<function>SPI_execute_plan
</function> is called.
873 This allows the prepared command to be used over a wider range of
874 situations than would be possible without parameters.
878 The plan returned by
<function>SPI_prepare
</function> can be used
879 only in the current invocation of the procedure, since
880 <function>SPI_finish
</function> frees memory allocated for a plan.
881 But a plan can be saved for longer using the function
882 <function>SPI_saveplan
</function>.
887 <title>Arguments
</title>
891 <term><literal>const char *
<parameter>command
</parameter></literal></term>
900 <term><literal>int
<parameter>nargs
</parameter></literal></term>
903 number of input parameters (
<literal>$
1<
/>,
<literal>$
2<
/>, etc.)
909 <term><literal>Oid *
<parameter>argtypes
</parameter></literal></term>
912 pointer to an array containing the
<acronym>OID
</acronym>s of
913 the data types of the parameters
921 <title>Return Value
</title>
924 <function>SPI_prepare
</function> returns a non-null pointer to an
925 execution plan. On error,
<symbol>NULL
</symbol> will be returned,
926 and
<varname>SPI_result
</varname> will be set to one of the same
927 error codes used by
<function>SPI_execute
</function>, except that
928 it is set to
<symbol>SPI_ERROR_ARGUMENT
</symbol> if
929 <parameter>command
</parameter> is
<symbol>NULL
</symbol>, or if
930 <parameter>nargs<
/> is less than
0, or if
<parameter>nargs<
/> is
931 greater than
0 and
<parameter>argtypes<
/> is
<symbol>NULL
</symbol>.
939 <type>SPIPlanPtr<
/> is declared as a pointer to an opaque struct type in
940 <filename>spi.h<
/>. It is unwise to try to access its contents
941 directly, as that makes your code much more likely to break in
942 future revisions of
<productname>PostgreSQL
</productname>.
946 There is a disadvantage to using parameters: since the planner does
947 not know the values that will be supplied for the parameters, it
948 might make worse planning choices than it would make for a normal
949 command with all constants visible.
954 <!-- *********************************************** -->
956 <refentry id=
"spi-spi-prepare-cursor">
958 <refentrytitle>SPI_prepare_cursor
</refentrytitle>
962 <refname>SPI_prepare_cursor
</refname>
963 <refpurpose>prepare a plan for a command, without executing it yet
</refpurpose>
966 <indexterm><primary>SPI_prepare_cursor
</primary></indexterm>
970 SPIPlanPtr SPI_prepare_cursor(const char *
<parameter>command
</parameter>, int
<parameter>nargs
</parameter>, Oid *
<parameter>argtypes
</parameter>, int
<parameter>cursorOptions
</parameter>)
975 <title>Description
</title>
978 <function>SPI_prepare_cursor
</function> is identical to
979 <function>SPI_prepare
</function>, except that it also allows specification
980 of the planner's
<quote>cursor options<
/> parameter. This is a bitmask
981 having the values shown in
<filename>nodes/parsenodes.h
</filename>
982 for the
<structfield>options<
/> field of
<structname>DeclareCursorStmt<
/>.
983 <function>SPI_prepare
</function> always takes these options as zero.
988 <title>Arguments
</title>
992 <term><literal>const char *
<parameter>command
</parameter></literal></term>
1001 <term><literal>int
<parameter>nargs
</parameter></literal></term>
1004 number of input parameters (
<literal>$
1<
/>,
<literal>$
2<
/>, etc.)
1010 <term><literal>Oid *
<parameter>argtypes
</parameter></literal></term>
1013 pointer to an array containing the
<acronym>OID
</acronym>s of
1014 the data types of the parameters
1020 <term><literal>int
<parameter>cursorOptions
</parameter></literal></term>
1023 integer bitmask of cursor options; zero produces default behavior
1031 <title>Return Value
</title>
1034 <function>SPI_prepare_cursor
</function> has the same return conventions as
1035 <function>SPI_prepare
</function>.
1040 <title>Notes
</title>
1043 Useful bits to set in
<parameter>cursorOptions<
/> include
1044 <symbol>CURSOR_OPT_SCROLL
</symbol>,
1045 <symbol>CURSOR_OPT_NO_SCROLL
</symbol>, and
1046 <symbol>CURSOR_OPT_FAST_PLAN
</symbol>. Note in particular that
1047 <symbol>CURSOR_OPT_HOLD
</symbol> is ignored.
1052 <!-- *********************************************** -->
1054 <refentry id=
"spi-spi-getargcount">
1056 <refentrytitle>SPI_getargcount
</refentrytitle>
1060 <refname>SPI_getargcount
</refname>
1061 <refpurpose>return the number of arguments needed by a plan
1062 prepared by
<function>SPI_prepare
</function></refpurpose>
1065 <indexterm><primary>SPI_getargcount
</primary></indexterm>
1069 int SPI_getargcount(SPIPlanPtr
<parameter>plan
</parameter>)
1074 <title>Description
</title>
1077 <function>SPI_getargcount
</function> returns the number of arguments needed
1078 to execute a plan prepared by
<function>SPI_prepare
</function>.
1083 <title>Arguments
</title>
1087 <term><literal>SPIPlanPtr
<parameter>plan
</parameter></literal></term>
1090 execution plan (returned by
<function>SPI_prepare
</function>)
1098 <title>Return Value
</title>
1100 The count of expected arguments for the
<parameter>plan
</parameter>.
1101 If the
<parameter>plan
</parameter> is
<symbol>NULL
</symbol> or invalid,
1102 <varname>SPI_result
</varname> is set to
<symbol>SPI_ERROR_ARGUMENT
</symbol>
1103 and
<literal>-
1</literal> is returned.
1108 <!-- *********************************************** -->
1110 <refentry id=
"spi-spi-getargtypeid">
1112 <refentrytitle>SPI_getargtypeid
</refentrytitle>
1116 <refname>SPI_getargtypeid
</refname>
1117 <refpurpose>return the data type OID for an argument of
1118 a plan prepared by
<function>SPI_prepare
</function></refpurpose>
1121 <indexterm><primary>SPI_getargtypeid
</primary></indexterm>
1125 Oid SPI_getargtypeid(SPIPlanPtr
<parameter>plan
</parameter>, int
<parameter>argIndex
</parameter>)
1130 <title>Description
</title>
1133 <function>SPI_getargtypeid
</function> returns the OID representing the type
1134 id for the
<parameter>argIndex
</parameter>'th argument of a plan prepared by
1135 <function>SPI_prepare
</function>. First argument is at index zero.
1140 <title>Arguments
</title>
1144 <term><literal>SPIPlanPtr
<parameter>plan
</parameter></literal></term>
1147 execution plan (returned by
<function>SPI_prepare
</function>)
1153 <term><literal>int
<parameter>argIndex
</parameter></literal></term>
1156 zero based index of the argument
1164 <title>Return Value
</title>
1166 The type id of the argument at the given index.
1167 If the
<parameter>plan
</parameter> is
<symbol>NULL
</symbol> or invalid,
1168 or
<parameter>argIndex
</parameter> is less than
0 or
1169 not less than the number of arguments declared for the
1170 <parameter>plan
</parameter>,
1171 <varname>SPI_result
</varname> is set to
<symbol>SPI_ERROR_ARGUMENT
</symbol>
1172 and
<symbol>InvalidOid
</symbol> is returned.
1177 <!-- *********************************************** -->
1179 <refentry id=
"spi-spi-is-cursor-plan">
1181 <refentrytitle>SPI_is_cursor_plan
</refentrytitle>
1185 <refname>SPI_is_cursor_plan
</refname>
1186 <refpurpose>return
<symbol>true
</symbol> if a plan
1187 prepared by
<function>SPI_prepare
</function> can be used with
1188 <function>SPI_cursor_open
</function></refpurpose>
1191 <indexterm><primary>SPI_is_cursor_plan
</primary></indexterm>
1195 bool SPI_is_cursor_plan(SPIPlanPtr
<parameter>plan
</parameter>)
1200 <title>Description
</title>
1203 <function>SPI_is_cursor_plan
</function> returns
<symbol>true
</symbol>
1204 if a plan prepared by
<function>SPI_prepare
</function> can be passed
1205 as an argument to
<function>SPI_cursor_open
</function>, or
1206 <symbol>false
</symbol> if that is not the case. The criteria are that the
1207 <parameter>plan
</parameter> represents one single command and that this
1208 command returns tuples to the caller; for example,
<command>SELECT<
/>
1209 is allowed unless it contains an
<literal>INTO<
/> clause, and
1210 <command>UPDATE<
/> is allowed only if it contains a
<literal>RETURNING<
/>
1216 <title>Arguments
</title>
1220 <term><literal>SPIPlanPtr
<parameter>plan
</parameter></literal></term>
1223 execution plan (returned by
<function>SPI_prepare
</function>)
1231 <title>Return Value
</title>
1233 <symbol>true
</symbol> or
<symbol>false
</symbol> to indicate if the
1234 <parameter>plan
</parameter> can produce a cursor or not, with
1235 <varname>SPI_result
</varname> set to zero.
1236 If it is not possible to determine the answer (for example,
1237 if the
<parameter>plan
</parameter> is
<symbol>NULL
</symbol> or invalid,
1238 or if called when not connected to SPI), then
1239 <varname>SPI_result
</varname> is set to a suitable error code
1240 and
<symbol>false
</symbol> is returned.
1245 <!-- *********************************************** -->
1247 <refentry id=
"spi-spi-execute-plan">
1249 <refentrytitle>SPI_execute_plan
</refentrytitle>
1253 <refname>SPI_execute_plan
</refname>
1254 <refpurpose>execute a plan prepared by
<function>SPI_prepare
</function></refpurpose>
1257 <indexterm><primary>SPI_execute_plan
</primary></indexterm>
1261 int SPI_execute_plan(SPIPlanPtr
<parameter>plan
</parameter>, Datum *
<parameter>values
</parameter>, const char *
<parameter>nulls
</parameter>,
1262 bool
<parameter>read_only
</parameter>, long
<parameter>count
</parameter>)
1267 <title>Description
</title>
1270 <function>SPI_execute_plan
</function> executes a plan prepared by
1271 <function>SPI_prepare
</function>.
<parameter>read_only
</parameter> and
1272 <parameter>count
</parameter> have the same interpretation as in
1273 <function>SPI_execute
</function>.
1278 <title>Arguments
</title>
1282 <term><literal>SPIPlanPtr
<parameter>plan
</parameter></literal></term>
1285 execution plan (returned by
<function>SPI_prepare
</function>)
1291 <term><literal>Datum *
<parameter>values
</parameter></literal></term>
1294 An array of actual parameter values. Must have same length as the
1295 plan's number of arguments.
1301 <term><literal>const char *
<parameter>nulls
</parameter></literal></term>
1304 An array describing which parameters are null. Must have same length as
1305 the plan's number of arguments.
1306 <literal>n
</literal> indicates a null value (entry in
1307 <parameter>values<
/> will be ignored); a space indicates a
1308 nonnull value (entry in
<parameter>values<
/> is valid).
1312 If
<parameter>nulls
</parameter> is
<symbol>NULL
</symbol> then
1313 <function>SPI_execute_plan
</function> assumes that no parameters are
1320 <term><literal>bool
<parameter>read_only
</parameter></literal></term>
1323 <literal>true<
/> for read-only execution
1329 <term><literal>long
<parameter>count
</parameter></literal></term>
1332 maximum number of rows to process or return
1340 <title>Return Value
</title>
1343 The return value is the same as for
<function>SPI_execute
</function>,
1344 with the following additional possible error (negative) results:
1348 <term><symbol>SPI_ERROR_ARGUMENT
</symbol></term>
1351 if
<parameter>plan
</parameter> is
<symbol>NULL
</symbol> or invalid,
1352 or
<parameter>count
</parameter> is less than
0
1358 <term><symbol>SPI_ERROR_PARAM
</symbol></term>
1361 if
<parameter>values
</parameter> is
<symbol>NULL
</symbol> and
1362 <parameter>plan
</parameter> was prepared with some parameters
1370 <varname>SPI_processed
</varname> and
1371 <varname>SPI_tuptable
</varname> are set as in
1372 <function>SPI_execute
</function> if successful.
1377 <title>Notes
</title>
1380 If one of the objects (a table, function, etc.) referenced by the
1381 prepared plan is dropped during the session then the result of
1382 <function>SPI_execute_plan
</function> for this plan will be unpredictable.
1387 <!-- *********************************************** -->
1389 <refentry id=
"spi-spi-execp">
1391 <refentrytitle>SPI_execp
</refentrytitle>
1395 <refname>SPI_execp
</refname>
1396 <refpurpose>execute a plan in read/write mode
</refpurpose>
1399 <indexterm><primary>SPI_execp
</primary></indexterm>
1403 int SPI_execp(SPIPlanPtr
<parameter>plan
</parameter>, Datum *
<parameter>values
</parameter>, const char *
<parameter>nulls
</parameter>, long
<parameter>count
</parameter>)
1408 <title>Description
</title>
1411 <function>SPI_execp
</function> is the same as
1412 <function>SPI_execute_plan
</function>, with the latter's
1413 <parameter>read_only
</parameter> parameter always taken as
1419 <title>Arguments
</title>
1423 <term><literal>SPIPlanPtr
<parameter>plan
</parameter></literal></term>
1426 execution plan (returned by
<function>SPI_prepare
</function>)
1432 <term><literal>Datum *
<parameter>values
</parameter></literal></term>
1435 An array of actual parameter values. Must have same length as the
1436 plan's number of arguments.
1442 <term><literal>const char *
<parameter>nulls
</parameter></literal></term>
1445 An array describing which parameters are null. Must have same length as
1446 the plan's number of arguments.
1447 <literal>n
</literal> indicates a null value (entry in
1448 <parameter>values<
/> will be ignored); a space indicates a
1449 nonnull value (entry in
<parameter>values<
/> is valid).
1453 If
<parameter>nulls
</parameter> is
<symbol>NULL
</symbol> then
1454 <function>SPI_execp
</function> assumes that no parameters are
1461 <term><literal>long
<parameter>count
</parameter></literal></term>
1464 maximum number of rows to process or return
1472 <title>Return Value
</title>
1475 See
<function>SPI_execute_plan
</function>.
1479 <varname>SPI_processed
</varname> and
1480 <varname>SPI_tuptable
</varname> are set as in
1481 <function>SPI_execute
</function> if successful.
1486 <!-- *********************************************** -->
1488 <refentry id=
"spi-spi-cursor-open">
1490 <refentrytitle>SPI_cursor_open
</refentrytitle>
1494 <refname>SPI_cursor_open
</refname>
1495 <refpurpose>set up a cursor using a plan created with
<function>SPI_prepare
</function></refpurpose>
1498 <indexterm><primary>SPI_cursor_open
</primary></indexterm>
1502 Portal SPI_cursor_open(const char *
<parameter>name
</parameter>, SPIPlanPtr
<parameter>plan
</parameter>,
1503 Datum *
<parameter>values
</parameter>, const char *
<parameter>nulls
</parameter>,
1504 bool
<parameter>read_only
</parameter>)
1509 <title>Description
</title>
1512 <function>SPI_cursor_open
</function> sets up a cursor (internally,
1513 a portal) that will execute a plan prepared by
1514 <function>SPI_prepare
</function>. The parameters have the same
1515 meanings as the corresponding parameters to
1516 <function>SPI_execute_plan
</function>.
1520 Using a cursor instead of executing the plan directly has two
1521 benefits. First, the result rows can be retrieved a few at a time,
1522 avoiding memory overrun for queries that return many rows. Second,
1523 a portal can outlive the current procedure (it can, in fact, live
1524 to the end of the current transaction). Returning the portal name
1525 to the procedure's caller provides a way of returning a row set as
1530 The passed-in data will be copied into the cursor's portal, so it
1531 can be freed while the cursor still exists.
1536 <title>Arguments
</title>
1540 <term><literal>const char *
<parameter>name
</parameter></literal></term>
1543 name for portal, or
<symbol>NULL
</symbol> to let the system
1550 <term><literal>SPIPlanPtr
<parameter>plan
</parameter></literal></term>
1553 execution plan (returned by
<function>SPI_prepare
</function>)
1559 <term><literal>Datum *
<parameter>values
</parameter></literal></term>
1562 An array of actual parameter values. Must have same length as the
1563 plan's number of arguments.
1569 <term><literal>const char *
<parameter>nulls
</parameter></literal></term>
1572 An array describing which parameters are null. Must have same length as
1573 the plan's number of arguments.
1574 <literal>n
</literal> indicates a null value (entry in
1575 <parameter>values<
/> will be ignored); a space indicates a
1576 nonnull value (entry in
<parameter>values<
/> is valid).
1580 If
<parameter>nulls
</parameter> is
<symbol>NULL
</symbol> then
1581 <function>SPI_cursor_open
</function> assumes that no parameters are
1588 <term><literal>bool
<parameter>read_only
</parameter></literal></term>
1591 <literal>true<
/> for read-only execution
1599 <title>Return Value
</title>
1602 Pointer to portal containing the cursor. Note there is no error
1603 return convention; any error will be reported via
<function>elog<
/>.
1608 <!-- *********************************************** -->
1610 <refentry id=
"spi-spi-cursor-open-with-args">
1612 <refentrytitle>SPI_cursor_open_with_args
</refentrytitle>
1616 <refname>SPI_cursor_open_with_args
</refname>
1617 <refpurpose>set up a cursor using a query and parameters
</refpurpose>
1620 <indexterm><primary>SPI_cursor_open_with_args
</primary></indexterm>
1624 Portal SPI_cursor_open_with_args(const char *
<parameter>name
</parameter>,
1625 const char *
<parameter>command
</parameter>,
1626 int
<parameter>nargs
</parameter>, Oid *
<parameter>argtypes
</parameter>,
1627 Datum *
<parameter>values
</parameter>, const char *
<parameter>nulls
</parameter>,
1628 bool
<parameter>read_only
</parameter>, int
<parameter>cursorOptions
</parameter>)
1633 <title>Description
</title>
1636 <function>SPI_cursor_open_with_args
</function> sets up a cursor
1637 (internally, a portal) that will execute the specified query.
1638 Most of the parameters have the same meanings as the corresponding
1639 parameters to
<function>SPI_prepare_cursor
</function>
1640 and
<function>SPI_cursor_open
</function>.
1644 For one-time query execution, this function should be preferred
1645 over
<function>SPI_prepare_cursor
</function> followed by
1646 <function>SPI_cursor_open
</function>.
1647 If the same command is to be executed with many different parameters,
1648 either method might be faster, depending on the cost of re-planning
1649 versus the benefit of custom plans.
1653 The passed-in data will be copied into the cursor's portal, so it
1654 can be freed while the cursor still exists.
1659 <title>Arguments
</title>
1663 <term><literal>const char *
<parameter>name
</parameter></literal></term>
1666 name for portal, or
<symbol>NULL
</symbol> to let the system
1673 <term><literal>const char *
<parameter>command
</parameter></literal></term>
1682 <term><literal>int
<parameter>nargs
</parameter></literal></term>
1685 number of input parameters (
<literal>$
1<
/>,
<literal>$
2<
/>, etc.)
1691 <term><literal>Oid *
<parameter>argtypes
</parameter></literal></term>
1694 an array containing the
<acronym>OID
</acronym>s of
1695 the data types of the parameters
1701 <term><literal>Datum *
<parameter>values
</parameter></literal></term>
1704 an array of actual parameter values
1710 <term><literal>const char *
<parameter>nulls
</parameter></literal></term>
1713 an array describing which parameters are null
1717 If
<parameter>nulls
</parameter> is
<symbol>NULL
</symbol> then
1718 <function>SPI_cursor_open_with_args
</function> assumes that no
1719 parameters are null.
1725 <term><literal>bool
<parameter>read_only
</parameter></literal></term>
1728 <literal>true<
/> for read-only execution
1734 <term><literal>int
<parameter>cursorOptions
</parameter></literal></term>
1737 integer bitmask of cursor options; zero produces default behavior
1745 <title>Return Value
</title>
1748 Pointer to portal containing the cursor. Note there is no error
1749 return convention; any error will be reported via
<function>elog<
/>.
1754 <!-- *********************************************** -->
1756 <refentry id=
"spi-spi-cursor-find">
1758 <refentrytitle>SPI_cursor_find
</refentrytitle>
1762 <refname>SPI_cursor_find
</refname>
1763 <refpurpose>find an existing cursor by name
</refpurpose>
1766 <indexterm><primary>SPI_cursor_find
</primary></indexterm>
1770 Portal SPI_cursor_find(const char *
<parameter>name
</parameter>)
1775 <title>Description
</title>
1778 <function>SPI_cursor_find
</function> finds an existing portal by
1779 name. This is primarily useful to resolve a cursor name returned
1780 as text by some other function.
1785 <title>Arguments
</title>
1789 <term><literal>const char *
<parameter>name
</parameter></literal></term>
1800 <title>Return Value
</title>
1803 pointer to the portal with the specified name, or
1804 <symbol>NULL
</symbol> if none was found
1809 <!-- *********************************************** -->
1811 <refentry id=
"spi-spi-cursor-fetch">
1813 <refentrytitle>SPI_cursor_fetch
</refentrytitle>
1817 <refname>SPI_cursor_fetch
</refname>
1818 <refpurpose>fetch some rows from a cursor
</refpurpose>
1821 <indexterm><primary>SPI_cursor_fetch
</primary></indexterm>
1825 void SPI_cursor_fetch(Portal
<parameter>portal
</parameter>, bool
<parameter>forward
</parameter>, long
<parameter>count
</parameter>)
1830 <title>Description
</title>
1833 <function>SPI_cursor_fetch
</function> fetches some rows from a
1834 cursor. This is equivalent to a subset of the SQL command
1835 <command>FETCH<
/> (see
<function>SPI_scroll_cursor_fetch
</function>
1836 for more functionality).
1841 <title>Arguments
</title>
1845 <term><literal>Portal
<parameter>portal
</parameter></literal></term>
1848 portal containing the cursor
1854 <term><literal>bool
<parameter>forward
</parameter></literal></term>
1857 true for fetch forward, false for fetch backward
1863 <term><literal>long
<parameter>count
</parameter></literal></term>
1866 maximum number of rows to fetch
1874 <title>Return Value
</title>
1877 <varname>SPI_processed
</varname> and
1878 <varname>SPI_tuptable
</varname> are set as in
1879 <function>SPI_execute
</function> if successful.
1884 <title>Notes
</title>
1887 Fetching backward may fail if the cursor's plan was not created
1888 with the
<symbol>CURSOR_OPT_SCROLL
</symbol> option.
1893 <!-- *********************************************** -->
1895 <refentry id=
"spi-spi-cursor-move">
1897 <refentrytitle>SPI_cursor_move
</refentrytitle>
1901 <refname>SPI_cursor_move
</refname>
1902 <refpurpose>move a cursor
</refpurpose>
1905 <indexterm><primary>SPI_cursor_move
</primary></indexterm>
1909 void SPI_cursor_move(Portal
<parameter>portal
</parameter>, bool
<parameter>forward
</parameter>, long
<parameter>count
</parameter>)
1914 <title>Description
</title>
1917 <function>SPI_cursor_move
</function> skips over some number of rows
1918 in a cursor. This is equivalent to a subset of the SQL command
1919 <command>MOVE<
/> (see
<function>SPI_scroll_cursor_move
</function>
1920 for more functionality).
1925 <title>Arguments
</title>
1929 <term><literal>Portal
<parameter>portal
</parameter></literal></term>
1932 portal containing the cursor
1938 <term><literal>bool
<parameter>forward
</parameter></literal></term>
1941 true for move forward, false for move backward
1947 <term><literal>long
<parameter>count
</parameter></literal></term>
1950 maximum number of rows to move
1958 <title>Notes
</title>
1961 Moving backward may fail if the cursor's plan was not created
1962 with the
<symbol>CURSOR_OPT_SCROLL
</symbol> option.
1967 <!-- *********************************************** -->
1969 <refentry id=
"spi-spi-scroll-cursor-fetch">
1971 <refentrytitle>SPI_scroll_cursor_fetch
</refentrytitle>
1975 <refname>SPI_scroll_cursor_fetch
</refname>
1976 <refpurpose>fetch some rows from a cursor
</refpurpose>
1979 <indexterm><primary>SPI_scroll_cursor_fetch
</primary></indexterm>
1983 void SPI_scroll_cursor_fetch(Portal
<parameter>portal
</parameter>, FetchDirection
<parameter>direction
</parameter>, long
<parameter>count
</parameter>)
1988 <title>Description
</title>
1991 <function>SPI_scroll_cursor_fetch
</function> fetches some rows from a
1992 cursor. This is equivalent to the SQL command
<command>FETCH<
/>.
1997 <title>Arguments
</title>
2001 <term><literal>Portal
<parameter>portal
</parameter></literal></term>
2004 portal containing the cursor
2010 <term><literal>FetchDirection
<parameter>direction
</parameter></literal></term>
2013 one of
<symbol>FETCH_FORWARD
</symbol>,
2014 <symbol>FETCH_BACKWARD
</symbol>,
2015 <symbol>FETCH_ABSOLUTE
</symbol> or
2016 <symbol>FETCH_RELATIVE
</symbol>
2022 <term><literal>long
<parameter>count
</parameter></literal></term>
2025 number of rows to fetch for
2026 <symbol>FETCH_FORWARD
</symbol> or
2027 <symbol>FETCH_BACKWARD
</symbol>; absolute row number to fetch for
2028 <symbol>FETCH_ABSOLUTE
</symbol>; or relative row number to fetch for
2029 <symbol>FETCH_RELATIVE
</symbol>
2037 <title>Return Value
</title>
2040 <varname>SPI_processed
</varname> and
2041 <varname>SPI_tuptable
</varname> are set as in
2042 <function>SPI_execute
</function> if successful.
2047 <title>Notes
</title>
2050 See the SQL
<xref linkend=
"sql-fetch" endterm=
"sql-fetch-title"> command
2051 for details of the interpretation of the
2052 <parameter>direction
</parameter> and
2053 <parameter>count
</parameter> parameters.
2057 Direction values other than
<symbol>FETCH_FORWARD
</symbol>
2058 may fail if the cursor's plan was not created
2059 with the
<symbol>CURSOR_OPT_SCROLL
</symbol> option.
2064 <!-- *********************************************** -->
2066 <refentry id=
"spi-spi-scroll-cursor-move">
2068 <refentrytitle>SPI_scroll_cursor_move
</refentrytitle>
2072 <refname>SPI_scroll_cursor_move
</refname>
2073 <refpurpose>move a cursor
</refpurpose>
2076 <indexterm><primary>SPI_scroll_cursor_move
</primary></indexterm>
2080 void SPI_scroll_cursor_move(Portal
<parameter>portal
</parameter>, FetchDirection
<parameter>direction
</parameter>, long
<parameter>count
</parameter>)
2085 <title>Description
</title>
2088 <function>SPI_scroll_cursor_move
</function> skips over some number of rows
2089 in a cursor. This is equivalent to the SQL command
2095 <title>Arguments
</title>
2099 <term><literal>Portal
<parameter>portal
</parameter></literal></term>
2102 portal containing the cursor
2108 <term><literal>FetchDirection
<parameter>direction
</parameter></literal></term>
2111 one of
<symbol>FETCH_FORWARD
</symbol>,
2112 <symbol>FETCH_BACKWARD
</symbol>,
2113 <symbol>FETCH_ABSOLUTE
</symbol> or
2114 <symbol>FETCH_RELATIVE
</symbol>
2120 <term><literal>long
<parameter>count
</parameter></literal></term>
2123 number of rows to move for
2124 <symbol>FETCH_FORWARD
</symbol> or
2125 <symbol>FETCH_BACKWARD
</symbol>; absolute row number to move to for
2126 <symbol>FETCH_ABSOLUTE
</symbol>; or relative row number to move to for
2127 <symbol>FETCH_RELATIVE
</symbol>
2135 <title>Return Value
</title>
2138 <varname>SPI_processed
</varname> is set as in
2139 <function>SPI_execute
</function> if successful.
2140 <varname>SPI_tuptable
</varname> is set to
<symbol>NULL<
/>, since
2141 no rows are returned by this function.
2146 <title>Notes
</title>
2149 See the SQL
<xref linkend=
"sql-fetch" endterm=
"sql-fetch-title"> command
2150 for details of the interpretation of the
2151 <parameter>direction
</parameter> and
2152 <parameter>count
</parameter> parameters.
2156 Direction values other than
<symbol>FETCH_FORWARD
</symbol>
2157 may fail if the cursor's plan was not created
2158 with the
<symbol>CURSOR_OPT_SCROLL
</symbol> option.
2163 <!-- *********************************************** -->
2165 <refentry id=
"spi-spi-cursor-close">
2167 <refentrytitle>SPI_cursor_close
</refentrytitle>
2171 <refname>SPI_cursor_close
</refname>
2172 <refpurpose>close a cursor
</refpurpose>
2175 <indexterm><primary>SPI_cursor_close
</primary></indexterm>
2179 void SPI_cursor_close(Portal
<parameter>portal
</parameter>)
2184 <title>Description
</title>
2187 <function>SPI_cursor_close
</function> closes a previously created
2188 cursor and releases its portal storage.
2192 All open cursors are closed automatically at the end of a
2193 transaction.
<function>SPI_cursor_close
</function> need only be
2194 invoked if it is desirable to release resources sooner.
2199 <title>Arguments
</title>
2203 <term><literal>Portal
<parameter>portal
</parameter></literal></term>
2206 portal containing the cursor
2214 <!-- *********************************************** -->
2216 <refentry id=
"spi-spi-saveplan">
2218 <refentrytitle>SPI_saveplan
</refentrytitle>
2222 <refname>SPI_saveplan
</refname>
2223 <refpurpose>save a plan
</refpurpose>
2226 <indexterm><primary>SPI_saveplan
</primary></indexterm>
2230 SPIPlanPtr SPI_saveplan(SPIPlanPtr
<parameter>plan
</parameter>)
2235 <title>Description
</title>
2238 <function>SPI_saveplan
</function> saves a passed plan (prepared by
2239 <function>SPI_prepare
</function>) in memory that will not be freed
2240 by
<function>SPI_finish
</function> nor by the transaction manager,
2241 and returns a pointer to the saved plan. This gives you the
2242 ability to reuse prepared plans in the subsequent invocations of
2243 your procedure in the current session.
2248 <title>Arguments
</title>
2252 <term><literal>SPIPlanPtr
<parameter>plan
</parameter></literal></term>
2255 the plan to be saved
2263 <title>Return Value
</title>
2266 Pointer to the saved plan;
<symbol>NULL
</symbol> if unsuccessful.
2267 On error,
<varname>SPI_result
</varname> is set thus:
2271 <term><symbol>SPI_ERROR_ARGUMENT
</symbol></term>
2274 if
<parameter>plan
</parameter> is
<symbol>NULL
</symbol> or invalid
2280 <term><symbol>SPI_ERROR_UNCONNECTED
</symbol></term>
2283 if called from an unconnected procedure
2292 <title>Notes
</title>
2295 The passed-in plan is not freed, so you might wish to do
2296 <function>SPI_freeplan
</function> on it to avoid leaking memory
2297 until
<function>SPI_finish<
/>.
2301 If one of the objects (a table, function, etc.) referenced by the
2302 prepared plan is dropped or redefined, then future executions of
2303 <function>SPI_execute_plan
</function> may fail or return different
2304 results than the plan initially indicates.
2311 <sect1 id=
"spi-interface-support">
2312 <title>Interface Support Functions
</title>
2315 The functions described here provide an interface for extracting
2316 information from result sets returned by
<function>SPI_execute<
/> and
2317 other SPI functions.
2321 All functions described in this section can be used by both
2322 connected and unconnected procedures.
2325 <!-- *********************************************** -->
2327 <refentry id=
"spi-spi-fname">
2329 <refentrytitle>SPI_fname
</refentrytitle>
2333 <refname>SPI_fname
</refname>
2334 <refpurpose>determine the column name for the specified column number
</refpurpose>
2337 <indexterm><primary>SPI_fname
</primary></indexterm>
2341 char * SPI_fname(TupleDesc
<parameter>rowdesc
</parameter>, int
<parameter>colnumber
</parameter>)
2346 <title>Description
</title>
2349 <function>SPI_fname
</function> returns a copy of the column name of the
2350 specified column. (You can use
<function>pfree
</function> to
2351 release the copy of the name when you don't need it anymore.)
2356 <title>Arguments
</title>
2360 <term><literal>TupleDesc
<parameter>rowdesc
</parameter></literal></term>
2363 input row description
2369 <term><literal>int
<parameter>colnumber
</parameter></literal></term>
2372 column number (count starts at
1)
2380 <title>Return Value
</title>
2383 The column name;
<symbol>NULL
</symbol> if
2384 <parameter>colnumber
</parameter> is out of range.
2385 <varname>SPI_result
</varname> set to
2386 <symbol>SPI_ERROR_NOATTRIBUTE
</symbol> on error.
2391 <!-- *********************************************** -->
2393 <refentry id=
"spi-spi-fnumber">
2395 <refentrytitle>SPI_fnumber
</refentrytitle>
2399 <refname>SPI_fnumber
</refname>
2400 <refpurpose>determine the column number for the specified column name
</refpurpose>
2403 <indexterm><primary>SPI_fnumber
</primary></indexterm>
2407 int SPI_fnumber(TupleDesc
<parameter>rowdesc
</parameter>, const char *
<parameter>colname
</parameter>)
2412 <title>Description
</title>
2415 <function>SPI_fnumber
</function> returns the column number for the
2416 column with the specified name.
2420 If
<parameter>colname
</parameter> refers to a system column (e.g.,
2421 <literal>oid<
/>) then the appropriate negative column number will
2422 be returned. The caller should be careful to test the return value
2423 for exact equality to
<symbol>SPI_ERROR_NOATTRIBUTE
</symbol> to
2424 detect an error; testing the result for less than or equal to
0 is
2425 not correct unless system columns should be rejected.
2430 <title>Arguments
</title>
2434 <term><literal>TupleDesc
<parameter>rowdesc
</parameter></literal></term>
2437 input row description
2443 <term><literal>const char *
<parameter>colname
</parameter></literal></term>
2454 <title>Return Value
</title>
2457 Column number (count starts at
1), or
2458 <symbol>SPI_ERROR_NOATTRIBUTE
</symbol> if the named column was not
2464 <!-- *********************************************** -->
2466 <refentry id=
"spi-spi-getvalue">
2468 <refentrytitle>SPI_getvalue
</refentrytitle>
2472 <refname>SPI_getvalue
</refname>
2473 <refpurpose>return the string value of the specified column
</refpurpose>
2476 <indexterm><primary>SPI_getvalue
</primary></indexterm>
2480 char * SPI_getvalue(HeapTuple
<parameter>row
</parameter>, TupleDesc
<parameter>rowdesc
</parameter>, int
<parameter>colnumber
</parameter>)
2485 <title>Description
</title>
2488 <function>SPI_getvalue
</function> returns the string representation
2489 of the value of the specified column.
2493 The result is returned in memory allocated using
2494 <function>palloc
</function>. (You can use
2495 <function>pfree
</function> to release the memory when you don't
2501 <title>Arguments
</title>
2505 <term><literal>HeapTuple
<parameter>row
</parameter></literal></term>
2508 input row to be examined
2514 <term><literal>TupleDesc
<parameter>rowdesc
</parameter></literal></term>
2517 input row description
2523 <term><literal>int
<parameter>colnumber
</parameter></literal></term>
2526 column number (count starts at
1)
2534 <title>Return Value
</title>
2537 Column value, or
<symbol>NULL
</symbol> if the column is null,
2538 <parameter>colnumber
</parameter> is out of range
2539 (
<varname>SPI_result
</varname> is set to
2540 <symbol>SPI_ERROR_NOATTRIBUTE
</symbol>), or no output function is
2541 available (
<varname>SPI_result
</varname> is set to
2542 <symbol>SPI_ERROR_NOOUTFUNC
</symbol>).
2547 <!-- *********************************************** -->
2549 <refentry id=
"spi-spi-getbinval">
2551 <refentrytitle>SPI_getbinval
</refentrytitle>
2555 <refname>SPI_getbinval
</refname>
2556 <refpurpose>return the binary value of the specified column
</refpurpose>
2559 <indexterm><primary>SPI_getbinval
</primary></indexterm>
2563 Datum SPI_getbinval(HeapTuple
<parameter>row
</parameter>, TupleDesc
<parameter>rowdesc
</parameter>, int
<parameter>colnumber
</parameter>, bool *
<parameter>isnull
</parameter>)
2568 <title>Description
</title>
2571 <function>SPI_getbinval
</function> returns the value of the
2572 specified column in the internal form (as type
<type>Datum
</type>).
2576 This function does not allocate new space for the datum. In the
2577 case of a pass-by-reference data type, the return value will be a
2578 pointer into the passed row.
2583 <title>Arguments
</title>
2587 <term><literal>HeapTuple
<parameter>row
</parameter></literal></term>
2590 input row to be examined
2596 <term><literal>TupleDesc
<parameter>rowdesc
</parameter></literal></term>
2599 input row description
2605 <term><literal>int
<parameter>colnumber
</parameter></literal></term>
2608 column number (count starts at
1)
2614 <term><literal>bool *
<parameter>isnull
</parameter></literal></term>
2617 flag for a null value in the column
2625 <title>Return Value
</title>
2628 The binary value of the column is returned. The variable pointed
2629 to by
<parameter>isnull
</parameter> is set to true if the column is
2630 null, else to false.
2634 <varname>SPI_result
</varname> is set to
2635 <symbol>SPI_ERROR_NOATTRIBUTE
</symbol> on error.
2640 <!-- *********************************************** -->
2642 <refentry id=
"spi-spi-gettype">
2644 <refentrytitle>SPI_gettype
</refentrytitle>
2648 <refname>SPI_gettype
</refname>
2649 <refpurpose>return the data type name of the specified column
</refpurpose>
2652 <indexterm><primary>SPI_gettype
</primary></indexterm>
2656 char * SPI_gettype(TupleDesc
<parameter>rowdesc
</parameter>, int
<parameter>colnumber
</parameter>)
2661 <title>Description
</title>
2664 <function>SPI_gettype
</function> returns a copy of the data type name of the
2665 specified column. (You can use
<function>pfree
</function> to
2666 release the copy of the name when you don't need it anymore.)
2671 <title>Arguments
</title>
2675 <term><literal>TupleDesc
<parameter>rowdesc
</parameter></literal></term>
2678 input row description
2684 <term><literal>int
<parameter>colnumber
</parameter></literal></term>
2687 column number (count starts at
1)
2695 <title>Return Value
</title>
2698 The data type name of the specified column, or
2699 <symbol>NULL
</symbol> on error.
<varname>SPI_result
</varname> is
2700 set to
<symbol>SPI_ERROR_NOATTRIBUTE
</symbol> on error.
2705 <!-- *********************************************** -->
2707 <refentry id=
"spi-spi-gettypeid">
2709 <refentrytitle>SPI_gettypeid
</refentrytitle>
2713 <refname>SPI_gettypeid
</refname>
2714 <refpurpose>return the data type
<acronym>OID
</acronym> of the specified column
</refpurpose>
2717 <indexterm><primary>SPI_gettypeid
</primary></indexterm>
2721 Oid SPI_gettypeid(TupleDesc
<parameter>rowdesc
</parameter>, int
<parameter>colnumber
</parameter>)
2726 <title>Description
</title>
2729 <function>SPI_gettypeid
</function> returns the
2730 <acronym>OID
</acronym> of the data type of the specified column.
2735 <title>Arguments
</title>
2739 <term><literal>TupleDesc
<parameter>rowdesc
</parameter></literal></term>
2742 input row description
2748 <term><literal>int
<parameter>colnumber
</parameter></literal></term>
2751 column number (count starts at
1)
2759 <title>Return Value
</title>
2762 The
<acronym>OID
</acronym> of the data type of the specified column
2763 or
<symbol>InvalidOid
</symbol> on error. On error,
2764 <varname>SPI_result
</varname> is set to
2765 <symbol>SPI_ERROR_NOATTRIBUTE
</symbol>.
2770 <!-- *********************************************** -->
2772 <refentry id=
"spi-spi-getrelname">
2774 <refentrytitle>SPI_getrelname
</refentrytitle>
2778 <refname>SPI_getrelname
</refname>
2779 <refpurpose>return the name of the specified relation
</refpurpose>
2782 <indexterm><primary>SPI_getrelname
</primary></indexterm>
2786 char * SPI_getrelname(Relation
<parameter>rel
</parameter>)
2791 <title>Description
</title>
2794 <function>SPI_getrelname
</function> returns a copy of the name of the
2795 specified relation. (You can use
<function>pfree
</function> to
2796 release the copy of the name when you don't need it anymore.)
2801 <title>Arguments
</title>
2805 <term><literal>Relation
<parameter>rel
</parameter></literal></term>
2816 <title>Return Value
</title>
2819 The name of the specified relation.
2824 <refentry id=
"spi-spi-getnspname">
2826 <refentrytitle>SPI_getnspname
</refentrytitle>
2830 <refname>SPI_getnspname
</refname>
2831 <refpurpose>return the namespace of the specified relation
</refpurpose>
2834 <indexterm><primary>SPI_getnspname
</primary></indexterm>
2838 char * SPI_getnspname(Relation
<parameter>rel
</parameter>)
2843 <title>Description
</title>
2846 <function>SPI_getnspname
</function> returns a copy of the name of
2847 the namespace that the specified
<structname>Relation
</structname>
2848 belongs to. This is equivalent to the relation's schema. You should
2849 <function>pfree
</function> the return value of this function when
2850 you are finished with it.
2855 <title>Arguments
</title>
2859 <term><literal>Relation
<parameter>rel
</parameter></literal></term>
2870 <title>Return Value
</title>
2873 The name of the specified relation's namespace.
2880 <sect1 id=
"spi-memory">
2881 <title>Memory Management
</title>
2884 <productname>PostgreSQL
</productname> allocates memory within
2885 <firstterm>memory contexts
</firstterm><indexterm><primary>memory
2886 context
</primary><secondary>in SPI
</secondary></indexterm>, which provide a convenient method of
2887 managing allocations made in many different places that need to
2888 live for differing amounts of time. Destroying a context releases
2889 all the memory that was allocated in it. Thus, it is not necessary
2890 to keep track of individual objects to avoid memory leaks; instead
2891 only a relatively small number of contexts have to be managed.
2892 <function>palloc
</function> and related functions allocate memory
2893 from the
<quote>current<
/> context.
2897 <function>SPI_connect
</function> creates a new memory context and
2898 makes it current.
<function>SPI_finish
</function> restores the
2899 previous current memory context and destroys the context created by
2900 <function>SPI_connect
</function>. These actions ensure that
2901 transient memory allocations made inside your procedure are
2902 reclaimed at procedure exit, avoiding memory leakage.
2906 However, if your procedure needs to return an object in allocated
2907 memory (such as a value of a pass-by-reference data type), you
2908 cannot allocate that memory using
<function>palloc
</function>, at
2909 least not while you are connected to SPI. If you try, the object
2910 will be deallocated by
<function>SPI_finish
</function>, and your
2911 procedure will not work reliably. To solve this problem, use
2912 <function>SPI_palloc
</function> to allocate memory for your return
2913 object.
<function>SPI_palloc
</function> allocates memory in the
2914 <quote>upper executor context
</quote>, that is, the memory context
2915 that was current when
<function>SPI_connect
</function> was called,
2916 which is precisely the right context for a value returned from your
2921 If
<function>SPI_palloc
</function> is called while the procedure is
2922 not connected to SPI, then it acts the same as a normal
2923 <function>palloc
</function>. Before a procedure connects to the
2924 SPI manager, the current memory context is the upper executor
2925 context, so all allocations made by the procedure via
2926 <function>palloc
</function> or by SPI utility functions are made in
2931 When
<function>SPI_connect
</function> is called, the private
2932 context of the procedure, which is created by
2933 <function>SPI_connect
</function>, is made the current context. All
2934 allocations made by
<function>palloc
</function>,
2935 <function>repalloc
</function>, or SPI utility functions (except for
2936 <function>SPI_copytuple
</function>,
2937 <function>SPI_returntuple
</function>,
2938 <function>SPI_modifytuple
</function>, and
2939 <function>SPI_palloc
</function>) are made in this context. When a
2940 procedure disconnects from the SPI manager (via
2941 <function>SPI_finish
</function>) the current context is restored to
2942 the upper executor context, and all allocations made in the
2943 procedure memory context are freed and cannot be used any more.
2947 All functions described in this section can be used by both
2948 connected and unconnected procedures. In an unconnected procedure,
2949 they act the same as the underlying ordinary server functions
2950 (
<function>palloc<
/>, etc.).
2953 <!-- *********************************************** -->
2955 <refentry id=
"spi-spi-palloc">
2957 <refentrytitle>SPI_palloc
</refentrytitle>
2961 <refname>SPI_palloc
</refname>
2962 <refpurpose>allocate memory in the upper executor context
</refpurpose>
2965 <indexterm><primary>SPI_palloc
</primary></indexterm>
2969 void * SPI_palloc(Size
<parameter>size
</parameter>)
2974 <title>Description
</title>
2977 <function>SPI_palloc
</function> allocates memory in the upper
2983 <title>Arguments
</title>
2987 <term><literal>Size
<parameter>size
</parameter></literal></term>
2990 size in bytes of storage to allocate
2998 <title>Return Value
</title>
3001 pointer to new storage space of the specified size
3006 <!-- *********************************************** -->
3008 <refentry id=
"spi-realloc">
3010 <refentrytitle>SPI_repalloc
</refentrytitle>
3014 <refname>SPI_repalloc
</refname>
3015 <refpurpose>reallocate memory in the upper executor context
</refpurpose>
3018 <indexterm><primary>SPI_repalloc
</primary></indexterm>
3022 void * SPI_repalloc(void *
<parameter>pointer
</parameter>, Size
<parameter>size
</parameter>)
3027 <title>Description
</title>
3030 <function>SPI_repalloc
</function> changes the size of a memory
3031 segment previously allocated using
<function>SPI_palloc
</function>.
3035 This function is no longer different from plain
3036 <function>repalloc
</function>. It's kept just for backward
3037 compatibility of existing code.
3042 <title>Arguments
</title>
3046 <term><literal>void *
<parameter>pointer
</parameter></literal></term>
3049 pointer to existing storage to change
3055 <term><literal>Size
<parameter>size
</parameter></literal></term>
3058 size in bytes of storage to allocate
3066 <title>Return Value
</title>
3069 pointer to new storage space of specified size with the contents
3070 copied from the existing area
3075 <!-- *********************************************** -->
3077 <refentry id=
"spi-spi-pfree">
3079 <refentrytitle>SPI_pfree
</refentrytitle>
3083 <refname>SPI_pfree
</refname>
3084 <refpurpose>free memory in the upper executor context
</refpurpose>
3087 <indexterm><primary>SPI_pfree
</primary></indexterm>
3091 void SPI_pfree(void *
<parameter>pointer
</parameter>)
3096 <title>Description
</title>
3099 <function>SPI_pfree
</function> frees memory previously allocated
3100 using
<function>SPI_palloc
</function> or
3101 <function>SPI_repalloc
</function>.
3105 This function is no longer different from plain
3106 <function>pfree
</function>. It's kept just for backward
3107 compatibility of existing code.
3112 <title>Arguments
</title>
3116 <term><literal>void *
<parameter>pointer
</parameter></literal></term>
3119 pointer to existing storage to free
3127 <!-- *********************************************** -->
3129 <refentry id=
"spi-spi-copytuple">
3131 <refentrytitle>SPI_copytuple
</refentrytitle>
3135 <refname>SPI_copytuple
</refname>
3136 <refpurpose>make a copy of a row in the upper executor context
</refpurpose>
3139 <indexterm><primary>SPI_copytuple
</primary></indexterm>
3143 HeapTuple SPI_copytuple(HeapTuple
<parameter>row
</parameter>)
3148 <title>Description
</title>
3151 <function>SPI_copytuple
</function> makes a copy of a row in the
3152 upper executor context. This is normally used to return a modified
3153 row from a trigger. In a function declared to return a composite
3154 type, use
<function>SPI_returntuple
</function> instead.
3159 <title>Arguments
</title>
3163 <term><literal>HeapTuple
<parameter>row
</parameter></literal></term>
3174 <title>Return Value
</title>
3177 the copied row;
<symbol>NULL
</symbol> only if
3178 <parameter>tuple
</parameter> is
<symbol>NULL
</symbol>
3183 <!-- *********************************************** -->
3185 <refentry id=
"spi-spi-returntuple">
3187 <refentrytitle>SPI_returntuple
</refentrytitle>
3191 <refname>SPI_returntuple
</refname>
3192 <refpurpose>prepare to return a tuple as a Datum
</refpurpose>
3195 <indexterm><primary>SPI_returntuple
</primary></indexterm>
3199 HeapTupleHeader SPI_returntuple(HeapTuple
<parameter>row
</parameter>, TupleDesc
<parameter>rowdesc
</parameter>)
3204 <title>Description
</title>
3207 <function>SPI_returntuple
</function> makes a copy of a row in
3208 the upper executor context, returning it in the form of a row type
<type>Datum
</type>.
3209 The returned pointer need only be converted to
<type>Datum
</type> via
<function>PointerGetDatum
</function>
3214 Note that this should be used for functions that are declared to return
3215 composite types. It is not used for triggers; use
3216 <function>SPI_copytuple<
/> for returning a modified row in a trigger.
3221 <title>Arguments
</title>
3225 <term><literal>HeapTuple
<parameter>row
</parameter></literal></term>
3234 <term><literal>TupleDesc
<parameter>rowdesc
</parameter></literal></term>
3237 descriptor for row (pass the same descriptor each time for most
3246 <title>Return Value
</title>
3249 <type>HeapTupleHeader
</type> pointing to copied row;
3250 <symbol>NULL
</symbol> only if
3251 <parameter>row
</parameter> or
<parameter>rowdesc
</parameter> is
3252 <symbol>NULL
</symbol>
3257 <!-- *********************************************** -->
3259 <refentry id=
"spi-spi-modifytuple">
3261 <refentrytitle>SPI_modifytuple
</refentrytitle>
3265 <refname>SPI_modifytuple
</refname>
3266 <refpurpose>create a row by replacing selected fields of a given row
</refpurpose>
3269 <indexterm><primary>SPI_modifytuple
</primary></indexterm>
3273 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>)
3278 <title>Description
</title>
3281 <function>SPI_modifytuple
</function> creates a new row by
3282 substituting new values for selected columns, copying the original
3283 row's columns at other positions. The input row is not modified.
3288 <title>Arguments
</title>
3292 <term><literal>Relation
<parameter>rel
</parameter></literal></term>
3295 Used only as the source of the row descriptor for the row.
3296 (Passing a relation rather than a row descriptor is a
3303 <term><literal>HeapTuple
<parameter>row
</parameter></literal></term>
3312 <term><literal>int
<parameter>ncols
</parameter></literal></term>
3315 number of column numbers in the array
3316 <parameter>colnum
</parameter>
3322 <term><literal>int *
<parameter>colnum
</parameter></literal></term>
3325 array of the numbers of the columns that are to be changed
3326 (column numbers start at
1)
3332 <term><literal>Datum *
<parameter>values
</parameter></literal></term>
3335 new values for the specified columns
3341 <term><literal>const char *
<parameter>Nulls
</parameter></literal></term>
3344 which new values are null, if any (see
3345 <function>SPI_execute_plan
</function> for the format)
3353 <title>Return Value
</title>
3356 new row with modifications, allocated in the upper executor
3357 context;
<symbol>NULL
</symbol> only if
<parameter>row
</parameter>
3358 is
<symbol>NULL
</symbol>
3362 On error,
<varname>SPI_result
</varname> is set as follows:
3365 <term><symbol>SPI_ERROR_ARGUMENT
</symbol></term>
3368 if
<parameter>rel<
/> is
<symbol>NULL<
/>, or if
3369 <parameter>row<
/> is
<symbol>NULL<
/>, or if
<parameter>ncols<
/>
3370 is less than or equal to
0, or if
<parameter>colnum<
/> is
3371 <symbol>NULL<
/>, or if
<parameter>values<
/> is
<symbol>NULL<
/>.
3377 <term><symbol>SPI_ERROR_NOATTRIBUTE
</symbol></term>
3380 if
<parameter>colnum<
/> contains an invalid column number (less
3381 than or equal to
0 or greater than the number of column in
3391 <!-- *********************************************** -->
3393 <refentry id=
"spi-spi-freetuple">
3395 <refentrytitle>SPI_freetuple
</refentrytitle>
3399 <refname>SPI_freetuple
</refname>
3400 <refpurpose>free a row allocated in the upper executor context
</refpurpose>
3403 <indexterm><primary>SPI_freetuple
</primary></indexterm>
3407 void SPI_freetuple(HeapTuple
<parameter>row
</parameter>)
3412 <title>Description
</title>
3415 <function>SPI_freetuple
</function> frees a row previously allocated
3416 in the upper executor context.
3420 This function is no longer different from plain
3421 <function>heap_freetuple
</function>. It's kept just for backward
3422 compatibility of existing code.
3427 <title>Arguments
</title>
3431 <term><literal>HeapTuple
<parameter>row
</parameter></literal></term>
3442 <!-- *********************************************** -->
3444 <refentry id=
"spi-spi-freetupletable">
3446 <refentrytitle>SPI_freetuptable
</refentrytitle>
3450 <refname>SPI_freetuptable
</refname>
3451 <refpurpose>free a row set created by
<function>SPI_execute<
/> or a similar
3452 function
</refpurpose>
3455 <indexterm><primary>SPI_freetuptable
</primary></indexterm>
3459 void SPI_freetuptable(SPITupleTable *
<parameter>tuptable
</parameter>)
3464 <title>Description
</title>
3467 <function>SPI_freetuptable
</function> frees a row set created by a
3468 prior SPI command execution function, such as
3469 <function>SPI_execute<
/>. Therefore, this function is usually called
3470 with the global variable
<varname>SPI_tupletable
</varname> as
3475 This function is useful if a SPI procedure needs to execute
3476 multiple commands and does not want to keep the results of earlier
3477 commands around until it ends. Note that any unfreed row sets will
3478 be freed anyway at
<function>SPI_finish<
/>.
3483 <title>Arguments
</title>
3487 <term><literal>SPITupleTable *
<parameter>tuptable
</parameter></literal></term>
3490 pointer to row set to free
3498 <!-- *********************************************** -->
3500 <refentry id=
"spi-spi-freeplan">
3502 <refentrytitle>SPI_freeplan
</refentrytitle>
3506 <refname>SPI_freeplan
</refname>
3507 <refpurpose>free a previously saved plan
</refpurpose>
3510 <indexterm><primary>SPI_freeplan
</primary></indexterm>
3514 int SPI_freeplan(SPIPlanPtr
<parameter>plan
</parameter>)
3519 <title>Description
</title>
3522 <function>SPI_freeplan
</function> releases a command execution plan
3523 previously returned by
<function>SPI_prepare
</function> or saved by
3524 <function>SPI_saveplan
</function>.
3529 <title>Arguments
</title>
3533 <term><literal>SPIPlanPtr
<parameter>plan
</parameter></literal></term>
3536 pointer to plan to free
3544 <title>Return Value
</title>
3547 <symbol>SPI_ERROR_ARGUMENT
</symbol> if
<parameter>plan
</parameter>
3548 is
<symbol>NULL
</symbol> or invalid
3555 <sect1 id=
"spi-visibility">
3556 <title>Visibility of Data Changes
</title>
3559 The following rules govern the visibility of data changes in
3560 functions that use SPI (or any other C function):
3565 During the execution of an SQL command, any data changes made by
3566 the command are invisible to the command itself. For
3569 INSERT INTO a SELECT * FROM a;
3571 the inserted rows are invisible to the
<command>SELECT
</command>
3578 Changes made by a command C are visible to all commands that are
3579 started after C, no matter whether they are started inside C
3580 (during the execution of C) or after C is done.
3586 Commands executed via SPI inside a function called by an SQL command
3587 (either an ordinary function or a trigger) follow one or the
3588 other of the above rules depending on the read/write flag passed
3589 to SPI. Commands executed in read-only mode follow the first
3590 rule: they cannot see changes of the calling command. Commands executed
3591 in read-write mode follow the second rule: they can see all changes made
3598 All standard procedural languages set the SPI read-write mode
3599 depending on the volatility attribute of the function. Commands of
3600 <literal>STABLE<
/> and
<literal>IMMUTABLE<
/> functions are done in
3601 read-only mode, while commands of
<literal>VOLATILE<
/> functions are
3602 done in read-write mode. While authors of C functions are able to
3603 violate this convention, it's unlikely to be a good idea to do so.
3610 The next section contains an example that illustrates the
3611 application of these rules.
3615 <sect1 id=
"spi-examples">
3616 <title>Examples
</title>
3619 This section contains a very simple example of SPI usage. The
3620 procedure
<function>execq
</function> takes an SQL command as its
3621 first argument and a row count as its second, executes the command
3622 using
<function>SPI_exec
</function> and returns the number of rows
3623 that were processed by the command. You can find more complex
3624 examples for SPI in the source tree in
3625 <filename>src/test/regress/regress.c
</filename> and in
3626 <filename>contrib/spi
</filename>.
3630 #include
"postgres.h"
3632 #include
"executor/spi.h"
3633 #include
"utils/builtins.h"
3635 #ifdef PG_MODULE_MAGIC
3639 int execq(text *sql, int cnt);
3642 execq(text *sql, int cnt)
3648 /* Convert given text object to a C string */
3649 command = text_to_cstring(sql);
3653 ret = SPI_exec(command, cnt);
3655 proc = SPI_processed;
3657 * If some rows were fetched, print them via elog(INFO).
3659 if (ret
> 0 && SPI_tuptable != NULL)
3661 TupleDesc tupdesc = SPI_tuptable-
>tupdesc;
3662 SPITupleTable *tuptable = SPI_tuptable;
3666 for (j =
0; j
< proc; j++)
3668 HeapTuple tuple = tuptable-
>vals[j];
3670 for (i =
1, buf[
0] =
0; i
<= tupdesc-
>natts; i++)
3671 snprintf(buf + strlen (buf), sizeof(buf) - strlen(buf),
" %s%s",
3672 SPI_getvalue(tuple, tupdesc, i),
3673 (i == tupdesc-
>natts) ?
" " :
" |");
3674 elog(INFO,
"EXECQ: %s", buf);
3686 (This function uses call convention version
0, to make the example
3687 easier to understand. In real applications you should use the new
3688 version
1 interface.)
3692 This is how you declare the function after having compiled it into
3693 a shared library (details are in
<xref linkend=
"dfunc">.):
3696 CREATE FUNCTION execq(text, integer) RETURNS integer
3697 AS '
<replaceable>filename
</replaceable>'
3703 Here is a sample session:
3706 =
> SELECT execq('CREATE TABLE a (x integer)',
0);
3712 =
> INSERT INTO a VALUES (execq('INSERT INTO a VALUES (
0)',
0));
3714 =
> SELECT execq('SELECT * FROM a',
0);
3715 INFO: EXECQ:
0 -- inserted by execq
3716 INFO: EXECQ:
1 -- returned by execq and inserted by upper INSERT
3723 =
> SELECT execq('INSERT INTO a SELECT x +
2 FROM a',
1);
3729 =
> SELECT execq('SELECT * FROM a',
10);
3732 INFO: EXECQ:
2 --
0 +
2, only one row inserted - as specified
3736 3 --
10 is the max value only,
3 is the real number of rows
3739 =
> DELETE FROM a;
3741 =
> INSERT INTO a VALUES (execq('SELECT * FROM a',
0) +
1);
3743 =
> SELECT * FROM a;
3746 1 -- no rows in a (
0) +
1
3749 =
> INSERT INTO a VALUES (execq('SELECT * FROM a',
0) +
1);
3752 =
> SELECT * FROM a;
3756 2 -- there was one row in a +
1
3759 -- This demonstrates the data changes visibility rule:
3761 =
> INSERT INTO a SELECT execq('SELECT * FROM a',
0) * x FROM a;
3768 =
> SELECT * FROM a;
3773 2 --
2 rows *
1 (x in first row)
3774 6 --
3 rows (
2 +
1 just inserted) *
2 (x in second row)
3776 rows visible to execq() in different invocations