Stored Procedures

Stored procedures are collections of SQL statements that can include control-flow constructs such as conditionals (IF), loops, and branching logic similar to what you would find in a general purpose programming language. They are stored and executed within the database and can be invoked by applications or other database routines.

Their main purpose is to reduce application-to-database round trips and to execute logic closer to the data, which can improve performance and efficiency for certain workloads.

A key downside is that the logic lives in the database rather than in the application codebase. This can increase cognitive load for developers and complicate versioning, testing, and deployment.

CREATE PROCEDURE transfer_money(from_acc INT, to_acc INT, amount DECIMAL)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE accounts SET balance = balance - amount WHERE id = from_acc;
    UPDATE accounts SET balance = balance + amount WHERE id = to_acc;
    COMMIT;
END;
$$;

CALL transfer_money(1, 2, 100.00);