skills.rest
matejformanek
matejformanekCOMMUNITY
Explore all skills by this author →
GitHub

fmgr-and-spi

Implement SQL-callable C functions and SPI-based SQL execution in PostgreSQL.

Updated Jun 1, 2026
Search Tags:#postgresql#spi#backend-development#c-extension#database-internals#fmgr
One-click install
npx skills add https://github.com/matejformanek/postgres-claude --skill fmgr-and-spi
Or copy as Structured Prompt for Agent▼
Please help me install this Agent Skill.
Skill: fmgr-and-spi
Source: https://github.com/matejformanek/postgres-claude/tree/main/.claude/skills/fmgr-and-spi
Command: npx skills add https://github.com/matejformanek/postgres-claude --skill fmgr-and-spi

SYSTEM DOCUMENTATION & REQUIREMENTS

💡 This Skill includes references (resource) components.

What problem does it solve?

This skill addresses the complexity of writing safe, performant, and correct C-language extensions for PostgreSQL, specifically handling the intricacies of the Function Manager (fmgr) and the Server Programming Interface (SPI).

Core Features & Use Cases

  • SQL-Callable C Functions: Provides the boilerplate and safety rules for implementing V1-style C functions, including argument extraction, NULL handling, and detoasting.
  • SPI Integration: Offers a robust playbook for executing SQL queries from within backend C code, including plan caching, cursor management, and subtransaction handling.
  • Use Case: Use this skill when developing custom PostgreSQL extensions that require high-performance C logic, complex data type manipulation, or embedded SQL execution within triggers and procedures.

Quick Start

Use the fmgr-and-spi skill to generate the boilerplate for a new SQL-callable C function that performs a complex calculation using SPI to query internal system catalogs.

Frequently Asked Questions about fmgr-and-spi

High-intent search queries and answers about installing and using this skill.

FAQPage Schema
How do I write a SQL-callable C function in PostgreSQL using fmgr?▼

To write a SQL-callable C function in PostgreSQL using fmgr, implement a V1-style function following safety rules for argument extraction, NULL handling, and detoasting. This provides the necessary boilerplate for safe, high-performance extension development.

What is the Server Programming Interface (SPI) used for in PostgreSQL backend development?▼

The Server Programming Interface (SPI) is used for executing SQL queries from within PostgreSQL backend C code. It provides a robust playbook for plan caching, cursor management, and subtransaction handling within custom database extensions.

How do I manage memory and transactions when executing SQL queries from C extensions?▼

When executing SQL queries from C extensions, manage memory and transactions using SPI patterns for plan caching and subtransaction handling. This ensures safe interaction with the PostgreSQL executor and maintains query lifecycle integrity.

When do I need to use C extensions instead of PL/pgSQL for PostgreSQL backend development?▼

You need C extensions for PostgreSQL backend development when requiring high-performance C logic, complex data type manipulation, or embedded SQL execution within triggers and procedures that exceed standard PL/pgSQL capabilities.

Does this SPI integration approach support plan caching and cursor management?▼

Yes, this SPI integration approach supports plan caching, cursor management, and subtransaction handling. These features facilitate complex SQL execution from within backend C code while maintaining safe interaction with the PostgreSQL query executor.

Related Skills

Explore More Software Engineering Skills→
preedeppreedep

postgres-fts

Build and test the kham-pg C extension against a local PostgreSQL instance.

Community
Advanced
Serg28Serg28

postgres-pro

Analyze PostgreSQL query performance and configure extensions and replication.

Community
Advanced
constructive-ioconstructive-io
52

orm-objects

Provides a type-safe ORM client for CRUD and custom queries on PostgreSQL.

Official
Intermediate
jeremylongshorejeremylongshore
2.6k

generating-stored-procedures

Generate stored procedures, functions, and triggers for PostgreSQL, MySQL, and SQL Server.

Community
Intermediate