Using Functions in Postgres

Introduction

• Functions are an extremely important concept in database-side programming, which are a collection of SQL or PL/pgSQL statements encapsulated into a single block of code that can be reused multiple times
• It acts like a function in other programming languages such as JavaScript and Python and it only runs when explicitly invoked using a SELECT or CALL statement or when nested inside another function.

Use cases

Functions in PostgreSQL are highly flexible and can be applied to many real-world problems in programming, typically serving the following main purposes:

• Encapsulation
  • Instead of forcing the backend application to send a long SQL query spanning hundreds of lines with multiple JOIN and GROUP BY clauses, you can hide all that complexity inside a Function.
  • Examples include revenue reports and performance statistics.
• Data Transformation
  • This is ideal when you need to perform mathematical calculations, process strings, or execute complex IF/ELSE logic on data before returning or storing it.
  • Examples include calculating age from date of birth, converting accented characters to unaccented ones and calculating taxes or shipping fees based on distance.
• Business Logic / Transactions
  • This applies when a user action requires inserting, updating, or deleting data across multiple tables simultaneously while ensuring data integrity where either everything succeeds or everything is rolled back upon error.
  • Examples include order payment features, account registration and bank transfers.

Advantages

• They can handle logic that pure SQL cannot achieve, such as using LOOP structures or complex IF/ELSE logic to process data.
• Security and abstraction: Teams utilizing data, such as frontend and backend teams, do not need to know the exact table structure, as they only need to pass input parameters to get the results.
• They prevent code duplication, making maintenance much simpler.
  • You only need to use the Function concisely wherever it is required.
  • If multiple applications share the output of a single Function, any changes to the business logic only require modifications within that specific Function.
• Network performance savings: Instead of the backend sending multiple consecutive SQL statements back and forth to the database, which wastes time waiting on network latency
  • Combining those statements into a single Function means the backend only needs to call it once, significantly boosting efficiency.
  • Because Postgres pre-compiles the Execution Plan for the Function, it runs noticeably faster than sending raw, individual SQL statements from the application.

Detail

First, create the tables as follows, as we explore how to effectively use a Function in an e-commerce project

CREATE TABLE categories (
    category_id SERIAL PRIMARY KEY,
    category_name VARCHAR(100) NOT NULL
);

CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    product_name VARCHAR(255) NOT NULL,
    category_id INT REFERENCES categories(category_id),
    price NUMERIC(12, 2) NOT NULL,
    stock_quantity INT NOT NULL DEFAULT 0
);

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    full_name VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE NOT NULL,
    membership_level VARCHAR(20) DEFAULT 'Standard'
);

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id),
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    total_amount NUMERIC(12, 2) DEFAULT 0.00,
    status VARCHAR(50) DEFAULT 'Pending'
);

CREATE TABLE order_items (
    item_id SERIAL PRIMARY KEY,
    order_id INT REFERENCES orders(order_id) ON DELETE CASCADE,
    product_id INT REFERENCES products(product_id),
    quantity INT NOT NULL,
    price_per_unit NUMERIC(12, 2) NOT NULL
);

Next, create this Function to see how Data Transformation is utilized

CREATE OR REPLACE FUNCTION transform_calculate_discounted_price(
    original_price NUMERIC,
    membership_level VARCHAR
)
RETURNS NUMERIC AS $$
DECLARE
    discount_rate NUMERIC := 0.00;
BEGIN
    IF membership_level = 'Silver' THEN
        discount_rate := 0.05;
    ELSIF membership_level = 'Gold' THEN
        discount_rate := 0.10;
    ELSE
        discount_rate := 0.00;
    END IF;

    RETURN ROUND(original_price * (1 - discount_rate), 2);
END;
$$ LANGUAGE plpgsql;

SELECT transform_calculate_discounted_price(1000.00, 'Gold') AS gold_customer_price;

• In e-commerce, there are discount policies based on the membership level and this Function transforms the original price of a product into a discounted price tailored to each individual customer.
• When using it, you simply call the function with the original product price and membership level as input parameters.

Next is an example of Encapsulation

