Stored functions and procedures

# A SQL function, a PL/pgSQL function and a procedure on PostgreSQL 18.6.
from pg_session import psql

psql("""
CREATE TABLE orders (id int PRIMARY KEY, customer_id int NOT NULL, amount_paise bigint NOT NULL, rate_bp int NOT NULL);
INSERT INTO orders VALUES (1, 7, 49900, 1800), (2, 7, 129900, 1800), (3, 8, 9950, 500), (4, 9, 25000, 1200);

-- 1. A SQL function with a standard body: one expression, parsed when the function is created.
CREATE FUNCTION gst_paise(amount_paise bigint, rate_bp integer)
RETURNS bigint
LANGUAGE sql
IMMUTABLE
RETURN round(amount_paise * rate_bp / 10000.0);

SELECT id, amount_paise, rate_bp, gst_paise(amount_paise, rate_bp) AS gst_paise,
       amount_paise + gst_paise(amount_paise, rate_bp) AS total_paise
FROM orders
ORDER BY id;

-- 2. A PL/pgSQL function: IF, ELSIF and an error of its own.
CREATE FUNCTION customer_tier(total_paise bigint)
RETURNS text
LANGUAGE plpgsql
IMMUTABLE
AS $$
BEGIN
  IF total_paise < 0 THEN
    RAISE EXCEPTION 'customer_tier: total_paise cannot be negative (%)', total_paise;
  ELSIF total_paise >= 5000000 THEN
    RETURN 'gold';
  ELSIF total_paise >= 1000000 THEN
    RETURN 'silver';
  END IF;
  RETURN 'standard';
END;
$$;

SELECT t AS total_paise, customer_tier(t) AS tier FROM (VALUES (999999), (1000000), (5000000)) AS v (t);
SELECT customer_tier(-1);

-- 3. A procedure: two writes behind one name, run on its own with CALL.
CREATE TABLE payments (order_id int NOT NULL, paid_paise bigint NOT NULL);
CREATE PROCEDURE record_payment(p_order int, p_paise bigint)
LANGUAGE sql
BEGIN ATOMIC
  INSERT INTO payments VALUES (p_order, p_paise);
  UPDATE orders SET amount_paise = amount_paise - p_paise WHERE id = p_order;
END;

CALL record_payment(2, 29900);
SELECT record_payment(2, 100);
SELECT o.id, o.amount_paise AS still_due_paise, p.paid_paise FROM orders AS o JOIN payments AS p ON p.order_id = o.id;
""")

CREATE TABLE
INSERT 0 4
CREATE FUNCTION
 id | amount_paise | rate_bp | gst_paise | total_paise 
----+--------------+---------+-----------+-------------
  1 |        49900 |    1800 |      8982 |       58882
  2 |       129900 |    1800 |     23382 |      153282
  3 |         9950 |     500 |       498 |       10448
  4 |        25000 |    1200 |      3000 |       28000
(4 rows)

CREATE FUNCTION
 total_paise |   tier   
-------------+----------
      999999 | standard
     1000000 | silver
     5000000 | gold
(3 rows)

ERROR:  customer_tier: total_paise cannot be negative (-1)
CONTEXT:  PL/pgSQL function customer_tier(bigint) line 4 at RAISE
CREATE TABLE
CREATE PROCEDURE
CALL
ERROR:  record_payment(integer, integer) is a procedure
LINE 1: SELECT record_payment(2, 100);
               ^
HINT:  To call a procedure, use CALL.
 id | still_due_paise | paid_paise 
----+-----------------+------------
  2 |          100000 |      29900
(1 row)

# A stored function and a stored procedure on MySQL 8.4.11, with binary logging on, as in a new installation.
from mysql_session import mysql

