Postgres is a relational database known for its scalability, reliability, and ease of use. It's one of the most popular databases in the world and one of the reasons for its popularity is the ability to extend the database with custom functions, data types and more using the Postgres Extensions API.

In this post, we'll learn to build a Postgres extension from scratch touching upon all the core concepts. Some knowledge of C, Docker and Postgres is preferred but not required.

At its core, a Postgres extension is a collection of 4 files:

1. The .c source code file that contains the main business logic for the extension.
2. A .sql file that contains the SQL code for the extension.
3. A .control file that serves as a manifest and defines the extension name, version, and dependencies.
4. A Makefile that defines how to build the extension.

The C source file

This is our main source code for the extension. We'll organize our source files in a src/ directory to keep things clean. Let's build up our simple "hello world" function by understanding each component first.

Every postgres extension starts with including the necessary header files. We need three essential headers:

• postgres.h - contains core data types and macros that every extension needs.
• fmgr.h - handles the Function Manager interface, defining macros like PG_FUNCTION_ARGS and PG_RETURN_* for function communication.
• utils/builtins.h - provides utility functions including cstring_to_text() for converting C strings to Postgres's text type.

1
2
3
#include "postgres.h"
#include "fmgr.h"
#include "utils/builtins.h"

Next, we need to include a mandatory macro that serves as a version check containing information about the Postgres version, architecture, and ABI compatibility details. Without it, Postgres will refuse to load your extension.

1
PG_MODULE_MAGIC;

Before defining our function, we need to register it with Postgres using a macro that declares metadata like the function name and calling convention. Think of it as registering your function with Postgres's function registry.

1
PG_FUNCTION_INFO_V1(hello_world);

The function name hello_world here must exactly match the function name you'll define in your SQL file and the actual C function name below.

Finally we define our function. Our function returns a Datum (Postgres's universal data type) and takes PG_FUNCTION_ARGS as input, which contains all function arguments and metadata. Inside the function, cstring_to_text() converts our C string to Postgres's internal text type, and PG_RETURN_TEXT_P() properly returns the text value.

1
2
3
4
Datum hello_world(PG_FUNCTION_ARGS)
{
    PG_RETURN_TEXT_P(cstring_to_text("Hello from hello world extension!"));
}

Putting it all together, here's our completed src/hello_world.c file

1
2
3
4
5
6
7
8
9
10
11
12
#include "postgres.h"
#include "fmgr.h"
#include "utils/builtins.h"

PG_MODULE_MAGIC;

PG_FUNCTION_INFO_V1(hello_world);

Datum hello_world(PG_FUNCTION_ARGS)
{
    PG_RETURN_TEXT_P(cstring_to_text("Hello from hello world extension!"));
}

When someone calls SELECT hello_world();, Postgres looks up the function metadata, calls our C function with the standard interface, creates a Postgres text object from our string, and returns it to the user. This demonstrates the fundamental pattern all Postgres extensions follow: receive arguments through the standard interface, process them using Postgres's data types, and return results using Postgres's macros.

The SQL File

The SQL file defines the SQL interface for your extension. It creates the function signatures that map to your C functions. We'll place all SQL files in a sql/ directory to keep them organized. Let's build up our SQL file step by step.

First, we add a safety mechanism to prevent the SQL file from being executed directly

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

This prevents the SQL file from being executed directly with psql. It should only be run through CREATE EXTENSION.

Next, we define the basic function signature:

1
2
CREATE OR REPLACE FUNCTION hello_world()
RETURNS text

CREATE OR REPLACE FUNCTION defines the SQL function signature, and RETURNS text specifies that the function returns a text value.

Now we link our SQL function to the C function:

1
2
AS 'MODULE_PATHNAME', 'hello_world'
LANGUAGE C

AS 'MODULE_PATHNAME', 'hello_world' links the SQL function to the C function. MODULE_PATHNAME is replaced by the path from the control file. LANGUAGE C indicates this is a C function.

Note: The second 'hello_world' parameter must exactly match your C function name and the name used in PG_FUNCTION_INFO_V1(). The MODULE_PATHNAME is automatically replaced with the module_pathname value from your control file.

Finally, we add performance optimization hints:

1
IMMUTABLE STRICT;

IMMUTABLE means the function always returns the same result for the same input, and STRICT means it returns NULL if any argument is NULL.

Here's our complete sql/hello_world--0.0.1.sql file:

Note: The SQL file name follows the pattern {extension_name}--{version}.sql. The hello_world part must match the EXTENSION name in your Makefile and the control file name. The 0.0.1 version must match the default_version in your control file.

1
2
3
4
5
6
7
-- complain if script is sourced in psql, rather than via CREATE EXTENSION
\echo Use "CREATE EXTENSION hello_world" to load this file. \quit

CREATE OR REPLACE FUNCTION hello_world()
RETURNS text
AS 'MODULE_PATHNAME', 'hello_world'
LANGUAGE C IMMUTABLE STRICT;

The Control File

The control file serves as a manifest for your extension. It contains metadata that Postgres uses to understand how to install, manage, and interact with your extension. Let's build up our control file step by step.

First, we need to provide a description and version for our extension:

1
2
3
# hello_world extension
comment = 'Simple hello world extension for Postgres'
default_version = '0.0.1'

The comment provides a human-readable description of what your extension does, while default_version specifies the version to install when no version is specified.

Next, we tell Postgres where to find our compiled shared library:

1
module_pathname = '$libdir/hello_world'

module_pathname points to the compiled shared library. $libdir is a Postgres variable that points to the library directory.

Finally, we configure how the extension behaves in different schemas:

1
relocatable = true

relocatable determines whether the extension can be moved to a different schema after installation.

Here's our complete hello_world.control file:

Note: The control file name must be {extension_name}.control. The hello_world part must exactly match the EXTENSION variable in your Makefile.

1
2
3
4
5
# hello_world extension
comment = 'Simple hello world extension for PostgreSQL'
default_version = '0.0.1'
module_pathname = '$libdir/hello_world'
relocatable = true

Note: The default_version = '0.0.1' must match the version in your SQL file name (hello_world--0.0.1.sql). The module_pathname = '$libdir/hello_world' should use the same name as your MODULE_big variable in the Makefile.

The Makefile

The Makefile defines how to build the extension. Let's build it up step by step to understand each component.

First, we define the basic identity of our extension:

1
2
EXTENSION = hello_world
MODULE_big = hello_world

EXTENSION is the name of the extension and MUST match the .control file name. MODULE_big is the name of the shared library that will be created.

Note: EXTENSION = hello_world must exactly match your control file name (hello_world.control) and the prefix of your SQL file (hello_world--0.0.1.sql). MODULE_big = hello_world should match the library name in your control file's module_pathname.

Next, we specify which files should be included in the build:

1
2
DATA = sql/hello_world--0.0.1.sql
OBJS = src/hello_world.o

DATA lists the SQL files that should be installed with the extension. OBJS specifies the object files that need to be compiled and linked.

Note: DATA = sql/hello_world--0.0.1.sql must point to your actual SQL file location and name. OBJS = src/hello_world.o will be compiled from src/hello_world.c - the .c extension becomes .o during compilation.

Now we set up the build configuration:

1
PG_CONFIG = pg_config

PG_CONFIG is the path to the pg_config command, which provides build configuration information.

Finally, we include the Postgres build system:

1
2
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)

