1SPI_EXECUTE(3)           PostgreSQL 14.3 Documentation          SPI_EXECUTE(3)
2
3
4

NAME

6       SPI_execute - execute a command
7

SYNOPSIS

9       int SPI_execute(const char * command, bool read_only, long count)
10

DESCRIPTION

12       SPI_execute executes the specified SQL command for count rows. If
13       read_only is true, the command must be read-only, and execution
14       overhead is somewhat reduced.
15
16       This function can only be called from a connected C function.
17
18       If count is zero then the command is executed for all rows that it
19       applies to. If count is greater than zero, then no more than count rows
20       will be retrieved; execution stops when the count is reached, much like
21       adding a LIMIT clause to the query. For example,
22
23           SPI_execute("SELECT * FROM foo", true, 5);
24
25       will retrieve at most 5 rows from the table. Note that such a limit is
26       only effective when the command actually returns rows. For example,
27
28           SPI_execute("INSERT INTO foo SELECT * FROM bar", false, 5);
29
30       inserts all rows from bar, ignoring the count parameter. However, with
31
32           SPI_execute("INSERT INTO foo SELECT * FROM bar RETURNING *", false, 5);
33
34       at most 5 rows would be inserted, since execution would stop after the
35       fifth RETURNING result row is retrieved.
36
37       You can pass multiple commands in one string; SPI_execute returns the
38       result for the command executed last. The count limit applies to each
39       command separately (even though only the last result will actually be
40       returned). The limit is not applied to any hidden commands generated by
41       rules.
42
43       When read_only is false, SPI_execute increments the command counter and
44       computes a new snapshot before executing each command in the string.
45       The snapshot does not actually change if the current transaction
46       isolation level is SERIALIZABLE or REPEATABLE READ, but in READ
47       COMMITTED mode the snapshot update allows each command to see the
48       results of newly committed transactions from other sessions. This is
49       essential for consistent behavior when the commands are modifying the
50       database.
51
52       When read_only is true, SPI_execute does not update either the snapshot
53       or the command counter, and it allows only plain SELECT commands to
54       appear in the command string. The commands are executed using the
55       snapshot previously established for the surrounding query. This
56       execution mode is somewhat faster than the read/write mode due to
57       eliminating per-command overhead. It also allows genuinely stable
58       functions to be built: since successive executions will all use the
59       same snapshot, there will be no change in the results.
60
61       It is generally unwise to mix read-only and read-write commands within
62       a single function using SPI; that could result in very confusing
63       behavior, since the read-only queries would not see the results of any
64       database updates done by the read-write queries.
65
66       The actual number of rows for which the (last) command was executed is
67       returned in the global variable SPI_processed. If the return value of
68       the function is SPI_OK_SELECT, SPI_OK_INSERT_RETURNING,
69       SPI_OK_DELETE_RETURNING, or SPI_OK_UPDATE_RETURNING, then you can use
70       the global pointer SPITupleTable *SPI_tuptable to access the result
71       rows. Some utility commands (such as EXPLAIN) also return row sets, and
72       SPI_tuptable will contain the result in these cases too. Some utility
73       commands (COPY, CREATE TABLE AS) don't return a row set, so
74       SPI_tuptable is NULL, but they still return the number of rows
75       processed in SPI_processed.
76
77       The structure SPITupleTable is defined thus:
78
79           typedef struct SPITupleTable
80           {
81               /* Public members */
82               TupleDesc   tupdesc;        /* tuple descriptor */
83               HeapTuple  *vals;           /* array of tuples */
84               uint64      numvals;        /* number of valid tuples */
85
86               /* Private members, not intended for external callers */
87               uint64      alloced;        /* allocated length of vals array */
88               MemoryContext tuptabcxt;    /* memory context of result table */
89               slist_node  next;           /* link for internal bookkeeping */
90               SubTransactionId subid;     /* subxact in which tuptable was created */
91           } SPITupleTable;
92
93       The fields tupdesc, vals, and numvals can be used by SPI callers; the
94       remaining fields are internal.  vals is an array of pointers to rows.
95       The number of rows is given by numvals (for somewhat historical
96       reasons, this count is also returned in SPI_processed).  tupdesc is a
97       row descriptor which you can pass to SPI functions dealing with rows.
98
99       SPI_finish frees all SPITupleTables allocated during the current C
100       function. You can free a particular result table earlier, if you are
101       done with it, by calling SPI_freetuptable.
102

ARGUMENTS

104       const char * command
105           string containing command to execute
106
107       bool read_only
108           true for read-only execution
109
110       long count
111           maximum number of rows to return, or 0 for no limit
112

RETURN VALUE

114       If the execution of the command was successful then one of the
115       following (nonnegative) values will be returned:
116
117       SPI_OK_SELECT
118           if a SELECT (but not SELECT INTO) was executed
119
120       SPI_OK_SELINTO
121           if a SELECT INTO was executed
122
123       SPI_OK_INSERT
124           if an INSERT was executed
125
126       SPI_OK_DELETE
127           if a DELETE was executed
128
129       SPI_OK_UPDATE
130           if an UPDATE was executed
131
132       SPI_OK_INSERT_RETURNING
133           if an INSERT RETURNING was executed
134
135       SPI_OK_DELETE_RETURNING
136           if a DELETE RETURNING was executed
137
138       SPI_OK_UPDATE_RETURNING
139           if an UPDATE RETURNING was executed
140
141       SPI_OK_UTILITY
142           if a utility command (e.g., CREATE TABLE) was executed
143
144       SPI_OK_REWRITTEN
145           if the command was rewritten into another kind of command (e.g.,
146           UPDATE became an INSERT) by a rule.
147
148       On error, one of the following negative values is returned:
149
150       SPI_ERROR_ARGUMENT
151           if command is NULL or count is less than 0
152
153       SPI_ERROR_COPY
154           if COPY TO stdout or COPY FROM stdin was attempted
155
156       SPI_ERROR_TRANSACTION
157           if a transaction manipulation command was attempted (BEGIN, COMMIT,
158           ROLLBACK, SAVEPOINT, PREPARE TRANSACTION, COMMIT PREPARED, ROLLBACK
159           PREPARED, or any variant thereof)
160
161       SPI_ERROR_OPUNKNOWN
162           if the command type is unknown (shouldn't happen)
163
164       SPI_ERROR_UNCONNECTED
165           if called from an unconnected C function
166

NOTES

168       All SPI query-execution functions set both SPI_processed and
169       SPI_tuptable (just the pointer, not the contents of the structure).
170       Save these two global variables into local C function variables if you
171       need to access the result table of SPI_execute or another
172       query-execution function across later calls.
173
174
175
176PostgreSQL 14.3                      2022                       SPI_EXECUTE(3)
Impressum