mysql("""
CREATE TABLE products (id INT PRIMARY KEY, name VARCHAR(40) NOT NULL, stock INT NOT NULL);
CREATE TABLE orders (id BIGINT AUTO_INCREMENT PRIMARY KEY, customer_id INT NOT NULL, product_id INT NOT NULL, qty INT NOT NULL);
INSERT INTO products VALUES (3, 'kettle', 5);

-- With binary logging on, a function must say whether it is deterministic or reads no data.
CREATE FUNCTION gst_paise(amount_paise BIGINT, rate_bp INT) RETURNS BIGINT
RETURN ROUND(amount_paise * rate_bp / 10000);
CREATE FUNCTION gst_paise(amount_paise BIGINT, rate_bp INT) RETURNS BIGINT
DETERMINISTIC NO SQL
RETURN ROUND(amount_paise * rate_bp / 10000);
SELECT gst_paise(49900, 1800), gst_paise(9950, 500);

-- A procedure body holds semicolons, so the mysql client must be told where the CREATE statement ends.
DELIMITER //
CREATE PROCEDURE place_order(IN p_customer INT, IN p_product INT, IN p_qty INT, OUT p_order_id BIGINT)
MODIFIES SQL DATA
BEGIN
  DECLARE v_stock INT;
  START TRANSACTION;
  SELECT stock INTO v_stock FROM products WHERE id = p_product FOR UPDATE;
  IF v_stock IS NULL OR v_stock < p_qty THEN
    ROLLBACK;
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'place_order: not enough stock';
  END IF;
  UPDATE products SET stock = stock - p_qty WHERE id = p_product;
  INSERT INTO orders (customer_id, product_id, qty) VALUES (p_customer, p_product, p_qty);
  SET p_order_id = LAST_INSERT_ID();
  COMMIT;
END//
DELIMITER ;

-- The OUT parameter comes back through a user variable.
CALL place_order(7, 3, 2, @order_id);
SELECT @order_id AS order_id;
CALL place_order(8, 3, 9, @order_id);
SELECT * FROM products;
""")

ERROR 1418 (HY000) at line 6: This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
+------------------------+----------------------+
| gst_paise(49900, 1800) | gst_paise(9950, 500) |
+------------------------+----------------------+
|                   8982 |                  498 |
+------------------------+----------------------+
+----------+
| order_id |
+----------+
|        1 |
+----------+
ERROR 1644 (45000) at line 35: place_order: not enough stock
+----+--------+-------+
| id | name   | stock |
+----+--------+-------+
|  3 | kettle |     3 |
+----+--------+-------+

"""SQLite has no CREATE FUNCTION: the program that opens the database registers its own SQL functions.

gst_paise(amount_paise, rate_bp) is the tax on an amount in paise, at a rate in basis points (1800 = 18 %),
rounded half up to a whole paisa. It exists only on the connection that registered it.
"""
import sqlite3


def gst_paise(amount_paise, rate_bp):
    if amount_paise is None or rate_bp is None:
        return None
    return (amount_paise * rate_bp + 5_000) // 10_000


shop = sqlite3.connect(":memory:")
shop.create_function("gst_paise", 2, gst_paise, deterministic=True)
shop.execute("CREATE TABLE orders (id INTEGER PRIMARY KEY, amount_paise INTEGER NOT NULL, rate_bp INTEGER NOT NULL)")
shop.executemany(
    "INSERT INTO orders VALUES (?, ?, ?)",
    [(1, 49_900, 1800), (2, 129_900, 1800), (3, 9_950, 500), (4, 25_000, 1200)],
)

print("id   amount  rate    gst    total")
for row in shop.execute(
    "SELECT id, amount_paise, rate_bp, gst_paise(amount_paise, rate_bp),"
    " amount_paise + gst_paise(amount_paise, rate_bp) FROM orders ORDER BY id"
):
    print("%-3d %7d %5d %6d %8d" % row)

# The function also works in WHERE, GROUP BY and ORDER BY, like a built-in one.
print(shop.execute("SELECT count(*) FROM orders WHERE gst_paise(amount_paise, rate_bp) > 5000").fetchone()[0], "orders pay more than 5000 paise of tax")

# A second connection, even in the same program, does not have it.
other = sqlite3.connect(":memory:")
try:
    other.execute("SELECT gst_paise(49900, 1800)")
except sqlite3.OperationalError as e:
    print("second connection:", e)

id   amount  rate    gst    total
1     49900  1800   8982    58882
2    129900  1800  23382   153282
3      9950   500    498    10448
4     25000  1200   3000    28000
2 orders pay more than 5000 paise of tax
second connection: no such function: gst_paise