PostgreSQL is an open-source database management system. It is a great way to learn to program, design and debug.

One of the main features of PostgreSQL is Extensions.

Extensions of PostgreSQL can be an as small extension as pg_fincore, used to understand the OS cache of your PostgreSQL pages to the large projects like PostGIS and Citus data.

The PostgreSQL extension is a piece of script or code which enhances the functionality of your PostgreSQL server.

In this post, we are going to understand…

Everything about PostgreSQL Extensions.

This post mainly focuses on behind the scenes of PostgreSQL extensions, we start with creating our own PostgreSQL extension and work on its internal aspects.

Before we begin, I highly recommend you to know the basics of

• How to create and compile a C Program on Linux
• What is shared library
• How to create and run a shared Library file
• How to create a simple function in PostgreSQL

here.

What is a PostgreSQL Extension?

PostgreSQL Extension is a package of one or more SQL objects (such as functions, procedures, tables, and indexes).

The advantage of packaging these related objects into a single extension, instead of using one or more scattered scripts, is that PostgreSQL will manage the whole set of objects as a single package.

PostgreSQL has got few default extensions out of the box and a user can create custom extensions based on the needs.

How to create a PostgreSQL Extension

A PostgreSQL extension can be created in PostgreSQL by the command

create extension extension_name;

But to execute the above command in your database properly, you must have at least two important files under the share directory called /isntall_directory/share/extension path.

They are

1. Control file.
2. SQL script file.

Control file: The file format must be extension_name.control, which tells the basics about an extension to PostgreSQL

SQL script file: The file in the format extension–version.sql contains the functions that you would like to add.

If the extension is to be updated with the modified SQL commands, the version of the new file should be extension–old_version–new_version.sql

When an extension is installed, all the packaged objects are placed into a database schema. If the extension can be moved to another schema, the extension is said to be relocatable.

Let us create our own PostgreSQL extension for add_ten function.

Step 1. Create a SQL script file (add_ten–1.0.sql)

add_ten—1.0.sql is just a normal function that we run in PostgreSQL.

Step 2. Create a control file (add_ten.control)

Here

comment: A descriptive comment about the extension, applied only at installation time.

default_version: The version to install as default when no specific version is required.

module_pathname: The value of this parameter will be substituted for each occurrence of MODULE_PATHNAME in the script file(s). If it is not set, no substitution is made. Typically, this is set to $libdir/shared_library_name and then MODULE_PATHNAME is used to CREATE FUNCTION commands for C-language functions so that the script files do not need to hard-wire the name of the shared library.

relocatable:  A Boolean flag that indicates if the extension can be relocated.

Other metadata may include directory, default_version,  comment, encoding,  module_pathname,  requires, superuser, relocatable, and schema.

Step 3. Create a Makefile to install the extension into your shared library.

Now, upon the installation of Makefile, the installer will look for the <name–version>.sql file and <name>.control in the current directory and place the files in PostgreSQL shared library.

to install the extension, simply run make install

The installation will create two files in the shared library path.

If the installation fails, make sure that your pg_config pointing to correct location.

or you can simply set path before invoking Makefile

export PATH=/usr/pgsql-10/bin:$PATH

Now, create and test extension in the database with create extension add_ten command

Check the version of the extension with \dx command.

Well, If I have the enhancements, How can I update the existing extension?

How can I update the existing PostgreSQL extension?

The above extension accepts a single value and adds 10 to that.

what if you want it to accept two values?

As you can see, the function is not accepting two values.

Now we update our existing extension to accept two values.

To update the existing extension, we need to create the two required files, control and SQL files again.

Step 1. Create a SQL script file (add_ten–1.0–1.1.sql)

Step 2. Create a control file (add_ten.control)

Step 3. Create a Makefile to install the extension into your shared library.

Step 4: run make install 

You got the new set into library path.

Step 5: Update the existing PostgreSQL Extension

you can update the existing PostgreSQL extension with below command.

ALTER EXTENSION <NAME> UPDATE TO ‘<VERSION>’;

Check the settings

Now the update path for PostgreSQL extension should look like this

Well, we have understood that an PostgreSQL  extension can be configured if we have a control file and SQL file.

As the PostgreSQL is written in C, what if you want to have a function written in C, in your extension?

PostgreSQL Extension based on C function

To run your C code in your PostgreSQL extensions you need a portable build system which is provided by PGXN. We will learn more about PGXN later in this post.

PostgreSQL installation provides a build infrastructure for extensions, called PGXS, which uses a global variable USE_PGXS to include the global PGXS makefiles so that simple PostgreSQL extension modules can be built simply against an already installed server. PGXS is mainly intended for extensions that include C code, although it can be used for pure-SQL extensions too.

Now, we write the same function called add 10 to given value in C function and create extension based on that.

When you write your function in C, in addition to your control file and SQL file, you will have a dynamic library shared object file (.so) as well.

Step 1. Create a SQL script file (add_cfun–1.0.sql)

Step 2. Create a control file (add_cfun.control)

Step 3. Create a C program(add_cfun.c)

Step 4. Create a Makefile to install the extension into your shared library.

At this point of time, we have 4 files.

Step 5. Now, install the PostgreSQL extension using make install

The above command installs the PostgreSQL extension and copies SQL file and Control file to a shared location and .so file shared library.

Step 6 Create an extension in database and test

Existing extensions so far in the database

How to drop an extension?

postgreshelp=# drop extension add_ten;
DROP EXTENSION
postgreshelp=#

We are going to retrospect what we have learned so far on the next page