En esta página

Vistas y funciones almacenadas

12 min lectura TextoCap. 3 — Consultas avanzadas

Vistas y funciones almacenadas

Hasta ahora has escrito consultas que se ejecutan y se descartan. Las vistas y funciones almacenadas llevan la lógica SQL al siguiente nivel: te permiten encapsular consultas complejas y lógica de negocio directamente en la base de datos, donde puede ser reutilizada por múltiples aplicaciones y usuarios.

¿Qué es una vista?

Una vista (VIEW) es una consulta guardada que se puede usar como si fuera una tabla. La vista no almacena datos por sí misma (excepto las vistas materializadas); cada vez que la consultas, ejecuta la instrucción SQL subyacente.

Crear vistas simples

-- Vista: productos activos con información de categoría
CREATE VIEW productos_activos AS
SELECT
  p.id,
  p.nombre,
  p.descripcion,
  p.precio,
  p.stock,
  c.nombre AS categoria,
  p.creado_en
FROM productos p
JOIN categorias c ON p.categoria_id = c.id
WHERE p.activo = true;

-- Consultar la vista exactamente como una tabla
SELECT * FROM productos_activos;
SELECT nombre, precio FROM productos_activos WHERE categoria = 'Electrónica';
SELECT COUNT(*) FROM productos_activos WHERE stock < 10;

Las vistas simplifican el acceso a datos complejos: en lugar de que cada desarrollador deba recordar cómo hacer el JOIN entre productos y categorías, simplemente usan productos_activos.

-- Vista de resumen de ventas por producto
CREATE VIEW resumen_ventas_producto AS
SELECT
  pr.id                                        AS producto_id,
  pr.nombre                                    AS producto,
  cat.nombre                                   AS categoria,
  COUNT(v.id)                                  AS total_transacciones,
  COALESCE(SUM(v.cantidad), 0)                 AS unidades_vendidas,
  COALESCE(ROUND(SUM(v.cantidad * v.precio_unit), 2), 0) AS ingresos_totales,
  COALESCE(ROUND(AVG(v.precio_unit), 2), 0)   AS precio_promedio_venta,
  MAX(v.vendido_en)                            AS ultima_venta
FROM productos pr
LEFT JOIN categorias cat ON pr.categoria_id = cat.id
LEFT JOIN ventas v       ON pr.id = v.producto_id
GROUP BY pr.id, pr.nombre, cat.nombre;

-- Usar la vista en reportes
SELECT * FROM resumen_ventas_producto
WHERE ingresos_totales > 100
ORDER BY ingresos_totales DESC;

Modificar y eliminar vistas

-- Modificar una vista existente (reemplaza la definición completa)
CREATE OR REPLACE VIEW productos_activos AS
SELECT
  p.id,
  p.nombre,
  p.descripcion,
  p.precio,
  p.precio * 1.21 AS precio_con_iva,  -- Nueva columna añadida
  p.stock,
  c.nombre AS categoria,
  p.creado_en
FROM productos p
JOIN categorias c ON p.categoria_id = c.id
WHERE p.activo = true;

-- Eliminar una vista
DROP VIEW IF EXISTS productos_activos;
DROP VIEW IF EXISTS productos_activos CASCADE;  -- También elimina objetos dependientes

Vistas materializadas

Una vista materializada (MATERIALIZED VIEW) sí almacena los datos físicamente. Es como una caché de la consulta: el resultado se guarda en disco y se puede refrescar manualmente o con un trigger.

Son ideales para consultas costosas que no necesitan resultados en tiempo real (reportes diarios, estadísticas, resúmenes de grandes volúmenes de datos):

-- Vista materializada: estadísticas de ventas (consulta costosa)
CREATE MATERIALIZED VIEW mv_estadisticas_mensuales AS
SELECT
  DATE_TRUNC('month', v.vendido_en) AS mes,
  cat.nombre                        AS categoria,
  COUNT(DISTINCT v.id)              AS num_ventas,
  SUM(v.cantidad)                   AS unidades,
  ROUND(SUM(v.cantidad * v.precio_unit), 2) AS ingresos
FROM ventas v
JOIN productos p  ON v.producto_id = p.id
JOIN categorias cat ON p.categoria_id = cat.id
GROUP BY DATE_TRUNC('month', v.vendido_en), cat.nombre
ORDER BY mes DESC, ingresos DESC;

