Unleashing PostgreSQL's Potential: Easy C Extension Development for Beginners! Part-1

While writing this article, I am following along a brilliant tutorial so to give credit where it is due.

For this article, I will be using Ubuntu and postgreSQL 11.18. Now, firstly you need to be sure that you've installed postgreSQL and the binary path has been added to your environment. This would allow you to run the pg_config command without specifying the path and something like this should show up:

The postgresql installation has a built-in infrastructure called pgxs that is used to make extensions. If you run the command

pg_config --pgxs

its directory should show up like so:

For the purpose of divergence from the actual blog post I will create an extension called subtractme that subtracts two numbers. In order to do this we need to create a Makefile that will build the extension as follows:

Then we need a control file which we will aptly name subtractme.control that contains the metadata for our extension:

comment = 'Simple number subtract function'
default_version = '0.0.1'
relocatable = true
module_pathname = '$libdir/subtractme'

Finally, getting to the good part we create our C function to perform the task:

Now with out three created files in the same directory we make the file.

Finally, before installing we need to create an SQL file with a create function whose name must be the same as the one we specified in the DATA parameter in the Makefile, in our case subtractme–0.0.1.sql. The rule would have the following contents:

CREATE OR REPLACE FUNCTION
addme(int,int) RETURNS int AS 'MODULE_PATHNAME','addme'
LANGUAGE C STRICT;

Now after running sudo make install something like this should show up and we are done:

In order to use it, we can start up a Postgres server and do the following and our function should work as intended: