Create a Custom Function in Postgres

User-defined functions can be written in C language. Such functions are compiled into dynamically loadable objects.

function implementation in c

The version-1 calling convention relies on macros to suppress most of the complexity of passing arguments and results. the declaration of a version-1 function will be as follows

Datum funcname(PG_FUNCTION_ARGS)

before that we will add the macro call

PG_FUNCTION_INFO_V1(funcname);

to declare the function name

then the function implementation of adding two numbers will be as follows.

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

PG_MODULE_MAGIC;

PG_FUNCTION_INFO_V1(add_one);

Datum
add_two_numbers(PG_FUNCTION_ARGS)
{
    int32   arg1 = PG_GETARG_INT32(0);
    int32   arg2 = PG_GETARG_INT32(1);

    PG_RETURN_INT32(arg1 + arg2);
}

notice that PG_RETURN_xxx or PG_GETARG_xxx
where xxx refer to the variable datatype

then, we could define the functions to PostgreSQL with commands like this

CREATE FUNCTION add_one(integer) 
RETURNS integer
AS 'DIRECTORY/funcs', 'add_two_numbers'
LANGUAGE C;

Ref:
postgresql documentation

Comments

[Anneta Wamono]

Hello. Thank you for sharing your knowledge in this article! As someone who hasn't used postgresSQL before, it was a bit hard to follow along. Perhaps a bit more context on why you're writing this function in C when postgres is a SQL system. And maybe an explanation of what a version-1 calling convention is.

[Ahmed Mohamed]

Hi Anneta, PostgreSQL provides four kinds of functions:

• query language functions (functions written in SQL)
• procedural language functions (functions written in, for example, PL/pgSQL or PL/Tcl)
• internal functions
• C-language functions this blog is a prerequisite for another blog AGE Contribution Guide to know how to contribute to AGE open-source repo

there are different calling convention types (version-1, version-0)
but version-0 is deprecated. it's a style of getting the function arguments
in version-1 getting function arguments like that

Datum add_one(PG_FUNCTION_ARGS)
{
    int32   arg = PG_GETARG_INT32(0);
    // the rest of the code
}

but in version-0 was like that

text * concat_text(text *arg1, text *arg2)
{
    // the rest of the code
}

i hope this answers your questions and sorry for the late reply

[Anneta Wamono]

Thank you! That makes things more clear.