Easy guide to writing PostgreSQL extensions

Among many interesting features that PostgreSQL has to offer, one of the most exciting ones is the ability to write extensions. It is possible to easily extend Postgres functionality by creating an extension module for it. Another important aspect is that you can write it in almost any language.

Before you get all riled up and start typing the code you have always wanted, take a deep breath! Postgres already comes with many useful extensions. These are the extensions that make its contrib module — check out the official list. You may use any of these extensions by simply installing them and then using CREATE EXTENSION extension_name on your Postgres client. 

If these extensions do not cover what you are looking for, then I came across this quite extensive list of available extensions. If nothing here could solve your problem, then you are in luck, because now you have a fair chance of writing an extension yourself.

This blog will cover the basic process to write a working Postgres extension. Let’s understand this process with a running example of creating an extension which converts a given text-to-title format. Let’s call this extension pg_make_title

Writing a Makefile

If this is the first time you are going to write the Makefile, don’t worry, this is a simple one. It should look something like this:

PGXS is the most interesting thing here. It allows you to install the extension on the already installed PostgreSQL server. All the environment variables, etc. it requires to set up the extension can then be easily accessed using pg_config.

Managing your extension’s control file

This is an important file that needs to be written. This is how the server knows the basic stuff about the extension, like version, etc. It is named as extension_name.control and would look something like this:

Handling extension.sql

This is the script which will install your extension. This is the code which will run when you call CREATE EXTENSION <MY_AWESOME_EXTENSION>; Any view, function, etc. that needs to be created for the working of the extension comes here. One important line to add at the beginning of the file:

Then comes the definition of the functions to be used:

Keep the name of the file something like pg_make_title–1.0.sql — the version 

the number after the extension name should be the same as the one given in the default_version field in the control file.

Now, let’s try using this extension. For this we need to go through following steps:

1. Run make install inside the extensions repo
2. Start your PostgreSQL server
3. Login to the client, here I am using psql
4. Now, at the client, run  create extension pg_make_title;
5. Now running \dx, should give you the details of this extension
6. Now try using the function provided here,

This will give you,

extension.xx

Here comes the interesting stuff. If you are writing an extension which calls some C functions or some complex SQL queries, then you write them here. These functions can be called from extension.sql

For our running example, let’s go one step further and write this function in C language. So now, our pg_make_title.c would look something like this:

Here PG_MODULE_MAGIC, is actually a magic block provided by PostgreSQL infrastructure to check for any incompatibility with respect to the version downloaded, etc. 

Next, PG_FUNCTION_INFO_V1(convert_to_title), is required for every function that is to be called from the Postgresql interface. 

At the end, Datum convert_to_title(PG_FUNCTION_ARGS) says that this function can take arguments and returns datum. Here, PG_FUNCTION_ARGS means that it takes any type and number of arguments.

After making these changes to the C source file, we also need to add …

… in the Makefile.

Do not forget to modify pg_make_title–1.0.sql like this:

Now it knows that the function definition is in C and not the SQL function as before.

Running make install in the repo will install the extension and will use the code from the C source file.

Regression test suite

It is a good practice to provide a testing mechanism for your extension with your extension. PostgreSQL helps here by providing the regression suite. For this you need to create a SQL directory inside which resides a SQL file with all the test cases. For our example, it may look something like this:

Next, add this line to your Makefile

Now run make installcheck to create an .out file in the results subdirectory. This is the output of running the tests you wrote in the test file. Once you verify all the output is as you expect it to be, create a subdirectory expected here and move this .out file there. Now every time make installcheck is issued, the results are checked against this file in the expected subdirectory.

README

Well, who doesn’t value the importance of a good README file? Particularly, when you have a super awesome extension doing the super awesome stuff that didn’t cross people’s minds before. Make it all shiny with a good README file.