PGXS is the Postgres Extension Building Infrastructure that provides all the build rules and targets we need.

Here's our complete Makefile:

1
2
3
4
5
6
7
EXTENSION = hello_world
MODULE_big = hello_world
DATA = sql/hello_world--0.0.1.sql
OBJS = src/hello_world.o
PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)

Local Development with Docker

Instead of installing Postgres development packages locally, we'll use Docker to ensure a consistent build environment. We'll use the official Postgres Docker image and build our extension in a container.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
ARG PG_MAJOR=17
FROM postgres:$PG_MAJOR
ARG PG_MAJOR

COPY . /tmp/pg_hello_world
WORKDIR /tmp/pg_hello_world

RUN apt-get update && \
    apt-get install -y --no-install-recommends \
            build-essential \
            postgresql-server-dev-$PG_MAJOR && \
    make clean && \
    make OPTFLAGS="" && \
    make install && \
    chmod -R 777 /tmp/pg_hello_world

This Dockerfile starts with the official PostgreSQL image, installs the necessary build tools and development headers, then compiles and installs our extension using the PGXS build system. We keep the build tools and source code available for testing and development.

Now that we have all our files, here's what our project structure looks like

1
2
3
4
5
6
7
8
hello_world/
├── src/
│   └── hello_world.c
├── sql/
│   └── hello_world--0.0.1.sql
├── hello_world.control
├── Makefile
└── Dockerfile

To test our extension locally, we'll use Docker Compose. Create a docker-compose.yml file in your project root:

1
2
3
4
5
6
7
8
9
10
services:
  postgres:
    build: .
    restart: always
    shm_size: 128mb
    environment:
      POSTGRES_USER: root
      POSTGRES_PASSWORD: password
    volumes:
      - ./:/tmp/pg_hello_world

This configuration builds our custom Postgres image with the hello_world extension and sets up the database with a password. Run the following command to start the container

1
2
3
4
5
6
7
8
# Build and start the container
docker compose up -d --build

# Check if the container is running
docker compose ps

NAME                   IMAGE                COMMAND                  SERVICE    CREATED          STATUS          PORTS
pghashlib-postgres-1   pghashlib-postgres   "docker-entrypoint.s…"   postgres   14 seconds ago   Up 14 seconds   5432/tcp

Now we can connect to the database and test our extension.

1
2
3
4
5
6
7
8
# Connect to the database
docker compose exec postgres psql

# Create the extension
CREATE EXTENSION hello_world;

# Test the function
SELECT hello_world();

