Yesterday’s post, “Passing Default Parameters to SQL Server Functions in C# & .NET”, looked at how to call functions in Microsoft SQL Server T-SQL functions and pass them default values.

In today’s post, we will look at the same thing for PostgreSQL.

The function we will use is as follows:

CREATE OR REPLACE FUNCTION get_day_of_week(day INT)
    RETURNS TEXT
    LANGUAGE plpgsql
AS $$
BEGIN
    RETURN CASE day
               WHEN 1 THEN 'Monday'
               WHEN 2 THEN 'Tuesday'
               WHEN 3 THEN 'Wednesday'
               WHEN 4 THEN 'Thursday'
               WHEN 5 THEN 'Friday'
               WHEN 6 THEN 'Saturday'
               ELSE 'Sunday'
        END;
END;
$$;

The function is invoked as follows, using Dapper:

using Dapper;
using Npgsql;

const string connection = "host=localhost;username=myuser;password=mypassword;database=spies";

await using (var cn = new NpgsqlConnection(connection))
{
    var result = await cn.QuerySingleAsync<string>("SELECT get_day_of_week(@Day)", new { Day = 3 });
    Console.WriteLine(result);
}

If you want to call the function with its default value, you invoke it like this:

await using (var cn = new NpgsqlConnection(connection))
{
    var result = await cn.QuerySingleAsync<string>("SELECT get_day_of_week()");
    Console.WriteLine(result);
}

Note that the invocation is not passing any parameters at all:

 var result = await cn.QuerySingleAsync<string>("SELECT get_day_of_week()");

TLDR

You can invoke functions in PostgreSQL and pass any required parameters. You can also instruct the database to use the function’s default parameters by not passing anything.

The code is in my GitHub.

Happy hacking!