Extending PostgreSQL with C

PostgreSQL has always been an excellent development platform, and extensible in a vast number of ways. Functions can be written in any number of high-level languages or plain SQL. Extensions in C are also strait-forward in many cases. The following examples are the major components you will need

Declarations

All functions have declaration written in SQL

 -- iputils--1.0.sql
 --
 CREATE FUNCTION inet_set_subnet(inet, inet) RETURNS inet
     AS 'iptuils', 'inet_set_subnet'
     LANGUAGE C IMMUTABLE STRICT;

This bit of SQL can be run by hand, but more likely you want to create a formal extension that can be loaded and unloaded with CREATE EXTENSION and DROP EXTENSION. Extensions need a small amount of metadata, here defined in iputils.control

 comment = 'calculate inet bits using standard subnet format'
 default_version = '1.0'
 module_pathname = '$libdir/iputils'
 relocatable = true

Build

Makefiles are easily constructed by incorporating the PGXS build bools

 RELEASE   = 1.0
 EXTENSION = iputils
 DATA      = $(EXTENSION)--${RELEASE}.sql
 DOCS      = README.md
 REGRESS   = iputils_test
 MODULES    = iputils

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

Now type gmake to build

Source

Except the build doesn't work yet; we didn't write the function. Here I define a function called inet_set_subnet which is similar to inet_set_masklen but calculating the network using a subnet mask

#include <sys/socket.h>

#include "postgres.h"
#include "fmgr.h"
#include "utils/inet.h"

#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif

PG_FUNCTION_INFO_V1(inet_set_subnet);

Datum
inet_set_subnet(PG_FUNCTION_ARGS)
{
    inet *src = PG_GETARG_INET_PP(0);
    inet *mask = PG_GETARG_INET_PP(1);
    inet *dst;

    int total = 0;
    int quads = 4;
    unsigned char oct;

    if (ip_family(src) != PGSQL_AF_INET)
        ereport(ERROR,
            (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
             errmsg("A netmask can only be applied to an IPv4 addresses")));

    /* clone the original data */
    dst = (inet *) palloc(VARSIZE_ANY(src));
    memcpy(dst, src, VARSIZE_ANY(src));

    while (quads-- > 0)
    {
        oct = ip_addr(mask)[quads];
        while (oct != 0) {
            total += oct & 0x1;
            oct >>= 1;
        }
    }
    ip_bits(dst) = total;

    PG_RETURN_INET_P(dst);
}

Use git grep, ctags or doxygen.postgresql.org to navigate the C API.

Tests

A stanard regression test harness is built into PGXS that runs a set of SQL scripts from the directory named sql/iputils_test.sql

CREATE EXTENSION iputils;
SELECT inet_set_subnet('10.10.1.1'::inet, '255.255.254.0'::inet);
SELECT inet_set_subnet('::1'::inet, '255.255.254.0'::inet);

and compares them with output in expected/iputils_test.out

CREATE EXTENSION iputils;
SELECT inet_set_subnet('10.10.1.1'::inet, '255.255.254.0'::inet);
 inet_set_subnet
-----------------
 10.10.1.1/23
(1 row)
SELECT inet_set_subnet('::1'::inet, '255.255.254.0'::inet);
ERROR:  A netmask can only be applied to an IPv4 addresses

To execute the regression tests make sure the user you're running as has CREATE DATABASE privilege

 sudo -u _postgresql createuser $USER -s

and run make installcheck

If you are writing your own test harness just load the function declaration and the server will attempt to load the shared object

$url = `pg_tmp -o "-c dynamic_library_path=/home/eradman/www/eradman.com"`
psql = "psql -At -q "
puts "using "

out, err, status = Open3.capture3(psql + " -f iputils--1.0.sql")
eq status.success?, true

This is useful because it enables you to quickly run tests against a shared object without having to install it as an extension.