Stored Procedure

These are bits of reusable code that runs on the PostgreSQL server, to write them use use plpgsql, these cannot be ported from one database to another, and the general goal is to have fewer statements, this can cause database lock in due to the inability of porting these stored procedure.

These are like internal scripts that can be invoked, a great example would be if an application needs around 10 SQL queries to be run to perform a task, but this can be slow to process them due to the back and forth that happens, using stored procedure there would be a single point to run all the 10 SQL queries resulting in better performance

One should have a strong reason to use them

If you have a major performance problem these are great.
Harder to test / modify
No database portability

Syntax (MySQL)

DELIMITER //
CREATE PROCEDURE transfer_funds(
    IN from_account INT,
    IN to_account INT,
    IN amount DECIMAL(10,2)
)
BEGIN
    START TRANSACTION;
    UPDATE accounts SET balance = balance - amount WHERE id = from_account;
    UPDATE accounts SET balance = balance + amount WHERE id = to_account;
    COMMIT;
END //
DELIMITER ;

Call it:

CALL transfer_funds(1, 2, 500.00);

Parameter Modes

Mode	Description
`IN`	Input only — the caller passes a value in (default)
`OUT`	Output only — the procedure sets a value for the caller to read
`INOUT`	Both — the caller passes a value in, the procedure modifies it

CREATE PROCEDURE get_balance(IN acct INT, OUT bal DECIMAL)
BEGIN
    SELECT balance INTO bal FROM accounts WHERE id = acct;
END;