How to Create a PostgreSQL Extension in C

How to Create a PostgreSQL Extension in C

PostgreSQL is a powerful, open-source relational database management system that offers a wide range of features. One of the most powerful features of PostgreSQL is its extensibility. By creating custom extensions, you can tailor the database to your needs and optimize its performance. In this article, we’ll take a hands-on approach to creating a simple PostgreSQL extension using C, and we’ll also explore why creating custom extensions is so important.

Why PostgreSQL Extensions Are Important

PostgreSQL extensions allow you to extend the database’s capabilities by adding new functions, types, and even entire modules. The key benefit of writing extensions in C is the performance. C is the native language for PostgreSQL’s internal operations, so writing extensions in C ensures that they run with minimal overhead.

• High Performance: C allows for direct, efficient access to system resources, which is crucial when dealing with large datasets or complex computations.
• Customization: You can implement algorithms or logic that is not available by default in PostgreSQL.
• Integration: C extensions can seamlessly integrate with PostgreSQL, providing direct memory access and minimizing latency.

In this tutorial, we will walk through the process of creating a simple integer subtraction function using C and PostgreSQL. We’ll demonstrate the steps involved in creating the extension and how you can start experimenting with it in your own database.

Step 1: Prepare the Environment

Before we start coding, we need to set up the environment. Follow these steps to install the necessary tools and libraries.

Install PostgreSQL development libraries: To compile the C code, we need PostgreSQL development headers and libraries.

dnf install -y postgresql13-devel

Install build tools: We also need the required build tools like make and gcc.

dnf install -y make gcc-c++ #redhat-rpm-config

Add PostgreSQL binaries to your PATH: Ensure that the correct version of PostgreSQL is accessible from the terminal.

export PATH=/usr/pgsql-13/bin:$PATH

Step 2: Create the Extension Files

Now we will create the files required for the extension: an SQL file, a C source file, and some configuration files.

int_subtract--0.0.1.sql

This SQL file defines the function signature that will be used in PostgreSQL.

cat int_subtract--0.0.1.sql
---------------------------

CREATE FUNCTION int_subtract(int4, int4)
RETURNS int4
AS '$libdir/int_subtract', 'int_subtract'
LANGUAGE C IMMUTABLE STRICT;

int_subtract.c

This C file contains the implementation of the int_subtract function, which simply subtracts two integers.

cat int_subtract.c
------------------

#include "postgres.h"
#include "fmgr.h"

PG_MODULE_MAGIC;
PG_FUNCTION_INFO_V1(int_subtract);
Datum int_subtract(PG_FUNCTION_ARGS) {
    int32 arg1 = PG_GETARG_INT32(0);  
    int32 arg2 = PG_GETARG_INT32(1);  
    PG_RETURN_INT32(arg1 - arg2);     

int_subtract.control

This file contains metadata about the extension, such as its version and description.

cat int_subtract.control
------------------------

comment = 'PostgreSQL Integer Subtraction Function'
default_version = '0.0.1'
relocatable = true
module_pathname = '$libdir/int_subtract'

Makefile

This file is used to compile and install the extension.

cat Makefile
-------------

EXTENSION = int_subtract
MODULE_big = int_subtract
DATA = int_subtract--0.0.1.sql
OBJS = int_subtract.o
PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)

Step 3: Build and Install the Extension

Now that we have the extension files, we can build and install the extension into PostgreSQL.

Run make to compile the extension:

make USE_PGXS=1

Install the extension into PostgreSQL:

make USE_PGXS=1 install

Create the extension in PostgreSQL: Once the extension is installed, we need to create it in the database.

CREATE EXTENSION int_subtract;

Step 4: Using the Extension

Now that the extension is created, let’s use it to perform integer subtraction.

Here’s how you can test it:

SELECT int_subtract(30, 8);


The result will be:
int_subtract
--------------
           22

Step 5: Verify the Extension

You can check that the extension has been installed correctly by querying the pg_extension table:

SELECT * FROM pg_extension;

postgres=# select * from pg_extension;
  oid  |   extname    | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition
-------+--------------+----------+--------------+----------------+------------+-----------+--------------
 13486 | plpgsql      |       10 |           11 | f              | 1.0        |           |
 24576 | plpython3u   |       10 |           11 | f              | 1.0        |           |
 24581 | pltclu       |       10 |           11 | f              | 1.0        |           |
 24765 | int_subtract |       10 |         2200 | t              | 0.0.1      |           |
(4 rows)

Conclusion

This tutorial provided a simple example of creating a PostgreSQL extension written in C, specifically a function for integer subtraction. This demonstrates how you can extend PostgreSQL’s capabilities with custom functions for better performance and integration. However, this is just the beginning. PostgreSQL extensions can be far more complex, ranging from custom data types and operators to sophisticated procedural languages and even full-fledged modules. As you become more familiar with creating extensions, you can build more advanced functionalities that cater specifically to your use case, whether it’s optimizing queries, adding new features, or integrating with other systems.