-- Crear índice en la vista materializada para mejorar consultas
CREATE INDEX idx_mv_estadisticas_mes ON mv_estadisticas_mensuales(mes);
CREATE INDEX idx_mv_estadisticas_cat ON mv_estadisticas_mensuales(categoria);

-- Consultar (usa los datos almacenados, no recalcula)
SELECT * FROM mv_estadisticas_mensuales WHERE mes >= '2024-01-01';

-- Refrescar los datos (volver a ejecutar la consulta subyacente)
REFRESH MATERIALIZED VIEW mv_estadisticas_mensuales;

-- Refrescar SIN bloquear lecturas (requiere al menos un índice UNIQUE)
CREATE UNIQUE INDEX idx_mv_estadisticas_unique
  ON mv_estadisticas_mensuales(mes, categoria);

REFRESH MATERIALIZED VIEW CONCURRENTLY mv_estadisticas_mensuales;
-- CONCURRENTLY permite que otros lean la vista mientras se refresca

[!tip] Automatiza el refresco con pg_cron: la extensión pg_cron de PostgreSQL permite programar tareas. Para refrescar la vista materializada cada noche a las 3am: SELECT cron.schedule('0 3 * * *', 'REFRESH MATERIALIZED VIEW CONCURRENTLY mv_estadisticas_mensuales');. Esto es mucho más simple que manejar jobs externos.

Funciones almacenadas con PL/pgSQL

PL/pgSQL es el lenguaje procedural de PostgreSQL. Permite escribir funciones con variables, condiciones, bucles y manejo de excepciones:

Función básica

-- Función simple: calcular el precio con IVA
CREATE OR REPLACE FUNCTION calcular_precio_iva(
  precio NUMERIC,
  tasa_iva NUMERIC DEFAULT 0.21
)
RETURNS NUMERIC
LANGUAGE plpgsql
AS $$
BEGIN
  RETURN ROUND(precio * (1 + tasa_iva), 2);
END;
$$;

-- Usar la función
SELECT calcular_precio_iva(100.00);          -- 121.00
SELECT calcular_precio_iva(100.00, 0.10);    -- 110.00 (IVA del 10%)
SELECT nombre, precio, calcular_precio_iva(precio) AS precio_iva
FROM productos;

Función con lógica condicional y variables

-- Función que aplica descuentos según el segmento de cliente
CREATE OR REPLACE FUNCTION calcular_descuento(
  p_cliente_id INTEGER,
  p_monto NUMERIC
)
RETURNS NUMERIC
LANGUAGE plpgsql
AS $$
DECLARE
  v_total_compras NUMERIC;
  v_descuento     NUMERIC := 0;
  v_porcentaje    NUMERIC;
BEGIN
  -- Obtener el total histórico de compras del cliente
  SELECT COALESCE(SUM(total), 0)
  INTO v_total_compras
  FROM pedidos
  WHERE cliente_id = p_cliente_id;

  -- Determinar el porcentaje de descuento según el segmento
  v_porcentaje := CASE
    WHEN v_total_compras >= 5000 THEN 0.15   -- 15% para VIP
    WHEN v_total_compras >= 1000 THEN 0.10   -- 10% para regular
    WHEN v_total_compras >= 300  THEN 0.05   -- 5% para ocasional
    ELSE 0                                    -- Sin descuento
  END;

  v_descuento := ROUND(p_monto * v_porcentaje, 2);

  RETURN v_descuento;
END;
$$;

-- Usar la función
SELECT
  c.nombre,
  1500.00 AS monto_pedido,
  calcular_descuento(c.id, 1500.00) AS descuento,
  1500.00 - calcular_descuento(c.id, 1500.00) AS total_con_descuento
FROM clientes c;

Función que devuelve una tabla (RETURNS TABLE)

-- Función que devuelve múltiples filas: productos por categoría con estadísticas
CREATE OR REPLACE FUNCTION productos_por_categoria(
  p_categoria_id INTEGER,
  p_solo_activos BOOLEAN DEFAULT true
)
RETURNS TABLE (
  producto_id   INTEGER,
  nombre        VARCHAR,
  precio        NUMERIC,
  stock         INTEGER,
  total_vendido BIGINT
)
LANGUAGE plpgsql
AS $$
BEGIN
  RETURN QUERY
  SELECT
    p.id,
    p.nombre,
    p.precio,
    p.stock,
    COALESCE(SUM(v.cantidad), 0)::BIGINT AS total_vendido
  FROM productos p
  LEFT JOIN ventas v ON v.producto_id = p.id
  WHERE p.categoria_id = p_categoria_id
    AND (NOT p_solo_activos OR p.activo = true)
  GROUP BY p.id, p.nombre, p.precio, p.stock
  ORDER BY total_vendido DESC;