CREATE OR REPLACE FUNCTION get_ecommerce_dashboard_stats(
    p_from_date TIMESTAMP,
    p_to_date TIMESTAMP
)
RETURNS TABLE (
    total_revenue NUMERIC(12, 2),
    total_orders INT,
    total_items_sold INT,
    top_selling_product_id INT,
    top_selling_product_name VARCHAR(255)
) AS $$
DECLARE
    v_top_product_id INT;
    v_top_product_name VARCHAR(255);
BEGIN
    SELECT oi.product_id, p.product_name
    INTO v_top_product_id, v_top_product_name
    FROM order_items oi
    JOIN orders o ON oi.order_id = o.order_id
    JOIN products p ON oi.product_id = p.product_id
    WHERE o.order_date BETWEEN p_from_date AND p_to_date
      AND o.status != 'Cancelled'
    GROUP BY oi.product_id, p.product_name
    ORDER BY SUM(oi.quantity) DESC
    LIMIT 1;

    RETURN QUERY
    SELECT 
        COALESCE(SUM(o.total_amount), 0.00) AS total_revenue,
        COUNT(DISTINCT o.order_id)::INT AS total_orders,
        COALESCE(SUM(oi.quantity), 0)::INT AS total_items_sold,
        v_top_product_id AS top_selling_product_id,
        COALESCE(v_top_product_name, 'Không có') AS top_selling_product_name
    FROM orders o
    LEFT JOIN order_items oi ON o.order_id = oi.order_id
    WHERE o.order_date BETWEEN p_from_date AND p_to_date
      AND o.status != 'Cancelled';
END;
$$ LANGUAGE plpgsql;

SELECT * FROM get_ecommerce_dashboard_stats('2025-01-01 00:00:00', '2025-07-01 23:59:59');

• Creating a statistical Function will accept two parameters, from date and to date and define the return type as a TABLE (a data table consisting of one row and multiple columns), with the calculated results including
  • Total revenue, which only counts successful or processing orders while ignoring cancelled ones.
  • Total number of created orders.
  • Total number of products sold.
  • The ID and name of the top-selling product during that timeframe.

Next is an example of implementing Business Logic

CREATE OR REPLACE FUNCTION encapsulate_place_order(
    p_customer_id INT,
    p_product_id INT,
    p_quantity INT
)
RETURNS INT AS $$
DECLARE
    v_order_id INT;
    v_product_price NUMERIC;
    v_discounted_price NUMERIC;
    v_customer_level VARCHAR;
    v_stock INT;
BEGIN
    SELECT stock_quantity, price INTO v_stock, v_product_price 
    FROM products WHERE product_id = p_product_id;
    
    IF v_stock < p_quantity THEN
        RAISE EXCEPTION 'Sản phẩm này không đủ hàng trong kho! Hiện còn: %', v_stock;
    END IF;

    SELECT membership_level INTO v_customer_level 
    FROM customers WHERE customer_id = p_customer_id;

    v_discounted_price := transform_calculate_discounted_price(v_product_price, v_customer_level);

    INSERT INTO orders (customer_id, total_amount, status)
    VALUES (p_customer_id, (v_discounted_price * p_quantity), 'Processing')
    RETURNING order_id INTO v_order_id;

    INSERT INTO order_items (order_id, product_id, quantity, price_per_unit)
    VALUES (v_order_id, p_product_id, p_quantity, v_discounted_price);

    UPDATE products 
    SET stock_quantity = stock_quantity - p_quantity
    WHERE product_id = p_product_id;

    RETURN v_order_id;
END;
$$ LANGUAGE plpgsql;

SELECT encapsulate_place_order(2, 1, 2) AS new_order_id;

• This Function is used to group complex statements, because when a user places an order, a sequence of actions must be executed, such as
  • Creating an order.
  • Adding products to the order details.
  • Calculating the total order amount with the member discount applied.
  • Updating and deducting the product stock.
• Instead of writing numerous SQL statements when implementing this on the application side and making multiple network calls, you can encapsulate this entire logic into a single Function and any side that needs it just calls this Function.
• With input parameters consisting of customer id, product id and quantity, this Function will generate and return the corresponding new order id.

Happy coding!

See more articles here.