1. Overview
Postgres is a huge database system consisting of a wide range of built-in data types, functions, features and operators that can be utilized to solve many common to complex problems. However, in the world full of complex problems, sometimes these are just not enough depending on the use case complexities.
Worry not, since Postgres version 9, it is possible to extend Postgres’s existing functionalities with the use of “extensions”
In this article, I will show you how to create your own extensions and add to Postgres.
Please note that this article is based on Postgres version 12 running on Ubuntu 18.04 and before you can create your own extensions, PG must have been built and installed first
2. Built-in Extensions
Before we jump into creating your own extensions, it is important to know that there is already a list of extensions available from the PG community included in the Postgres software distribution.
The detailed information of community supplied extensions can be found in this link: https://www.postgresql.org/docs/9.1/contrib.html
3. Build and Install PG Default Extensions
All the PG community extensions are located in the directory below. This is also where we will be adding our own extensions
[PG SOURCE DIR]/postgres/contrib
where [PG SOURCE DIR] is the directory to your PG source code
These modules are not built automatically unless you build the ‘world’ target. To Manually build and install them, use these commands.
cd contrib
make
sudo make install
The above command will install the extensions to
$SHAREDIR/extension
and required C shared libraries to
$LIBDIR
where $SHAREDIR and $LIBDIR are the values returned by pg_config
For the extensions that utilize the C language as implementation, there will be a C shared libraries (.so) being produced by the make command. This C shared library contains all the methods supported by the extension.
With default extensions and libraries installed, we can then see the installed extensions by the following queries
SELECT pg_available_extensions();
SELECT pg_available_extension_versions();
4. Create Extension Using plpqsql Language
For this example, we will create a very simple extension that will count the number of specified character of a given string. This extension takes 2 input arguments, first being the string, and second being the desired character. It will return an integer indicating the number of occurance of the desired characters presented in the string
first, let’s navigate to the contrib directory to add our extension
cd [PG SOURCE DIR]/contrib
let’s create a new directory called char_count. This will be the name of the extension
mkdir char_count
cd char_count
create the folders for defining testcases later
mkdir sql
mkdir expected
create and an extension control file using this naming convention:
[Extension name].control
in our case, it is:
char_count.control
# char_count extension
comment = 'function to count number of specified characters'
default_version = '1.0'
module_pathname = '$libdir/char_count'
relocatable = true
create a data sql file using this naming convention:
[Extension name]--[Extension version].sql
in our case, it is:
char_count–1.0.sql
\echo Use "CREATE EXTENSION char_count" to load this file. \quit
CREATE FUNCTION char_count(TEXT, CHAR)
RETURNS INTEGER
LANGUAGE plpgsql IMMUTABLE STRICT
AS $$
DECLARE
charCount INTEGER := 0;
i INTEGER := 0;
inputText TEXT := $1;
targetChar CHAR := $2;
BEGIN
WHILE i <= length(inputText) LOOP
IF substring( inputText from i for 1) = targetChar THEN
charCount := charCount + 1;
END IF;
i := i + 1;
END LOOP;
RETURN(charCount);
END;
$$;
Please note that the first echo line enforces the function to be loaded as extension
Create a Makefile
# contrib/char_count/Makefile
EXTENSION = char_count
DATA = char_count--1.0.sql
PGFILEDESC = "char_count - count number of specified character"
REGRESS = char_count
ifdef USE_PGXS
PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)
else
subdir = contrib/char_count
top_builddir = ../..
include $(top_builddir)/src/Makefile.global
include $(top_srcdir)/contrib/contrib-global.mk
endif
With the files in place ,we can go ahread and run within the char_count extension folder
sudo make install
This will install char_count extension to $SHAREDIR
Now we can connect to the PG server and make use of the new extension that we have just added:

