Building Multi-Step Procedures: Data Validation, Insert, and Logging

In real business scenarios, you usually need to do more than just one operation—you gotta chain actions together. For example, when an order comes in, you need to check the customer data, save the order, and write an audit log. A multi-step procedure lets you combine all these steps into one logic flow and guarantees integrity thanks to transactions: if anything goes wrong at any step, everything rolls back.

With newer PostgreSQL versions, especially since we got standalone procedures (CREATE PROCEDURE) and better transaction handling, it's important to get the difference between a PL/pgSQL function and a procedure, and also how to work with savepoints (SAVEPOINT), rollbacks, and error blocks the right way.

Basics of Multi-Step Procedure Structure

A typical business procedure has these steps:

1. Data validation — check input arguments, make sure the customer/product exists, etc.
2. Data insert — actually add (or update) the record(s).
3. Logging or audit — write info about a successful or failed operation.

You can do each step inside a single transaction (atomically), or, if the process is "long" or needs partial error handling, you can create savepoints (SAVEPOINT) and use exception blocks for local rollbacks.

Example: Adding an Order with Integrity Control

Let's look at this situation — you have three tables:

• customers — customers
• orders — orders
• order_log — order log

Here's the schema:

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL
);

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT NOT NULL REFERENCES customers(customer_id),
    order_date TIMESTAMP NOT NULL DEFAULT NOW(),
    amount NUMERIC(10,2) NOT NULL
);

CREATE TABLE order_log (
    log_id SERIAL PRIMARY KEY,
    order_id INT,
    log_message TEXT NOT NULL,
    log_date TIMESTAMP NOT NULL DEFAULT NOW()
);

Building a Multi-Step Procedure: FUNCTION or PROCEDURE?

Important!

• If you need full control over transactions (savepoints, explicit COMMIT/ROLLBACK) — use CREATE PROCEDURE.
• If your procedure is logically atomic ("all or nothing") and is called from other SQL queries — use a function.

Function version (atomic logic):

CREATE OR REPLACE FUNCTION add_order(
    p_customer_id INT,
    p_amount NUMERIC(10,2)
) RETURNS VOID AS $$
DECLARE
    v_order_id INT;
BEGIN
    -- 1. Customer validation
    IF NOT EXISTS (SELECT 1 FROM customers WHERE customer_id = p_customer_id) THEN
        RAISE EXCEPTION 'Customer with ID % does not exist', p_customer_id;
    END IF;

    -- 2. Insert order
    INSERT INTO orders (customer_id, amount)
    VALUES (p_customer_id, p_amount)
    RETURNING order_id INTO v_order_id;

    -- 3. Logging
    INSERT INTO order_log (order_id, log_message)
    VALUES (v_order_id, 'Order successfully created.');

    RAISE NOTICE 'Order % for customer % added successfully', v_order_id, p_customer_id;
END;
$$ LANGUAGE plpgsql;

Note: functions in PostgreSQL always run inside a single outer transaction. You can't use transaction control (COMMIT, ROLLBACK, SAVEPOINT) inside a function. Rollback or commit happens outside.

Version with error handling and error logging:

CREATE OR REPLACE FUNCTION add_order_with_error_logging(
    p_customer_id INT,
    p_amount NUMERIC(10,2)
) RETURNS VOID AS $$
DECLARE
    v_order_id INT;
BEGIN
    BEGIN
        -- Customer validation
        IF NOT EXISTS (SELECT 1 FROM customers WHERE customer_id = p_customer_id) THEN
            RAISE EXCEPTION 'Customer with ID % does not exist', p_customer_id;
        END IF;

        -- Insert order
        INSERT INTO orders (customer_id, amount)
        VALUES (p_customer_id, p_amount)
        RETURNING order_id INTO v_order_id;

        -- Logging
        INSERT INTO order_log (order_id, log_message)
        VALUES (v_order_id, 'Order successfully created.');

        RAISE NOTICE 'Order % for customer % added successfully', v_order_id, p_customer_id;
    EXCEPTION
        WHEN OTHERS THEN
            INSERT INTO order_log (log_message)
            VALUES (format('Error: %s', SQLERRM));
            RAISE; -- Rollback the whole function transaction
    END;
END;
$$ LANGUAGE plpgsql;

BEGIN ... EXCEPTION ... END block: In PL/pgSQL, inside functions and procedures, this block creates a virtual savepoint. All changes inside the block are rolled back if an error happens.

Partial Commits and Step-by-Step Handling: Why Use Procedures

If you need step-by-step commits (real partial commits) — use PROCEDURES!

Since PostgreSQL 11, you can write standalone procedures (CREATE PROCEDURE) that can manage transactions on the server side. Only in PROCEDURES (not functions!) can you explicitly run COMMIT and ROLLBACK. But: SAVEPOINT, RELEASE SAVEPOINT, and ROLLBACK TO SAVEPOINT are all forbidden in PL/pgSQL — for partial rollback inside functions and procedures, use exception handlers (BEGIN ... EXCEPTION ... END) instead.

Example procedure with step-by-step handling and error processing

CREATE OR REPLACE PROCEDURE add_order_step_by_step(
    p_customer_id INT,
    p_amount NUMERIC(10,2)
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_order_id INT;
BEGIN
    -- First block: customer validation
    BEGIN
        IF NOT EXISTS (SELECT 1 FROM customers WHERE customer_id = p_customer_id) THEN
            RAISE EXCEPTION 'Customer with ID % does not exist', p_customer_id;
        END IF;
    EXCEPTION
        WHEN OTHERS THEN
            INSERT INTO order_log (log_message)
            VALUES (format('Error (validate): %s', SQLERRM));
            RETURN;
    END;

    -- Second block: insert order
    BEGIN
        INSERT INTO orders (customer_id, amount)
        VALUES (p_customer_id, p_amount)
        RETURNING order_id INTO v_order_id;
    EXCEPTION
        WHEN OTHERS THEN
            INSERT INTO order_log (log_message)
            VALUES (format('Error (order): %s', SQLERRM));
            RETURN;
    END;

    -- Third block: log successful operation
    BEGIN
        INSERT INTO order_log (order_id, log_message)
        VALUES (v_order_id, 'Order successfully created.');
    EXCEPTION
        WHEN OTHERS THEN
            -- Doesn't really matter if logging fails here
            RAISE NOTICE 'Failed to write log for order %', v_order_id;
    END;

    RAISE NOTICE 'Order % for customer % added successfully (procedure)', v_order_id, p_customer_id;
END;
$$;

Calling the procedure:

CALL add_order_step_by_step(1, 150.50);

Best Practices for Transactions and Procedures

• Use functions for atomic business operations — when you want "all or nothing".
• For step-by-step commits or isolated rollbacks of steps — use procedures and call them outside an explicit transaction (autocommit mode).
• For "partial rollback" use BEGIN ... EXCEPTION ... END blocks — inside them, PL/pgSQL creates a savepoint and rolls back the block's changes if there's an error.
• Log errors — it's the best way to figure out why something didn't load or failed.
• Don't use SAVEPOINT, RELEASE SAVEPOINT, or ROLLBACK TO SAVEPOINT inside PL/pgSQL functions or procedures — all three throw a syntax error. Use BEGIN ... EXCEPTION ... END blocks for partial rollback instead.

Testing: Success and Error Scenarios

-- Add a customer
INSERT INTO customers (name, email) VALUES ('John Doe', 'john.doe@example.com');

-- Call the function (should work fine)
SELECT add_order(1, 300.00);

-- Call the function with a non-existent customer (will throw an error)
SELECT add_order(999, 100.00);

-- Check the log table
SELECT * FROM order_log;

2

Task

SQL SELF, level 53, lesson 2

Locked

Creating a procedure for inserting data with logging

Creating a procedure for inserting data with logging

Open