How to write PostgreSQL extensions in C++

Creating a completely new extension for PostgreSQL involves several steps.
Here we will create a basic extension with that adds SQL function to calculate factorial of a given integer. We will write the extension code in c++.

Create the Factorial Extension

Setp 1:
Create factorial_extension folder in share directory in the postgres installation folder.
Set up the folder structure. This folder contains file factorial_extension.control, factorial_extension--1.0.sql, factorial_extension.cpp and MakeFile.

factorial_extension/
    |
    +-- factorial_extension.control
    |
    +-- factorial_extension.cpp
    |
    +-- factorial_extension--1.0.sql
    |
    +-- Makefile

Setp 2:
Write code for factorial in C++

#include <postgres.h>
#include <fmgr.h>
#include <utils/numeric.h>

#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif

extern "C" {
    PG_FUNCTION_INFO_V1(factorial);

    Datum factorial(PG_FUNCTION_ARGS) {
        int32 arg = PG_GETARG_INT32(0);

        if (arg < 0)
            ereport(ERROR,
                    (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
                     errmsg("Factorial input must be a non-negative integer")));

        int64 result = 1;
        for (int i = 2; i <= arg; ++i)
            result *= i;

        PG_RETURN_INT64(result);
    }
}

Setp 3:
Write the control file. The control file for an extension, also known as the .control file, is a metadata file that provides information about the extension. It contains details such as the name, version, author, module path, dependencies, and other important attributes of the extension.

# factorial_extension.control
comment = 'Extension to calculate factorial'
default_version = '1.0'
module_pathname = '$libdir/factorial_extension'
relocatable = false

Step 4:
Write the SQL script (factorial_extension--1.0.sql):

-- factorial_extension--1.0.sql

-- Create the extension schema
CREATE SCHEMA factorial_extension;

-- Create the SQL function
CREATE OR REPLACE FUNCTION factorial_extension.factorial(integer)
RETURNS bigint AS 'factorial_extension', 'factorial'
LANGUAGE C STRICT;

-- Grant execute permission to public (change to appropriate roles if needed)
GRANT EXECUTE ON FUNCTION factorial_extension.factorial(integer) TO PUBLIC;

Step 4:
Write the MakeFile:

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

Run the Extension

Step 1: Start or restart the PostgreSQL server
Step 2: Connect to the database where you want to install the extension
Step 3: To install the extension run the following command.

CREATE EXTENSION factorial_extension;

Use the Extension

SELECT factorial_extension.factorial(5);

Comments

[chang tong]

your Makefile does not use g++