5. Create a Test Case for the New Extension
We have already created a sql folder from previous steps, let’s create a new .sql file for our test case
char_count.sql
CREATE EXTENSION char_count;
SELECT char_count('aaaabbbbbbbcc','a');
SELECT char_count('aaaabbbbbbbcc','b');
SELECT char_count('aaaabbbbbbbcc','c');
SELECT char_count('aaaabbbbbbbcc1111222222233333335555590','x');
SELECT char_count('aaaabbbbbbbcc1111222222233333335555590','c');
SELECT char_count('aaaabbbbbbbcc1111222222233333335555590','b');
SELECT char_count('aaaabbbbbbbcc1111222222233333335555590','5');
SELECT char_count('aaaabbbbbbbcc1111222222233333335555590','3');
SELECT char_count('aaaabbbbbbbcc1111222222233333335555590','2');
SELECT char_count('aaaabbbbbbbcc1111222222233333335555590','1');
SELECT char_count('aaaabbbbbbbcc1111222222233333335555590','0');
SELECT char_count('aaaabbbbbbbcc1111222222233333335555590','asd');
Please note that in the Makefile, we have to also specifiy the name of the regression tests with this line:
REGRESS = char_count
Run the testcase and Obtain Results
make installcheck
For the first time, the regression test will fail, because we have not provided the expected output file (.out file) for the test case. A new folder “results” is created upon running the regression test, and there is a (.out) file inside containing all the output from the test case
CREATE EXTENSION char_count;
SELECT char_count('aaaabbbbbbbcc','a');
char_count
------------
4
(1 row)
SELECT char_count('aaaabbbbbbbcc','b');
char_count
------------
7
(1 row)
SELECT char_count('aaaabbbbbbbcc','c');
char_count
------------
2
(1 row)
SELECT char_count('aaaabbbbbbbcc1111222222233333335555590','x');
char_count
------------
0
(1 row)
SELECT char_count('aaaabbbbbbbcc1111222222233333335555590','c');
char_count
------------
2
(1 row)
SELECT char_count('aaaabbbbbbbcc1111222222233333335555590','b');
char_count
------------
7
(1 row)
SELECT char_count('aaaabbbbbbbcc1111222222233333335555590','5');
char_count
------------
5
(1 row)
SELECT char_count('aaaabbbbbbbcc1111222222233333335555590','3');
char_count
------------
7
(1 row)
SELECT char_count('aaaabbbbbbbcc1111222222233333335555590','2');
char_count
------------
7
(1 row)
SELECT char_count('aaaabbbbbbbcc1111222222233333335555590','1');
char_count
------------
4
(1 row)
SELECT char_count('aaaabbbbbbbcc1111222222233333335555590','0');
char_count
------------
1
(1 row)
SELECT char_count('aaaabbbbbbbcc1111222222233333335555590','asd');
ERROR: value too long for type character(1)
CONTEXT: PL/pgSQL function char_count(text,character) line 7 during statement block local variable initialization
We should examine this .out file and made sure the outputs are all correct and we will copy it over to the expected folder
cp char_count/results/char_count.out char_count/expected
6. Create your Own Extension Using C Language
In the previous section, we created a extension using plpgsql function language. This is in many ways very similar to the ‘CREATE FUNCTION’ commands except that in the above example, we specifically states that the function can only be loaded through the CREATE EXTENSION command.
In most cases, the custom extensions are mostly built in C codes because of its flexibility and performance benefits.
To demonstrate this, we will create a new extension called char_count_c. Let’s repeat some of the process above:
cd [PG_SOURCE_DIR]/contrib
mkdir char_count_c
cd char_count_c
mkdir expected
mkdir sql
create a control file for (char_count_c.control):
# char_count_c extension
comment = 'c function to count number of specified characters'
default_version = '1.0'
module_pathname = '$libdir/char_count_c'
relocatable = true
create a data sql file (char_count_c–1.0.sql)
\echo Use "CREATE EXTENSION char_count" to load this file. \quit
CREATE FUNCTION char_count_c(TEXT, TEXT) RETURNS INTEGER
AS '$libdir/char_count_c'
LANGUAGE C IMMUTABLE STRICT
This is where it differs from the previous method to add extension. In here we specifically set the LANGUAGE to be C as oppose to plpgsql.
$libdir/char_count_c is important as this is the path in which the PG will try to find a corresponding C share library when char_count_c extension is loaded.
Now, create a Makefile
MODULES = char_count_c
EXTENSION = char_count_c
DATA = char_count_c--1.0.sql
PGFILEDESC = "char_count_c - count number of specified character"
REGRESS = char_count_c
ifdef USE_PGXS
PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)
else
subdir = contrib/char_count_c
top_builddir = ../..
include $(top_builddir)/src/Makefile.global
include $(top_srcdir)/contrib/contrib-global.mk
endif
Here we added a new line called MODULES = char_count_c. This line will actually compile your C code into a shared library (.so) file which will be used by PG when char_count_c extension is loaded.
Create a new C source file
#include "postgres.h"
#include "fmgr.h"
#include "utils/builtins.h"
PG_MODULE_MAGIC;
PG_FUNCTION_INFO_V1(char_count_c);
Datum
char_count_c(PG_FUNCTION_ARGS)
{
int charCount = 0;
int i = 0;
text * inputText = PG_GETARG_TEXT_PP(0);
text * targetChar = PG_GETARG_TEXT_PP(1);
int inputText_sz = VARSIZE(inputText)-VARHDRSZ;
int targetChar_sz = VARSIZE(targetChar)-VARHDRSZ;
char * cp_inputText = NULL;
char * cp_targetChar = NULL;
if ( targetChar_sz > 1 )
{
elog(ERROR, "arg1 must be 1 char long");
}
cp_inputText = (char *) palloc ( inputText_sz + 1);
cp_targetChar = (char *) palloc ( targetChar_sz + 1);
memcpy(cp_inputText, VARDATA(inputText), inputText_sz);
memcpy(cp_targetChar, VARDATA(targetChar), targetChar_sz);
elog(INFO, "arg0 length is %d, value %s", (int)strlen(cp_inputText), cp_inputText );
elog(INFO, "arg1 length is %d, value %s", (int)strlen(cp_targetChar), cp_targetChar );
while ( i < strlen(cp_inputText) )
{
if( cp_inputText[i] == cp_targetChar[0] )
charCount++;
i++;
}
pfree(cp_inputText);
pfree(cp_targetChar);
PG_RETURN_INT32(charCount);
}
Now we can compile the extension
make
If make is successful, there should be a new C shared library created

