Funciones y stored procedures en PL/pgSQL

PostgreSQL permite encapsular lógica del lado del servidor mediante dos objetos diferenciados: funciones y procedimientos. Ambos se escriben habitualmente en PL/pgSQL, el lenguaje procedural por defecto de PostgreSQL, que añade variables, control de flujo y manejo de excepciones al SQL puro. A pesar de compartir sintaxis general, funciones y procedures cumplen papeles distintos: las primeras forman parte de expresiones y devuelven datos, mientras que los segundos se invocan con CALL y pueden orquestar transacciones con COMMIT y ROLLBACK.

Funciones con CREATE FUNCTION

Una función se declara con CREATE FUNCTION ... RETURNS ... LANGUAGE plpgsql. El cuerpo se delimita con $$ ... $$ para evitar escapes de comillas internas. La cláusula RETURNS específica el tipo devuelto, que puede ser un tipo escalar, un tipo compuesto, una tabla (RETURNS TABLE (...)) o un SETOF de filas.

CREATE OR REPLACE FUNCTION calcular_iva(importe NUMERIC, tipo NUMERIC DEFAULT 0.21)
RETURNS NUMERIC
LANGUAGE plpgsql
IMMUTABLE
AS $$
BEGIN
    RETURN ROUND(importe * tipo, 2);
END;
$$;

SELECT calcular_iva(100);        -- 21.00
SELECT calcular_iva(100, 0.10);  -- 10.00

La marca IMMUTABLE indica al planificador que la función devuelve siempre el mismo resultado para los mismos argumentos y puede cachearse. Otras marcas habituales son STABLE (resultado estable dentro de una consulta) y VOLATILE (valor por defecto, sin garantía de estabilidad). Elegir la marca correcta es importante para que PostgreSQL optimice consultas que llaman a la función.

Las funciones pueden invocarse desde cualquier contexto que admita una expresión: un SELECT, un WHERE, una columna generada, una vista o un trigger.

SELECT producto,
       precio,
       calcular_iva(precio) AS iva,
       precio + calcular_iva(precio) AS total
FROM productos;

Variables locales y control de flujo

El bloque DECLARE lista variables locales con su tipo. Si se omite el bloque, se pueden usar solo los parámetros. PL/pgSQL soporta IF/ELSIF/ELSE/END IF, CASE, bucles LOOP, WHILE y FOR (tanto numéricos como sobre resultados de consulta) y la sentencia RAISE para emitir mensajes.

CREATE OR REPLACE FUNCTION clasificar_pedido(importe NUMERIC)
RETURNS TEXT
LANGUAGE plpgsql
IMMUTABLE
AS $$
DECLARE
    categoria TEXT;
BEGIN
    IF importe < 50 THEN
        categoria := 'pequeno';
    ELSIF importe < 500 THEN
        categoria := 'medio';
    ELSE
        categoria := 'grande';
    END IF;

    RAISE NOTICE 'Pedido de % clasificado como %', importe, categoria;
    RETURN categoria;
END;
$$;

El bloque CASE admite dos formas, una simple que evalúa una expresión y otra con condiciones arbitrarias:

CREATE OR REPLACE FUNCTION etiqueta_estado(estado TEXT)
RETURNS TEXT
LANGUAGE plpgsql
AS $$
BEGIN
    CASE estado
        WHEN 'P' THEN RETURN 'Pendiente';
        WHEN 'E' THEN RETURN 'Enviado';
        WHEN 'C' THEN RETURN 'Cancelado';
        ELSE RETURN 'Desconocido';
    END CASE;
END;
$$;

Los bucles FOR se usan habitualmente para iterar sobre el resultado de una consulta sin tener que declarar un cursor explícito:

CREATE OR REPLACE FUNCTION total_facturado_cliente(p_cliente_id INT)
RETURNS NUMERIC
LANGUAGE plpgsql
AS $$
DECLARE
    suma NUMERIC := 0;
    fila RECORD;
