Generación automática de informes según horario

Cuando trabajas con bases de datos pequeñas, no pasa nada si lanzas consultas o procedimientos manualmente para crear informes. Pero en el mundo real, las bases de datos crecen tanto que cualquier tarea repetitiva hay que automatizarla sí o sí. Imagina que cada día te piden preparar un informe de ventas. Aunque la consulta tarde solo dos minutos, en un año habrás gastado más de 12 horas haciéndolo. Mejor aprovecha ese tiempo para tomarte un café mientras el procedimiento automático lo hace por ti.

La automatización te ayuda a:

• Reducir el trabajo manual.
• Asegurar la regularidad de los informes (por ejemplo, informes diarios, semanales).
• Minimizar la probabilidad de errores humanos.
• Aumentar la confianza en tus informes: siempre se crean con los parámetros establecidos.

Pasos principales para la generación automática de informes

La ejecución automática de informes incluye estos pasos:

1. Crear un procedimiento en PL/pgSQL que genere el informe.
2. Configurar el registro de resultados (si hace falta).
3. Usar un planificador de tareas para lanzar el procedimiento según horario.

¡Vamos a hacerlo paso a paso!

Creando un procedimiento para generar el informe

Para empezar, vamos a crear un procedimiento sencillo que calcule el total de ventas de todos los pedidos del día actual y guarde el resultado en una tabla de logs. Nuestra tabla de logs ya existe (la llamaremos sales_report_log):

CREATE TABLE sales_report_log (
    report_date DATE NOT NULL,
    total_sales NUMERIC NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Ahora creamos el procedimiento en PL/pgSQL:

CREATE OR REPLACE FUNCTION generate_daily_sales_report()
RETURNS VOID AS $$
BEGIN
    -- Calcular el total de ventas del día actual
    INSERT INTO sales_report_log (report_date, total_sales)
    SELECT CURRENT_DATE, SUM(order_total)
    FROM orders
    WHERE order_date = CURRENT_DATE;

    RAISE NOTICE 'Informe para % creado con éxito', CURRENT_DATE;
END;
$$ LANGUAGE plpgsql;

¿Qué pasa aquí?

• Usamos la función agregada SUM() para calcular el total de ventas de la tabla orders.
• La fecha del informe (report_date) siempre es la fecha actual (CURRENT_DATE).
• El resultado se guarda en la tabla sales_report_log.
• El mensaje RAISE NOTICE está para depuración: avisa que el informe se creó correctamente.

Probando el procedimiento

Antes de automatizar la ejecución de este procedimiento, siempre es útil probarlo manualmente. Ejecuta la función:

SELECT generate_daily_sales_report();

Ahora revisa el contenido de la tabla sales_report_log:

SELECT * FROM sales_report_log;

Si ves una fila con la fecha actual y el valor correcto del total de ventas — ¡enhorabuena, tu función funciona!

Automatizando tareas en PostgreSQL

A veces mola que la base de datos haga cosas sola: lanzar informes, limpiar registros antiguos o actualizar agregados según horario. PostgreSQL permite esto usando la extensión pg_cron o un planificador externo — el cron del sistema o el Task Scheduler.

Si trabajas en Linux, la mejor opción es pg_cron. Esta extensión ejecuta SQL directamente dentro de PostgreSQL, sin tener que tirar de shell o scripts.

Para instalar pg_cron (no olvides cambiar XX por tu versión de PostgreSQL):

sudo apt install postgresql-XX-cron

Después de instalarlo, hay que activarlo en la configuración. Abre postgresql.conf y añade la línea:

shared_preload_libraries = 'pg_cron'

Luego reinicia PostgreSQL y activa la extensión en tu base de datos:

CREATE EXTENSION pg_cron;

Ahora puedes programar una tarea. Por ejemplo, lanzar la función generate_daily_sales_report() cada día a medianoche:

SELECT cron.schedule(
    'daily_sales_report',
    '0 0 * * *',
    $$ SELECT generate_daily_sales_report(); $$
);

Aquí:

• 'daily_sales_report' — nombre de la tarea;
• '0 0 * * *' — horario al estilo cron (en este caso — cada día a las 00:00);
• El SQL entre $$ — el código que se ejecutará.

Para ver todas las tareas programadas, usa:

SELECT * FROM cron.job;

Si usas Windows o macOS, pg_cron o no está soportado (en Windows), o requiere compilarlo a mano desde el código fuente (en macOS). Es un rollo, y normalmente es más fácil usar el planificador del sistema.

Así se hace:

1. Crea un archivo SQL con el comando que necesitas:

echo "SELECT generate_daily_sales_report();" > /path/to/script.sql

2. Usa psql para ejecutar el archivo:

psql -h localhost -U postgres -d your_database -f /path/to/script.sql

3. Añade la ejecución de este comando al planificador de tareas:

   • En Linux/macOS: usando crontab -e:

     0 0 * * * psql -h localhost -U postgres -d your_database -f /path/to/script.sql
     

   • En Windows: usando Task Scheduler, crea una tarea que lance psql.exe con los parámetros necesarios.

• Si estás en Linux, usa pg_cron — es cómodo y está integrado en PostgreSQL.
• Si estás en Windows o Mac, mejor usa el planificador del sistema (cron o Task Scheduler) y lanza el SQL con psql.

Así puedes automatizar cualquier tarea en PostgreSQL sin complicarte la vida.

Ejemplos de informes automáticos

1. Informe diario por regiones

Supón que quieres crear automáticamente un informe de ventas por cada región. Puedes ampliar nuestra función así:

CREATE OR REPLACE FUNCTION generate_regional_sales_report()
RETURNS VOID AS $$
BEGIN
    INSERT INTO regional_sales_report_log (region, report_date, total_sales)
    SELECT region, CURRENT_DATE, SUM(order_total)
    FROM orders
    WHERE order_date = CURRENT_DATE
    GROUP BY region;

    RAISE NOTICE 'Informe regional para % creado con éxito', CURRENT_DATE;
END;
$$ LANGUAGE plpgsql;

2. Informe mensual

De forma parecida puedes crear un procedimiento para generar el informe del mes. Solo cambia el filtro en la consulta:

WHERE order_date BETWEEN date_trunc('month', CURRENT_DATE)
                     AND date_trunc('month', CURRENT_DATE) + interval '1 month - 1 day';

Errores típicos y cómo evitarlos

Al generar informes automáticamente pueden surgir problemas:

• Error de sintaxis en la función: prueba siempre las funciones manualmente antes de automatizarlas.
• Frecuencia de ejecución de tareas: si la tarea se lanza demasiado a menudo, puede sobrecargar la base de datos. Ajusta el horario con cabeza.
• Duplicado de datos: si el informe se lanza varias veces al día, puede haber duplicados. Usa claves únicas para evitar repeticiones.

Esta lección te ha mostrado cómo configurar la generación automática de informes en PostgreSQL. Ahora puedes optimizar tus procesos analíticos y dejar más tiempo para tareas importantes... como buscar bugs, escribir código o soñar con la perfección de tus consultas SQL.