A database function is a stored routine that accepts inputs, performs computation or data operations, and returns a value or result inside the database. Applications call it by name, while the database centralizes logic close to the data. section 2.6
Use database functions when:
- Multiple clients or microservices must share complex business logic tightly coupled to stored data. section 2.6
- Multistep operations would otherwise require several application-to-database round trips. section 2.6
- A database-backed API needs to produce or consume messages through SQL and transactional data operations. section 11.1
Listing 2.20 Function that returns the product price
CREATE OR REPLACE FUNCTION products.get_product_price(product_id INT)
RETURNS NUMERIC(10, 2) AS $$
SELECT price
FROM products.catalog
WHERE id = product_id;
$$ LANGUAGE sql;
A minimal SQL function returns a product price by accepting a product identifier, querying the catalog, and declaring a numeric return type. section 2.6
Shopping-cart workflow
A PostgreSQL function can add an item to a customer's pending order, create that order when necessary, merge repeated products, and return the resulting rows. A second function can find the pending order, calculate its total, mark it ordered, and return the checkout result. Calling the functions for customer 3 adds products priced at 13.99 and 19.99, then checkout returns a total of 47.97. section 2.6.1
Listing 2.22 Implementing the order_add_item function
CREATE OR REPLACE FUNCTION sales.order_add_item(customer_id_param INT,
product_id_param INT, quantity_param INT)
RETURNS TABLE (order_id UUID, prod_id INT,
quantity INT, prod_price DECIMAL) AS $$
DECLARE
pending_order_id UUID;
BEGIN
SELECT id INTO pending_order_id
FROM sales.orders
WHERE customer_id = customer_id_param
AND status = 'pending'
LIMIT 1;
IF pending_order_id IS NULL THEN
INSERT INTO sales.orders (customer_id, status)
VALUES (customer_id_param, 'pending')
RETURNING id INTO pending_order_id;
END IF;
MERGE INTO sales.order_items AS oi
USING (SELECT id, price FROM products.catalog
WHERE id = product_id_param) AS prod
ON oi.product_id = prod.id AND oi.order_id = pending_order_id
WHEN MATCHED THEN
UPDATE SET quantity = quantity_param
WHEN NOT MATCHED THEN
INSERT (order_id, product_id, quantity, price)
VALUES (pending_order_id, prod.id, quantity_param, prod.price);
RETURN QUERY
SELECT oi.order_id, oi.product_id, oi.quantity, oi.price as prod_price
FROM sales.order_items as oi
WHERE oi.order_id = pending_order_id;
END;
$$ LANGUAGE plpgsql;
The order_add_item implementation shows parameters, local variables, conditional order creation, catalog lookup, and returned table results. section 2.6.1
Listing 2.24 Implementing the order_checkout function
CREATE OR REPLACE FUNCTION sales.order_checkout(customer_id_param INT)
RETURNS TABLE (order_id UUID, customer_id INT, total_amount DECIMAL) AS $$
DECLARE
pending_order_id UUID;
final_total_amount DECIMAL := 0;
BEGIN
SELECT id INTO pending_order_id
FROM sales.orders as o
WHERE o.customer_id = customer_id_param
AND status = 'pending'
LIMIT 1;
IF pending_order_id IS NULL THEN
RAISE EXCEPTION 'No pending order found for customer %',
customer_id_param;
END IF;
SELECT SUM(oi.quantity * oi.price) INTO final_total_amount
FROM sales.order_items oi
WHERE oi.order_id = pending_order_id;
UPDATE sales.orders
SET status = 'ordered',
total_amount = final_total_amount,
order_date = CURRENT_TIMESTAMP
WHERE id = pending_order_id;
UPDATE products.catalog
SET stock_quantity = stock_quantity - oi.quantity
FROM sales.order_items oi
WHERE products.catalog.id = oi.product_id
AND oi.order_id = pending_order_id;
RETURN QUERY
SELECT o.id, o.customer_id, o.total_amount
FROM sales.orders as o
WHERE o.id = pending_order_id;
END;
$$ LANGUAGE plpgsql;
The order_checkout implementation demonstrates exception handling, aggregation, updates, and a returned order summary. section 2.6.1