BEGIN
    FOR fila IN
        SELECT importe FROM pedidos WHERE cliente_id = p_cliente_id
    LOOP
        suma := suma + fila.importe;
    END LOOP;

    RETURN suma;
END;
$$;

Para un contador numérico clásico:

DO $$
DECLARE
    i INT;
BEGIN
    FOR i IN 1..5 LOOP
        RAISE NOTICE 'Iteracion %', i;
    END LOOP;
END;
$$;

El bloque DO ejecuta código PL/pgSQL anónimo, útil para scripts puntuales.

Parámetros IN, OUT e INOUT

Por defecto los parámetros son IN (solo lectura). OUT declara valores de salida sin necesidad de RETURNS, e INOUT combina ambos sentidos. Cuando hay varios OUT el tipo devuelto es un registro compuesto.

CREATE OR REPLACE FUNCTION estadisticas_pedidos(
    p_cliente_id INT,
    OUT num_pedidos INT,
    OUT importe_total NUMERIC,
    OUT importe_medio NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    SELECT COUNT(*), COALESCE(SUM(importe), 0), COALESCE(AVG(importe), 0)
      INTO num_pedidos, importe_total, importe_medio
    FROM pedidos
    WHERE cliente_id = p_cliente_id;
END;
$$;

SELECT * FROM estadisticas_pedidos(42);

El INOUT es menos habitual, pero resulta cómodo cuando una función recibe un valor y lo devuelve transformado.

CREATE OR REPLACE FUNCTION normalizar_email(INOUT email TEXT)
LANGUAGE plpgsql
AS $$
BEGIN
    email := LOWER(TRIM(email));
END;
$$;

SELECT normalizar_email('  User@Example.com  ');

Tipos compuestos: RECORD, %ROWTYPE y %TYPE

PL/pgSQL ofrece tres mecanismos para manipular tipos compuestos. %TYPE toma el tipo de una columna existente, %ROWTYPE representa la fila completa de una tabla, y RECORD es un tipo dinámico que se ajusta al resultado asignado.

CREATE OR REPLACE FUNCTION email_cliente(p_cliente_id INT)
RETURNS TEXT
LANGUAGE plpgsql
AS $$
DECLARE
    v_email clientes.email%TYPE;
BEGIN
    SELECT email INTO v_email FROM clientes WHERE id = p_cliente_id;
    RETURN v_email;
END;
$$;

Usar %TYPE evita hardcodear el tipo: si la columna email cambia de VARCHAR(100) a TEXT, la función sigue funcionando sin modificarla. %ROWTYPE es útil cuando se necesita toda la fila:

CREATE OR REPLACE FUNCTION ficha_cliente(p_cliente_id INT)
RETURNS clientes
LANGUAGE plpgsql
AS $$
DECLARE
    cli clientes%ROWTYPE;
BEGIN
    SELECT * INTO cli FROM clientes WHERE id = p_cliente_id;
    RETURN cli;
END;
$$;

RECORD es el tipo más flexible pero también el menos tipado:

DO $$
DECLARE
    r RECORD;
BEGIN
    FOR r IN SELECT id, nombre FROM clientes ORDER BY id LIMIT 3
    LOOP
        RAISE NOTICE '% - %', r.id, r.nombre;
    END LOOP;
END;
$$;

Devolver conjuntos de filas

Una función puede devolver un conjunto con RETURNS TABLE(...) o RETURNS SETOF tipo. La sentencia RETURN QUERY devuelve el resultado de una consulta, y RETURN NEXT emite fila a fila.

CREATE OR REPLACE FUNCTION top_clientes(p_limite INT)
RETURNS TABLE(cliente_id INT, total NUMERIC)
LANGUAGE plpgsql
STABLE
AS $$
BEGIN
    RETURN QUERY
    SELECT p.cliente_id, SUM(p.importe)
    FROM pedidos p
    GROUP BY p.cliente_id
    ORDER BY SUM(p.importe) DESC
    LIMIT p_limite;
END;
$$;

SELECT * FROM top_clientes(10);

La versión con RETURN NEXT es útil cuando cada fila se calcula con lógica no expresable como un solo SELECT:

CREATE OR REPLACE FUNCTION fibonacci(p_n INT)
RETURNS SETOF BIGINT
LANGUAGE plpgsql
IMMUTABLE
AS $$
DECLARE
    a BIGINT := 0;
    b BIGINT := 1;
    t BIGINT;
    i INT;
BEGIN
    FOR i IN 1..p_n LOOP
        RETURN NEXT a;
        t := a + b;
        a := b;
        b := t;
    END LOOP;
END;
$$;

SELECT * FROM fibonacci(10);

Procedures con CREATE PROCEDURE y CALL

Los procedimientos se introdujeron en PostgreSQL 11. A diferencia de las funciones, no devuelven un valor como parte de una expresión y se invocan con CALL. Su diferencia principal es que pueden contener COMMIT y ROLLBACK, lo que permite encapsular transacciones compuestas desde el servidor.

CREATE OR REPLACE PROCEDURE archivar_pedidos_antiguos(p_dias INT)
LANGUAGE plpgsql
AS $$
DECLARE
    v_borrados INT;
BEGIN
    INSERT INTO pedidos_historico
    SELECT * FROM pedidos WHERE fecha < NOW() - (p_dias || ' days')::INTERVAL;

    GET DIAGNOSTICS v_borrados = ROW_COUNT;
    RAISE NOTICE 'Archivados % pedidos', v_borrados;

    DELETE FROM pedidos WHERE fecha < NOW() - (p_dias || ' days')::INTERVAL;

    COMMIT;
END;
$$;

CALL archivar_pedidos_antiguos(365);

GET DIAGNOSTICS lee variables del sistema como ROW_COUNT (filas afectadas por la última sentencia) o PG_CONTEXT (traza). Es habitual usarla para registrar métricas tras operaciones masivas.

COMMIT y ROLLBACK dentro de un procedure

Un procedure no puede llamarse desde SELECT; debe invocarse con CALL y fuera de una transacción explícita de cliente (porque dentro de una transacción el propio COMMIT del procedure fallaría). Esta restricción lo diferencia claramente de una función.

CREATE OR REPLACE PROCEDURE procesar_lote_facturas(p_mes DATE)
LANGUAGE plpgsql
AS $$
DECLARE
    v_ids BIGINT[];
    v_id  BIGINT;
BEGIN
    -- Materializamos los ids en un array para poder hacer COMMIT dentro del bucle
    -- sin mantener un cursor abierto sobre facturas_pendientes.
    SELECT array_agg(id)
      INTO v_ids
    FROM facturas_pendientes
    WHERE mes = p_mes;

    IF v_ids IS NULL THEN
        RETURN;
    END IF;

    FOREACH v_id IN ARRAY v_ids LOOP
        UPDATE facturas_pendientes SET estado = 'procesando' WHERE id = v_id;
        COMMIT;

        BEGIN
            INSERT INTO facturas_procesadas SELECT * FROM facturas_pendientes WHERE id = v_id;
            DELETE FROM facturas_pendientes WHERE id = v_id;
            COMMIT;
        EXCEPTION WHEN OTHERS THEN
            -- El sub-bloque hace rollback implicito al capturar la excepcion;
            -- marcamos la fila como fallida y confirmamos ese cambio.
            UPDATE facturas_pendientes SET estado = 'error' WHERE id = v_id;
            COMMIT;
        END;
    END LOOP;
END;
$$;

CALL procesar_lote_facturas('2026-04-01');

Este patrón permite procesar grandes volúmenes por lotes sin mantener una única transacción enorme, reduciendo el riesgo de bloat y de locks prolongados.

Manejo de excepciones

Los bloques EXCEPTION ... WHEN ... THEN capturan errores SQL concretos (unique_violation, foreign_key_violation, division_by_zero) o el comodín OTHERS. Dentro del bloque están disponibles SQLSTATE y SQLERRM.

CREATE OR REPLACE FUNCTION insertar_cliente_seguro(
    p_email TEXT,
    p_nombre TEXT
) RETURNS INT
LANGUAGE plpgsql
AS $$
DECLARE
    v_id INT;
BEGIN
    INSERT INTO clientes(email, nombre)
    VALUES (p_email, p_nombre)
    RETURNING id INTO v_id;

    RETURN v_id;
EXCEPTION
    WHEN unique_violation THEN
        RAISE NOTICE 'Email % ya existe, recuperando id existente', p_email;
        SELECT id INTO v_id FROM clientes WHERE email = p_email;
        RETURN v_id;
    WHEN OTHERS THEN
        RAISE WARNING 'Error no esperado: % (SQLSTATE=%)', SQLERRM, SQLSTATE;
        RETURN NULL;
END;
$$;

Cuándo usar FUNCTION y cuándo PROCEDURE

La regla práctica es sencilla. Se usa FUNCTION cuando se quiere obtener datos (un valor, un registro o un conjunto de filas) para consumir desde SQL, cuando la operación debe poder formar parte de expresiones (índices, vistas, generated columns, triggers) o cuando la unidad de trabajo es atómica sin necesidad de varios COMMIT intermedios.

Se usa PROCEDURE cuando el objetivo es ejecutar un proceso desde un cliente o un job, con efectos secundarios (cargas, migraciones, limpiezas) y con la necesidad de controlar transacciones (hacer commit cada N filas, reiniciar en caso de error). También es la forma recomendada cuando se llama desde psql o desde un scheduler externo y no interesa recoger un valor de retorno.

Llamar funciones y procedures desde otras rutinas

Tanto funciones como procedures pueden invocarse desde código PL/pgSQL. La sintaxis es distinta: las funciones se usan como expresiones (v := mi_fn(...) o SELECT mi_fn(...) INTO v), los procedures se llaman con CALL dentro del cuerpo.

CREATE OR REPLACE FUNCTION coste_con_descuento(
    p_importe NUMERIC,
    p_cliente_id INT
) RETURNS NUMERIC
LANGUAGE plpgsql
STABLE
AS $$
DECLARE
    v_descuento NUMERIC;
BEGIN
    SELECT descuento INTO v_descuento FROM clientes WHERE id = p_cliente_id;
    RETURN p_importe * (1 - COALESCE(v_descuento, 0));
END;
$$;

CREATE OR REPLACE PROCEDURE aplicar_descuentos_pedidos()
LANGUAGE plpgsql
AS $$
DECLARE
    fila RECORD;
BEGIN
    FOR fila IN SELECT id, cliente_id, importe FROM pedidos WHERE procesado = false
    LOOP
        UPDATE pedidos
        SET importe = coste_con_descuento(fila.importe, fila.cliente_id),
            procesado = true
        WHERE id = fila.id;
    END LOOP;
    COMMIT;
END;
$$;

Metadatos y mantenimiento

PostgreSQL almacena las rutinas en pg_proc. La vista information_schema.routines ofrece una cara más portable. Para inspeccionar una función desde psql basta con \df nombre (describe) o \sf nombre (imprime el cuerpo completo).

SELECT routine_schema, routine_name, routine_type
FROM information_schema.routines
WHERE routine_schema = 'public'
ORDER BY routine_type, routine_name;

Una buena práctica es versionar el código de las rutinas en un repositorio Git con migraciones Flyway, Liquibase o dbmate, de forma que CREATE OR REPLACE deje claro el diff entre versiones. Encapsular lógica en la base de datos ahorra viajes de red y centraliza reglas de negocio críticas, pero exige disciplina de despliegue equivalente a la del código de aplicación.