Writing Advanced Functions in C - PostgreSQL Server Programming - page 211

Adding functionality to add(int, int)
While our function works, it adds nothing in the preceding code just using SELECT A
+ B , but functions written in C are capable of so much more. Let's start adding some
more functionality to our function.
Smart handling of NULL arguments
Notice the use of STRICT keyword in the CREATE FUNCTION add(int a, int
b) in the previously mentioned code. This means that the function will not be called
if any of the arguments are NULL , but instead NULL is returned straight away. This is
similar to how most PostgreSQL operators works, including the + sign when adding
two integers—if any of the arguments are NULL the complete result is NULL as well.
Next, we will extend our function to be smarter about NULL inputs and act like Post-
greSQL's sum() aggregate function, which ignores NULL values in inputs and still
produces sum of all non-null values.
For this, we need to do two things:
1. Make sure that the function is called when either of the arguments are
NULL .
2. Handle NULL arguments by effectively converting a NULL argument to 0
and returning NULL only in cases where both arguments are null.
The first one is easy—just leave out the STRICT keyword when declaring the function.
The latter one also seems easy as we just leave out STRICT and let the function ex-
ecute. For a function with int arguments, this almost seems to do the trick. All NULL
values show up as 0's and the only thing you miss will be returning NULL if both argu-
ments are NULL .
Unfortunately, this only works by coincidence. It is not guaranteed to work in future
versions, and even worse, if you do it the same way for pass by reference types it will
cause PostgreSQL to crash on null pointer references.
Next we show how to do it properly. We need now to do two things: record if we have
any non-null values and add all the non-null values we see: