Creación de procedimientos con varios pasos: validación de datos, inserción y logging

En escenarios de negocio reales, no basta con hacer una sola operación, sino que hay que construir una cadena de acciones: por ejemplo, cuando llega un pedido — validar los datos del cliente, guardar el pedido, registrar el evento para auditoría. Un procedimiento de varios pasos te permite juntar todo esto en una sola lógica y garantizar la integridad gracias a las transacciones: si algo falla en cualquier paso, todo se revierte.

Con las nuevas versiones de PostgreSQL, sobre todo desde que existen los procedimientos (CREATE PROCEDURE) y se amplió el manejo de transacciones, es importante entender la diferencia entre función y procedimiento en PL/pgSQL, y también cómo trabajar bien con puntos de guardado (SAVEPOINT), rollbacks y bloques de manejo de errores.

Bases de la estructura de un procedimiento de varios pasos

Un procedimiento típico de negocio tiene estos pasos:

1. Validación de datos — validar los argumentos de entrada, existencia del cliente/producto, etc.
2. Inserción de datos — añadir (o actualizar) el/los registro(s) realmente.
3. Logging o auditoría — guardar información sobre la operación exitosa o fallida.

Cada paso se puede hacer dentro de una sola transacción (atómicamente), o si el proceso es "largo" o necesita manejar errores por partes, puedes crear puntos de guardado (SAVEPOINT) y usar bloques de manejo de excepciones para hacer rollback local.

Ejemplo: añadir un pedido con control de integridad

Vamos a ver esta situación — hay tres tablas:

• customers — clientes
• orders — pedidos
• order_log — log de pedidos

Preparamos el esquema:

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()
);

Creando un procedimiento de varios pasos: ¿FUNCIÓN o PROCEDIMIENTO?

¡Importante!

• Si necesitas control total sobre las transacciones (puntos de guardado, COMMIT/ROLLBACK explícitos) — usa CREATE PROCEDURE.
• Si la lógica es atómica ("todo o nada") y la llamas desde otras consultas SQL — usa una función.

Versión como función (lógica atómica):

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. Validación del cliente
    IF NOT EXISTS (SELECT 1 FROM customers WHERE customer_id = p_customer_id) THEN
        RAISE EXCEPTION 'Cliente con ID % no existe', p_customer_id;
    END IF;

    -- 2. Inserción del pedido
    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, 'Pedido creado correctamente.');

    RAISE NOTICE 'Pedido % para cliente % añadido correctamente', v_order_id, p_customer_id;
END;
$$ LANGUAGE plpgsql;

Peculiaridad: las funciones en PostgreSQL siempre se ejecutan dentro de una transacción externa. No puedes usar control transaccional (COMMIT, ROLLBACK, SAVEPOINT) dentro de una función. El rollback o commit ocurre fuera.

Versión con manejo de errores y logging de errores:

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
        -- Validación del cliente
        IF NOT EXISTS (SELECT 1 FROM customers WHERE customer_id = p_customer_id) THEN
            RAISE EXCEPTION 'Cliente con ID % no existe', p_customer_id;
        END IF;

        -- Inserción del pedido
        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, 'Pedido creado correctamente.');

        RAISE NOTICE 'Pedido % para cliente % añadido correctamente', v_order_id, p_customer_id;
    EXCEPTION
        WHEN OTHERS THEN
            INSERT INTO order_log (log_message)
            VALUES (format('Error: %', SQLERRM));
            RAISE; -- Rollback de toda la transacción de la función
    END;
END;
$$ LANGUAGE plpgsql;

Bloque BEGIN ... EXCEPTION ... END: En PL/pgSQL, dentro de funciones y procedimientos, este bloque crea un savepoint virtual. Todos los cambios dentro del bloque se revierten si ocurre un error.

Commits parciales y procesamiento paso a paso: por qué usar procedimientos

Si necesitas commit por etapas (realmente guardar parcialmente) — ¡usa PROCEDIMIENTOS!

En PostgreSQL desde la versión 11 puedes escribir procedimientos (CREATE PROCEDURE) que pueden manejar transacciones y savepoints en el servidor. Solo en los PROCEDIMIENTOS (¡no en funciones!) puedes hacer COMMIT, ROLLBACK, SAVEPOINT, RELEASE SAVEPOINT explícitamente. Pero: el comando ROLLBACK TO SAVEPOINT en un procedimiento PL/pgSQL está prohibido — usa manejadores de excepciones.

Ejemplo de procedimiento con procesamiento paso a paso y manejo de errores

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
    -- Primer bloque: validación del cliente
    BEGIN
        IF NOT EXISTS (SELECT 1 FROM customers WHERE customer_id = p_customer_id) THEN
            RAISE EXCEPTION 'Cliente con ID % no existe', p_customer_id;
        END IF;
    EXCEPTION
        WHEN OTHERS THEN
            INSERT INTO order_log (log_message)
            VALUES (format('Error (validar): %', SQLERRM));
            RETURN;
    END;

    -- Segundo bloque: inserción del pedido
    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 (pedido): %', SQLERRM));
            RETURN;
    END;

    -- Tercer bloque: logging de la operación exitosa
    BEGIN
        INSERT INTO order_log (order_id, log_message)
        VALUES (v_order_id, 'Pedido creado correctamente.');
    EXCEPTION
        WHEN OTHERS THEN
            -- Aquí da igual, aunque el logging falle
            RAISE NOTICE 'No se pudo escribir el log para el pedido %', v_order_id;
    END;

    RAISE NOTICE 'Pedido % para cliente % añadido correctamente (procedimiento)', v_order_id, p_customer_id;
END;
$$;

Llamada al procedimiento:

CALL add_order_step_by_step(1, 150.50);

Buenas prácticas al trabajar con transacciones y procedimientos

• Usa funciones para operaciones de negocio atómicas — cuando necesitas el principio de "todo o nada".
• Para commits paso a paso o rollback aislado de etapas — usa procedimientos y llámalos fuera de una transacción explícita (modo autocommit).
• Para "rollback parcial" usa bloques BEGIN ... EXCEPTION ... END — dentro de ellos PL/pgSQL crea un savepoint y revierte los cambios del bloque si hay error.
• Haz logging de errores — es la mejor forma de entender por qué algo no se cargó o no funcionó.
• No uses ROLLBACK TO SAVEPOINT en procedimientos PL/pgSQL — eso da error de sintaxis (limitación de PostgreSQL 17+).

Testing: escenario exitoso y con error

-- Añadimos un cliente
INSERT INTO customers (name, email) VALUES ('John Doe', 'john.doe@example.com');

-- Llamamos a la función (debería ir bien)
SELECT add_order(1, 300.00);

-- Llamamos a la función con un cliente que no existe (dará error)
SELECT add_order(999, 100.00);

-- Revisamos el log
SELECT * FROM order_log;

2

Tarea

SQL SELF, nivel 53, lección 2

Bloqueada

Creación de un procedimiento para insertar datos con registro de logs

Creación de un procedimiento para insertar datos con registro de logs

Abrir