Understanding Logic, Reusability and Integrity On SQL ; Procedures, Functions and Transactions.

#sql #data #postgres #analytics

SQL is widely known for data querying and manipulation but systems do grow; data becomes larger; processes become repetitive and operations become sensitive. SQL has some features which enables it to be considered a fully fledged programming language. Some of the features which I discuss in this article are procedures, functions and transactions. Each of these concepts serve distinct purposes.
Procedures execute operations, functions return values, and transactions ensure those operations are safe.

Stored Procedures

These are set of SQL statements stored in the database and executed as a unit which are used to perform tasks such as UPDATE, INSERT, DELETE etc.
They are triggered by calling them and passing the expected parameters by the procedure.
Here is an example of a procedure:

CREATE OR REPLACE PROCEDURE increase_salary(p_dept TEXT, p_percent NUMERIC)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE employees
    SET salary = salary + (salary * p_percent / 100)
    WHERE department = p_dept;
END;
$$;

Here is how a procedure is called:

CALL increase_salary('IT', 10);

Functions

This is a reusable logic block which can return values and can be used inside queries for data selection.
It can be used with SELECT, WHERE and is great for reusability.

CREATE OR REPLACE FUNCTION get_avg_salary(p_dept TEXT)
RETURNS NUMERIC
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN (
        SELECT AVG(salary)
        FROM employees
        WHERE department = p_dept
    );
END;
$$;

Here is an example of how the function can be used:

SELECT name, salary
FROM employees
WHERE salary > get_avg_salary(department);

Transactions

Transactions are used to group a set of code operations in which if any of it fails then the whole execution is aborted. This ensures that data is only changed when the full code execution structure is successful.
Transactions are best for data safety as they prevent partial updates.

BEGIN;

UPDATE accounts
SET balance = balance - 100
WHERE id = 1;

UPDATE accounts
SET balance = balance + 100
WHERE id = 2;

COMMIT;

If it fails:

ROLLBACK;

How They Work Together

It will not be complete to conclude the article without showing an example of how these concepts can be used together.
Here is a simple scenario showing the use of the three concepts interdependently.
Function:

CREATE OR REPLACE FUNCTION get_balance(acc_id INT)
RETURNS NUMERIC AS $$
BEGIN
    RETURN (SELECT balance FROM accounts WHERE id = acc_id);
END;
$$ LANGUAGE plpgsql;

Procedure:

CREATE OR REPLACE PROCEDURE transfer_money(from_id INT, to_id INT, amount NUMERIC)
LANGUAGE plpgsql
AS $$
BEGIN
    IF get_balance(from_id) < amount THEN
        RAISE EXCEPTION 'Insufficient funds';
    END IF;

    UPDATE accounts SET balance = balance - amount WHERE id = from_id;
    UPDATE accounts SET balance = balance + amount WHERE id = to_id;
END;
$$;

Transaction:

BEGIN;
CALL transfer_money(1, 2, 100);
COMMIT;

Conclusion

SQL has a wide range of capabilities more than just being a querying language. Having features such as procedures, functions, transactions and many others helps it to be an efficient tool for use on data in whichever way needed. Mastering it comes a long way in helping the analysis of data directly from the database.

DEV Community