SQL Stored Procedures and Functions in MySQL and PostgreSQL

At the Malwa Mart returns desk in Rajwada, there is a laminated card taped next to the till. It lists four steps: check the bill number, check the item was on that bill, add the money back to the customer's wallet, and write the return in the register.

A new cashier does not need to know why each step exists. They read "Return for bill 104, Poha" and follow the card. Every cashier in every store does it the same way, and when the owner changes the rule, he reprints one card.

In a database, that card is a stored procedure. "Bill 104" and "Poha" are its parameters, the four steps are its body, and the cashier reading the card is the application running CALL.

A stored procedure is a named, precompiled set of SQL statements stored in the database, which applications execute by name with parameters, instead of sending each statement separately.

This article follows SQL transactions and ACID, because most useful procedures wrap a transaction. The syntax shown targets MySQL 8 and PostgreSQL 16. SQLite has no stored procedures, so the runnable examples show how a Python application gets the same behaviour.

What is a stored procedure in SQL?

A procedure lives in the database next to the tables. Once created, any application, report tool or user with permission can run it by name.

Without a procedure, the application sends every step across the network. With one, it sends a single CALL and the database runs the steps next to the data.

The main benefits:

• One round trip. The application sends one CALL instead of four or five statements, which matters on slow networks.
• Same rules everywhere. The Android app, the website and the store tills all call the same procedure, so the payment logic cannot drift between them.
• Tighter security. You can grant a user permission to run the procedure but not to touch the tables directly.

Creating a stored procedure in MySQL

MySQL procedures use BEGIN ... END blocks. Because the body contains semicolons, the MySQL command-line client needs a temporary DELIMITER so it knows where the whole procedure ends.

-- MySQL 8
DELIMITER //
CREATE PROCEDURE place_wallet_order(
    IN  p_order_id    INT,
    IN  p_customer_id INT,
    IN  p_store_id    INT,
    IN  p_amount      INT,
    OUT p_status      VARCHAR(40)
)
BEGIN
    DECLARE v_balance INT;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_status = 'failed';
    END;

    START TRANSACTION;
    SELECT balance INTO v_balance FROM wallets
    WHERE customer_id = p_customer_id FOR UPDATE;      -- lock the wallet row

    IF v_balance IS NULL OR v_balance < p_amount THEN
        ROLLBACK;
        SET p_status = 'insufficient balance';
    ELSE
        INSERT INTO orders (order_id, customer_id, store_id, amount, payment_mode)
        VALUES (p_order_id, p_customer_id, p_store_id, p_amount, 'Wallet');
        UPDATE wallets SET balance = balance - p_amount WHERE customer_id = p_customer_id;
        COMMIT;
        SET p_status = 'ok';
    END IF;
END //
DELIMITER ;

CALL place_wallet_order(112, 1, 2, 900, @status);
SELECT @status;                                         -- 'ok'

Four details matter:

1. Parameter modes. IN passes a value in, OUT sends one back, and INOUT does both. The caller reads OUT values through session variables such as @status.
2. DECLARE order. Local variables come first, then handlers. MySQL rejects them in any other order.
3. The exit handler. If any statement raises an error, the handler rolls back and sets the status, so the caller never sees a half-finished payment.
4. FOR UPDATE. It locks Asha's wallet row until COMMIT, so two simultaneous payments cannot both pass the balance check.

To stop with your own error message, MySQL uses SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient balance';. To remove a procedure, run DROP PROCEDURE IF EXISTS place_wallet_order;.

Creating a stored procedure in PostgreSQL

PostgreSQL added CREATE PROCEDURE in version 11. The body is usually written in PL/pgSQL, PostgreSQL's procedural language, and placed between $$ quotes, so no DELIMITER trick is needed.

