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
DECLAREdefine variables locales.BEGIN ... ENDdelimita el bloque ejecutable.IF,CASE,LOOP,WHILEyFORcontrolan el flujo.RETURN,RETURN NEXTyRETURN QUERYdevuelven resultados según el tipo declarado.RAISEcomunica mensajes o errores.EXCEPTIONtrata condiciones de error concretas.- PL/pgSQL no dispone de una sentencia
GOTOgeneral.
PL/pgSQL complementa a SQL: antes de escribir lógica procedural, comprueba si una consulta declarativa resuelve el problema de forma más sencilla.