Funciones y PL/pgSQL

PostgreSQL permite programar lógica dentro de la base de datos con funciones en SQL puro o en PL/pgSQL, su lenguaje procedural (con variables, condiciones, ciclos y manejo de errores). En esta lección aprenderás a crear funciones que devuelven valores y tablas, procedimientos con control de transacciones y triggers para updated_at y auditoría. Si vienes del curso de SQL, esto profundiza lo visto en Procedimientos y triggers.

Concepto

Objeto	Se invoca con	Devuelve	Controla transacciones
Función	SELECT mi_funcion(...)	Un valor, una fila o una tabla	No
Procedimiento	CALL mi_procedimiento(...)	Nada (o parámetros INOUT)	Sí (COMMIT/ROLLBACK dentro)
Función trigger	Automáticamente	trigger	No

Lenguajes disponibles: sql (consultas simples), plpgsql (lógica con variables y control de flujo) y, con extensiones, Python, JavaScript y otros.

Anatomía de una función

CREATE OR REPLACE FUNCTION nombre(parametro tipo, ...)
RETURNS tipo_de_retorno
LANGUAGE plpgsql            -- o sql
STABLE                      -- volatilidad (ver abajo)
AS $$
DECLARE
  -- variables
BEGIN
  -- código
  RETURN valor;
END;
$$;

El cuerpo va entre $$ ... $$ (dollar quoting): así no tienes que escapar las comillas simples de adentro.

Funciones en SQL puro

Para cálculos sencillos, LANGUAGE sql es más corto y PostgreSQL puede optimizarlo mejor (incluso "incrustarlo" en la consulta).

CREATE OR REPLACE FUNCTION precio_con_iva(precio NUMERIC, tasa NUMERIC DEFAULT 0.16)
RETURNS NUMERIC
LANGUAGE sql
IMMUTABLE
AS $$
  SELECT ROUND(precio * (1 + tasa), 2);
$$;

SELECT precio_con_iva(899);          -- 1042.84
SELECT precio_con_iva(899, 0.08);    -- 970.92  (IVA fronterizo)
SELECT precio_con_iva(tasa => 0.08, precio => 899);   -- parámetros con nombre

Funciones que devuelven tablas

CREATE OR REPLACE FUNCTION productos_de_categoria(p_categoria_id INT)
RETURNS TABLE (id INT, nombre TEXT, precio NUMERIC)
LANGUAGE sql
STABLE
AS $$
  SELECT p.id, p.nombre, p.precio
  FROM productos p
  WHERE p.categoria_id = p_categoria_id
  ORDER BY p.precio;
$$;

-- Se usa como una tabla
SELECT * FROM productos_de_categoria(1);

id	nombre	precio
1	Audífonos Bluetooth	899.00
2	Teclado mecánico	1250.00
3	Monitor 24 pulgadas	3499.00

Es como una vista con parámetros. Supabase expone este tipo de funciones como endpoints de su API (RPC).

PL/pgSQL: variables, condiciones y ciclos

Variables y SELECT INTO

CREATE OR REPLACE FUNCTION resumen_cliente(p_cliente_id INT)
RETURNS TEXT
LANGUAGE plpgsql
STABLE
AS $$
DECLARE
  v_nombre   TEXT;
  v_pedidos  INT;
  v_total    NUMERIC := 0;      -- valor inicial
BEGIN
  SELECT nombre INTO v_nombre FROM clientes WHERE id = p_cliente_id;

  IF NOT FOUND THEN              -- FOUND indica si la última consulta trajo filas
    RETURN 'Cliente no encontrado';
  END IF;

  SELECT COUNT(DISTINCT pe.id), COALESCE(SUM(d.cantidad * d.precio_unitario), 0)
    INTO v_pedidos, v_total
  FROM pedidos pe
  JOIN detalle_pedido d ON d.pedido_id = pe.id
  WHERE pe.cliente_id = p_cliente_id
    AND pe.estado <> 'cancelado';

  RETURN format('%s: %s pedido(s), total $%s', v_nombre, v_pedidos, v_total);
END;
$$;

SELECT resumen_cliente(1);   -- Ana López: 2 pedido(s), total $5096.00
SELECT resumen_cliente(99);  -- Cliente no encontrado

format() funciona como un printf: %s inserta texto, %I un identificador con comillas seguras y %L un literal con comillas seguras.

Condicionales

