PostgreSQL ships with a large set of extensions in its contrib module, listed in the official documentation. Installing one and running CREATE EXTENSION extension_name from a client is enough to activate it. Beyond contrib, there are extensive third-party collections of extensions worth checking before writing anything new. When none of them fit, the extension mechanism itself is available, and it can be driven from almost any language.

This walkthrough follows a single example: an extension called pg_make_title that converts text into title format.

The Makefile and control file

The Makefile for a first extension is minimal:

1

2

3

4

5

6

PGFILEDESC = "Extension to convert a text to tile format"

EXTENSION = pg_make_title

DATA = pg_make_title--1.0.sql

PG_CONFIG = pg_config

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

include $(PGXS)

The key ingredient is PGXS, which lets you install against an already-installed PostgreSQL server. The environment variables it needs are reachable through pg_config.

The control file, named extension_name.control, tells the server the basics about the extension, version included:

1

2

3

4

comment = 'convert text to title format'

default_version = '1.0'

module_pathname = '$libdir/pg_make_title

relocatable = true

What goes into the install script

The .sql file is the install script executed when CREATE EXTENSION <MY_AWESOME_EXTENSION>; runs. Every view and function the extension needs is created here. It starts with a line identifying the file:

1

2

-- complain if script is sourced in psql, rather than via CREATE EXTENSION

\echo Use "CREATE EXTENSION pg_make_title" to load this file. \quit

Function definitions follow:

1

2

3

4

5

6

7

8

9

10

11

12

/* Now define */

CREATE FUNCTION convert_to_title(inp text)

RETURNS text

LANGUAGE plpgsql IMMUTABLE STRICT

  AS $$

    DECLARE

      ret text;

    BEGIN

      ret := (SELECT INITCAP(inp));

    RETURN(ret);

    END;

  $$;

Name the file pg_make_title--1.0.sql. The version number in the filename must match default_version in the control file.

Installing and loading it

  1. Run make install inside the extensions repo.
  2. Start the PostgreSQL server.
  3. Log in to the client — psql in this example.
  4. Run create extension pg_make_title;
  5. Check the details of the extension by running \dx.
  6. Call the function it provides:

1

SELECT convert_to_title('create your first extension');

The result:

1

2

3

4

      convert_to_title      

-----------------------------

Create Your First Extension

(1 row)

Moving the logic into C

Extensions that call C functions or run complex SQL put that code in a separate source file, and the functions defined there can be called from the install script. The example goes one step further and implements the function in C, in pg_make_title.c:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

/*--------------------------------------------------

* pg_make_title.c

* -------------------------------------------------

*/

#include "postgres.h"

#include "utils/builtins.h"

#include "c.h"

PG_MODULE_MAGIC;

PG_FUNCTION_INFO_V1(convert_to_title);

Datum

convert_to_title(PG_FUNCTION_ARGS)

{

    char *input = text_to_cstring(PG_GETARG_TEXT_PP(0));

…

    PG_RETURN_CSTRING(input);

}

Three pieces carry the meaning here. PG_MODULE_MAGIC is a magic block supplied by the PostgreSQL infrastructure so version incompatibilities are detected. PG_FUNCTION_INFO_V1(convert_to_title) is required for every function callable from the PostgreSQL interface. Datum convert_to_title(PG_FUNCTION_ARGS) declares a function that accepts arguments and returns a datum; PG_FUNCTION_ARGS means it may take any type and number of arguments.

The Makefile needs one addition:

1

MODULES = pg_make_title

The install script has to change as well, so the server knows the definition lives in C rather than SQL:

1

2

3

4

/* Now define */

CREATE FUNCTION convert_to_title(inp text)

RETURNS text AS 'MODULE_PATHNAME'

LANGUAGE C STRICT VOLATILE;

A further make install in the repo installs the extension using the C source file.

Regression tests via installcheck

It is good practice to ship a testing mechanism alongside the extension, and PostgreSQL provides the regression suite for this. Create a SQL directory containing a file with all the test cases:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

CREATE EXTENSION pg_make_title version '1.0';

--1. Check normal string

SELECT  convert_to_title('create your first extension');

--2. Check string starting with space

SELECT  convert_to_title(' create your first extension');

--3. Check string starting with number

SELECT  convert_to_title('1 create your first extension');

--4. Check empty string

SELECT  convert_to_title('');

--5. Check multiple strings

SELECT  convert_to_title('create your first extension','create your second extension');

Add this line to the Makefile:

1

REGRESS = pg_make_title

Running make installcheck then produces an .out file in the results subdirectory holding the output of those tests. After verifying that the output matches expectations, create an expected subdirectory and move the .out file into it. From then on, every make installcheck compares its results against that file.

A README is worth writing too — all the more so for an extension that does something nobody thought to do before.