Enhanced PL/pgSQL functions using conditionals and validation checking in PostgreSQL.

In today’s example, I will add to a previous post by enhancing an existing PL/pgSQL function utilizing conditionals and defined validation checking.

Note: All data, names or naming found within the database presented in this post, are strictly used for practice, instruction and testing purposes. It by no means depicts actual data belonging to or being used by any party or organization.

I will be using Xubuntu Linux 16.04.2 LTS (Xenial Xerus), PostgreSQL 9.6.2 and pgAdmin for these exercises.

Getting acquainted with the cut_log table as shown below:

Previously constructed, this function is designed to help automate a potentially redundant task of updating the cuts and new_lt columns, found in the cut_log table, for assets of pipe:

To strive in developing the best solutions, some form of validation checking should be incorporated into this function to construct certain rules for specific instances of events. Validating that the pipe_id is present in the table and the cuts value is a valid length of greater than zero, would be solid considerations. With a different approach to the same function, one option of accomplishing these validation checks is presented in the below example:

This updated approach resembles the original function however at line 6, differences begin. Length validation checking starts with the conditional IF-THEN statement. IF-THEN statements allow you to execute specific commands between the THEN and END IF, only if the condition (boolean expression) is true. In another sense, this concept would be similarly related as such:

IF this_expression_is_true THEN
carry_out_this_operation_or_execution

Let’s dig down a little deeper to gain a better understanding concerning boolean expressions and boolean values. When a boolean expression is evaluated, it results in a true or false boolean value. In the expression 2 > 1, we know this is true because 2 is greater than 1. This expression evaluates to true. However, the expression 13 > 33 evaluates to false, since 13 is not greater than 33. Including these boolean expression checks and conditionals in the function, provides measures to take certain actions depending upon the result of the test condition. The line:

IF cut_amt > 0 THEN

is checking the value of the cut_amt parameter using the greater than comparison operator when the function is called, and will THEN do something (carry out operations) if that value is greater than zero. If determined the value is greater than zero (evaluates to true), execution carries out as follows:

EXECUTE    
    'UPDATE cut_log 
    SET cuts = $2, new_lt = length - $2 
    WHERE pipe_id = $1'
    using jt_id, cut_amt;

The above command string after the EXECUTE statement is identical to the first version of the cut_input function but now the difference is, it will only work or execute if the value of cut_amt is greater than zero, which the IF-THEN statement has checked and determined it to be true.

But what if the cut_amt parameter is zero or a negative value? This line takes care of that:

ELSE
        RAISE EXCEPTION 'Error! Cuts value must be greater than zero.';

If the value of cut_amt is less than zero (evaluated to false by the IF-THEN statement), control is passed to the ELSE statement. In human dialog terms, the IF-THEN statement is saying “Hey, I am checking the cut_amt value and it is zero or less. I cannot do anything with it now. So you (ELSE statement) take it from here.” By implementing this ELSE statement, you can execute an alternate set of statements since the value of cut_amt is zero or less than zero (condition in the IF-THEN statement is not true).

---

It is worth mentioning that all IF-THEN statements do not have or need an accompanying ELSE statement.

---

The line,

RAISE EXCEPTION 'Error! Cuts value must be greater than zero.';

is not included in the first version of the cut_input function. The RAISE statement is used to report messages and raise errors. EXCEPTION is one of several available level options, used for specifying the severity of the error. EXCEPTION is the default level option. After EXCEPTION, a simple string literal of the error message text to be reported can be provided. In this example ‘Error! Cuts value must be greater than zero.’ is what is reported. Let’s test the function so far and make sure it works as expected with the cut_amt values:

Calling the function in the above snippet, a value of -10.0 is passed in for the cut_amt parameter. Since -10.0 > 0 is false, program execution is sent to the ELSE statement, which can be seen with the displayed error message informing the user that an unacceptable value has been passed in.

Calling the function with a value of 0.0 as the argument?

Identical results.

0.0 > 0 evaluates to false and execution is again passed to the ELSE statement. Once again, the error message informs the user of an unacceptable parameter value.

The cut_input function is now checking for acceptable values for the cut_amt parameter and handling the values according to the provided specifications.
Continuing with the function body:

EXECUTE 
    'SELECT * FROM cut_log
    WHERE pipe_id = $1'
    INTO STRICT my_row
    USING jt_id;
    RETURN 'pipe_id: '||my_row.pipe_id||' updated successfully.';
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                RAISE EXCEPTION 'Error! pipe_id: % was not found in the table.', $1;

---

See the online documentation concerning the interpretation of SELECT with INTO and PostgreSQL’s regular SELECT INTO command.

---

Two differences in this area of the function to note that is not present in the first version are the STRICT option and another EXCEPTION clause. By specifying the STRICT option in the INTO clause, the query now must return exactly one row or a run-time error will be reported. However, checks for this condition must be implemented in the function to take action when this error occurs. Studying the code further:

EXCEPTION 
     WHEN NO_DATA_FOUND THEN 
        RAISE EXCEPTION 'Error! pipe_id: % was not found in the table.', $1;

To catch the error, an EXCEPTION block is used. NO_DATA_FOUND is a PL/pgSQL error code for no rows, meaning, no rows were returned from the query. By specifying in the WHERE clause of the query that the pipe_id has to be the same (matches) as the jt_id argument supplied to the cut_input function, if a row is not returned, then we can assume that record is not present in the pipe_id column of this table.
Note: It is worth mentioning to be mindful of spelling and letter case of the jt_id parameter passed into the function. ‘Pipe-123’ and ‘PIPE-123’ are not the same. ‘PIPE-123’ could very well be present in the table but if ‘Pipe-123’ is passed into the function as the jt_id parameter, this error will occur as a result of the incorrect or improper case and/or spelling. Not because the jt_id passed into the function does not exist in the table.
As seen here:

WHEN NO_DATA_FOUND THEN
    RAISE EXCEPTION 'Error! pipe_id: % was not found in the table.', $1;

Once more using:

RAISE EXCEPTION 'Error! jt_id: % was not found in the table.', $1;

To report this error. Notice the % placeholder embedded within the string literal. Also just after the comma, there is $1, meaning substitute the % in the error message for whatever the jt_id argument ($1) of the cut_input function is.
Testing the function now by passing in a jt_id that does not exist in the table:

The error occurs and is caught since ‘SomePipe-292’ did not return a row from the SELECT query.

By passing in a jt_id that exists in the table and an acceptable value for the cut_amt parameter:

And confirming the output results below:

concludes the function works as designed to the specifications.

This is but one solution to check for existing values within a database table column. There are many other possibilities for achieving these same results. Depending upon certain needs, checking this value first, may be a top priority. Or your only priority. With PostgreSQL and PL/pgSQL, we have that ability to reach the same conclusion, in multiple different ways.

Hopefully, through this example, you have an idea of how using conditionals and validation checks can enhance and strengthen PL/pgSQL functions, opening many possibilities within your database programming needs. With data safety and integrity paramount to successful operations, incorporating these types of practices can only help us face and conquer these challenges.

Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening here.

To receive notifications for the latest post from this blog via email, please subscribe by clicking the ‘Follow Digital Owl’s Prose’ button in the sidebar!

---

Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not hard and fast rules or the utmost best solution(s). Your particular goals and needs may vary. Use those practices that best meet and benefit your particular goals and needs.

---