1. Overview

PostgreSQL is a great open-source database management system that offers users a lot of options to meet their unique requirements. One of the strengths of PostgreSQL is its flexibility in creating customized SQL functions. In this blog post, We’ll use the example of a basic function called get_sum to demonstrate various approaches.

2. build a SQL function in different ways

First of all, let’s assume you want to build a SQL function to calcualte the sum of two integers.

2.1 using SQL

For straightforward SQL functions like get_sum, the simplest way is to use SQL directly. Here’s how:

postgres=# CREATE OR REPLACE FUNCTION sql_get_sum(a integer, b integer) RETURNS integer AS $$
    SELECT a + b;
$$ LANGUAGE SQL;
CREATE FUNCTION


postgres=# SELECT sql_get_sum(1, 1);
 get_sum 
---------
       2
(1 row)

2.2 using PL/pgSQL

The PL/pgSQL language provides more advanced features. You can create the get_sum function like this:

postgres=# CREATE OR REPLACE FUNCTION pgsql_get_sum(INT, INT) RETURNS INT LANGUAGE PLPGSQL
as $$
BEGIN 
    return $1 + $2; 
END
$$;
CREATE FUNCTION

postgres=# SELECT pgsql_get_sum(1, 1);
 pgsql_get_sum 
---------------
             2
(1 row)

2.3 using PL/Tcl

If you want to use languages like PL/Tcl, PL/Perl, or PL/Python, you’ll need to compile them first.

./configure --with-tcl --with-perl --with-python && make -j && make install

Here’s how to set up PL/Tcl and create the get_sum function:

postgres=# CREATE EXTENSION pltcl;
CREATE EXTENSION

postgres=# CREATE OR REPLACE FUNCTION tcl_get_sum(INT, INT) RETURNS INT AS '
    return [expr $1 + $2]
' LANGUAGE pltcl;
CREATE FUNCTION

postgres=# SELECT tcl_get_sum(1, 1);
 tcl_get_sum 
-------------
           2
(1 row)

2.4 using PL/Perl

Similar to PL/Tcl, you’ll need to enable the PL/Perl extension and then create the function:

postgres=# CREATE EXTENSION plperl;
CREATE EXTENSION

postgres=# CREATE OR REPLACE FUNCTION perl_get_sum(int, int) RETURNS INTEGER AS $$
    return $_[0] + $_[1];
$$ LANGUAGE plperl;
CREATE FUNCTION

postgres=# SELECT perl_get_sum(1, 1);
 perl_get_sum 
--------------
            2
(1 row)

2.5 using PL/Python

Enable the PL/Python extension before creating the function:

postgres=# CREATE EXTENSION plpython3u;
CREATE EXTENSION

postgres=# CREATE OR REPLACE FUNCTION python_get_sum(x INT, y INT) RETURNS INT AS $$
    z = x + y
    return z
$$ LANGUAGE plpython3u;
CREATE FUNCTION

postgres=# SELECT python_get_sum(1, 1);
 python_get_sum 
----------------
              2
(1 row)

2.6 using SQL system function

For immediate availability or more complex functions, you can integrate your SQL function into PostgreSQL’s system functions. Here’s how:

First, Determine an unused OID:

$ src/include/catalog/unused_oids
... ...
6107 - 6109
6122 - 6149
6208 - 6223
6273 - 9999
... ...

With this information above, you can pick any unsed OID (of course you need to consider how to manage this OID for your product to align with future Postgres releases). In the example below, I pick up 6273 for demo purpose.

Second, modify src/include/catalog/pg_proc.dat with below information added to the SQL system function definition array.

{ oid => '6273', descr => 'get the sum of two integers',
  proname => 'sys_get_sum', proisstrict => 'f', prorettype => 'int8',
  proargtypes => 'int4 int4', prosrc => 'sys_get_sum' },

Third, modify the corresponding C file, src/backend/utils/adt/numeric.c to add below implementation to the very bottom.

Datum sys_get_sum(PG_FUNCTION_ARGS)
{
    int64   a = 0, b = 0;
    if (PG_ARGISNULL(0) || PG_ARGISNULL(1))
    {
        elog(ERROR, "two integers are reuquired as the input.");
        PG_RETURN_NULL();
    }
    a = PG_GETARG_INT32(0);
    b = PG_GETARG_INT32(1);
    PG_RETURN_INT64(a + b);
}

Last, compile, install, and restart postgres server, and then you should be able to use sys_get_sum SQL function like below immediately.

postgres=# SELECT sys_get_sum(1, 1);
 sys_get_sum 
-------------
           2
(1 row)

2.7 using an extension

Add a SQL function to Postgres SQL system function may require some extra maintence effort especially when you need merge your logic to later Postgres releases. Building a dedicated extension can make the maintence much easier. Here is the simple process to build your own get_sum extension.

first, create a folder named get_sum under contrib/, then add a file get_sum.c with below content.

#include "postgres.h"
#include "fmgr.h"

PG_MODULE_MAGIC;

PG_FUNCTION_INFO_V1(get_sum);

Datum
get_sum(PG_FUNCTION_ARGS)
{
    bool isnull, isnull2;
    int a = 0, b = 0, sum = 0;

    isnull = PG_ARGISNULL(0);
    isnull2 = PG_ARGISNULL(1);
    if (isnull || isnull2)
      ereport( ERROR,
               ( errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
               errmsg("the input must be two integers")));

    a = PG_GETARG_INT32(0);
    b = PG_GETARG_INT32(1);
    sum = a + b;

    PG_RETURN_INT32(sum);
}

second, add a control file, get_sum.control, with below content

# get_sum postgresql extension
comment = 'extension for a sum of two integers'
default_version = '1.0'
module_pathname = '$libdir/get_sum'
relocatable = true

third, add a version control file, get_sum--1.0.sql with below content,

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

CREATE OR REPLACE FUNCTION get_sum(int, int) RETURNS int
AS '$libdir/get_sum'
LANGUAGE C IMMUTABLE STRICT;

forth, add a file Makefile with below content

MODULES = get_sum
EXTENSION = get_sum
DATA = get_sum--1.0.sql

ifdef USE_PGXS
PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)
else
subdir = contrib/get_sum
top_builddir = ../..
include $(top_builddir)/src/Makefile.global
include $(top_srcdir)/contrib/contrib-global.mk
endif

Finally, make and make install within get_sum folder, and then you can verify the get_sum extension like below.

postgres=# CREATE EXTENSION get_sum;
CREATE EXTENSION
postgres=# SELECT get_sum(1, 1);
 get_sum 
---------
       2
(1 row)

3. Summary

In this blog post, we’ve explored seven different approaches to customizing SQL functions in PostgreSQL and I hope it can help you when you when want to build your own SQL functions.

David Zhang

A software developer specialized in C/C++ programming with experience in hardware, firmware, software, database, network, and system architecture. Now, working in HighGo Software Inc, as a senior PostgreSQL architect.