07. Examples

1. Introduction

pldotnet is a programming language that allows you to write Functions, Procedures, and DO blocks in PostgreSQL using either C# or F#. In this section, we will look at how to use different types of arguments and return values in all the types of structures blocks.

All function examples will have the SQL code to create it and an example of a SELECT statement to run it, followed by the output. On the other hand the procedure examples will have a CALL instead of a SELECT. All DO Block examples will have only the DO Block and the output.

You can find tests for all types of structures and for both plcsharp and plfsharp languages in the tests folder.

2. Functions

To create a PostgreSQL function, you can use the CREATE FUNCTION command. If a function with the same name already exists, you can use CREATE OR REPLACE FUNCTION to replace it. The AS $$ and $$ LANGUAGE keywords indicate the start and end of the function's body, which is written in C# or F# depending on the specified programming language. plcsharp refers to C# and plfsharp refers to F#.

2.1 C#

This section includes examples of PostgreSQL functions written using plcsharp, the C# procedural language.

2.1.1 Basic type

CREATE OR REPLACE FUNCTION sum2Integer(a integer, b integer) RETURNS integer AS $$
if (a == null)
    a = 0;

if (b == null)
    b = 0;

return a+b;
$$ LANGUAGE plcsharp;

The SELECT executing it:

SELECT sum2Integer(32770, 100);

The Output:

 sum2integer
-------------
       32870
(1 row)

2.1.2 Array

CREATE OR REPLACE FUNCTION sumIntegerArray(integers integer[]) RETURNS integer AS $$
Array flatten_integers = Array.CreateInstance(typeof(object), integers.Length);
ArrayHandler.FlatArray(integers, ref flatten_integers);
int integers_sum = 0;
for(int i = 0; i < flatten_integers.Length; i++)
{
    if (flatten_integers.GetValue(i) == null)
        continue;
    integers_sum = integers_sum + (int)flatten_integers.GetValue(i);
}
return integers_sum;
$$ LANGUAGE plcsharp STRICT;

The SELECT executing it:

SELECT sumIntegerArray(ARRAY[2047483647::integer, null::integer, 304325::integer, 4356::integer]);

The Output:

 sumintegerarray
-----------------
      2047792328
(1 row)

2.1.3 String

CREATE OR REPLACE FUNCTION concatenateText(a text, b text) RETURNS text AS $$
if (a == null)
a = "";

    if (b == null)
        b = "";

    string c = a + " " + b;
    return c;

$$
LANGUAGE plcsharp;

The SELECT executing it:

SELECT concatenateText('red', 'blue');

The Output:

 concatenatetext
-----------------
 red blue
(1 row)

2.1.4 Struct types

CREATE OR REPLACE FUNCTION modifyNetMask(my_inet INET, n INT) RETURNS INET AS
$$
return (my_inet.Address, my_inet.Netmask + n);
$$
LANGUAGE plcsharp STRICT;

The SELECT executing it:

SELECT modifyNetMask(INET '192.168.0.1/24', 6);

The Output:

 modifynetmask
----------------
 192.168.0.1/30
(1 row)

2.2 F#

This section contains some PostgreSQL functions written in plfsharp, the F# procedural language.

2.2.1 Basic type

CREATE OR REPLACE FUNCTION distanceBetweenPointsFSharp(pointa point, pointb point) RETURNS float8 AS $$
let pointa = if pointa.HasValue then pointa.Value else  NpgsqlPoint(0.0, 0.0)
let pointb = if pointb.HasValue then pointb.Value else  NpgsqlPoint(0.0, 0.0)
let dif_x = pointa.X - pointb.X
let dif_y = pointa.Y - pointb.Y
let distance = Math.Sqrt(dif_x * dif_x + dif_y * dif_y)
distance
$$ LANGUAGE plfsharp;

The SELECT executing it:

SELECT distanceBetweenPointsFSharp(POINT(1.5,2.75), POINT(3.0,4.75));

The Output:

 distancebetweenpointsfsharp
-----------------------------
                         2.5
(1 row)

2.2.2 Array

CREATE OR REPLACE FUNCTION increaseCircleFSharp(orig_value CIRCLE) RETURNS CIRCLE AS $$
let orig_value = if orig_value.HasValue then orig_value.Value else NpgsqlCircle(NpgsqlPoint(0, 0), 3)
NpgsqlCircle(orig_value.Center, (orig_value.Radius + 1.0))
$$ LANGUAGE plfsharp;

The SELECT executing it:

SELECT increaseCircleFSharp(NULL::CIRCLE);

The Output:

 increasecirclefsharp
----------------------
 <(0,0),4>
(1 row)

2.2.3 String

CREATE OR REPLACE FUNCTION modifyXmlFSharp(a XML) RETURNS XML AS $$
    let mutable new_xml: string = ""
    if System.Object.ReferenceEquals(a, null) ||a.Equals("") then
        new_xml <- "<?xml version=\"1.0\" encoding=\"utf-8\"?><title>Hello, World, it was null!</title>"
    else
        new_xml <- a
    new_xml <- (new_xml.Replace("Hello", "Goodbye")).Replace("World", "beautiful World")

    new_xml
$$ LANGUAGE plfsharp;

The SELECT executing it:

SELECT modifyXmlFSharp('<?xml version="1.0" encoding="utf-8"?><title>Hello, World!</title>'::XML)::text;

The Output:

                                modifyxmlfsharp
--------------------------------------------------------------------------------
 <?xml version="1.0" encoding="utf-8"?><title>Goodbye, beautiful World!</title>
(1 row)

2.2.4 Struct type

