Creating SQL functions

Introduction #

The Navigator365 system is a no-code system, meaning there’s no need to write code in any programming language to develop the application. Furthermore, unlike low-code systems, there’s no need to create snippets in scripting languages ​​like SQL. Of course, we can never anticipate every possible situation, so it’s possible that snippets will be necessary for certain specific functionalities. However, when designing the Navigator365 system, we strive to minimize such situations. When creating new functionalities at the form, list, or process (action) level, we strive to design the system so that the application designer doesn’t have to resort to low-code methods.

For this reason, we’ve introduced certain restrictions on building queries that can be used within the system. The primary limitation is the lack of access to database tables. When writing a query, we can use mentions (references to form fields), SQL operators, etc., but we won’t access the data stored in the tables. While this isn’t a major limitation in form conditions, it can be a serious limitation if we want to create a list based on an SQL query or use the Execute SQL action. The result of executing elements configured this way will be a “No data found” message. At this point, we must first define the SQL function we will reference in the query.

Why is this limitation possible when we can write functions anyway? Primarily, convenience, elegance, and security.

Convenience – when creating an application in Navigator365 and looking at the configuration, we see a simple function reference instead of a multi-line SQL script. This solution is much more readable and simply looks better in the configuration.

Elegance – when writing documentation, it’s better to describe the set of functions used in the system, along with their parameters and returned results, rather than providing full scripts. Moreover, functions can be used in many places. Let’s look at a simple example – a function that validates data. We use it in many places, it works fine, but suddenly a change is needed. Instead of searching the entire system, we change a single function.

Security – we can limit access to creating functions to a small group of administrators and open application development to a wider group of operators. This ensures that the application developer will not break anything in the database and will only rely on mechanisms created in a controlled manner by the administrator.

Database Access #

Navigator365 uses a PostgreSQL database. The pgAdmin tool is installed along with the system and database installation. This is a web-based tool, accessible from the same address as the system, for example:

https://my.servewr.name.com/pgadmin/

Upon opening the page, a login window appears.

Login details will be provided to the administrator after system installation.

After logging in, we see the home page and a list of servers. Typically, there will be one server and one or more databases, for example, if we have a production and test environment set up.

On the home page, pay attention to the links – there are links to the PostgreSQL documentation, the pgAdmin homepage, and the PostgreSQL community support page. These resources will be very helpful in everyday work, especially when we’re just starting to explore this database.

Before we begin, we need to find the appropriate system elements. To do this, open the Databases > Database_Name > Schemas > Public branch, where we already have elements such as tables, functions, and procedures. The next screenshot shows an example of the Documents table with the Query tools window open, where we can test our queries.

Creating a Function #

If we want to create a new function, we need to find the Functions branch and select Create > Function, which is available by right-clicking.

In the following tabs, we need to define our function.

The first thing we need to do is name our function. The naming convention is arbitrary, but it’s a good idea to stick to a specific format.

By default, we use the following schema:

_fn_get_form_description()

The underline indicates that this is a private function, available only in our environment (the out-of-the-box system already has many functions starting with fn, and these should not be edited). Get – retrieves data. The form name and feature define the function’s purpose, e.g., _fn_get_contractor_nip_validation

In the Definition tab, we define the return data type and function parameters, e.g.,

In the Code tab, we have the most important part of our configuration – the definition of our function. Here, we simply enter the SQL code to be executed.

Below is the full description of the function:

DECLARE
    clean_nip TEXT;
    check_sum INT := 0;
    weights INT[] := ARRAY[6,5,7,2,3,4,5,6,7];
    i INT;
BEGIN
    -- Remove all non-digital characters
    clean_nip := regexp_replace(nip, '[^0-9]', '', 'g');
    
    -- Chech if lenght is 10
    IF length(clean_nip) <> 10 OR clean_nip !~ '^[0-9]{10}$' THEN
        RETURN FALSE;
    END IF;
    
    -- Calculate checksum (first 9 digits * weight)
    FOR i IN 1..9 LOOP
        check_sum := check_sum + 
            (substring(clean_nip, i, 1)::INT * weights[i]);
    END LOOP;
    
-- Check if the remainder of the division by 11 equals the 10th digit
    IF (check_sum % 11) = substring(clean_nip, 10, 1)::INT THEN
        RETURN TRUE;
    END IF;
    
    RETURN FALSE;
END;

The last tab we need to pay attention to is the Security tab. This contains the function permissions.

Remember to add the navigator365_readonly user here. This is the user who will run our function. If they don’t have permissions, the function won’t run.

Now, simply configure the validation condition on the form, for example:

The result can be seen in the screenshot below:

Queries to Navigator365 Tables #

If we want to access database tables and retrieve this data, for example, to display it in a list, we need to know the database structure. Below are the basic tables we can use.

• Documents: The basic table where all system documents are stored. This also includes system documents such as users and user groups. If we want to extract documents based on a specific form, we need to search for documents with the appropriate form_id value.
• Forms: This table stores form definitions. If we want to limit the documents in our function to a single form, it’s best to add this table (join) and add a condition on the form’s guid.
• Document_choice_values: This table stores values ​​from selection attributes such as checkboxes, radiobuttons, or dropdown lists. This table includes columns such as value_id, which indicates the ID of the selected element, value_name_pl, which stores the element’s value, and document_id, which stores the ID of the document containing our attribute. It’s worth noting that this table doesn’t allow us to easily associate a specific record from document_choice_values ​​with a specific attribute on the form. We have a form_attribute_id column, but we need to specify the attribute ID here, which isn’t explicitly provided. This is made easier by the next table:
• Form_attributes: This table stores attribute configurations on the form. The most important thing for us is that we have a guid column here, which we can easily read from the form in the system. A simple query: SELECT id FROM form_attributes WHERE guid = ‘guid from form’ will find the id, which we will use to search the document_choice_values ​​table.

Simple attributes, such as text, numbers, and dates, are stored in columns in the Documents table. To reference them, we need to know which column they are stored in and enter it directly in the query – in the example below, we reference the date fields through the date2 and date3 columns.

CREATE OR REPLACE FUNCTION public._fn_get_absences_conflict(IN data_od date,IN data_do date,IN user_id integer)
    RETURNS TABLE(id integer, signature text, date_from date, date_to date, user_conflict_id integer, user_name text)
    LANGUAGE 'plpgsql'
    VOLATILE
    PARALLEL UNSAFE
    COST 100    ROWS 1000 
    
AS $BODY$
BEGIN
RETURN QUERY
select 
    documents.id, 
    documents.signature, 
    documents.date2, 
    documents.date3,
    document_choice_values.value_id, 
    document_choice_values.value_name_pl 
from documents 
    left join forms on documents.form_id=forms.id 
    left join document_choice_values on document_choice_values.document_id=documents.id
where 
documents.is_deleted is false
and forms.guid='b3416b30-e276-469e-b8ec-c0a631d345a8'
and document_choice_values.form_attribute_id=(select form_attributes.id from form_attributes where guid='a83a8615-4ecf-4e3a-bad8-193dc2766696')
and document_choice_values.value_id <> user_id
and 
    (
     (date2<data_od and  date3>data_do)
     OR
     (date2>data_od and date2<data_do)
     or
     (date3>data_od and date3<data_do)
    );
    
END;
$BODY$;