How Extensibility Works
PostgreSQL is extensible because its operation is catalog-driven.
PostgreSQL stores much more information in its catalogs: not only information about tables and columns, but also information about data types, functions, access methods, and so on. These tables can be modified by the user, and since PostgreSQL bases its operation on these tables, this means that PostgreSQL can be extended by users.
The PostgreSQL server can moreover incorporate user-written code into itself through dynamic loading.
The 3 directories for Extension directory
The following three directories are used for storing the files related to creating an extension in PG, these are :
1- /path/to/source/tree/contrib/ – This is top-level directory for extension in the PostgreSQL source code.
2- /path/to/source/tree/build_dir/contrib – If you build original code in build_dir directory, the contrib code will be built in the same build_dir path.
If you build the original code in the source tree, the contrib code will be built in the source tree dir, not in the build_dir directory. The build_dir depends whether you mkdir it.
3- /path/to/source/tree/psql/share/extension – I install all files under the directory /path/to/source/tree/psql
instead of /usr/local/pgsql
. So the extension is intall under the path. This is the shared library for the extension is stored.When I install my own extensions, the extension will be stored under /share/extension path.
There are two kinds of ways to add an extension.
The PostgreSQL server comes pre-installed with a number of extensions, in order to load these extensions in the database the user needs to run the “create extension” command and provide the name of the extension. The corresponding extension files (mentioned below) are executed in order to load the extension in the database.
PostgreSQL also allows the user to create there own extensions and load them in the database. This blog focusses on how you can create your own extensions
to PostgreSQL and load them in the database.
How to Use PostgreSQL Community Supported Extensions
run the “create extension” command and provide a name of the extension.
CREATE EXTENSION [ IF NOT EXISTS ] extension_name
[ WITH ] [ SCHEMA schema_name ]
[ VERSION version ]
[ FROM old_version ]
[ CASCADE ]
for example I create extension pg_buffercache.
1 2 3 4 5 6 7 8 | postgres=# create extension pg_buffercache; postgres=# \dx List of installed extensions Name | Version | Schema | Description ——————–+———+————+————————————————- pg_buffercache | 1.3 | public | examine the shared buffer cache pg_stat_statements | 1.7 | public | track execution statistics of all SQL statements executed plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language |
Add your own extension on PG
To be able to run the create extension command in your database, your extension needs at least two files: a control file and an extension’s SQL script file in the format.
The simple plpgsql function allows us to encode any integer into its base36 representation. If we copied these two files into the postgres SHAREDIR/extension directory, then we could start using the extension with CREATE EXTENSION. But we won’t bother users with figuring out where to put these files and how to copy them manually – that’s what Makefiles are made for. So, let’s add one to our project.
Create a base36 extension to pg
create a base36 folder
I add base36 folder under the directory (/path/to/source/tree/build_dir/contrib/)
$ mkdir base36 –you can put anywhere you like.
add base36–0.0.1.sql, base36. control, Makefile in the folder
Control file
base36.control
comment = ‘base36 datatype’
default_version = ‘0.0.1’
relocatable = true
SQL file
base36–0.0.1.sql
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 | — complain if script is sourced in psql, rather than via CREATE EXTENSION \echo Use “CREATE EXTENSION base36” to load this file. \quit CREATE FUNCTION base36_encode(digits int) RETURNS text LANGUAGE plpgsql IMMUTABLE STRICT AS $$ DECLARE chars char[]; ret varchar; val int; BEGIN IF digits = 0 THEN RETURN(‘0’); END IF; chars := ARRAY[ ‘0’,’1′,’2′,’3′,’4′,’5′,’6′,’7′,’8′,’9′,’a’,’b’,’c’,’d’,’e’,’f’,’g’,’h’, ‘i’,’j’,’k’,’l’,’m’,’n’,’o’,’p’,’q’,’r’,’s’,’t’, ‘u’,’v’,’w’,’x’,’y’,’z’ ]; val := digits; ret := ”; WHILE val != 0 LOOP ret := chars[(val % 36)+1] || ret; val := val / 36; END LOOP; RETURN(ret); END; $$; |
Most of the environment variables needed to build an extension are setup in pg_config and can simply be reused.
export PGDATA=/home/postgres/postgresql/pg11/psql/data(data directory)
export PATH=/home/postgres/postgresql/pg11/psql/bin(postgresql bin directory)
Makefile
Every PostgreSQL installation from 9.1 onwards provides a build infrastructure for extensions called PGXS, allowing extensions to be easily built against an already-installed server. Most of the environment variables needed to build an extension are setup in pg_config and can simply be reused.
For our example this Makefile fits our needs.
Makefile
1 2 3 4 5 6 7 | EXTENSION = base36 # the extensions name DATA = base36–0.0.1.sql # script files to install # postgres build stuff PG_CONFIG = pg_config PGXS := $(shell $(PG_CONFIG) –pgxs) include $(PGXS) |
Now we can start using the extension run
$ make install
.sql and .control will be installed to dir $PGDATA/share/extension/
You can see the extension was installed in the share/extension path.