CREATE OR REPLACE FUNCTION nivel_cliente(total_gastado NUMERIC)
RETURNS TEXT
LANGUAGE plpgsql
IMMUTABLE
AS $$
BEGIN
  IF total_gastado >= 5000 THEN
    RETURN 'Oro';
  ELSIF total_gastado >= 1500 THEN
    RETURN 'Plata';
  ELSIF total_gastado > 0 THEN
    RETURN 'Bronce';
  ELSE
    RETURN 'Sin compras';
  END IF;
END;
$$;

-- También existe CASE dentro de PL/pgSQL:
-- CASE WHEN x > 0 THEN ... ELSE ... END CASE;

Ciclos

CREATE OR REPLACE FUNCTION reporte_stock_bajo(p_minimo INT)
RETURNS SETOF TEXT          -- devuelve varias filas de texto
LANGUAGE plpgsql
STABLE
AS $$
DECLARE
  r RECORD;                 -- una fila de cualquier forma
BEGIN
  FOR r IN SELECT nombre, stock FROM productos WHERE stock <= p_minimo ORDER BY stock LOOP
    RETURN NEXT format('%s: quedan %s', r.nombre, r.stock);
  END LOOP;
  RETURN;
END;
$$;

SELECT * FROM reporte_stock_bajo(10);

reporte_stock_bajo
Monitor 24 pulgadas: quedan 5
Teclado mecánico: quedan 10

Otros ciclos: LOOP ... EXIT WHEN cond; END LOOP;, WHILE cond LOOP ... END LOOP;, FOR i IN 1..10 LOOP ... END LOOP;, FOREACH x IN ARRAY arr LOOP ... END LOOP;.

Piensa en conjuntos, no en ciclos

Antes de escribir un FOR que recorre filas y hace un UPDATE por cada una, pregúntate si un solo UPDATE ... WHERE o un INSERT ... SELECT hace lo mismo. SQL procesa conjuntos de filas muchísimo más rápido que un ciclo fila por fila.

Errores: RAISE y EXCEPTION

-- Mensajes de distintos niveles
RAISE NOTICE 'Procesando pedido %', p_id;          -- informativo (se ve en psql)
RAISE WARNING 'Stock bajo en producto %', p_id;
RAISE EXCEPTION 'El pedido % no existe', p_id;     -- aborta y revierte

-- Con código y detalle personalizados
RAISE EXCEPTION 'Stock insuficiente'
  USING ERRCODE = 'P0001',
        DETAIL  = 'Solicitado: 10, disponible: 5',
        HINT    = 'Reduce la cantidad';

Capturar errores con un bloque EXCEPTION:

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

  RETURN v_id;
EXCEPTION
  WHEN unique_violation THEN
    RAISE EXCEPTION 'El correo % ya está registrado', p_email;
  WHEN not_null_violation THEN
    RAISE EXCEPTION 'Nombre y correo son obligatorios';
END;
$$;

Nombres de error comunes: unique_violation, foreign_key_violation, check_violation, not_null_violation, division_by_zero, no_data_found, others (cualquiera).

Volatilidad: IMMUTABLE, STABLE, VOLATILE

Le dice al optimizador qué tan "predecible" es tu función:

Categoría	Significado	Ejemplos
IMMUTABLE	Mismos argumentos → siempre el mismo resultado; no lee tablas	precio_con_iva, lower()
STABLE	Mismo resultado dentro de una consulta; puede leer tablas	productos_de_categoria, now()
VOLATILE (por defecto)	Puede cambiar en cada llamada o modifica datos	random(), funciones con INSERT/UPDATE

Importa porque:

• Solo funciones IMMUTABLE pueden usarse en índices de expresión y columnas generadas.
• STABLE/IMMUTABLE permiten al optimizador evaluarlas menos veces.
• Marcar mal una función (por ejemplo, IMMUTABLE una que lee tablas) produce resultados incorrectos cacheados.

Procedimientos (CALL)

Los procedimientos pueden hacer COMMIT y ROLLBACK internos: útiles para procesos por lotes largos.

CREATE OR REPLACE PROCEDURE archivar_pedidos_viejos(p_meses INT DEFAULT 12, p_lote INT DEFAULT 1000)
LANGUAGE plpgsql
AS $$
DECLARE
  v_movidos INT;
