Skip site navigation (1)Skip section navigation (2)

FreeBSD Manual Pages

  
 
  

home | help
SPI_EXECUTE(3)		PostgreSQL 9.6.20 Documentation		SPI_EXECUTE(3)

NAME
       SPI_execute - execute a command

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

DESCRIPTION
       SPI_execute executes the	specified SQL command for count	rows. If
       read_only is true, the command must be read-only, and execution
       overhead	is somewhat reduced.

       This function can only be called	from a connected procedure.

       If count	is zero	then the command is executed for all rows that it
       applies to. If count is greater than zero, then no more than count rows
       will be retrieved; execution stops when the count is reached, much like
       adding a	LIMIT clause to	the query. For example,

	   SPI_execute("SELECT * FROM foo", true, 5);

       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,

	   SPI_execute("INSERT INTO foo	SELECT * FROM bar", false, 5);

       inserts all rows	from bar, ignoring the count parameter.	However, with

	   SPI_execute("INSERT INTO foo	SELECT * FROM bar RETURNING *",	false, 5);

       at most 5 rows would be inserted, since execution would stop after the
       fifth RETURNING result row is retrieved.

       You can pass multiple commands in one string; SPI_execute returns the
       result for the command executed last. The count 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.

       When read_only is false,	SPI_execute 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.

       When read_only is true, SPI_execute does	not update either the snapshot
       or the command counter, and it allows only plain	SELECT 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.

       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.

       The actual number of rows for which the (last) command was executed is
       returned	in the global variable SPI_processed. 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 EXPLAIN) also return row sets, and
       SPI_tuptable will contain the result in these cases too.	Some utility
       commands	(COPY, CREATE TABLE AS)	don't return a row set,	so
       SPI_tuptable is NULL, but they still return the number of rows
       processed in SPI_processed.

       The structure SPITupleTable is defined thus:

	   typedef struct
	   {
	       MemoryContext tuptabcxt;	   /* memory context of	result table */
	       uint64	   alloced;	   /* number of	alloced	vals */
	       uint64	   free;	   /* number of	free vals */
	       TupleDesc   tupdesc;	   /* row descriptor */
	       HeapTuple  *vals;	   /* rows */
	   } SPITupleTable;

       vals is an array	of pointers to rows. (The number of valid entries is
       given by	SPI_processed.)	 tupdesc is a row descriptor which you can
       pass to SPI functions dealing with rows.	 tuptabcxt, alloced, and free
       are internal fields not intended	for use	by SPI callers.

       SPI_finish frees	all SPITupleTables allocated during the	current
       procedure. You can free a particular result table earlier, if you are
       done with it, by	calling	SPI_freetuptable.

ARGUMENTS
       const char * command
	   string containing command to	execute

       bool read_only
	   true	for read-only execution

       long count
	   maximum number of rows to return, or	0 for no limit

RETURN VALUE
       If the execution	of the command was successful then one of the
       following (nonnegative) values will be returned:

       SPI_OK_SELECT
	   if a	SELECT (but not	SELECT INTO) was executed

       SPI_OK_SELINTO
	   if a	SELECT INTO was	executed

       SPI_OK_INSERT
	   if an INSERT	was executed

       SPI_OK_DELETE
	   if a	DELETE was executed

       SPI_OK_UPDATE
	   if an UPDATE	was executed

       SPI_OK_INSERT_RETURNING
	   if an INSERT	RETURNING was executed

       SPI_OK_DELETE_RETURNING
	   if a	DELETE RETURNING was executed

       SPI_OK_UPDATE_RETURNING
	   if an UPDATE	RETURNING was executed

       SPI_OK_UTILITY
	   if a	utility	command	(e.g., CREATE TABLE) was executed

       SPI_OK_REWRITTEN
	   if the command was rewritten	into another kind of command (e.g.,
	   UPDATE became an INSERT) by a rule.

       On error, one of	the following negative values is returned:

       SPI_ERROR_ARGUMENT
	   if command is NULL or count is less than 0

       SPI_ERROR_COPY
	   if COPY TO stdout or	COPY FROM stdin	was attempted

       SPI_ERROR_TRANSACTION
	   if a	transaction manipulation command was attempted (BEGIN, COMMIT,
	   ROLLBACK, SAVEPOINT,	PREPARE	TRANSACTION, COMMIT PREPARED, ROLLBACK
	   PREPARED, or	any variant thereof)

       SPI_ERROR_OPUNKNOWN
	   if the command type is unknown (shouldn't happen)

       SPI_ERROR_UNCONNECTED
	   if called from an unconnected procedure

NOTES
       All SPI query-execution functions set both SPI_processed	and
       SPI_tuptable (just the pointer, not the contents	of the structure).
       Save these two global variables into local procedure variables if you
       need to access the result table of SPI_execute or another
       query-execution function	across later calls.

PostgreSQL 9.6.20		     2020			SPI_EXECUTE(3)

NAME | SYNOPSIS | DESCRIPTION | ARGUMENTS | RETURN VALUE | NOTES

Want to link to this manual page? Use this URL:
<https://www.freebsd.org/cgi/man.cgi?query=SPI_execute&sektion=3&manpath=FreeBSD+13.0-RELEASE+and+Ports>

home | help