PostgreSQL Stored Procedures and Functions Tutorial || PostgreSQL Stored Procedures and User-Defined Functions

What You’ll Learn

In this lesson, you will learn how to use PostgreSQL stored procedures and user-defined functions to centralize reusable order validation and calculation logic inside a database.

Understand the difference between a function and a stored procedure.
Create a function that calculates an order total.
Create a procedure that validates an order and raises database errors.
Reuse functions inside procedures and other queries.
Recognize common design and maintenance considerations for database-side logic.

The Concept

A user-defined function is a database routine that accepts input, performs work, and returns a value. Functions are useful for calculations and queries that need to be reused from SQL statements.

A stored procedure is a database routine that performs an operation. In PostgreSQL, procedures are invoked with CALL. They are useful for workflows such as validating an order, changing its status, or applying a business rule.

For example, an application could calculate order totals in application code, but that can create problems when multiple applications use the same database. A reporting script, administration tool, and web application might each implement slightly different rules. Keeping the logic in the database gives those clients one shared implementation.

This lesson uses PostgreSQL’s PL/pgSQL syntax. The exact syntax for routines differs between database systems, so always confirm which database engine your project uses before copying routine definitions.

Basic Example

The following example creates a small order schema, defines a function for calculating an order total, and defines a procedure that validates the order before it is processed.

DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS orders;

CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    customer_name text NOT NULL,
    order_status text NOT NULL DEFAULT 'pending',
    discount_amount numeric(10, 2) NOT NULL DEFAULT 0,
    CHECK (order_status IN ('pending', 'approved', 'rejected'))
);

CREATE TABLE order_items (
    order_item_id integer PRIMARY KEY,
    order_id integer NOT NULL REFERENCES orders(order_id),
    product_name text NOT NULL,
    quantity integer NOT NULL CHECK (quantity > 0),
    unit_price numeric(10, 2) NOT NULL CHECK (unit_price >= 0)
);

INSERT INTO orders (order_id, customer_name)
VALUES (1001, 'Maya Chen');

INSERT INTO order_items (
    order_item_id,
    order_id,
    product_name,
    quantity,
    unit_price
)
VALUES
    (1, 1001, 'Wireless keyboard', 1, 49.99),
    (2, 1001, 'USB-C cable', 2, 12.50);

CREATE OR REPLACE FUNCTION order_total(p_order_id integer)
RETURNS numeric(10, 2)
LANGUAGE plpgsql
STABLE
AS $$
DECLARE
    v_total numeric(10, 2);
BEGIN
    SELECT COALESCE(SUM(quantity * unit_price), 0)::numeric(10, 2)
    INTO v_total
    FROM order_items
    WHERE order_id = p_order_id;

    RETURN v_total;
END;
$$;

CREATE OR REPLACE PROCEDURE validate_order(p_order_id integer)
LANGUAGE plpgsql
AS $$
DECLARE
    v_total numeric(10, 2);
    v_status text;
BEGIN
    SELECT order_status
    INTO v_status
    FROM orders
    WHERE order_id = p_order_id;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Order % does not exist', p_order_id;
    END IF;

    IF v_status <> 'pending' THEN
        RAISE EXCEPTION 'Order % must be pending before validation', p_order_id;
    END IF;

    v_total := order_total(p_order_id);

    IF v_total <= 0 THEN
        RAISE EXCEPTION 'Order % must contain items with a positive total', p_order_id;
    END IF;

    RAISE NOTICE 'Order % passed validation with total %', p_order_id, v_total;
END;
$$;

CALL validate_order(1001);

SELECT order_total(1001) AS total;

Expected Output

NOTICE:  Order 1001 passed validation with total 74.99
 total
--------
  74.99
(1 row)

How the Code Works

PostgreSQL procedures coordinate order workflows, while reusable functions calculate totals and other derived values.

The order_total function receives an order ID through p_order_id. It multiplies each item’s quantity by its unit price, adds the results, and returns the total as a numeric value with two decimal places.

COALESCE changes a NULL sum into zero. This matters when an order exists but has no matching items, because SUM normally returns NULL in that situation. The COALESCE for NULL values pattern is useful whenever reusable database logic must handle missing data safely.

The STABLE classification tells PostgreSQL that the function does not modify the database and returns consistent results during one statement when its underlying data does not change. It is an appropriate classification for this read-only calculation.

The validate_order procedure performs a workflow rather than returning a calculated value. It checks that:

The order exists.
The order is still pending.
The calculated total is positive.

SELECT ... INTO stores the order status in a local variable. After that query, PostgreSQL’s FOUND variable indicates whether a row was found. The procedure uses RAISE EXCEPTION to stop with an error when a validation rule fails and RAISE NOTICE to report a successful validation.

Calling order_total(1001) from a SELECT demonstrates function usage. Calling validate_order(1001) with CALL demonstrates procedure usage.

Another Example

A useful next step is to separate a discount calculation from the procedure that applies a discount. The function below calculates the final amount, while the procedure updates the order’s stored discount after checking that the value is valid.

CREATE OR REPLACE FUNCTION order_payable_total(p_order_id integer)
RETURNS numeric(10, 2)
LANGUAGE plpgsql
STABLE
AS $$
DECLARE
    v_payable_total numeric(10, 2);
BEGIN
    SELECT GREATEST(order_total(o.order_id) - o.discount_amount, 0)::numeric(10, 2)
    INTO v_payable_total
    FROM orders AS o
    WHERE o.order_id = p_order_id;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Order % does not exist', p_order_id;
    END IF;

    RETURN v_payable_total;
