diff options
Diffstat (limited to 'doc/src/sgml/man3/SPI_execute.3')
| -rw-r--r-- | doc/src/sgml/man3/SPI_execute.3 | 344 |
1 files changed, 344 insertions, 0 deletions
diff --git a/doc/src/sgml/man3/SPI_execute.3 b/doc/src/sgml/man3/SPI_execute.3 new file mode 100644 index 0000000..80efbcd --- /dev/null +++ b/doc/src/sgml/man3/SPI_execute.3 @@ -0,0 +1,344 @@ +'\" t +.\" Title: SPI_execute +.\" Author: The PostgreSQL Global Development Group +.\" Generator: DocBook XSL Stylesheets vsnapshot <http://docbook.sf.net/> +.\" Date: 2023 +.\" Manual: PostgreSQL 15.5 Documentation +.\" Source: PostgreSQL 15.5 +.\" Language: English +.\" +.TH "SPI_EXECUTE" "3" "2023" "PostgreSQL 15.5" "PostgreSQL 15.5 Documentation" +.\" ----------------------------------------------------------------- +.\" * Define some portability stuff +.\" ----------------------------------------------------------------- +.\" ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ +.\" http://bugs.debian.org/507673 +.\" http://lists.gnu.org/archive/html/groff/2009-02/msg00013.html +.\" ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ +.ie \n(.g .ds Aq \(aq +.el .ds Aq ' +.\" ----------------------------------------------------------------- +.\" * set default formatting +.\" ----------------------------------------------------------------- +.\" disable hyphenation +.nh +.\" disable justification (adjust text to left margin only) +.ad l +.\" ----------------------------------------------------------------- +.\" * MAIN CONTENT STARTS HERE * +.\" ----------------------------------------------------------------- +.SH "NAME" +SPI_execute \- execute a command +.SH "SYNOPSIS" +.sp +.nf +int SPI_execute(const char * \fIcommand\fR, bool \fIread_only\fR, long \fIcount\fR) +.fi +.SH "DESCRIPTION" +.PP +\fBSPI_execute\fR +executes the specified SQL command for +\fIcount\fR +rows\&. If +\fIread_only\fR +is +true, the command must be read\-only, and execution overhead is somewhat reduced\&. +.PP +This function can only be called from a connected C function\&. +.PP +If +\fIcount\fR +is zero then the command is executed for all rows that it applies to\&. If +\fIcount\fR +is greater than zero, then no more than +\fIcount\fR +rows will be retrieved; execution stops when the count is reached, much like adding a +LIMIT +clause to the query\&. For example, +.sp +.if n \{\ +.RS 4 +.\} +.nf +SPI_execute("SELECT * FROM foo", true, 5); +.fi +.if n \{\ +.RE +.\} +.sp +will retrieve at most 5 rows from the table\&. Note that such a limit is only effective when the command actually returns rows\&. For example, +.sp +.if n \{\ +.RS 4 +.\} +.nf +SPI_execute("INSERT INTO foo SELECT * FROM bar", false, 5); +.fi +.if n \{\ +.RE +.\} +.sp +inserts all rows from +bar, ignoring the +\fIcount\fR +parameter\&. However, with +.sp +.if n \{\ +.RS 4 +.\} +.nf +SPI_execute("INSERT INTO foo SELECT * FROM bar RETURNING *", false, 5); +.fi +.if n \{\ +.RE +.\} +.sp +at most 5 rows would be inserted, since execution would stop after the fifth +RETURNING +result row is retrieved\&. +.PP +You can pass multiple commands in one string; +\fBSPI_execute\fR +returns the result for the command executed last\&. The +\fIcount\fR +limit applies to each command separately (even though only the last result will actually be returned)\&. The limit is not applied to any hidden commands generated by rules\&. +.PP +When +\fIread_only\fR +is +false, +\fBSPI_execute\fR +increments the command counter and computes a new +snapshot +before executing each command in the string\&. The snapshot does not actually change if the current transaction isolation level is +SERIALIZABLE +or +REPEATABLE READ, but in +READ COMMITTED +mode the snapshot update allows each command to see the results of newly committed transactions from other sessions\&. This is essential for consistent behavior when the commands are modifying the database\&. +.PP +When +\fIread_only\fR +is +true, +\fBSPI_execute\fR +does not update either the snapshot or the command counter, and it allows only plain +\fBSELECT\fR +commands to appear in the command string\&. The commands are executed using the snapshot previously established for the surrounding query\&. This execution mode is somewhat faster than the read/write mode due to eliminating per\-command overhead\&. It also allows genuinely +stable +functions to be built: since successive executions will all use the same snapshot, there will be no change in the results\&. +.PP +It is generally unwise to mix read\-only and read\-write commands within a single function using SPI; that could result in very confusing behavior, since the read\-only queries would not see the results of any database updates done by the read\-write queries\&. +.PP +The actual number of rows for which the (last) command was executed is returned in the global variable +\fISPI_processed\fR\&. If the return value of the function is +SPI_OK_SELECT, +SPI_OK_INSERT_RETURNING, +SPI_OK_DELETE_RETURNING, or +SPI_OK_UPDATE_RETURNING, then you can use the global pointer +SPITupleTable *SPI_tuptable +to access the result rows\&. Some utility commands (such as +\fBEXPLAIN\fR) also return row sets, and +SPI_tuptable +will contain the result in these cases too\&. Some utility commands (\fBCOPY\fR, +\fBCREATE TABLE AS\fR) don\*(Aqt return a row set, so +SPI_tuptable +is NULL, but they still return the number of rows processed in +\fISPI_processed\fR\&. +.PP +The structure +SPITupleTable +is defined thus: +.sp +.if n \{\ +.RS 4 +.\} +.nf +typedef struct SPITupleTable +{ + /* Public members */ + TupleDesc tupdesc; /* tuple descriptor */ + HeapTuple *vals; /* array of tuples */ + uint64 numvals; /* number of valid tuples */ + + /* Private members, not intended for external callers */ + uint64 alloced; /* allocated length of vals array */ + MemoryContext tuptabcxt; /* memory context of result table */ + slist_node next; /* link for internal bookkeeping */ + SubTransactionId subid; /* subxact in which tuptable was created */ +} SPITupleTable; +.fi +.if n \{\ +.RE +.\} +.sp +The fields +tupdesc, +vals, and +numvals +can be used by SPI callers; the remaining fields are internal\&. +vals +is an array of pointers to rows\&. The number of rows is given by +numvals +(for somewhat historical reasons, this count is also returned in +\fISPI_processed\fR)\&. +tupdesc +is a row descriptor which you can pass to SPI functions dealing with rows\&. +.PP +\fBSPI_finish\fR +frees all +SPITupleTables allocated during the current C function\&. You can free a particular result table earlier, if you are done with it, by calling +\fBSPI_freetuptable\fR\&. +.SH "ARGUMENTS" +.PP +const char * \fIcommand\fR +.RS 4 +string containing command to execute +.RE +.PP +bool \fIread_only\fR +.RS 4 +true +for read\-only execution +.RE +.PP +long \fIcount\fR +.RS 4 +maximum number of rows to return, or +0 +for no limit +.RE +.SH "RETURN VALUE" +.PP +If the execution of the command was successful then one of the following (nonnegative) values will be returned: +.PP +SPI_OK_SELECT +.RS 4 +if a +\fBSELECT\fR +(but not +\fBSELECT INTO\fR) was executed +.RE +.PP +SPI_OK_SELINTO +.RS 4 +if a +\fBSELECT INTO\fR +was executed +.RE +.PP +SPI_OK_INSERT +.RS 4 +if an +\fBINSERT\fR +was executed +.RE +.PP +SPI_OK_DELETE +.RS 4 +if a +\fBDELETE\fR +was executed +.RE +.PP +SPI_OK_UPDATE +.RS 4 +if an +\fBUPDATE\fR +was executed +.RE +.PP +SPI_OK_MERGE +.RS 4 +if a +\fBMERGE\fR +was executed +.RE +.PP +SPI_OK_INSERT_RETURNING +.RS 4 +if an +\fBINSERT RETURNING\fR +was executed +.RE +.PP +SPI_OK_DELETE_RETURNING +.RS 4 +if a +\fBDELETE RETURNING\fR +was executed +.RE +.PP +SPI_OK_UPDATE_RETURNING +.RS 4 +if an +\fBUPDATE RETURNING\fR +was executed +.RE +.PP +SPI_OK_UTILITY +.RS 4 +if a utility command (e\&.g\&., +\fBCREATE TABLE\fR) was executed +.RE +.PP +SPI_OK_REWRITTEN +.RS 4 +if the command was rewritten into another kind of command (e\&.g\&., +\fBUPDATE\fR +became an +\fBINSERT\fR) by a +rule\&. +.RE +.PP +On error, one of the following negative values is returned: +.PP +SPI_ERROR_ARGUMENT +.RS 4 +if +\fIcommand\fR +is +NULL +or +\fIcount\fR +is less than 0 +.RE +.PP +SPI_ERROR_COPY +.RS 4 +if +\fBCOPY TO stdout\fR +or +\fBCOPY FROM stdin\fR +was attempted +.RE +.PP +SPI_ERROR_TRANSACTION +.RS 4 +if a transaction manipulation command was attempted (\fBBEGIN\fR, +\fBCOMMIT\fR, +\fBROLLBACK\fR, +\fBSAVEPOINT\fR, +\fBPREPARE TRANSACTION\fR, +\fBCOMMIT PREPARED\fR, +\fBROLLBACK PREPARED\fR, or any variant thereof) +.RE +.PP +SPI_ERROR_OPUNKNOWN +.RS 4 +if the command type is unknown (shouldn\*(Aqt happen) +.RE +.PP +SPI_ERROR_UNCONNECTED +.RS 4 +if called from an unconnected C function +.RE +.SH "NOTES" +.PP +All SPI query\-execution functions set both +\fISPI_processed\fR +and +\fISPI_tuptable\fR +(just the pointer, not the contents of the structure)\&. Save these two global variables into local C function variables if you need to access the result table of +\fBSPI_execute\fR +or another query\-execution function across later calls\&. |