BEGIN
  LOOP
    WITH lote AS (
      DELETE FROM pedidos
      WHERE id IN (
        SELECT id FROM pedidos
        WHERE estado IN ('entregado', 'cancelado')
          AND fecha < CURRENT_DATE - make_interval(months => p_meses)
        LIMIT p_lote
      )
      RETURNING *
    )
    INSERT INTO pedidos_archivo SELECT * FROM lote;

    GET DIAGNOSTICS v_movidos = ROW_COUNT;
    RAISE NOTICE 'Archivados: %', v_movidos;
    EXIT WHEN v_movidos = 0;

    COMMIT;   -- confirma cada lote: no bloquea la tabla por horas
  END LOOP;
END;
$$;

CALL archivar_pedidos_viejos(12, 500);

(Supone que pedidos_archivo tiene la misma estructura y que el detalle se archiva aparte o tiene ON DELETE CASCADE.)

Triggers

En PostgreSQL un trigger tiene dos partes: una función que devuelve trigger y el trigger que la conecta con una tabla y un evento.

Variables disponibles dentro de la función:

Variable	Contenido
NEW	La fila nueva (INSERT, UPDATE)
OLD	La fila anterior (UPDATE, DELETE)
TG_OP	'INSERT', 'UPDATE', 'DELETE'
TG_TABLE_NAME	Nombre de la tabla
TG_WHEN	'BEFORE' o 'AFTER'

Trigger 1: updated_at automático

El trigger más común de cualquier proyecto:

-- Una sola función reutilizable para todas las tablas
CREATE OR REPLACE FUNCTION set_actualizado_en()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  NEW.actualizado_en := now();
  RETURN NEW;          -- en BEFORE, la fila que se guardará
END;
$$;

ALTER TABLE productos ADD COLUMN actualizado_en TIMESTAMPTZ NOT NULL DEFAULT now();

CREATE TRIGGER trg_productos_actualizado_en
BEFORE UPDATE ON productos
FOR EACH ROW
EXECUTE FUNCTION set_actualizado_en();

-- Reutilízala en otras tablas
-- CREATE TRIGGER trg_clientes_actualizado_en BEFORE UPDATE ON clientes
--   FOR EACH ROW EXECUTE FUNCTION set_actualizado_en();

Trigger 2: auditoría genérica con JSONB

Una sola tabla y una sola función para auditar cualquier tabla:

CREATE TABLE auditoria (
  id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tabla      TEXT NOT NULL,
  operacion  TEXT NOT NULL,
  registro_id TEXT,
  antes      JSONB,
  despues    JSONB,
  usuario    TEXT NOT NULL DEFAULT current_user,
  creado_en  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE OR REPLACE FUNCTION auditar()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  INSERT INTO auditoria (tabla, operacion, registro_id, antes, despues)
  VALUES (
    TG_TABLE_NAME,
    TG_OP,
    COALESCE(NEW.id, OLD.id)::text,
    CASE WHEN TG_OP IN ('UPDATE', 'DELETE') THEN to_jsonb(OLD) END,
    CASE WHEN TG_OP IN ('INSERT', 'UPDATE') THEN to_jsonb(NEW) END
  );
  RETURN COALESCE(NEW, OLD);
END;
$$;

CREATE TRIGGER trg_productos_auditoria
AFTER INSERT OR UPDATE OR DELETE ON productos
FOR EACH ROW EXECUTE FUNCTION auditar();

UPDATE productos SET precio = 949.00 WHERE id = 1;

SELECT operacion, antes ->> 'precio' AS precio_antes, despues ->> 'precio' AS precio_despues
FROM auditoria ORDER BY id DESC LIMIT 1;

operacion	precio_antes	precio_despues
UPDATE	899.00	949.00

Trigger 3: solo cuando cambia algo concreto

CREATE TRIGGER trg_productos_precio
AFTER UPDATE OF precio ON productos                    -- solo si se tocó la columna precio
FOR EACH ROW
WHEN (OLD.precio IS DISTINCT FROM NEW.precio)          -- y el valor realmente cambió
EXECUTE FUNCTION auditar();

IS DISTINCT FROM compara tratando NULL como un valor (a diferencia de <>).

Administrar

-- psql: \df (funciones), \d productos (muestra triggers de la tabla)
DROP TRIGGER IF EXISTS trg_productos_auditoria ON productos;
DROP FUNCTION IF EXISTS auditar();
ALTER TABLE productos DISABLE TRIGGER trg_productos_auditoria;   -- desactivar temporalmente
ALTER TABLE productos ENABLE TRIGGER trg_productos_auditoria;

Seguridad: SECURITY DEFINER

Por defecto una función se ejecuta con los permisos de quien la llama (SECURITY INVOKER). Con SECURITY DEFINER se ejecuta con los permisos de su dueño: útil para permitir una acción concreta sin dar acceso a la tabla completa.

CREATE OR REPLACE FUNCTION app.registrar_contacto(p_email TEXT, p_mensaje TEXT)
RETURNS void
LANGUAGE sql
SECURITY DEFINER
SET search_path = app, pg_temp        -- ¡obligatorio por seguridad!
AS $$
  INSERT INTO app.contactos (email, mensaje) VALUES (p_email, p_mensaje);
$$;

REVOKE ALL ON FUNCTION app.registrar_contacto(TEXT, TEXT) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION app.registrar_contacto(TEXT, TEXT) TO anonimo;

SECURITY DEFINER sin search_path

Una función SECURITY DEFINER sin SET search_path puede ser engañada: un atacante crea una tabla o función con el mismo nombre en un esquema que aparece antes en su search_path, y tu función (con permisos elevados) la usa. Fija siempre el search_path y califica los nombres.

¿Cuándo poner lógica en la base de datos?

Buenos candidatos: updated_at, auditoría, validaciones que deben cumplirse sin importar qué app escriba, funciones de reporte reutilizables, operaciones atómicas complejas expuestas como RPC (Supabase). Evita esconder reglas de negocio cambiantes (precios, promociones) en triggers: son difíciles de probar y de descubrir para el equipo.

Ejercicios

1. Crea una función SQL total_pedido(p_pedido_id INT) que devuelva el total de un pedido.
2. Crea una función PL/pgSQL aplicar_descuento(p_producto_id INT, p_porcentaje NUMERIC) que lance una excepción si el porcentaje no está entre 1 y 90, actualice el precio y devuelva el precio nuevo.
3. Crea un trigger que convierta a minúsculas y quite espacios del email de clientes antes de insertar o actualizar.
4. ¿Qué volatilidad le corresponde a cada una? a) Función que calcula la edad a partir de una fecha de nacimiento usando CURRENT_DATE. b) Función que convierte pesos a centavos. c) Función que inserta un registro en logs.

