How to Use PostgreSQL SPI for Advanced Internal SQL Operations

PostgreSQL isn’t just a SQL database, it’s a full-blown development platform. One of the most powerful, yet lesser-known tools in PostgreSQL is the SPI, the Server Programming Interface.

If you’re a PostgreSQL extension developer, systems engineer, or performance hacker, this post will walk you through exactly how to use SPI, and even better, how to create your first working SPI-based extension.

What is SPI?

SPI (Server Programming Interface) is an internal API in PostgreSQL that allows C functions to execute SQL statements inside the PostgreSQL server process.

Think of it like this:

You’re writing native C code, but you still get to run SQL queries just like any other user!

This is incredibly useful when:

• You want maximum performance (faster than plpgsql)
• You need low-level control.
• You want to build native extensions.

What You’ll Build

You’ll create a PostgreSQL extension using SPI that logs every time it’s called by inserting into a table using a SQL query executed from C.

Here’s what we’ll cover:

• Project structure
• C code using SPI
• SQL installation script
• Makefile
• Compiling and testing

Directory Structure

Create a folder like this:

my_spi_ext/
+-- my_spi_ext.c
+-- my_spi_ext.control
+-- my_spi_ext--1.0.sql
+-- Makefile

Step 1: C Code with SPI

my_spi_ext.c

#include "postgres.h"
#include "fmgr.h"
#include "executor/spi.h"
#include "utils/builtins.h"
PG_MODULE_MAGIC;
PG_FUNCTION_INFO_V1(log_call);
Datum
log_call(PG_FUNCTION_ARGS)
{
    int ret;
    if (SPI_connect() != SPI_OK_CONNECT)
        elog(ERROR, "SPI_connect failed");
    const char *sql = "INSERT INTO spi_log(event_time, note) VALUES (now(), 'Function called from C via SPI')";
    
    ret = SPI_execute(sql, false, 0);
    if (ret != SPI_OK_INSERT)
        elog(ERROR, "SPI_execute failed");
    SPI_finish();
    PG_RETURN_TEXT_P(cstring_to_text("Call logged via SPI!"));
}

Step 2: Control File

my_spi_ext.control

comment = 'Simple SPI-based PostgreSQL extension'
default_version = '1.0'
relocatable = true
module_pathname = '$libdir/my_spi_ext'

Step 3: SQL Installation Script

my_spi_ext--1.0.sql

CREATE TABLE IF NOT EXISTS spi_log (
    id serial PRIMARY KEY,
    event_time timestamp,
    note text
);
CREATE FUNCTION log_call()
RETURNS text
AS 'my_spi_ext', 'log_call'
LANGUAGE C STRICT;

Step 4: Makefile

Makefile

EXTENSION = my_spi_ext
MODULES = my_spi_ext
DATA = my_spi_ext--1.0.sql
PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)

Step 5: Build and Install

1. Compile the extension

Inside your my_spi_ext/ directory:

make
sudo make install

2. Load it in PostgreSQL

-- Connect to your database first
CREATE EXTENSION my_spi_ext;
SELECT log_call();
SELECT * FROM spi_log;

What Just Happened?

• You created a C function inside PostgreSQL.
• That function used SPI_connect / SPI_execute to run SQL
• PostgreSQL executed that SQL inside its own server process, with maximum speed.

Access Table Data Dynamically

With SPI_execute, you can inspect rows, iterate over SPI_tuptable, and access columns like:

#include "postgres.h"
#include "fmgr.h"
#include "executor/spi.h"
#include "utils/builtins.h"
PG_MODULE_MAGIC;
PG_FUNCTION_INFO_V1(log_user_emails);
Datum
log_user_emails(PG_FUNCTION_ARGS)
{
    int ret;
    int proc;
    // Start SPI session
    if ((ret = SPI_connect()) != SPI_OK_CONNECT)
        elog(ERROR, "SPI_connect failed: error code %d", ret);
    // Execute your query
    ret = SPI_execute("SELECT email FROM user_login_events", true, 0);
    if (ret != SPI_OK_SELECT)
        elog(ERROR, "SPI_execute failed: error code %d", ret);
    proc = SPI_processed;
    if (proc > 0) {
        SPITupleTable *tuptable = SPI_tuptable;
        TupleDesc tupdesc = tuptable->tupdesc;
        for (int i = 0; i < proc; i++) {
            HeapTuple tuple = tuptable->vals[i];
            char *email = SPI_getvalue(tuple, tupdesc, 1);  // column index = 1
            if (email != NULL)
                elog(INFO, "Login by: %s", email);
            else
                elog(INFO, "Login by: NULL email");
        }
    } else {
        elog(INFO, "No login events found.");
    }
    SPI_finish();  // End SPI session
    PG_RETURN_VOID();
}

Useful for building analyzers, hooks, and decision engines!

Why Use SPI Over PL/pgSQL?

What Can You Build with SPI?

• Native Caching System
• Row-Level Auditing

• Custom Optimizer Hints
• Triggers with External Hooks
• Real-Time Queue Systems
• AI-Powered Query Advisors (via PL/Python + SPI)

SPI turns PostgreSQL from just a database into a full platform. With it, you can build fast, intelligent, native features that go far beyond SQL. Whether you’re building a cache, a policy engine, or a machine learning integration, SPI is your tool for total control.

PostgreSQL SPI doesn’t just run queries, it runs innovation.