Regress test
I also add a regress test in Makefile and add regress test SQL as viewed below.

Add REGRESS = base36_test
in the Makefile as viewed below:
1 2 3 4 5 6 7 | EXTENSION = base36 # the extensions name DATA = base36–0.0.1.sql # script files to install REGRESS = base36_test # our test script file (without extension) # postgres build stuff PG_CONFIG = pg_config PGXS := $(shell $(PG_CONFIG) –pgxs) include $(PGXS) |
add regress test sql
1 2 | $ mkdir sql $ mkdir result |
add sql/base36_test.sql
1 2 3 4 5 | CREATE EXTENSION base36; SELECT base36_encode(0); SELECT base36_encode(1); SELECT base36_encode(36); SELECT base36_encode(123456789); |
Regress test begin
1 2 3 4 | $ make installcheck $ mkdir expected $ mv results/base36_test.out to expected $ make installcheck |
regress test result

create extension base36

C- Language function to make it work with postgres
add base36.c and update base36-0.0.1 to base36-0.0.2.sql, and update Makefile and base36.control as viewed below

drop extension base36;
base36–0.0.2.sql
1 2 3 4 | \echo Use “CREATE EXTENSION base36” to load this file. \quit CREATE FUNCTION base36_encode(integer) RETURNS text AS ‘$libdir/base36’ LANGUAGE C IMMUTABLE STRICT; # this part for C language |
base36.control
1 2 3 4 | # base36 extension comment = ‘base36 datatype’ default_version = ‘0.0.2’ relocatable = true |
Makefile
1 2 3 4 5 6 7 8 9 | EXTENSION = base36 # the extensions name DATA = base36–0.0.1.sql # script files to install REGRESS = base36_test # our test script file (without extension) MODULES = base36 # our c module file to build # postgres build stuff PG_CONFIG = pg_config PGXS := $(shell $(PG_CONFIG) –pgxs) include $(PGXS) |
base36.c
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 | #include “postgres.h” #include “fmgr.h” #include “utils/builtins.h” PG_MODULE_MAGIC; PG_FUNCTION_INFO_V1(base36_encode); Datum base36_encode(PG_FUNCTION_ARGS) { int32 arg = PG_GETARG_INT32(0); if (arg < 0) ereport(ERROR, ( errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE), errmsg(“negative values are not allowed”), errdetail(“value %d is negative”, arg), errhint(“make it positive”) ) ); char base36[36] = “0123456789abcdefghijklmnopqrstuvwxyz”; /* max 6 char + ‘\0’ */ char *buffer = palloc(7 * sizeof(char)); unsigned int offset = sizeof(buffer); buffer[–offset] = ‘\0’; do { buffer[–offset] = base36[arg % 36]; } while (arg /= 36); PG_RETURN_TEXT_P(cstring_to_text(&buffer[offset])); } |
Then we command again. $ make install & make installcheck
create extension base36;
You will see as below

I add function in base36.c
if (arg < 0)
ereport(ERROR,
(
errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
errmsg("negative values are not allowed"),
errdetail("value %d is negative", arg),
errhint("make it positive")
)
);
result

reference
https://www.postgresql.org/docs/12/extend.html
https://www.postgresql.org/docs/12/contrib.html
http://big-elephants.com/2015-10/writing-postgres-extensions-part-i/
Recent Comments