Ver solución

-- 1
CREATE OR REPLACE FUNCTION total_pedido(p_pedido_id INT)
RETURNS NUMERIC LANGUAGE sql STABLE AS $$
  SELECT COALESCE(SUM(cantidad * precio_unitario), 0)
  FROM detalle_pedido WHERE pedido_id = p_pedido_id;
$$;

SELECT id, total_pedido(id) FROM pedidos;

-- 2
CREATE OR REPLACE FUNCTION aplicar_descuento(p_producto_id INT, p_porcentaje NUMERIC)
RETURNS NUMERIC LANGUAGE plpgsql AS $$
DECLARE
  v_precio NUMERIC;
BEGIN
  IF p_porcentaje NOT BETWEEN 1 AND 90 THEN
    RAISE EXCEPTION 'Porcentaje inválido: % (debe estar entre 1 y 90)', p_porcentaje;
  END IF;

  UPDATE productos
  SET precio = ROUND(precio * (1 - p_porcentaje / 100), 2)
  WHERE id = p_producto_id
  RETURNING precio INTO v_precio;

  IF NOT FOUND THEN
    RAISE EXCEPTION 'Producto % no existe', p_producto_id;
  END IF;

  RETURN v_precio;
END;
$$;

SELECT aplicar_descuento(4, 10);   -- 314.10

-- 3
CREATE OR REPLACE FUNCTION normalizar_email()
RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  NEW.email := lower(trim(NEW.email));
  RETURN NEW;
END;
$$;

CREATE TRIGGER trg_clientes_email
BEFORE INSERT OR UPDATE OF email ON clientes
FOR EACH ROW EXECUTE FUNCTION normalizar_email();

4. a) STABLE (depende de CURRENT_DATE, que cambia de un día a otro pero es fijo dentro de una consulta). b) IMMUTABLE. c) VOLATILE (modifica datos).

Resumen

• Las funciones se crean con CREATE OR REPLACE FUNCTION ... AS $$ ... $$ en sql o plpgsql.
• RETURNS TABLE / SETOF crean funciones que se consultan como tablas ("vistas con parámetros").
• PL/pgSQL ofrece DECLARE, SELECT INTO, IF, FOR, RAISE y bloques EXCEPTION.
• Declara la volatilidad correcta: IMMUTABLE, STABLE o VOLATILE.
• Los procedimientos (CALL) pueden hacer COMMIT por lotes.
• Triggers: función que devuelve trigger + CREATE TRIGGER; casos estrella: updated_at y auditoría con JSONB.