Building A Simple PL/pgSQL Postgres Extension

Postgres offers powerful extension capabilities that allow you to enhance its functionality. Even a simple PL/pgSQL extension provides advantages by neatly packaging related functions into a single bundle. This article walks you through creating the simplest form of a Postgres extension using PL/pgSQL.

Required Files

To build a PL/pgSQL extension, you need the following files:

1. Control file: Specifies what the extension is and how Postgres loads it
2. SQL file: Contains the SQL code for the extension
3. Makefile: Provides instructions for installing the extension

Two additional files make installation and testing easier:

4. Setup script: Installs the extension in a schema
5. Test script: Verifies the extension works properly

You’ll also need Postgres installed locally on your system to build and test the extension.

The Control File

The control file must be named <extension_name>.control. For our example, we’ll create pg_make_title.control:

comment = 'Convert Text to Title Format'
default_version = '1.0'
module_pathname = '$libdir/pg_make_title'
relocatable = true

This short file defines:

• A description of the extension’s purpose
• The current version to load (in quotes)
• The location where Postgres will look to install the extension
• Whether the extension is relocatable (set to true for our simple extension)

The SQL File

The SQL file must follow the naming convention <extension_name>--<version>.sql. For our example, we’ll create pg_make_title--1.0.sql:

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

-- convet_to_title will take a string and convert the initial charactes of each word to a capital letter.
CREATE FUNCTION IF NOT EXISTS convert_to_title(s text) RETURNS text
LANGUAGE plpgsql IMMUTABLE STRICT 
AS $$
DECLARE
	rv text;
BEGIN
	rv := (SELECT INITCAP(s));
	RETURN(rv);
END;
$$;

This file defines a function that capitalizes the first letter of each word in a text string.

The Makefile

Before creating the Makefile, ensure you have:

1. GNU make installed (check with make --version)
2. Postgres configuration in your PATH (verify with pg_config --version)

Now create a Makefile with the following content:

PGFILEDESC = "Extension to convert a string of words to title by capaitilizing the first letter of each word"
EXTENSION = pg_make_title
DATA = pg_make_title--1.0.sql

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

The first three lines define:

• A description of the extension
• The extension name
• The SQL file to install (must match the version in the control file)

The remaining lines leverage Postgres’s extension system to handle the installation.

Installing the Extension

1. Install the extension files into Postgres directories:

$ make install

Note: Depending on your Postgres installation, you might need administrator privileges for this step.

2. Create a setup.sql file to install the extension in your database:

CREATE EXTENSION IF NOT EXISTS pg_make_title;

3. Run the setup script:

$ psql -f setup.sql

Testing the Extension

You can perform a simple test in psql:

SELECT convert_to_title('a title for something');

This should return: A Title For Something

For more comprehensive testing, create a test.sql file:

$ make install

Once the extension is installed in the Postgres direcotry you can install the extension in your Postres login account. Save this in the setup.sql file.

$ psql pschlump=# CREATE EXTENSION IF NOT EXISTS pg_make_title; pschlump=# \q

And then run a simple test.

$ psql
pschlump=# SELECT convert_to_title('a title for something');
   convert_to_title
-----------------------
 A Title For Something
(1 row)

pschlump=# \q

A more comprehensive test, in the file test1.sql:

-- A tests that results in 'PASS'/'FAIL' 

DO $$
DECLARE
 	l_txt text;
 	n_err int;
	l_msg text;
BEGIN

 	n_err = 0;

	SELECT convert_to_title('12345')
		into l_txt;
	if l_txt != '12345' then
		RAISE NOTICE 'Failed to convert 12345 to 12345';
		n_err = n_err + 1;
	end if;

	SELECT convert_to_title('A Title')
		into l_txt;
	if l_txt != 'A Title' then
		RAISE NOTICE 'Failed to convert "A Title" to "A Title"';
		n_err = n_err + 1;
	end if;

	SELECT convert_to_title('a title')
		into l_txt;
	if l_txt != 'A Title' then
		RAISE NOTICE 'Failed to convert "a title" to "A Title"';
		n_err = n_err + 1;
	end if;


	if n_err = 0 then
		RAISE NOTICE 'PASS';
	else 
		RAISE NOTICE 'FAIL';
	end if;

END;
$$ LANGUAGE plpgsql;

Run the test script:

$ psql -f test.sql

You should see a “PASS” notice if all tests succeed.

Source Code

All code for this example is available on GitHub at https://github.com/pschlump/pgextension01.

Creating a PL/pgSQL extension is a straightforward process that provides a clean way to package and distribute Postgres functionality. This simple example demonstrates the fundamental structure, but the same principles apply to more complex extensions.