END;
$$;

CREATE OR REPLACE PROCEDURE apply_order_discount(
    p_order_id integer,
    p_discount_amount numeric(10, 2)
)
LANGUAGE plpgsql
AS $$
BEGIN
    IF p_discount_amount < 0 THEN
        RAISE EXCEPTION 'Discount cannot be negative';
    END IF;

    IF NOT EXISTS (
        SELECT 1
        FROM orders
        WHERE order_id = p_order_id
    ) THEN
        RAISE EXCEPTION 'Order % does not exist', p_order_id;
    END IF;

    IF p_discount_amount > order_total(p_order_id) THEN
        RAISE EXCEPTION 'Discount cannot exceed the order total';
    END IF;

    UPDATE orders
    SET discount_amount = p_discount_amount
    WHERE order_id = p_order_id;

    RAISE NOTICE 'Applied discount % to order %',
        p_discount_amount,
        p_order_id;
END;
$$;

CALL apply_order_discount(1001, 10.00);

SELECT
    order_id,
    order_total(order_id) AS subtotal,
    discount_amount,
    order_payable_total(order_id) AS amount_due
FROM orders
WHERE order_id = 1001;

This example demonstrates composition: the procedure reuses order_total, and the payable-total function also reuses it. If the definition of an order subtotal changes later, these routines have one central calculation to update instead of several independent copies.

Common Mistakes

Using a procedure where a returned value is needed: Use a function when a query, report, or other routine needs a result such as an order total. Use a procedure for an operation such as validation or updating a status.
Ignoring missing rows: A query that finds no order can leave a variable as NULL. Explicitly check FOUND or use an existence test before continuing.
Forgetting that aggregate functions can return NULL: An order without items produces a NULL sum. Use COALESCE when zero is the correct fallback.
Duplicating business rules: If the application validates discounts one way and the procedure validates them another way, inconsistent data can be created. Keep authoritative rules in one routine or one clearly defined database layer.
Assuming validation guarantees later updates: A separate validation call and a later update can be affected by concurrent transactions. For critical workflows, perform the validation and update in one procedure and choose an appropriate transaction or locking strategy.

Try It Yourself

Create a function named order_item_count that accepts an order ID and returns the number of item rows belonging to that order. Then test it with order 1001.

After that, modify validate_order so an order with no item rows receives a more specific error message. Think about whether checking the item count or checking the calculated total better expresses the rule you want to enforce.

Challenge

Create a function named order_subtotal_before_discount that returns the subtotal for an order. Then create a procedure named approve_order that:

Rejects the request if the order does not exist.
Rejects the request unless the order is currently pending.
Rejects the request if the subtotal is less than 50.00.
Changes the order status to approved when all checks pass.

Use the procedure to approve order 1001, then query the order to confirm its status.

Solution

CREATE OR REPLACE FUNCTION order_subtotal_before_discount(p_order_id integer)
RETURNS numeric(10, 2)
LANGUAGE plpgsql
STABLE
AS $$
DECLARE
    v_subtotal numeric(10, 2);
BEGIN
    SELECT COALESCE(SUM(oi.quantity * oi.unit_price), 0)::numeric(10, 2)
    INTO v_subtotal
    FROM orders AS o
    LEFT JOIN order_items AS oi
        ON oi.order_id = o.order_id
    WHERE o.order_id = p_order_id;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Order % does not exist', p_order_id;
    END IF;

    RETURN v_subtotal;
END;
$$;

CREATE OR REPLACE PROCEDURE approve_order(p_order_id integer)
LANGUAGE plpgsql
AS $$
DECLARE
    v_status text;
    v_subtotal numeric(10, 2);
BEGIN
    SELECT order_status
    INTO v_status
    FROM orders
    WHERE order_id = p_order_id;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Order % does not exist', p_order_id;
    END IF;

    IF v_status <> 'pending' THEN
        RAISE EXCEPTION 'Order % is already %', p_order_id, v_status;
    END IF;

    v_subtotal := order_subtotal_before_discount(p_order_id);

    IF v_subtotal < 50.00 THEN
        RAISE EXCEPTION
            'Order % has subtotal %, but approval requires at least 50.00',
            p_order_id,
            v_subtotal;
    END IF;

    UPDATE orders
    SET order_status = 'approved'
    WHERE order_id = p_order_id;

    RAISE NOTICE 'Order % was approved', p_order_id;
END;
$$;

CALL approve_order(1001);

SELECT order_id, order_status
FROM orders
WHERE order_id = 1001;

The function handles the reusable subtotal calculation, including the no-items case. The procedure owns the approval workflow and updates the row only after every validation succeeds. Because the sample order has a subtotal of 74.99, the final query shows it as approved.

For larger routines, inspect the queries they execute just as you would inspect application queries. PostgreSQL’s PostgreSQL EXPLAIN guide can help you investigate slow calculations or unexpectedly expensive validation queries.

Key Takeaways

Functions return values and can be used inside queries, while procedures perform operations and are invoked with CALL.
Centralizing order calculations and validation prevents different applications from implementing conflicting business rules.
Use explicit checks for missing rows, invalid states, and nullable aggregate results.
Small routines are easier to reuse, test, and maintain than one large procedure containing every rule.
When routines perform expensive queries or multi-step workflows, consider transaction behavior, concurrency, permissions, and query performance.