A-Guide-to-Create-User-Defined-Extension-Modules-to-Postgres

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

# 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

\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

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:

# 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

\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.