Vistas y Funciones

Las vistas son consultas guardadas que se comportan como tablas virtuales. Simplifican consultas complejas, añaden una capa de seguridad (ocultando columnas sensibles), y centralizan la lógica de negocio. Las Materialized Views guardan el resultado en disco para consultas muy costosas.

-- Vista básica: encapsular una consulta compleja
CREATE VIEW resumen_pedidos AS
SELECT
  u.id AS user_id,
  u.nombre,
  u.email,
  COUNT(o.id) AS total_pedidos,
  COALESCE(SUM(o.total), 0) AS gasto_total,
  MAX(o.fecha) AS ultimo_pedido
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.nombre, u.email;

-- Usar la vista como si fuera una tabla
SELECT * FROM resumen_pedidos
WHERE gasto_total > 1000
ORDER BY gasto_total DESC;

-- Reemplazar vista existente
CREATE OR REPLACE VIEW resumen_pedidos AS
SELECT u.id AS user_id, u.nombre, u.email,
       COUNT(o.id) AS total_pedidos,
       COALESCE(SUM(o.total), 0) AS gasto_total,
       MAX(o.fecha) AS ultimo_pedido,
       u.activo  -- columna nueva agregada
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.nombre, u.email, u.activo;

-- MATERIALIZED VIEW: guarda el resultado en disco
-- Útil para consultas muy costosas que se consultan frecuentemente
CREATE MATERIALIZED VIEW stats_diarias AS
SELECT
  DATE(fecha) AS dia,
  COUNT(*) AS num_pedidos,
  SUM(total) AS ingresos,
  AVG(total) AS ticket_promedio
FROM orders
GROUP BY DATE(fecha);

-- Refrescar la materialized view (necesario cuando cambian los datos)
REFRESH MATERIALIZED VIEW stats_diarias;
-- Refrescar sin bloquear lecturas (requiere UNIQUE index)
REFRESH MATERIALIZED VIEW CONCURRENTLY stats_diarias;

-- Función PL/pgSQL con parámetros
CREATE OR REPLACE FUNCTION obtener_top_clientes(
  p_limite INTEGER DEFAULT 10,
  p_desde DATE DEFAULT '2024-01-01'
)
RETURNS TABLE(nombre TEXT, email TEXT, gasto DECIMAL)
LANGUAGE plpgsql AS $$
BEGIN
  RETURN QUERY
  SELECT u.nombre::TEXT, u.email::TEXT,
         COALESCE(SUM(o.total), 0) AS gasto
  FROM users u
  LEFT JOIN orders o ON o.user_id = u.id
    AND o.fecha >= p_desde
  GROUP BY u.nombre, u.email
  ORDER BY gasto DESC
  LIMIT p_limite;
END;
$$;

-- Llamar la función
SELECT * FROM obtener_top_clientes(5, '2024-06-01');