You should see the following output

1
2
3
4
            hello_world            
-----------------------------------
 Hello from hello world extension!
(1 row)

When you're done testing, you can stop and remove the containers

1
2
# Stop the containers
docker compose down

Writing Tests

Testing is crucial for extension development since bugs can crash the entire PostgreSQL server. PostgreSQL provides pg_regress, a robust testing framework used by PostgreSQL itself and the standard way to test extensions.

For our hello_world extension, we'll create test files in a dedicated tests/ directory:

1
2
3
4
5
6
7
8
9
10
11
12
13
hello_world/
├── src/
│   └── hello_world.c
├── sql/
│   └── hello_world--0.0.1.sql
├── tests/
│   ├── sql/
│   │   └── hello_world.sql      # Test SQL commands
│   └── expected/
│       └── hello_world.out      # Expected output
├── hello_world.control
├── Makefile
└── Dockerfile

Create the test SQL file tests/sql/hello_world.sql:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- Test basic functionality
SELECT hello_world();

-- Test function properties
SELECT 
    proname,
    provolatile,
    proisstrict
FROM pg_proc 
WHERE proname = 'hello_world';

-- Test extension metadata
SELECT 
    extname,
    extversion
FROM pg_extension 
WHERE extname = 'hello_world';

Generate the expected output by running the test manually and saving the output to tests/expected/hello_world.out:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
-- Test basic functionality
SELECT hello_world();
            hello_world            
-----------------------------------
 Hello from hello world extension!
(1 row)

-- Test function properties
SELECT 
    proname,
    provolatile,
    proisstrict
FROM pg_proc 
WHERE proname = 'hello_world';
   proname   | provolatile | proisstrict 
-------------+-------------+-------------
 hello_world | i           | t
(1 row)

-- Test extension metadata
SELECT 
    extname,
    extversion
FROM pg_extension 
WHERE extname = 'hello_world';
   extname   | extversion 
-------------+------------
 hello_world | 0.0.1
(1 row)

The updated Makefile includes several improvements over the basic configuration. The new test configuration automatically discovers test files, loads the extension automatically, and provides better error reporting:

Update your Makefile to include the test configuration:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
EXTENSION = hello_world
MODULE_big = hello_world
DATA = sql/hello_world--0.0.1.sql
OBJS = src/hello_world.o
PG_CONFIG = pg_config

# Test configuration
TESTS = $(wildcard tests/sql/*.sql)
REGRESS = $(patsubst tests/sql/%.sql,%,$(TESTS))
REGRESS_OPTS = --inputdir=tests --load-extension=$(EXTENSION)

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

# Custom test target that shows diffs on failure
test:
	@$(MAKE) --silent installcheck || (echo "=== DIFF ===" && cat regression.diffs 2>/dev/null && exit 1)

.PHONY: test

Let's break down what each new line does:

• TESTS = $(wildcard tests/sql/*.sql) - Automatically discovers all .sql files in the tests/sql/ directory, making it easy to add new tests without modifying the Makefile
• REGRESS = $(patsubst tests/sql/%.sql,%,$(TESTS)) - Converts the full file paths to test names by removing the directory prefix and .sql extension
• --load-extension=$(EXTENSION) - Automatically loads your extension before running tests, eliminating the need for manual CREATE EXTENSION commands in test files
• The custom test target makes it easier to run tests and see the diffs on failure.

Running Tests with Docker

You can run the pg_regress tests in your Docker environment by building and installing the extension first:

1
2
3
4
5
# Build and start the extension 
docker compose up -d --build

# Run the regression tests
docker compose exec postgres make installcheck

The make installcheck command will run your test SQL files against the installed extension and compare the output with your expected results. If everything is working, you should see the following output:

1
2
3
4
5
6
echo "# +++ regress install-check in  +++" && /usr/lib/postgresql/17/lib/pgxs/src/makefiles/../../src/test/regress/pg_regress --inputdir=./ --bindir='/usr/lib/postgresql/17/bin'    --inputdir=tests --load-extension=hello_world --dbname=contrib_regression hello_world
# +++ regress install-check in  +++
# using postmaster on Unix socket, default port
ok 1         - hello_world                                 7 ms
1..1
# All 1 tests passed.

What's Next?

Creating Postgres extensions opens up endless possibilities for extending your database's capabilities. From simple utility functions to complex data types and operators, extensions allow you to implement performance-critical functionality directly in the database server.

The pattern we've explored - C functions wrapped in SQL interfaces, managed through control files and built with PGXS - forms the foundation for all Postgres extensions. Whether you're building a simple function or a complex full-text search engine, these same principles apply.

In a future post, we'll explore how to build more complex extensions for real world use cases and also explore pgrx, a tool for building Postgres extensions in Rust.

Additional Resources

• Percona Blog - Writing Postgres Extensions in C
• Cybertec - Easy Guide to Writing Postgres Extensions
• Postgres Extension Tutorial by Ishaan Adarsh