Saltar al contenido principal

PL/pgSQL

PL/pgSQL es el lenguaje procedural integrado en PostgreSQL. Permite combinar sentencias SQL con variables, condiciones, bucles y gestión de errores dentro de funciones, procedimientos y bloques anónimos.

Los ejemplos de esta unidad presuponen las tablas que se mencionan en cada consulta. Por ejemplo, usuarios(id, nombre, edad), empleados(id, nombre, edad) y datos(fecha, valor).

Una primera función​

Una función declara sus parámetros, el tipo que devuelve y el lenguaje de su cuerpo:

CREATE OR REPLACE FUNCTION calcular_promedio(a INTEGER, b INTEGER)
RETURNS NUMERIC
LANGUAGE plpgsql
AS $$
BEGIN
RETURN (a + b) / 2.0;
END;
$$;

Podemos ejecutarla desde una consulta:

SELECT calcular_promedio(8, 5);

Parámetros y resultados tabulares​

RETURNS TABLE permite devolver varias columnas. Conviene que los nombres de salida no coincidan con los de la tabla para evitar referencias ambiguas:

CREATE OR REPLACE FUNCTION obtener_usuario(p_id INTEGER)
RETURNS TABLE (nombre_usuario TEXT, edad_usuario INTEGER)
LANGUAGE plpgsql
AS $$
BEGIN
RETURN QUERY
SELECT u.nombre, u.edad
FROM usuarios AS u
WHERE u.id = p_id;
END;
$$;
SELECT * FROM obtener_usuario(1);

Variables y condiciones​

Las variables se declaran antes de BEGIN. Después podemos asignarles valores y tomar decisiones:

CREATE OR REPLACE FUNCTION clasificar_edad(p_edad INTEGER)
RETURNS TEXT
LANGUAGE plpgsql
AS $$
DECLARE
categoria TEXT;
BEGIN
IF p_edad < 0 THEN
categoria := 'edad no válida';
ELSIF p_edad < 18 THEN
categoria := 'menor de edad';
ELSE
categoria := 'mayor de edad';
END IF;

RETURN categoria;
END;
$$;

Bucles​

PL/pgSQL ofrece LOOP, WHILE y FOR. Este ejemplo suma los números de 1 hasta el límite recibido:

CREATE OR REPLACE FUNCTION sumar_hasta(limite INTEGER)
RETURNS INTEGER
LANGUAGE plpgsql
AS $$
DECLARE
numero INTEGER;
total INTEGER := 0;
BEGIN
FOR numero IN 1..limite LOOP
total := total + numero;
END LOOP;

RETURN total;
END;
$$;

Gestión de errores​

El bloque EXCEPTION permite tratar errores concretos. Aquí capturamos una división entre cero:

CREATE OR REPLACE FUNCTION dividir(dividendo NUMERIC, divisor NUMERIC)
RETURNS NUMERIC
LANGUAGE plpgsql
AS $$
BEGIN
RETURN dividendo / divisor;
EXCEPTION
WHEN division_by_zero THEN
RAISE NOTICE 'No se puede dividir entre cero';
RETURN NULL;
END;
$$;

No conviene capturar todos los errores de forma indiscriminada: identificar una condición concreta hace más fácil detectar y corregir problemas reales.

Combinar PL/pgSQL con funciones de ventana​

Las funciones de ventana pertenecen a SQL, no exclusivamente a PL/pgSQL. Podemos utilizarlas dentro de una función cuando formen parte de una consulta:

CREATE OR REPLACE FUNCTION medias_moviles()
RETURNS TABLE (
fecha_resultado DATE,
valor_resultado INTEGER,
promedio_movil NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
RETURN QUERY
SELECT
d.fecha,
d.valor,
AVG(d.valor) OVER (
ORDER BY d.fecha
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
)
FROM datos AS d;
END;
$$;

Registros y %ROWTYPE​

%ROWTYPE crea una variable con la misma estructura que una fila de una tabla:

DO $$
DECLARE
empleado empleados%ROWTYPE;
BEGIN
SELECT *
INTO STRICT empleado
FROM empleados
WHERE id = 1;

RAISE NOTICE 'Empleado: %, edad: %', empleado.nombre, empleado.edad;
END;
$$;

STRICT exige que la consulta devuelva exactamente una fila. Si devuelve cero o más de una, PostgreSQL genera un error que podemos tratar si es necesario.

Para insertar datos es preferible indicar las columnas de forma explícita:

INSERT INTO empleados (nombre, edad)
VALUES ('Ana', 32);

Cursores​

Un cursor permite recorrer el resultado de una consulta fila a fila. Para muchos problemas basta con una consulta SQL normal; utiliza cursores solo cuando el procesamiento secuencial aporte valor.

DO $$
DECLARE
empleado_cursor CURSOR FOR
SELECT nombre, edad FROM empleados ORDER BY id;
empleado RECORD;
BEGIN
OPEN empleado_cursor;

LOOP
FETCH empleado_cursor INTO empleado;
EXIT WHEN NOT FOUND;
RAISE NOTICE '% tiene % años', empleado.nombre, empleado.edad;
END LOOP;

CLOSE empleado_cursor;
END;
$$;

Funciones, procedimientos y transacciones​

Una función no puede ejecutar COMMIT ni ROLLBACK. La transacción se controla desde quien invoca la función:

BEGIN;
SELECT calcular_promedio(8, 5);
COMMIT;

Los procedimientos creados con CREATE PROCEDURE y los bloques anónimos DO pueden controlar transacciones en contextos de llamada permitidos por PostgreSQL. Esa capacidad depende de cómo se invoquen y no puede trasladarse a una función.

Ideas clave​

  • DECLARE define variables locales.
  • BEGIN ... END delimita el bloque ejecutable.
  • IF, CASE, LOOP, WHILE y FOR controlan el flujo.
  • RETURN, RETURN NEXT y RETURN QUERY devuelven resultados según el tipo declarado.
  • RAISE comunica mensajes o errores.
  • EXCEPTION trata condiciones de error concretas.
  • PL/pgSQL no dispone de una sentencia GOTO general.

PL/pgSQL complementa a SQL: antes de escribir lógica procedural, comprueba si una consulta declarativa resuelve el problema de forma más sencilla.