Creating simple extensions for PostgreSQL

When you are using PostgreSQL you should already know that PostgreSQL comes with a set of extensions by default. It might be that you need to install an additional package if you installed PostgreSQL with a package manager to get those extensions. Usually it is called something with “contrib” in its name. There is also the PostgreSQL Extension Network which list a lot of external extensions that might be useful for you. If you can’t find what you’re looking for, you can still write your own extension. How to do that is the topic of this post: We’ll create a very simple extension for PostgreSQL.

Lets assume you always need a table containing the list of countries with a few properties like this:

postgres=# create table countries ( id int primary key, name text, alpha2 text, alpha3 text );
CREATE TABLE
postgres=# copy countries 
           from program 'curl https://raw.githubusercontent.com/stefangabos/world_countries/master/data/en/countries.csv'
           with (header true, delimiter ',', format csv);
COPY 193

This gives as 193 countries and each country has a few properties:

postgres=# select * from countries limit 5;
 id |    name     | alpha2 | alpha3 
----+-------------+--------+--------
  4 | Afghanistan | af     | afg
  8 | Albania     | al     | alb
 12 | Algeria     | dz     | dza
 20 | Andorra     | ad     | and
 24 | Angola      | ao     | ago
(5 rows)

Packaging this into an extension is quite easy. You need a so called control file that provides some basic information about your extension. In my case it looks like this and I’ve placed it where all the other extensions are as well:

postgres@debian11:/u01/app/postgres/product/DEV/db_1/share/extension/ [pg15] pwd
/u01/app/postgres/product/DEV/db_1/share/extension
postgres@debian11:/u01/app/postgres/product/DEV/db_1/share/extension/ [pg15] cat my_cool_extension.control
# my cool extension control file
comment = 'my cool extension for providing a table with countries'
default_version = '1.0'
relocatable = false
trusted = true

Now we need the table and the data and this goes into a simple SQL file:

postgres@debian11:/home/postgres/ [pg15] pg_dump --column-inserts --table=countries postgres > /u01/app/postgres/product/DEV/db_1/share/extension/my_cool_extension--1.0.sql
postgres@debian11:/home/postgres/ [pg15] psql -c "drop table countries" postgres

If you look at the default extensions they all have the first two lines of the SQL files like e.g. this:

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

This prevents that the file can directly be piped into psql and we should do the same. Only “create extension” should be used to create extensions:

postgres@debian11:/home/postgres/ [pgdev] sed -i '1s/^/-- complain if script is sourced in psql, rather than via CREATE EXTENSION/' /u01/app/postgres/product/DEV/db_1/share/extension/my_cool_extension--1.0.sql
postgres@debian11:/home/postgres/ [pgdev] sed -i '2s/^/\echo Use "CREATE EXTENSION my_cool_extension" to load this file. \quitn/' /u01/app/postgres/product/DEV/db_1/share/extension/my_cool_extension--1.0.sql

That’s all you need to do. Having these two files in place the extension is recognized by PostgreSQL:

postgres=# select * from pg_available_extensions where comment like 'my cool%';
       name        | default_version | installed_version |                       comment                        
-------------------+-----------------+-------------------+------------------------------------------------------
 my_cool_extension | 1.0             |                   | my cool extension for providing a table of countries
(1 row)

Now we can install it as usual:

postgres=# create extension my_cool_extension;
CREATE EXTENSION
postgres=# dx
                                  List of installed extensions
       Name        | Version |   Schema   |                     Description                      
-------------------+---------+------------+------------------------------------------------------
 my_cool_extension | 1.0     | public     | my cool extension for providing a table of countries
 plpgsql           | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)
postgres=# select * from countries limit 5;
 id |    name     | alpha2 | alpha3 
----+-------------+--------+--------
  4 | Afghanistan | af     | afg
  8 | Albania     | al     | alb
 12 | Algeria     | dz     | dza
 20 | Andorra     | ad     | and
 24 | Angola      | ao     | ago
(5 rows)

Over time you might need to update your extension so lets assume in our case that there is a new country. What you need to do is create a new SQL file that does the necessary changes and update the default version in the control file:

postgres@debian11:/u01/app/postgres/product/DEV/db_1/share/extension/ [pgdev] pwd
/u01/app/postgres/product/DEV/db_1/share/extension
postgres@debian11:/u01/app/postgres/product/DEV/db_1/share/extension/ [pgdev] cat my_cool_extension--1.0--1.1.sql
-- complain if script is sourced in psql, rather than via ALTER EXTENSION
echo Use "ALTER EXTENSION my_cool_extension UPDATE TO '1.1'" to load this file. quit
-- the list of new countries
insert into countries (id,name,alpha2,alpha3) values (-1,'my new country','aaa','aaa');

postgres@debian11:/u01/app/postgres/product/DEV/db_1/share/extension/ [pgdev] grep default my_cool_extension.control 
default_version = '1.1'

Afterwards update the extension using alter extension:

postgres=# select * from pg_available_extensions where name = 'my_cool_extension';
       name        | default_version | installed_version |                       comment                        
-------------------+-----------------+-------------------+------------------------------------------------------
 my_cool_extension | 1.1             | 1.0               | my cool extension for providing a table of countries
(1 row)

postgres=# alter extension my_cool_extension update;
ALTER EXTENSION
postgres=# select count(*) from countries where id = -1;
 count 
-------
     1
(1 row)

All fine, ready to go.