The C++ 14 specification came with huge improvements: Auto type deduction, lambdas, movable RValue expressions etc. — the list is huge. Its a dream come true for lazy programmers like me : write less code & create fewer bugs šŸ˜‰ And get huge performance gains for free.

OK this time I thought I’d try some functions in C++ rather than PL/SQL. It could be easier for development. Ohh, well…But Postgresql is a world of C code. Other than the list of restrictions, I couldn’t find anything in the documentation. No working examples.

Looks like there is a lot of confusion around. I see many queries on different forums that remain unanswered. I thought I’d give it a try.

After the first couple of attempts, i figured out that wrapping my code in an extern ā€œCā€ could do the magic.

Here is a simple sample function, where i am trying to use type deduction, STL and a loop iterator. My file name is extension.cpp and it contains following lines

extern "C" {
#include <postgres.h>    //all C headers and macros go inside extern "C"
#include <utils/rel.h>
PG_MODULE_MAGIC;
}

#include<vector>           //C++ headers go outside

extern "C" {
int sumofall(){        //wrap the C++ function inside the extern "C" block
auto sum_of_all = 0;
std::vector<int> arr {1,2,3,4,5,6,7};
for (auto& i : arr )
     sum_of_all += i;
return sum_of_all;
}
}

I know that this could be construed as dumb code. A C++ coder may say that this function must be a C++ “constexpr” (Constant expression) so that the calculation is done at compile time and there is zero overhead at runtime. But here my intention is to show that we won’t have any restriction in using C++ features.

I used the Postgres sandbox created using BigSQL Packagemanger for this demonstration as it comes with all the header files and libraries. At the same time it is portable (relocatable) also.

This can be compiled with:

g++ -c -fPIC -Wall -Werror -g3 -O0 -I/home/vagrant/bigsql/pg96/include/postgresql/server extension.cpp 

Make it a shared object

g++ -shared -o my-extension.so extension.o

Copy the same to library location

cp my-extension.so ~/bigsql/pg96/lib/postgresql/

Create the function definition

postgres=# CREATE OR REPLACE FUNCTION 
   sumofall() RETURNS integer AS 'my-extension' 
LANGUAGE C STRICT;

Yes it works!!!

postgres=# select sumofall();
sumofall
----------
       28
(1 row)

Now Lets see how we can put some business logic into a function.

int bonus(const int score,const int type){
   if (type ==1 || score > 100) return std::rint(score+score*0.05);
   else return std::rint(score+score*0.06);
}

This function can be invoked from SQL with:

postgres=# select empnm,bonus(score,type) from emp;
 empnm | bonus
-------+-------
 emp1  |   103
 emp2  |   105
 emp3  |   105
 emp4  |   106
 emp5  |   106
 emp6  |   106

Note: I used g++ (GCC) 6.2.1 to compile above code.