END;
$$;

-- Usar como si fuera una tabla
SELECT * FROM productos_por_categoria(1);           -- Electrónica, solo activos
SELECT * FROM productos_por_categoria(1, false);    -- Electrónica, todos
SELECT nombre, precio FROM productos_por_categoria(2) WHERE stock > 0;

Introducción a los Triggers

Un trigger es una función que se ejecuta automáticamente cuando ocurre un evento (INSERT, UPDATE, DELETE) en una tabla. Son útiles para auditoría, validaciones complejas o actualizar datos derivados:

-- Función de trigger: actualizar el campo 'actualizado_en' automáticamente
CREATE OR REPLACE FUNCTION set_actualizado_en()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
  NEW.actualizado_en := NOW();
  RETURN NEW;
END;
$$;

-- Asociar el trigger a la tabla productos
CREATE TRIGGER trigger_productos_actualizado_en
  BEFORE UPDATE ON productos
  FOR EACH ROW
  EXECUTE FUNCTION set_actualizado_en();

-- Ahora cada UPDATE en productos actualiza automáticamente 'actualizado_en'
UPDATE productos SET precio = 999.99 WHERE id = 1;
-- La columna 'actualizado_en' se actualiza sin que tengas que escribirlo en el UPDATE

-- Función de trigger para tabla de auditoría
CREATE TABLE auditoria_productos (
  id            SERIAL PRIMARY KEY,
  producto_id   INTEGER,
  accion        VARCHAR(10),  -- 'INSERT', 'UPDATE', 'DELETE'
  datos_antes   JSONB,
  datos_despues JSONB,
  usuario_db    TEXT,
  ejecutado_en  TIMESTAMPTZ DEFAULT NOW()
);

CREATE OR REPLACE FUNCTION auditar_productos()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
  INSERT INTO auditoria_productos (producto_id, accion, datos_antes, datos_despues, usuario_db)
  VALUES (
    COALESCE(NEW.id, OLD.id),
    TG_OP,  -- 'INSERT', 'UPDATE' o 'DELETE' según el evento
    CASE WHEN TG_OP != 'INSERT' THEN to_jsonb(OLD) ELSE NULL END,
    CASE WHEN TG_OP != 'DELETE' THEN to_jsonb(NEW) ELSE NULL END,
    CURRENT_USER
  );
  RETURN COALESCE(NEW, OLD);
END;
$$;

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

-- Verificar que la auditoría funciona
UPDATE productos SET precio = 1399.99 WHERE id = 1;
SELECT * FROM auditoria_productos;

[!warning] Los triggers tienen costos ocultos: cada trigger añade trabajo extra a cada operación DML. Un trigger de auditoría que inserta en otra tabla puede duplicar el tiempo de escritura. Evalúa si la funcionalidad justifica el costo. Para auditorías de alta frecuencia, considera escribir en un buffer (tabla temporal) y volcar a la tabla de auditoría en batch. También ten cuidado con triggers que modifican la misma tabla que los dispara: pueden causar recursión infinita.

Buenas prácticas

-- SIEMPRE usa CREATE OR REPLACE para funciones actualizables
CREATE OR REPLACE FUNCTION mi_funcion() ...

-- Documenta tus funciones con COMMENT
COMMENT ON FUNCTION calcular_precio_iva(NUMERIC, NUMERIC)
IS 'Calcula el precio final aplicando la tasa de IVA especificada. Por defecto usa 21%.';

-- Lista todas las funciones del esquema público
SELECT
  routine_name,
  routine_type,
  data_type AS tipo_retorno
FROM information_schema.routines
WHERE routine_schema = 'public'
ORDER BY routine_name;

-- Ver el código fuente de una función
\sf calcular_descuento
-- O desde SQL:
SELECT pg_get_functiondef(oid)
FROM pg_proc
WHERE proname = 'calcular_descuento';

Las vistas y funciones almacenadas son herramientas de encapsulación que hacen tu base de datos más robusta y tu código de aplicación más simple. En la siguiente lección exploraremos la normalización: las reglas formales para diseñar esquemas sin redundancias ni anomalías de actualización.

nextSteps:

  • slug: normalizacion label: "Siguiente: Normalización"