Creating a PostgreSQL procedural language – Part 1 – Setup

PostgreSQL supports many procedural languages, which can be used to write user defined functions or stored procedures.  There are four that are readily available as part of the standard PostgreSQL distribution: PL/pgSQL, PL/Tcl, PL/Perl, PL/Python.  Yet procedural languages don’t have to be created as part of the core project.  There are a number more that are available as a result of PostgreSQL being so highly extensible.

The following is only the beginning of an example to create a new procedural language for PostgreSQL. At some point later we will have something that enables user defined functions and stored procedures written in the Julia programming language to actually execute.

The PostgreSQL documentation has a very light example for what is required to handle a new procedural language.  It is recommended to create this new feature as an extension when creating a procedural language outside of the core project.  All that is needed to get started the extension started are:

• a control file that defines the basic properties of the extension
• a SQL file that creates the extension’s objects
• a C file for the extension itself
• a Makefile to build the extension

More detailed information on these individual files are in the PostgreSQL documentation.  But here are brief examples of each file that gets us an extension that can be built and loaded into PostgreSQL.

pljulia.control:

comment = 'PL/Julia procedural language'
default_version = '0.1'
module_pathname = '$libdir/pljulia'
relocatable = false
schema = pg_catalog
superuser = false

pljulia–0.1.sql:

CREATE FUNCTION pljulia_call_handler()
RETURNS language_handler
AS 'MODULE_PATHNAME'
LANGUAGE C;

CREATE LANGUAGE pljulia
HANDLER pljulia_call_handler;

COMMENT ON LANGUAGE pljulia IS 'PL/Julia procedural language';

pljulia.c:

#include <postgres.h>
#include <fmgr.h>

PG_MODULE_MAGIC;

PG_FUNCTION_INFO_V1(pljulia_call_handler);

/*
 * Handle function, procedure, and trigger calls.
 */

Datum
pljulia_call_handler(PG_FUNCTION_ARGS)
{
    return 0;
}

Makefile:

PGFILEDESC = "PL/Julia - procedural language"

EXTENSION = pljulia
EXTVERSION = 0.1

MODULE_big = pljulia

OBJS = pljulia.o

DATA = pljulia.control pljulia--0.1.sql

pljulia.o: pljulia.c

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

And that’s all that is needed for the first round of instant gratification.  The full code is available on GitHub.

This is enough to build, install, and create a PL/Julia extension.  While you can now create user defined functions and stored procedures with PL/Julia, these functions and procedures still won’t be able to do anything particularly useful so we will save those examples for later.