%h1 Extending PostgreSQL with C %p 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 %h2 Declarations %p All functions have declaration written in SQL :codeblock :::sql -- iputils--1.0.sql -- CREATE FUNCTION inet_set_subnet(inet, inet) RETURNS inet AS 'iptuils', 'inet_set_subnet' LANGUAGE C IMMUTABLE STRICT; %p 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 %code CREATE EXTENSION and = succeed '.' do %code DROP EXTENSION Extensions need a small amount of metadata, here defined in %code iputils.control :codeblock comment = 'calculate inet bits using standard subnet format' default_version = '1.0' module_pathname = '$libdir/iputils' relocatable = true %h2 Build %p Makefiles are easily constructed by incorporating the %a{:href=>"https://www.postgresql.org/docs/current/static/extend-pgxs.html"} PGXS build bools :codeblock :::make 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) %p Now type %code gmake to build %h2 Source %p Except the build doesn't work yet; we didn't write the function. Here I define a function called %code inet_set_subnet which is similar to %a{:href=>"https://www.postgresql.org/docs/current/functions-net.html"} inet_set_masklen but calculating the network using a subnet mask :codeblock :::c #include #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); } %p Use = succeed ',' do %code git grep %a{:href=>"http://vimhelp.appspot.com/tagsrch.txt.html"} ctags or %a{:href=>"https://doxygen.postgresql.org/"} doxygen.postgresql.org to navigate the C API. %h2 Tests %p A stanard regression test harness is built into PGXS that runs a set of SQL scripts from the directory named %code sql/iputils_test.sql :codeblock :::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); %p and compares them with output in %code expected/iputils_test.out :codeblock 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 %p To execute the regression tests make sure the user you're running as has %code CREATE DATABASE privilege :codeblock sudo -u _postgresql createuser $USER -s %p and run %code make installcheck %p If you are writing your own test harness just load the function declaration and the server will attempt to load the shared object :codeblock :::ruby $url = `pg_tmp -o "-c dynamic_library_path=#{Dir.pwd}"` psql = "psql -At -q #{$url}" puts "using #{$url}" out, err, status = Open3.capture3(psql + " -f iputils--1.0.sql") eq status.success?, true %p This is useful because it enables you to quickly run tests against a shared object without having to install it as an extension.