Stored procedures and functions

Code that lives in the database

A stored program is code you save inside the database and the server runs by name. A procedure is a named batch of work: you run it with CALL, it hands values back through OUT parameters, and it can run its own transaction (Transactions and concurrency). A function returns one value and lives inside a query the way ROUND() does, so it should only read. MySQL calls both routines, the word you see in privilege names and system tables.

DELIMITER $$
CREATE PROCEDURE place_order(IN p_customer INT, IN p_product INT, IN p_qty INT, OUT p_order_id INT)
BEGIN
  START TRANSACTION;
  INSERT INTO orders (customer_id, order_date) VALUES (p_customer, CURDATE());
  SET p_order_id = LAST_INSERT_ID();
  INSERT INTO order_items (order_id, product_id, qty, unit_price)
    SELECT p_order_id, id, p_qty, price FROM products WHERE id = p_product;
  COMMIT;
END $$
DELIMITER ;

DELIMITER $$
CREATE FUNCTION customer_lifetime_total(p_customer INT) RETURNS DECIMAL(10,2)
READS SQL DATA
BEGIN
  RETURN (SELECT COALESCE(SUM(total), 0) FROM orders
          WHERE customer_id = p_customer AND status <> 'cancelled');
END $$
DELIMITER ;

SELECT name, customer_lifetime_total(id) AS lifetime FROM customers;

Where they help and where they hurt

They went in and out of fashion; you will meet teams on both sides.

MySQL How MySQL does it

MySQL's stored program language is SQL/PSM, and in Community Edition it is the only one. JavaScript routines exist since 9.0, in Enterprise Edition and HeatWave only. Everything here holds in 8.4 LTS, 9.7 LTS and 26.x.

DELIMITER $$ is a client trick. The mysql client cuts input at every semicolon, so you tell it to cut at $$ while you send a body full of them. The server never sees the word. MySQL Shell and Workbench 26 handle it differently, so test one small routine before pasting a long script (Clients and tools).

The body also has DECLARE, IF, loops, cursors, condition handlers and SIGNAL; look them up when you meet one.

DETERMINISTIC and the binary log

Binary logging is on by default and replicas replay it, so the server wants to know whether a function is safe to replay. With the binlog on, CREATE FUNCTION is refused unless it declares at least one of DETERMINISTIC, NO SQL or READS SQL DATA. That is error 1418. Procedures are exempt. The escape hatch log_bin_trust_function_creators is deprecated since 8.0.34; fix the declaration.

Privileges and the definer

Grant the app EXECUTE on the procedure instead of rights on the tables: the body runs with its DEFINER's privileges, which is the default SQL SECURITY mode (Users, roles and security). The footgun is a definer account missing on the server you restored onto, which fails every call with error 1449.

PostgreSQL How PostgreSQL does it

PostgreSQL has functions in many languages: SQL, PL/pgSQL (the one you will see everywhere), PL/Python, PL/Perl and PL/Tcl ship in the core distribution, and extensions add more. Procedures with CALL and transaction control arrived in 11, so older material calls everything a function. The body is a string in dollar quotes, so psql reads it as one statement and there is no DELIMITER dance. A function can also return a whole result set with RETURNS TABLE.

CREATE PROCEDURE archive_cancelled_orders()
LANGUAGE plpgsql AS $$
BEGIN
  INSERT INTO orders_archive SELECT * FROM orders WHERE status = 'cancelled';
  DELETE FROM orders WHERE status = 'cancelled';
  COMMIT;          -- allowed in a procedure, never in a function
END;
$$;

CALL archive_cancelled_orders();

One surprise: EXECUTE on a new function goes to PUBLIC by default.

Volatility: what you promise the planner

Where MySQL asks about determinism for the binary log, PostgreSQL asks for the planner. VOLATILE (the default) means the result can change at any moment, so the function runs for every row. STABLE means it holds within one statement, where table-reading functions belong. IMMUTABLE means same arguments, same result, forever, and only immutable functions may go into expression indexes. Marking a table-reading function IMMUTABLE gives wrong answers.

Both Side by side

The app-side cousin: prepared statements

A prepared statement is SQL with placeholders that the server parses once; the values travel separately and are never parsed as SQL, which is why it stops injection (Users, roles and security).

The DBA angle

To see what exists: SHOW PROCEDURE STATUS in MySQL, information_schema.ROUTINES to filter or join, and \df in psql.

mysqldump skips routines unless you pass --routines, while MySQL Shell's dump utilities and pg_dump include them by default (Backup and recovery).

When a nightly job takes four hours, look inside it: events_statements_summary_by_program in MySQL's Performance Schema, pg_stat_user_functions with track_functions = pl in PostgreSQL. The usual culprits are cursors and functions called once per row.

• MySQL 8.4: Stored Routines

  Syntax, privileges, cursors, handlers.

• MySQL 8.4: Stored Program Binary Logging

  The DETERMINISTIC rule and error 1418.

• PostgreSQL: PL/pgSQL

  Variables to exception handling.

• PostgreSQL: CREATE PROCEDURE

  CALL and COMMIT inside procedures.

• PostgreSQL: Function Volatility Categories

  VOLATILE, STABLE and IMMUTABLE.