Tutorial con ejemplos de procedimientos almacenados en PostgreSQL

Introducción

Los procedimientos almacenados en PostgreSQL juegan un papel importante y ofrecen varios beneficios para la gestión de bases de datos. En este tutorial, veremos cómo usar procedimientos almacenados en PostgreSQL para manejar operaciones SQL de forma más eficiente y segura. Este artículo está dirigido a ingenieros de software y administradores de bases de datos que buscan mejorar sus habilidades usando funciones más avanzadas de PostgreSQL.

¿Qué es un procedimiento almacenado?

Antes de empezar con los ejemplos, es importante entender el concepto de procedimiento almacenado. En PostgreSQL, un procedimiento almacenado es un objeto ejecutable precompilado que contiene una serie de comandos SQL. Su función principal es realizar operaciones en la base de datos, como insertar, actualizar, eliminar y crear. Son especialmente útiles para operaciones que implican varios pasos o lógica compleja, ya que permiten una mejor gestión de transacciones y reducen la vulnerabilidad de la base de datos.

Nota: los ejemplos y comandos de este tutorial son compatibles con PostgreSQL 11 y versiones más recientes.

Requisitos previos

Para este tutorial, necesitas tener instalado y configurado lo siguiente:

1. Una instalación de PostgreSQL en tu máquina local o servidor.
2. Una herramienta de línea de comandos o una GUI como Beekeeper Studio para ejecutar los comandos SQL.

Cómo crear un procedimiento almacenado en PostgreSQL

Históricamente, PostgreSQL ha usado la palabra clave FUNCTION para definir rutinas que pueden devolver un valor. Las funciones en PostgreSQL pueden realizar operaciones y deben devolver un valor o un conjunto, que puede ser un valor escalar o una tabla. También se pueden usar en consultas SQL.

PROCEDURE, introducido en PostgreSQL 11, se usa para definir rutinas que no devuelven un valor. Los procedimientos sirven para realizar acciones como modificar el estado de la base de datos sin devolver datos directamente. Se invocan con la sentencia CALL, a diferencia de las funciones, que suelen invocarse como parte de una sentencia SELECT.

Cómo usar PROCEDURE para actualizar una tabla

Empecemos creando un procedimiento almacenado sencillo en PostgreSQL. Vamos a crear un procedimiento para actualizar el salario de un empleado.

CREATE OR REPLACE PROCEDURE update_employee_salary(p_id INT, p_salary NUMERIC)
LANGUAGE plpgsql
AS $$
BEGIN
   UPDATE employees SET salary = p_salary WHERE employee_id = p_id;
END;
$$;

En el código anterior, update_employee_salary es el nombre del procedimiento, que acepta dos parámetros: el ID del empleado (p_id) y el nuevo salario (p_salary).

Cómo llamar a un procedimiento almacenado en PostgreSQL

Para llamar al procedimiento que acabamos de crear, usamos el comando CALL como se muestra a continuación:

CALL update_employee_salary(1, 50000);

El comando anterior actualizará el salario del empleado con ID 1 a 50000.

Uso avanzado: procedimientos con lógica condicional

Los procedimientos también pueden usar lógica más compleja, como sentencias condicionales y manejo de errores, para realizar operaciones más sofisticadas.

Este procedimiento actualiza el departamento de un empleado según las condiciones indicadas, mostrando el uso de estructuras de control dentro de los procedimientos.

CREATE OR REPLACE PROCEDURE update_employee_department(employee_id INT, new_department_id INT)
LANGUAGE plpgsql
AS $$
BEGIN
    IF new_department_id IS NOT NULL THEN
        UPDATE employees
        SET department_id = new_department_id
        WHERE id = employee_id;
    ELSE
        RAISE EXCEPTION 'Department ID cannot be null.';
    END IF;
END;
$$;

Resultado esperado:

CREATE PROCEDURE

Cómo usar FUNCTION para obtener datos

A diferencia de PROCEDURE, una FUNCTION en PostgreSQL es más adecuada para operaciones en las que se espera un valor de retorno. Esto es especialmente útil para obtener datos y hacer cálculos que deben formar parte de consultas SQL. Aquí crearemos un ejemplo de una función que obtiene información según condiciones específicas.

Esta función obtendrá los datos de un empleado según su ID. Mostrará cómo se pueden usar las funciones para devolver datos que luego se pueden usar directamente en consultas SQL.

CREATE OR REPLACE FUNCTION get_employee_details(employee_id INT)
RETURNS TABLE(first_name VARCHAR, last_name VARCHAR, department_id INT) AS $$
BEGIN
    RETURN QUERY 
    SELECT first_name, last_name, department_id
    FROM employees
    WHERE id = employee_id;
END;
$$ LANGUAGE plpgsql;

Resultado esperado:

CREATE FUNCTION

Cómo invocar la función

Para obtener datos con esta FUNCTION, usas la sentencia SELECT, igual que al consultar una tabla.

SELECT * FROM get_employee_details(1);

Resultado esperado:

Aquí, el resultado serán los datos del empleado con ID 1. Si el empleado existe, se mostrarán su nombre, apellido e ID de departamento. Si no existe, el resultado estará vacío.

 first_name | last_name | department_id 
------------+-----------+---------------
 John       | Doe       | 1
(1 row)

Conclusión

En este tutorial aprendimos a escribir procedimientos almacenados en PostgreSQL usando PROCEDURE y FUNCTION, y cómo invocarlos. Saber usar procedimientos almacenados ayuda a organizar mejor el código y a mejorar la seguridad y la velocidad de ejecución. Estos ejemplos son sencillos, pero los conceptos son los mismos para operaciones más complejas.

Los procedimientos almacenados en PostgreSQL ofrecen una forma robusta de encapsular lógica, asegurando que las operaciones se realicen de forma eficiente y segura. Al integrarlos en el diseño de tu base de datos, puedes lograr aplicaciones más fáciles de mantener y más confiables. Usa esta guía como punto de partida para explorar las amplias posibilidades que ofrecen las capacidades procedurales de PostgreSQL.