Creating New Modules using PostgreSQL Create Extension

Introduction

As you may know, PostgreSQL is designed for extensibility and customization, this means that we can extend the functionality of your database using extensions. PostgreSQL extension functionality allows for bundling multiple SQL objects together in a single package that can be loaded or removed from your database. Once you load it into your database, extensions can function as built-in features.

PostgreSQL database has many features and offers a wide range of data types, functions, operators etc. But sometimes it is not enough for some use cases. We can easily extend PostgreSQL’s functionality through extensions. This is very useful feature for database developers and administrators.

In this blog we can cover two things below.

1. How to use PostgreSQL community supported extension
2. How to create and use extension in PostgreSQL

How to Use PostgreSQL Community Supported Extensions

There are a number of contrib modules/extensions in PostgreSQL. These modules are maintained by PostgreSQL community. We can use these modules and it’s functionality in PostgreSQL by creating an extension.

Lets see how to use the functionality of the hstore extension in PostgreSQL.

Hstore extension

The hstore module implements hstore data type which stores key-value pairs in a single value. We can use hstore data type in many cases, such as semi-structured data or rows with many attributes that are rarely queried. Note that keys and values are just text strings only. To use this functionality, we need to create extension for it in PostgreSQL.

Lets see how to use hstore data type.

CREATE TABLE books (
 book_code serial primary key,
 book_title VARCHAR (20),
 attr hstore
);

Before creating the hstore data type, you need to enable the hstore extension which loads the contrib module to your PostgreSQL.

CREATE EXTENSION hstore;

There are various hstore functions to retrieve data from the database. You can check the functions and examples here.

Please check the additional supplied modules in PostgreSQL.

How to Create an Extension

Extensibility is one of the most powerful feature in PostgreSQL. You can add new functionality for a particular use case by using contrib module and install it using CREATE EXTENSION.

In this section, we are going to learn how to create a simple contrib module and how to use its functionality in PostgreSQL.

Extension Files

To be able to run the CREATE EXTENSION command in your database, your extension must need at least two files:

1. Control file
   The file format must be extension_name.control, which tells the basics about extension to PostgreSQL, and must be placed in the installation’s SHAREDIR/extension directory.
2. SQL script file
   The file in the format extension–version.sql contains the functions that you would like to add.

The file format of the control file in the extension is same as postgresql.conf file, namely a list of parameter_name = value assignments, one per line.

Example

Please check the below complete example of an SQL-only extension, create Oracle compatible NVL function in PostgreSQL. There are many cases but here we can consider only one case for example.

The SQL script file nvlfunc–1.0.sql looks like this…

Nvlfunc–1.0.sql file:

--complain if script is sourced in psql, rather than via CREATE EXTENSION
echo Use "CREATE EXTENSION nvlfunc" to load this file. quit

CREATE OR REPLACE FUNCTION public.NVL(SMALLINT,SMALLINT)
RETURNS SMALLINT AS $$
SELECT COALESCE($1,$2);
$$ LANGUAGE SQL IMMUTABLE;

The control file nvlfunc looks like this…

Nvlfunc.conntrol file:

# nvlfunc extension
comment = 'Oracle compatible nvl function'
default_version = '1.0'
module_pathname = '$libdir/nvlfunc'
relocatable = false

While you hardly need a makefile to install these files into the correct directory, you could use a Makefile containing this:

Makefile:

EXTENSION = nvlfunc
DATA = nvlfunc--1.0.sql

PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)

If you have implemented the function using ‘C’ language then you need to add the file in the makefile.

Installation

The command make install will install the control file and sql script file into the correct directory as reported by pg_config.

Once the files are installed, use the CREATE EXTENSION command to load the objects into any particular database in PostgreSQL.

Please check the following steps to install the nvlfunc extension and you can also add this file in your extension directory:

INSTALL.nvlfunc file:

This module is a PostgreSQL extension which provides the Oracle compatible nvl function feature.
Use below command in source directory of nvlfunc to install the module.
make install
Then use the below command to create extension nvlfunc in database.
CREATE EXTENSION nvlfunc;
Use the below command to remove the nvlfunc extension from database.
DROP EXTENSION nvlfunc;
Use below command in source directory of nvlfunc to uninstall the module.
make uninstall
Note:
This extension module requires PostgreSQL 9.1 or later because CREATE EXTENSION
feature is available in PostgreSQL 9.1 and later version.

PostgreSQL extensions must be installed in your database before you can use their functionality. To install a particular extension, run the CREATE EXTENSION command from psql to load the packaged objects into the database.

Testing

Once you create the extension, it is recommended to create a test case for that extension so that when you are installing this module in a different PostgreSQL version, you can then check whether all the test cases are working as expected or not.

This is an optional step but you can create it. The test cases look like this:

sql/nvlfunc.sql file:

SELECT NVL(NULL::SMALLINT, 11::SMALLINT);

Then you can add expected output in another file. The expected output file looks like this:

expected/nvlfunc.out file:

SELECT NVL(NULL::SMALLINT, 11::SMALLINT);
 nvl 
-----
  11
(1 row)

This is just one example, that’s why only one test case is added. But when you are creating a new extension, you can add more test cases.

Nvlfunc extension directory structure:

# mkdir nvlfunc
# cd nvlfunc
# ls
Makefile         nvlfunc.control    nvlfunc--1.0.sql    sql     expected    INSTALL.nvlfunc    README

Pros

1. Easy to extend the PostgreSQL functionality
2. Very easy to create and install the extension
3. Easy to test for regressions on different versions of PostgreSQL

Cons

1. There is no special cons but test the feature you are adding in the extension before you use it.

Conclusion

PostgreSQL extensibility is a very powerful feature, you can create your own extension for particular use cases and use it in your production database. There are many PostgreSQL community supported extensions like hstore, postgres_fdw, dblink etc. and third party extensions like Orafce, all used for particular use cases.