1. Settings

You can customize the pldotnet source code by modifying the dotnet_src/Engine.cs file. There, you can find the following options to change:

• AlwaysNullable: By default, this is set to false, meaning that pldotnet will only map data types as nullable when the user function is created without the STRICT option. However, you can set it to true to always map data types as nullable.
• PrintSourceCode: The default is false, but you can change it to true to print the generated code to the console before compiling the dynamic codes.
• SaveSourceCode: By default, this is set to false, but you can change it to true to save the generated code in a directory.
• PathToSaveSourceCode: The default location to save the generated code is /tmp/PlDotNET/GeneratedCodes, this only be used when SaveSourceCode = true, the directory needs to have 0700 mode.
• PathToTemporaryFiles: The default location of pldotnet temporary files is /tmp/PlDotNET/. This is used with plfsharp to save some files while compiling the dynamic F# code. This directory must also have 0700 mode.

2. PostgreSQL reports

In order to report messages in PostgreSQL, you can utilize the Elog class and its various methods. The following example demonstrates how to use these methods within a DO block:

do $$
Elog.Debug("Here is a debug message...");
Elog.Log("Here is a log message...");
Elog.Info("Here is an info message...");
Elog.Notice("Here is a notice message...");
Elog.Warning("Here is a warning message...");
$$ language plcsharp;

The output of this inline function is similar to what you would see when using the appropriate Python functions to report messages with pl/python:

INFO:  Here is an info message...
NOTICE:  Here is a notice message...
WARNING:  Here is a warning message...
DO

To raise an error and stop code execution, you can use the Elog.Error method or throw an Exception. The following example illustrates this concept:

do $$
try {
    Elog.Error("Here is the first error message...");
}
catch (Exception e) {
    Elog.Info($"***{e.Message}***");
    throw new Exception("Here is the second error message...");
    Elog.Info("This message must not be executed.");
}
$$ language plcsharp;

Here is the output of the above DO block:

INFO:  ***Here is the first error message...***
WARNING:  Exception: Here is the second error message...
ERROR:  PL.NET function "plcsharp_inline_block".

3. Error handling

3.1 Compilation errors

When a user creates a function using plcsharp or plfsharp procedural languages, the pldotnet extension generates the corresponding dynamic codes and attempts to compile them. In case of any errors or issues with the user's code, the compilation process, which uses Roslyn or FSharp.Compiler.Service, will fail and the pldotnet will report the errors to the user.

As an example, consider the following function that contains three different errors that will result in a failure during the compilation process:

CREATE FUNCTION compilation_errors() RETURNS INTEGER AS $$
    int a = b;
    string c = a;
    return c;
$$ language plcsharp;

When this function is attempted to be compiled, pldotnet will report the compilation error like this:

namespace PlDotNET.UserSpace
{
    public static class UserFunction
    {
        public static int? compilation_errors()
        {
#line 1
            int a = b;
            string c = a;
            return c;
        }
    }
}
**********
Here are the compilation results:
(1,21): error CS0103: The name 'b' does not exist in the current context
(2,24): error CS0029: Cannot implicitly convert type 'int' to 'string'
(3,20): error CS0029: Cannot implicitly convert type 'string' to 'int?'

WARNING:  SystemException: PL.NET could not compile the generated C# code.
ERROR:  PL.NET function "compilation_errors".

It's important to note that due to the error, the function will not be created in the database, meaning it will not be available for execution. To use the function, the user will need to fix the errors in the code and try to create the function again. This is a safety mechanism that prevents the storage and execution of faulty code in the database.

3.2 Runtime errors

Errors that occur only during the execution of a program, known as runtime errors, can include issues such as accessing an index outside of an array's bounds or dividing by zero. These types of errors cannot be detected during the compilation process and are only reported when the program is executed and the specific function is called.

For example, consider the following procedure, where we define an array a with a length of 5. However, the code attempts to assign a value to an index that exceeds the size of the array:

CREATE PROCEDURE runtime_error() AS $$
    int[] a = { 0, 1, 2, 3, 4 };
    a[5] = 5;
$$ language plcsharp;

Even though this procedure can be created in the database, and will return the message CREATE PROCEDURE, if it is called using the command CALL runtime_error(), an error message will be displayed:

WARNING:  IndexOutOfRangeException: Index was outside the bounds of
the array.
ERROR:  PL.NET function "runtime_error".

An example of another runtime error is demonstrated in the following procedure, where an attempt is made to convert a nullable integer argument to a non-nullable integer variable:

CREATE PROCEDURE runtime_error(a INTEGER) AS $$
    int b = (int)a;
$$ language plcsharp;

While this procedure may work when the argument passed in is not null, if it is called with the command CALL runtime_error(NULL::INTEGER), it will fail and generate the following error message:

WARNING:  InvalidOperationException: Nullable object must have a value.
ERROR:  PL.NET function "runtime_error".

This error occurs because the argument passed in is null, and C# cannot convert a null value to a non-nullable type. To avoid this error, the argument should be checked for null before the conversion, or the variable b should be defined as nullable integer (int?).