-- PostgreSQL 16
CREATE OR REPLACE PROCEDURE place_wallet_order(
    p_order_id INT, p_customer_id INT, p_store_id INT, p_amount INT,
    INOUT p_status TEXT
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_balance INT;
BEGIN
    SELECT balance INTO v_balance FROM wallets
    WHERE customer_id = p_customer_id FOR UPDATE;

    IF v_balance IS NULL OR v_balance < p_amount THEN
        RAISE EXCEPTION 'Insufficient balance for customer %', p_customer_id;
    END IF;

    INSERT INTO orders (order_id, customer_id, store_id, amount, payment_mode)
    VALUES (p_order_id, p_customer_id, p_store_id, p_amount, 'Wallet');
    UPDATE wallets SET balance = balance - p_amount WHERE customer_id = p_customer_id;
    p_status := 'ok';
END;
$$;

CALL place_wallet_order(112, 1, 2, 900, NULL);          -- returns p_status = 'ok'

Here the error path uses RAISE EXCEPTION. The exception aborts the statement, so the caller's transaction rolls back the order insert along with everything else. PostgreSQL procedures may also run COMMIT and ROLLBACK inside the body when they are called outside an explicit transaction block, which functions can never do.

How does SQLite handle procedures?

SQLite is an embedded library that runs inside your application, so there is no network round trip to save, and it has no CREATE PROCEDURE. You write the procedure in the host language instead.

The Python function below plays the role of place_wallet_order, but goes one step further: it prices the bill from the products table, so the caller passes only product ids and quantities. The with con: block is the transaction.

import sqlite3

con = sqlite3.connect(":memory:")
con.execute("PRAGMA foreign_keys = ON")
con.executescript("""
CREATE TABLE products (product_id INTEGER PRIMARY KEY, product_name TEXT, category TEXT, price INT);
CREATE TABLE wallets (customer_id INTEGER PRIMARY KEY, balance INT CHECK (balance >= 0));
CREATE TABLE orders (order_id INTEGER PRIMARY KEY, customer_id INT, store_id INT, amount INT, payment_mode TEXT);
CREATE TABLE order_items (order_id INT, product_id INT REFERENCES products(product_id), quantity INT,
    PRIMARY KEY (order_id, product_id));
INSERT INTO products VALUES (1,'Basmati Rice 5kg','Grocery',520), (2,'Toor Dal 1kg','Grocery',165), (8,'Sev 500g','Snacks',120);
INSERT INTO wallets VALUES (1, 1500), (7, 300);
""")

def place_wallet_order(order_id, customer_id, store_id, lines):   # returns (status, amount) like OUT params
    try:
        with con:                                   # BEGIN ... COMMIT, or ROLLBACK on error
            con.executemany("INSERT INTO order_items VALUES (?, ?, ?)", [(order_id, p, q) for p, q in lines])
            amount = con.execute("""SELECT SUM(p.price * i.quantity) FROM order_items i
                JOIN products p ON p.product_id = i.product_id WHERE i.order_id = ?""", (order_id,)).fetchone()[0]
            con.execute("INSERT INTO orders VALUES (?, ?, ?, ?, 'Wallet')", (order_id, customer_id, store_id, amount))
            con.execute("UPDATE wallets SET balance = balance - ? WHERE customer_id = ?", (amount, customer_id))
        return "ok", amount
    except sqlite3.IntegrityError as err:
        return f"failed: {err}", None

print(place_wallet_order(112, 1, 2, [(2, 2), (8, 1)]))     # 2 x dal + 1 x sev
print(place_wallet_order(113, 7, 4, [(1, 1)]))             # Neha cannot afford rice
print(place_wallet_order(114, 1, 1, [(9, 1)]))             # product 9 does not exist
print("Wallets:", con.execute("SELECT * FROM wallets").fetchall(), "| item rows:", con.execute("SELECT COUNT(*) FROM order_items").fetchone()[0])

Order 112 costs 450 rupees and succeeds, leaving Asha with 1,050. Neha's order fails on the wallet CHECK, and order 114 fails the foreign key because product 9 does not exist. In both failures the item rows already inserted were rolled back, so only order 112's two lines remain.

What is the difference between a stored procedure and a function?

A stored function (also called a user-defined function, or UDF) takes inputs and returns a value. You use it inside a query, wherever you would use a built-in like ROUND().

-- MySQL 8
DELIMITER //
CREATE FUNCTION with_gst(p_amount DECIMAL(10,2), p_rate DECIMAL(5,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    RETURN ROUND(p_amount * (1 + p_rate / 100), 2);
END //
DELIMITER ;

-- PostgreSQL 16: a function can be written in plain SQL
CREATE OR REPLACE FUNCTION with_gst(p_amount NUMERIC, p_rate NUMERIC)
RETURNS NUMERIC
LANGUAGE sql IMMUTABLE
AS $$ SELECT ROUND(p_amount * (1 + p_rate / 100), 2) $$;

SELECT product_name, price, with_gst(price, 5) AS price_with_gst FROM products;

DETERMINISTIC (MySQL) and IMMUTABLE (PostgreSQL) promise that the same inputs always give the same output. MySQL with binary logging on (the default) refuses a function declared without DETERMINISTIC, NO SQL or READS SQL DATA, and PostgreSQL uses it to optimise and to allow the function in indexes.

Think "procedure does a job, function computes a value". The way each is called decides what it may do.

User-defined functions in SQLite

SQLite cannot store a function in the database file, but Python can register one on the connection. After create_function, SQL on that connection can call it like a built-in.

import sqlite3

def with_gst(amount, rate):
    return None if amount is None else round(amount * (1 + rate / 100), 2)

con = sqlite3.connect(":memory:")
con.create_function("with_gst", 2, with_gst, deterministic=True)
con.executescript("""
CREATE TABLE products (product_id INTEGER PRIMARY KEY, product_name TEXT, category TEXT, price INT);
INSERT INTO products VALUES (2,'Toor Dal 1kg','Grocery',165), (4,'Butter 500g','Dairy',275),
 (6,'Namkeen Mix 400g','Snacks',110), (7,'Detergent 2kg','Household',340);
""")
for row in con.execute("SELECT product_name, price, with_gst(price, 5) FROM products WHERE with_gst(price, 5) > 150"):
    print(row)

It works in SELECT and WHERE, but exists only on this connection, so every program must register it again.

A custom aggregate: median per store

Python can also add aggregate functions. SQLite has no MEDIAN, so here is one, used with GROUP BY on the Malwa Mart orders.

import sqlite3, statistics

class Median:
    def __init__(self):
        self.values = []
    def step(self, value):                      # called once per row in the group
        if value is not None:
            self.values.append(value)
    def finalize(self):                         # called once per group
        return statistics.median(self.values) if self.values else None

con = sqlite3.connect(":memory:")
con.create_aggregate("median", 1, Median)
con.execute("CREATE TABLE orders (order_id INT, store_id INT, amount INT)")
con.executemany("INSERT INTO orders VALUES (?, ?, ?)", [(101,1,1040), (102,2,330), (103,1,275), (104,3,180),
    (105,2,680), (106,4,120), (107,3,1560), (108,1,220), (109,4,340), (110,2,465), (111,3,None)])
for row in con.execute("SELECT store_id, COUNT(amount), ROUND(AVG(amount), 1), median(amount) FROM orders GROUP BY store_id"):
    print(row)

Store 1's average is 511.7 rupees, pulled up by one large order, while its median is 275. Like built-in aggregates, the custom median skips the NULL amount on order 111. PostgreSQL gets the same result with PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount).

When should you use stored procedures?

For automatic actions, such as logging every price change, a procedure is the wrong tool, because someone has to remember to call it. A trigger fires on its own.

Common mistakes with stored procedures

• Forgetting DELIMITER in MySQL. The client stops at the first semicolon inside the body and reports a syntax error.
• No error handling. A procedure that starts a transaction and fails halfway can leave locks or partial work. Add an exit handler (MySQL) or let the exception roll back (PostgreSQL).
• Parameter names that match column names. WHERE customer_id = customer_id is always true. Prefix parameters with p_ and variables with v_.
• Building SQL strings inside the procedure. Dynamic SQL with concatenated input is as open to injection as it is in Python. Pass values as parameters.
• Keeping procedures out of version control. Save every CREATE PROCEDURE script in your project repository, like any other code.

Writing a procedure is a common round in backend and data roles. Debugshala's analytics course in Indore with database programming practice covers procedures alongside joins and reports, and the free coding challenges on Debugshala Test help you practise the logic.

Key takeaways

• A stored procedure is named SQL saved in the database and run with CALL; it can take IN, OUT and INOUT parameters.
• A function returns a value and is used inside queries; mark it DETERMINISTIC or IMMUTABLE when it is.
• MySQL needs DELIMITER and DECLARE handlers; PostgreSQL uses PL/pgSQL between $$ quotes and RAISE EXCEPTION.
• SQLite has no procedures; use host-language functions with transactions, plus create_function and create_aggregate.
• Use procedures for shared multi-step operations, and avoid them for logic that changes often or belongs in a simple view.