1. Nullable

By default, all types in PostgreSQL are nullable. Therefore, our default handling is to map parameters to the nullable type (T?) in .NET, which is similar to the Maybe type in Haskell.

If a PostgreSQL function has been defined as STRICT or RETURN NULL ON NULL INPUT, it will not be executed if any of its arguments are null. In these situations, we represent the type as the non-nullable T type in .NET. If you want to use nullable types for these types, you can set the AlwaysNullable variable to true in the dotnet_src/Engine.cs file.

Please note that after making changes to the dotnet_src/Engine.cs file, you will need to rebuild the Debian packages and reinstall pldotnet.

The following sections contain examples of how the generated C# and F# function should look for nullable and non-nullable types.

1.1 C#

The PostgreSQL function below was previously defined in the Basic usage page, which explains how to create functions using pldotnet in the database.

CREATE OR REPLACE FUNCTION are_integers_equal(a integer, b integer) RETURNS boolean AS $$
return a == b;
$$ LANGUAGE plcsharp;

The C# functions below illustrate the generated code for nullable and non-nullable types, respectively.

public static bool? are_integers_equal(int? a, int? b)
{
    return a == b;
}

public static bool? are_integers_equal(int a, int b)
{
    return a == b;
}

zsh:1: command not found: fm

CREATE OR REPLACE FUNCTION are_integers_equal(a integer, b integer) RETURNS boolean AS $$
a.Equals(b)
$$ LANGUAGE plfsharp;

The following F# code examples demonstrate the generated code for nullable and non-nullable types, respectively.

static member are_integers_equal (a: Nullable<int>) (b: Nullable<int>) : Nullable<bool> =
    a.Equals(b)

static member are_integers_equal (a: int) (b: int) : Nullable<bool> =
    a.Equals(b)

It is important to note that the Nullable<T> type is only used for F# structures, not classes. As a result, there is no change for class objects when using the nullable type on F#.

2. Array

All supported data types also support arrays of that type, whether they are single-dimensional or multi-dimensional. Currently, this is achieved using the Npgsql convention of mapping them to Array<T>, but this type mapping is cumbersome compared to the slightly different T[] representation.

The following are two PostgreSQL functions written using plcsharp and plfsharp, respectively, that modify an one-dimensional array of integers. The function takes in an array of integers and a integer as input, and returns the modified array.

CREATE OR REPLACE FUNCTION modify_1d_array(a integer[], b int) RETURNS integer[] AS $$
a.SetValue(b, 0);
return a;
$$ LANGUAGE plcsharp;

CREATE OR REPLACE FUNCTION modify_1d_array(a integer[], b int) RETURNS integer[] AS $$
a.SetValue(b.Value, 0);
a
$$ LANGUAGE plfsharp;

Here is the generated code for both the C# and F# versions of the function:

public static Array? modify_1d_array(Array? a, int? b)
{
    a.SetValue(b, 0);
    return a;
}

static member modify_1d_array (a: Array) (b: Nullable<int>) : Array =
    a.SetValue(b.Value, 0);
    a

When using the Array class, we do not need to worry about using multidimensional arrays, as demonstrated in the following code. However, this method has lower performance than using T[]. Therefore, we plan to use T[] for array mapping in the future, which will require the user to specify the dimensions of the array when creating the function.

CREATE OR REPLACE FUNCTION modify_array(a integer[], b int) RETURNS integer[] AS $$
let dim = a.Rank
match dim with
| 1 ->
    a.SetValue(b, 0) |> ignore
    a
| 2 ->
    a.SetValue(b, 0, 0) |> ignore
    a
| 3 ->
    a.SetValue(b, 0, 0, 0) |> ignore
    a
| _ ->
    Elog.Info("Returning the input array...") |> ignore
    a
$$ LANGUAGE plfsharp;

Now, with the following command:

select modify_array(array[1,2,3,4,5], 10);

We got the following output:

  modify_array
 --------------
  {10,2,3,4,5}

A second example:

select modify_array(array[[1,2,null],[3,4,5]], 10);

The output:

      modify_array
 -----------------------
  {{10,2,NULL},{3,4,5}}