Procedimientos Almacenados y Funciones en PostgreSQL

PostgreSQL es una de las bases de datos relacionales más avanzadas y de código abierto que existen. Ofrece una amplia gama de características, incluida la capacidad de crear procedimientos almacenados y funciones, lo que permite encapsular lógica de negocios directamente en la base de datos. 

Un procedimiento almacenado es un conjunto de instrucciones SQL que puedes guardar y ejecutar en el servidor de base de datos. Estos procedimientos pueden aceptar parámetros, realizar operaciones complejas y devolver resultados. Son útiles para encapsular lógica de negocios, mejorar el rendimiento y simplificar el mantenimiento del código.

Una función en PostgreSQL es similar a un procedimiento almacenado, pero generalmente se usa para devolver un valor o un conjunto de resultados. Las funciones pueden ser escalar (devolviendo un único valor) o de conjunto (devolviendo una tabla de resultados).

Vamos a crear una función simple que suma dos números.

CREATE OR REPLACE FUNCTION suma(a INTEGER, b INTEGER)

RETURNS INTEGER AS $$

BEGIN

    RETURN a + b;

END;

$$ LANGUAGE plpgsql;

- **CREATE OR REPLACE FUNCTION**: Esta declaración crea una nueva función o reemplaza una existente.

- **RETURNS INTEGER**: Indica que la función devuelve un valor entero.

- **LANGUAGE plpgsql**: Especifica que la función está escrita en PL/pgSQL, el lenguaje de procedimientos de PostgreSQL.

Para ejecutar la función:

SELECT suma(3, 5); -- Devuelve 8

Vamos a crear una función que devuelve una lista de usuarios con su respectiva edad.

CREATE OR REPLACE FUNCTION obtener_usuarios()

RETURNS TABLE(nombre VARCHAR, edad INTEGER) AS $$

BEGIN

    RETURN QUERY

    SELECT nombre, edad FROM usuarios;

END;

$$ LANGUAGE plpgsql;

Para ejecutar la función:

SELECT * FROM obtener_usuarios();

A partir de PostgreSQL 11, se introdujo el soporte para procedimientos almacenados, que son similares a las funciones pero permiten realizar operaciones de control de transacciones (COMMIT y ROLLBACK) dentro del procedimiento.

Vamos a crear un procedimiento que inserta un nuevo usuario en la tabla `usuarios`.

CREATE OR REPLACE PROCEDURE insertar_usuario(nombre VARCHAR, edad INTEGER)

LANGUAGE plpgsql AS $$

BEGIN

    INSERT INTO usuarios (nombre, edad) VALUES (nombre, edad);

END;

$$;

Para ejecutar el procedimiento:

CALL insertar_usuario('Juan', 30);

Los procedimientos almacenados y las funciones en PostgreSQL son herramientas poderosas para encapsular lógica de negocios y mejorar la eficiencia de las operaciones de base de datos. Con el uso adecuado, pueden simplificar el desarrollo y mantenimiento de aplicaciones, al tiempo que mejoran el rendimiento y la seguridad.