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.