Building a simple Postgres Extension in C

May 8, 2025

Postgres offers powerful extensibility features that allow developers to enhance its functionality. While building extensions in PL/pgSQL is the simplest approach, creating extensions in C provides superior performance and additional capabilities.

This guide will walk you through creating a basic C extension for Postgres that converts text to title case format.

Required Files

To build a C extension for Postgres, you’ll need:

Control file - Specifies extension metadata and loading instructions
SQL file - Defines the interface between Postgres and your C code
C source file - Contains the implementation code
Makefile - Handles compilation and installation

Additionally, two helpful files:

setup.sql - Installs the extension
test.sql - Verifies the extension works correctly

Prerequisites

Postgres installed locally
C compiler (gcc in this example)
Basic understanding of C programming

The Control File

Create a file named c_make_title.control:

comment = 'Convert Text to Title Format'
default_version = '1.2'
module_pathname = '$libdir/c_make_title'
relocatable = true

This file defines:

A description of the extension
The current version number
Where Postgres will find the compiled module
Whether the extension can be relocated across schemas

The SQL Interface File

Create c_make_title--1.2.sql (matching the version specified in the control file):

-- complain if script is sourced in psql, rather than via CREATE EXTENSION
\echo Use "CREATE EXTENSION c_make_title" to load this file. \quit

-- convert_to_title will take a string and convert the initial characters of each word to a capital letter
CREATE FUNCTION c_convert_to_title(inp text) RETURNS text
AS 'MODULE_PATHNAME', 'c_convert_to_title'
LANGUAGE c STRICT VOLATILE;

This SQL file creates a function that:

Takes text as input and returns text
Links to the C implementation
Specifies that the function is implemented in C

The C Implementation

Create c_make_title.c:

/* -------------------------------------------------
 * c_make_title.c
 * -------------------------------------------------
 */
#include "postgres.h"
#include "utils/builtins.h"
#include "c.h"

#include <stdio.h>
#include <ctype.h>
#include <string.h>

#include "fmgr.h"
#include "varatt.h"

/**
 * to_title_case will convert a string to title case, where the first letter of
 * each word is uppercase and the rest are lowercase.
 *
 * @param str The string to convert (modified in-place)
 * @param len Length of the string
 */
static void to_title_case(char* str, int32 len) {
    int new_word = 1;  // Flag to track word boundaries
    int i;

    for (i = 0; i < len; i++) {
        if (isspace(str[i]) || ispunct(str[i])) {
            new_word = 1;  // Next character will be the start of a new word
            str[i] = str[i];
        } else {
            if (new_word) {
                str[i] = toupper(str[i]);  // Convert first letter of word to uppercase
                new_word = 0;
            } else {
                str[i] = tolower(str[i]);  // Convert rest of letters to lowercase
            }
        }
    }
}

PG_MODULE_MAGIC;

PG_FUNCTION_INFO_V1(c_convert_to_title);

Datum
c_convert_to_title(PG_FUNCTION_ARGS)
{
    text  *arg1 = PG_GETARG_TEXT_PP(0);             // extract arg 0
    int32 arg1_size = VARSIZE_ANY_EXHDR(arg1);      // get length
    int32 new_text_size = arg1_size + VARHDRSZ;     // calc len for return value
    text *new_text = (text *) palloc(new_text_size); // allocate for return

    SET_VARSIZE(new_text, new_text_size);           // Set len for return
    memcpy(VARDATA(new_text), VARDATA_ANY(arg1), arg1_size); // copy in data

    to_title_case(VARDATA(new_text), arg1_size);    // modify string to be title

    PG_RETURN_TEXT_P(new_text);
}

Important C Programming Considerations

When writing C extensions for Postgres, remember these key points:

String Handling Differences
- C uses null-terminated strings
- Postgres uses length-prefixed string formats
- Don’t use standard C string functions directly on Postgres strings
- Converting between formats requires careful memory management
Memory Management
- Always use palloc()/pfree() instead of malloc()/free()
- Postgres has its own optimized memory allocation system

The Makefile

Create a Makefile:

#
PGFILEDESC = "C Extension to convert a text to title format"
EXTENSION = c_make_title
MODULE_big = c_make_title
DATA = c_make_title--1.2.sql
OBJS = c_make_title.o

PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)

This Makefile:

Describes your extension
Specifies the extension name
Defines the module to compile
Lists the SQL and object files
Uses Postgres’s build system for compilation

Before using the Makefile, verify:

GNU make is installed (brew install make on macOS)
Postgres is properly configured (pg_config --version)

Building and Installing

Compile the code:
```
$ make
```
Install the extension:
```
$ make install
```
Note: On some systems, you may need elevated permissions for this step.

Create a setup file (setup.sql):

DROP EXTENSION IF EXISTS c_make_title;
CREATE EXTENSION IF NOT EXISTS c_make_title;

Install the extension in your database:
```
$ psql -f setup.sql
```

Testing the Extension

Test with a simple query:

SELECT c_convert_to_title('a title for something');

Expected output:

   c_convert_to_title
------------------------
 A Title For Something

For comprehensive testing, create a test.sql file:

DO $$
DECLARE
    l_txt text;
    n_err int;
    l_msg text;
BEGIN
    n_err = 0;

    SELECT c_convert_to_title('12345')
        INTO l_txt;
    IF l_txt != '12345' THEN
        RAISE NOTICE 'Failed to convert 12345 to 12345';
        n_err = n_err + 1;
    END IF;

    SELECT c_convert_to_title('A Title')
        INTO l_txt;
    IF l_txt != 'A Title' THEN
        RAISE NOTICE 'Failed to convert "A Title" to "A Title"';
        n_err = n_err + 1;
    END IF;

    SELECT c_convert_to_title('a title')
        INTO l_txt;
    IF l_txt != 'A Title' THEN
        RAISE NOTICE 'Failed to convert "a title" to "A Title"';
        n_err = n_err + 1;
    END IF;

    IF n_err = 0 THEN
        RAISE NOTICE 'PASS';
    ELSE
        RAISE NOTICE 'FAIL';
    END IF;
END;
$$ LANGUAGE plpgsql;

Run the test:

$ psql -f test.sql

Source Code

The complete code for this extension is available on GitHub at https://github.com/pschlump/pgextension02.