This document describes how to create your own custom function for PostgreSQL. Currently only creating a C function is described, a shared object is built and the function is imported into PostgreSQL with CREATE FUNCTION. It is assumed the reader is familar with PostgreSQL and with building in C.

Building the Function

After determining need, in this example running external commands we must built the library.

/*******************************************************************************
 * pgfunc.c - David Busby - Edoceo, Inc.
 * This library allows the PostgreSQL server to run arbitrary commands. DANGER!
 ******************************************************************************/
// This is for talking to Postgres
#include "/usr/include/pgsql/server/postgres.h"
#include <stdlib.h>
#include <stdio.h>

// Tell Postgre the name of the custom function
PG_FUNCTION_INFO_V1(very_unsafe);

Datum danger_man(PG_FUNCTION_ARGS)
{
  text *arg1 = PG_GETARG_TEXT_P(0);
  int4 ret = system(arg1);
  PG_RETURN_INT32(WEXITSTATUS(ret));
}

Now compile the code. Some warnings will be spit when compiling, like parameter names (without types) in function declaration, this is normal.

gcc -fpic -c pgfunc.c
gcc -shared -o pgfunc.so pgfunc.o

With the shared object built the function can now be added to Postgre. Using psql tell Postgre the name of the function and which file to find the function in. The '.so' part can be left off the file name, Postgre knows what to do.

-- Create the function
CREATE FUNCTION danger_man(varchar(64)) RETURNS int4 AS '/mnt/edoceo/src/pgfunc/pgfunc' LANGUAGE C;
-- Test the function
SELECT danger_man('ls');
-- If it doesn't work then destroy it and try again
-- One could try the LOAD method as well
DROP FUNCTION danger_man();

For more information see C-Language Functions in the PostgreSQL documentation.