CREATE OR REPLACE FUNCTION modifyIPFSharp(my_inet INET, n INT) RETURNS INET AS $$
let struct (address, netmask) = if my_inet.HasValue then my_inet.Value else (IPAddress.Parse("127.0.0.1"), 21)
let bytes = address.GetAddressBytes()
let size = bytes.Length
bytes[size-1] <- bytes[size-1] + byte n.Value
struct (IPAddress(bytes), netmask)
$$ LANGUAGE plfsharp;

The SELECT executing it:

SELECT modifyIPFSharp(INET '2001:db8:3333:4444:5555:6666:1.2.3.4/25', 20);

The Output:

             modifyipfsharp
-----------------------------------------
 2001:db8:3333:4444:5555:6666:102:318/25
(1 row)

3. Procedures

To create a PostgreSQL procedure, you can use the CREATE PROCEDURE command. If a procedure with the same name already exists, you can use CREATE OR REPLACE PROCEDURE to replace it. The AS $$ and $$ LANGUAGE keywords mark the beginning and end of the procedure's body, which is written in either C# (plcsharp) or F# (plfsharp), depending on the specified programming language.

3.1 C#

This section includes examples of PostgreSQL procedures written using plcsharp, the C# procedural language.

3.1.1 Basic type

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

The CALL statements executing it:

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

The Output:

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

3.1.2 Array

CREATE OR REPLACE PROCEDURE printSmallestValueProcedure(doublevalues double precision[]) AS $$
double min = double.MaxValue;
for(int i = 0; i < doublevalues.Length; i++)
{
    double value = (double)doublevalues.GetValue(i);
    min = min < value ? min : value;
}
Elog.Info($"Minimum value = {min}");
$$ LANGUAGE plcsharp;

The CALL statements executing it:

CALL printSmallestValueProcedure(ARRAY[2.25698, 2.85956, 2.85456, 0.00128, 0.00127, 2.36875]);
CALL printSmallestValueProcedure(ARRAY[2.25698, -2.85956, 2.85456, -0.00128, 0.00127, 12.36875, -23.2354]);

The Output:

INFO:  Minimum value = 0.00127
CALL
INFO:  Minimum value = -23.2354
CALL

3.2 F#

This section contains examples of PostgreSQL procedures written using plfsharp, the F# procedural language.

3.2.1 Basic type

CREATE OR REPLACE PROCEDURE printSumProcedureFSharp(a integer, b integer) AS $$
let c = a.Value + b.Value
Elog.Info("[F#] c = " + c.ToString())
$$ LANGUAGE plfsharp;

The CALL statements executing it:

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

The Output:

INFO:  [F#] c = 35
CALL
INFO:  [F#] c = 1725
CALL

3.2.2 String

CREATE OR REPLACE PROCEDURE sayHelloFSharp(name TEXT) AS $$
let message = "Hello, " + name + "! Welcome to plfsharp."
Elog.Info(message)
$$ LANGUAGE plfsharp;

The CALL statements executing it:

CALL sayHelloFSharp('Mikael'::TEXT);
CALL sayHelloFSharp('Rosicley'::TEXT);
CALL sayHelloFSharp('Todd'::TEXT);

The Output:

INFO:  Hello, Mikael! Welcome to plfsharp.
CALL
INFO:  Hello, Rosicley! Welcome to plfsharp.
CALL
INFO:  Hello, Todd! Welcome to plfsharp.
CALL

4. `DO` blocks

DO Blocks are temporary scripts that are executed and then discarded.

To create a DO block, use the DO $$ and $$ LANGUAGE keywords to define the beginning and end of the block's body, which is written in either C# (plcsharp) or F# (plfsharp).

4.1 C#

This section includes examples of PostgreSQL DO blocks written using plcsharp, the C# procedural language.

4.1.1 Basic type

DO $$
    int c = 1450 + 275;
    Elog.Info($"c = {c}");
$$
language plcsharp;

The output:

INFO:  c = 1725
DO

4.1.2 Array

do $$
    double[] doublevalues = {2.25698, 2.85956, 2.85456, 0.00128, 0.00127, 2.36875};
    double min = double.MaxValue;
    for(int i = 0; i < doublevalues.Length; i++)
    {
        double value = (double)doublevalues.GetValue(i);
        min = min < value ? min : value;
    }
    Elog.Info($"Minimum value = {min}");
$$ language plcsharp;

The output:

INFO:  Minimum value = 0.00127
DO

4.2 F#

This section includes examples of PostgreSQL DO blocks written using plfsharp, the F# procedural language.

4.2.1 Basic type

DO $$
    let c = 1450 + 275
    Elog.Info("c = " + c.ToString())
$$ language plfsharp;

The output:

INFO:  c = 1725
DO

4.2.2 String

DO $$
    let message = "PL.NET IS THE BEST PROCEDURE LANGUAGE!"
    Elog.Info(message)
$$
language plfsharp;

The output:

INFO:  PL.NET IS THE BEST PROCEDURE LANGUAGE!
DO

07. Examples

1. Introduction

2. Functions

2.1 C#

2.1.1 Basic type

2.1.2 Array

2.1.3 String

2.1.4 Struct types

2.2 F#

2.2.1 Basic type

2.2.2 Array

2.2.3 String

2.2.4 Struct type

3. Procedures

3.1 C#

3.1.1 Basic type

3.1.2 Array

3.2 F#

3.2.1 Basic type

3.2.2 String

4. DO blocks

4.1 C#

4.1.1 Basic type

4.1.2 Array

4.2 F#

4.2.1 Basic type

4.2.2 String

4. `DO` blocks