fmgr-and-spilisted

Write a SQL-callable C function or call PostgreSQL fmgr / SPI from C — covers PG_FUNCTION_INFO_V1, PG_GETARG_* / PG_RETURN_*, PG_ARGISNULL, SRF_* set-returning function ValuePerCall and Materialize modes, composite / polymorphic returns, DirectFunctionCall* / OidFunctionCall* / FunctionCallInvoke fmgr entry points, plus SPI_connect / SPI_execute / SPI_prepare / SPI_finish, plan caching, SPI cursors, subxact rollback, and SPI return codes. Use whenever a PG patch or extension adds a `Datum foo(PG_FUNCTION_ARGS)` entry point, exposes a set-returning function, calls fmgr from backend C, or embeds SQL via SPI in a backend / trigger / PL handler. Skip for plpgsql / PL/Python / PL/Perl user-side function authoring, libpq / psycopg / JDBC / node-postgres client-side query execution, generic executor questions (use executor-and-planner), and non-PG embedded SQL (Oracle OCI, SQLite C API).
matejformanek/postgres-claude · ★ 0 · API & Backend · score 70

Install: claude install-skill matejformanek/postgres-claude

# fmgr and SPI — operational playbook The fmgr is how every SQL-callable C function in PostgreSQL gets invoked. SPI is how that same C code runs SQL back into the executor. They sit adjacent in any non-trivial extension or PL. All confidence tags are `[verified-by-code]` unless noted. --- ## 1. Writing a SQL-callable C function (fmgr V1) V0 ("old style") was removed; V1 is the only supported convention. [verified-by-code] `src/include/fmgr.h:382-394` ("Version-0 ... is not supported anymore. Version 1 is the call convention defined in this header file"). ### 1.1 Minimum boilerplate ```c #include "postgres.h" #include "fmgr.h" PG_MODULE_MAGIC; /* exactly once per .so */ PG_FUNCTION_INFO_V1(my_func); /* once per exported func */ Datum my_func(PG_FUNCTION_ARGS) { int32 a = PG_GETARG_INT32(0); text *t = PG_GETARG_TEXT_PP(1); /* ... */ PG_RETURN_INT32(a + VARSIZE_ANY_EXHDR(t)); } ``` `PG_FUNCTION_ARGS` expands to `FunctionCallInfo fcinfo` [verified-by-code] `src/include/fmgr.h:193`. `PG_FUNCTION_INFO_V1(name)` defines `pg_finfo_<name>()` returning `{ api_version = 1 }`, marks the function `PGDLLEXPORT`, and (importantly) emits an extern declaration of the C function so you don't have to [verified-by-code] `src/include/fmgr.h:417-426`. `PG_MODULE_MAGIC` (or `PG_MODULE_MAGIC_EXT(.name=..., .version=...)`) must appear exactly once in a multi-source-file module [verified-by-code] `src/include/fmgr.h:441-549