SPI_MODIFYTUPLE(3)

💅 SPI_MODIFYTUPLE — bestie edition

okay so we're in the SPI_MODIFYTUPLE(3) girlies today, and this one is genuinely so useful. It's part of the PostgreSQL 15.19 docs, so you know it's legit — no cap.

---

✨ NAME ✨

SPI_modifytuple — makes a whole new row by swapping out selected fields of a row you already have 🎀

---

🌸 SYNOPSIS 🌸

HeapTuple SPI_modifytuple(Relation rel, HeapTuple row, int ncols,
                          int * colnum, Datum * values, const char * nulls)

---

🦋 DESCRIPTION 🦋

so the way this works: SPI_modifytuple builds you a brand new row by substituting new values for the columns you picked, and it just copies the original row's columns everywhere else. Periodt. 💅

the input row is not modified, bestie — she stays exactly as she was, untouched, main character energy 🫶

The new row is returned in the upper executor context. Slay.

lowkey important though: this function can only be used while connected to SPI. If you're not connected, it returns NULL and sets SPI_result to SPI_ERROR_UNCONNECTED. iykyk 😭

---

🧚 ARGUMENTS 🧚

• Relation rel — honestly it's used only as the source of the row descriptor for the row. That's it. (and the docs themselves are like "passing a relation rather than a row descriptor is a misfeature" 💀 so yeah, noted.)
• HeapTuple row — the row you wanna modify 🍓
• int ncols — how many columns you're changing
• int * colnum — an array of length ncols with the numbers of the columns being changed. and omg remember: column numbers start at 1 ⭐
• Datum * values — an array of length ncols with the new values for those columns you specified
• const char * nulls — an array of length ncols describing which of the new values are null

ok so for nulls, here's the tea:

• if nulls is NULL, then SPI_modifytuple assumes no new values are null. easy ✨
• otherwise, each entry of the nulls array should be ' ' if the corresponding new value is non-null, or 'n' if the corresponding new value is null. (in that null case, the actual value sitting in the corresponding values entry doesn't matter — she's irrelevant 💀)
• and crucially: nulls is not a text string, it's just an array — it does not need a '\0' terminator. no null terminator needed, we're built different 🙈

---

👑 RETURN VALUE 👑

a new row with the modifications, allocated in the upper executor context — or NULL on error (check SPI_result for an error indication, bestie).

when it errors out, SPI_result gets set like this:

💀 SPI_ERROR_ARGUMENT

if rel is NULL, or if row is NULL, or if ncols is less than or equal to 0, or if colnum is NULL, or if values is NULL.

like... that's a lot of ways to be NULL bestie, pls 😭

💀 SPI_ERROR_NOATTRIBUTE

if colnum contains an invalid column number (less than or equal to 0, or greater than the number of columns in row).

💀 SPI_ERROR_UNCONNECTED

if SPI is not active. (remember the whole "must be connected to SPI" thing? yeah. that. 🔥)

---

and that's the whole page, gorgeous — go modify some tuples 💜

mwah 💋 this has been your girl, signing off — page pulled straight from PostgreSQL 15.19, dated 2026, and honestly? iconic. periodt. 🎀✨