How to call a possibly null returning sql function from C extension in PostgresQL

I'm writing a PostgresQL C extension and I'd like to call a sql function that can possibly return null value. I cannot use DirectFunctionCall family of functions, because they raise an error when called function that returns null.

More specifically my use case is as follows: I want to call to_regprocedure to get an OID of a user provided function and call it if it exists and do nothing otherwise. Both to_regprocedure and user provided function can return null.

I tried DirectFunctionCall, but it raises an error.

I could probably use SPI, but have not tried that, because I'd rather avoid using it just for this single case.

Ideally, I'd like to have a function similar to DirectFunctionCall that does not raise an error when called function return null.

Solution

Here are some possible options to consider that avoid spi (but it is an option too):

Use COALESCE() allowing you to substitute a default value for a null returned from the called function.
Assuming you can't amend the exiting function, you could use a "wrapper function" to call the existing function passing through whatever arguments it needs. If the called function returns null or raises an error it returns something other than null. For example, if you want the wrapper function to return the text value 'error' when the called function returns null or raises an error, you can modify the wrapper function like this:

create or replace function my_wrapper_function(arg text) returns text language plpgsql as $$
begin
    begin
        return my_function(arg);
    exception when others then
        return 'error';
    end;
end
$$;

Use a PG_TRY block to catch the error raised by DirectFunctionCall() when the called function returns null allows you to handle the error and continue execution. In the PG_CATCH block, you can check whether the error was raised due to the called function returning null and clear the error if that is the case.