1. Functions

In PostgreSQL, a function is a block of SQL code that is stored in the database and can be called by name from other parts of the code.

CREATE OR REPLACE FUNCTION function_name([arg_name arg_type]*) RETURNS <return_type> AS $$
<your_code>
$$ LANGUAGE <language> [STRICT];

Functions can accept input parameters and return output parameters or result sets. They can be used to encapsulate a specific task or set of tasks that may need to be performed multiple times within a larger block of code. Functions can be called from other functions, triggers, or statements within the database, and they can be used to improve the modularity and maintainability of the code. First, you use the syntax to create a normal function in SQL:

Here is important to clarify some tags in the code above:

• <language>: The chosen language, that can be either plcsharp for C# or plfsharp for F#;
• <your_code>: Here you insert the code in the language you choose;
• <return_type>: Datatype of the function's response;
• [STRICT]: This keyword is optional. When inserted, it forbids the function to accept NULL values;
• [arg_name arg_type]*: This is a list of arguments for the function.

Here is an example of a C# Function that takes two integer values and returns if they are equal to a boolean value:

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

Just for the record, the F# Version of the function would be the following:

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

now, to run the function you can just call it inside a statement. For example, the following SELECT:

SELECT are_integers_equal(0,1), are_integers_equal(1,1), are_integers_equal(NULL,1), are_integers_equal(NULL,NULL);

The expected output for both of them would be the following:

 are_integers_equal | are_integers_equal | are_integers_equal | are_integers_equal
------------------+------------------+------------------+------------------
 f                | t                | f                | t
(1 row)

2. Procedures

In PostgreSQL, a procedure is a named block of SQL statements that can be invoked repeatedly. Procedures can accept input parameters and return output parameters, and they can be used to encapsulate complex business logic or to perform common tasks.

For them, you can do it the same way as Functions, but they don't return any value. In the Future, they can change some data with SPIs, but for now, you can create as follows:

CREATE OR REPLACE PROCEDURE funcName([arg_name arg_type]*) AS $$
<your_code>
$$ LANGUAGE <language>;

Here is important to clarify some tags in the code above:

• <language>: The chosen language, that can be either plcsharp for C# or plfsharp for F#;
• <your_code>: Here you insert the code in the language you choose;
• [arg_name arg_type]*: This is a list of arguments for the function.

This is an example:

CREATE OR REPLACE PROCEDURE print_sum_procedure(a integer, b integer) AS $$
int c = (int)a + (int)b;
Elog.Info($"c = {c}");
$$ LANGUAGE plcsharp;

With the following CALL statements

CALL print_sum_procedure(10, 25);
CALL print_sum_procedure(1450, 275);

You get the following output:

INFO:  c = 35
CALL
INFO:  c = 1725
CALL

You can see that it didn't return any data, just logged the value on the shell.

3. DO Blocks

In PostgreSQL, a DO block is a block of code that is executed as a single unit within a larger block of code. DO blocks are similar to procedures in other programming languages, but they are not stored in the database and cannot be called by name. DO blocks are typically used for defining variables or for executing a series of statements that do not need to be called repeatedly.

The DO Blocks is similar to a script, and have the following characteristics:

• They don't return any value;
• Don't take arguments;
• Are not stored in the database after execution.

Here is a template of a DO Block:

do $$
<your_code>
$$ language <language>;

A simple example in F# would be this one:

do $$
    let c = 10 + 25
    Elog.Info("c = " + c.ToString())
$$ language plfsharp;

Upon execution, this block of code would print the following output on the shell:

INFO:  c = 35
DO