SPI_EXECUTE(3)

📦 version 14.24, in opensuse-leap 16.0

💅 SPI_EXECUTE(3) — the PostgreSQL one, bestie ✨

okay so you're in the PostgreSQL 14.24 docs, specifically the C function section, and we're about to talk about SPI_execute 🎀 this is the one that actually runs your SQL from inside a C function, and honestly? iconic.

🌸 NAME 🌸

SPI_execute — execute a command (yes bestie, literally just run the SQL, that's the whole vibe) 🔥

✨ SYNOPSIS ✨

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

that's the signature, don't @ me, copy it exactly 🫶

🦋 DESCRIPTION 🦋

so SPI_execute executes the SQL command you give it, for count rows. if read_only is true, the command must be read-only (no sneaky writes, bestie 💀), and the execution overhead is somewhat reduced — which is giving efficiency queen energy ngl.

this function can only be called from a connected C function. not connected? not happening. SPI_ERROR_UNCONNECTED is waiting for you 🙈

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);

this 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. the limit said "not today" 💀. 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. RETURNING really said "I'll make the limit work" and slayed 💅

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. rules do what they want, no cap.

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. consistency is the moment ✨

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. stable queen behavior 👑

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. mixing them is giving chaos and we don't stan chaos 🙅‍♀️

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. so even when there's no row set, the count still pulls up 😌

the structure SPITupleTable is defined thus:

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;

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 SPI_processed). tupdesc is a row descriptor which you can pass to SPI functions dealing with rows. so tupdesc, vals, numvals are the public besties, the rest are private and we respect their privacy 🫶

SPI_finish 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 SPI_freetuptable. freeing early is self-care ✨

🎀 ARGUMENTS 🎀

  • const char * command — string containing command to execute. yes, the SQL goes here, bestie.
  • bool read_only — true for read-only execution. no writes allowed when this is on, periodt.
  • long count — maximum number of rows to return, or 0 for no limit. 0 means "give me everything" 💖

👑 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. rules said "plot twist" 💀

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. not here, bestie 🚫
  • 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 — but if it does, something is deeply wrong 💀)
  • SPI_ERROR_UNCONNECTED — if called from an unconnected C function. connect first, babes 🫶

⭐ 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 C function variables if you need to access the result table of SPI_execute or another query-execution function across later calls. don't lose your globals, bestie, they're fragile 💖

fr fr, the way that this function carries the whole SPI execution game... it's giving main character energy and we love to see it 🌟

PostgreSQL 14.24, dated 2026 — that's the source and version, bestie, and honestly this man page ate and left no crumbs 💅✨🎀🦋💖🌸🫶🔥👑💜

📦 where this page comes from