Let’s go ahread and install
sudo make install
This will copy the
char_count_c–1.0.sql and char_count_c.control to $SHAREDIR/extension
and char_count_c.so to $LIBDIR
Make sure char_count_c.so is indeed installed to the $LIBDIR, otherwise, PG will not be able to find it when the extension is loaded.
With the extension installed, we can connect to the PG server and use the new extension

Create a new test case in char_count_c/sql
let’s make a copy of the test case from previous “char_count” example and change the names to “char_count_c”
CREATE EXTENSION char_count_c;
SELECT char_count_c('aaaabbbbbbbcc','a');
SELECT char_count_c('aaaabbbbbbbcc','b');
SELECT char_count_c('aaaabbbbbbbcc','c');
SELECT char_count_c('aaaabbbbbbbcc1111222222233333335555590','x');
SELECT char_count_c('aaaabbbbbbbcc1111222222233333335555590','c');
SELECT char_count_c('aaaabbbbbbbcc1111222222233333335555590','b');
SELECT char_count_c('aaaabbbbbbbcc1111222222233333335555590','5');
SELECT char_count_c('aaaabbbbbbbcc1111222222233333335555590','3');
SELECT char_count_c('aaaabbbbbbbcc1111222222233333335555590','2');
SELECT char_count_c('aaaabbbbbbbcc1111222222233333335555590','1');
SELECT char_count_c('aaaabbbbbbbcc1111222222233333335555590','0');
SELECT char_count_c('aaaabbbbbbbcc1111222222233333335555590','asd');
Please note that in the Makefile, we have to also specifiy the name of the regression tests with this line:
REGRESS = char_count_c
Run the test case
make installcheck
copy the .out file to expected folder
cp char_count_c/results/char_count_c.out char_count_c/expected
7. Add the new extensions to global Makefile
If you would like to have your extensions built along with the community ones, instead of building individually, you will need to modify the global extension Makefile located in [PG SOURCE DIR]/contrib/Makefile, and add:
char_count and char_count_c in SUBDIRS parameter
8. Summary
Postgres is a very flexibile and powerful database system that provides different ways for the end users to extend existing functionalities to fulfill his or her business needs.
From the examples above, we have learned that since Postgres version 9, we are able to create new extensions using either plpgsql or C language and be able to create regression tests as part of the extension build to ensure the extensions will work as intended.

Cary is a Senior Software Developer in HighGo Software Canada with 8 years of industrial experience developing innovative software solutions in C/C++ in the field of smart grid & metering prior to joining HighGo. He holds a bachelor degree in Electrical Engineering from University of British Columnbia (UBC) in Vancouver in 2012 and has extensive hands-on experience in technologies such as: Advanced Networking, Network & Data security, Smart Metering Innovations, deployment management with Docker, Software Engineering Lifecycle, scalability, authentication, cryptography, PostgreSQL & non-relational database, web services, firewalls, embedded systems, RTOS, ARM, PKI, Cisco equipment, functional and Architecture Design.
Recent Comments