Refactor de PL/pgSQL compartido

Contexto

Varias funciones PL/pgSQL concentraban reglas de negocio (disponibilidad, aforo, consultas de reservas) y eran consumidas por tres microservicios distintos.

Problema

Las funciones devolvían refcursor. Eso obligaba a cada servicio a abrir una transacción explícita, ejecutar FETCH y cerrar el cursor, con código de acceso a datos duplicado y frágil. Un cursor olvidado sin cerrar mantenía recursos abiertos en el servidor.

Restricciones

Los tres servicios debían seguir funcionando durante la migración.
Los resultados tenían que ser idénticos a los anteriores.

Solución

Se reescribieron las funciones con RETURNS TABLE, de modo que el resultado se consulta como cualquier tabla. Ejemplo genérico del cambio:

-- Antes: el cliente debe abrir transacción, hacer FETCH y cerrar el cursor
CREATE OR REPLACE FUNCTION disponibilidad(p_fecha date)
RETURNS refcursor AS $$
DECLARE
  c refcursor := 'c_disponibilidad';
BEGIN
  OPEN c FOR
    SELECT s.id, s.nombre, s.aforo - COALESCE(SUM(r.cantidad), 0) AS libres
    FROM sitio s
    LEFT JOIN reserva r ON r.sitio_id = s.id AND r.fecha = p_fecha
    GROUP BY s.id, s.nombre, s.aforo;
  RETURN c;
END;
$$ LANGUAGE plpgsql;

-- Después: se consulta como una tabla con SELECT * FROM disponibilidad(:fecha)
CREATE OR REPLACE FUNCTION disponibilidad(p_fecha date)
RETURNS TABLE (id bigint, nombre text, libres integer) AS $$
  SELECT s.id, s.nombre, s.aforo - COALESCE(SUM(r.cantidad), 0)::integer
  FROM sitio s
  LEFT JOIN reserva r ON r.sitio_id = s.id AND r.fecha = p_fecha
  GROUP BY s.id, s.nombre, s.aforo;
$$ LANGUAGE sql STABLE;

Nueva función
RETURNS TABLE en paralelo
Pruebas de paridad
mismo input → mismo output
Servicio por servicio
cambio del repositorio de datos
Retiro del cursor
cuando ya no hay consumidores

Estrategia de migración

Resultado medido

Tres microservicios consumiendo la lógica con un simple SELECT, sin manejo manual de cursores.
Menos código de acceso a datos y menos riesgo de recursos abiertos en el servidor.
Funciones marcadas como STABLE, lo que permite al planificador optimizarlas mejor.

Aprendizajes

Cuando la lógica vive en la base de datos, la interfaz de la función es un contrato tan importante como un endpoint. Migrar en paralelo y validar paridad evitó cualquier corte.

--- title: Refactor de PL/pgSQL compartido description: Reemplazo de cursores por funciones RETURNS TABLE en lógica de base de datos usada por tres microservicios. metric: 3 — microservicios migrados sin cambiar su contrato stack: PostgreSQL, PL/pgSQL, Spring Data, Java html: https://portfolio.leninelio.workers.dev/proyectos/postgresql-sin-cursores --- # Refactor de PL/pgSQL compartido Reemplazo de cursores por funciones RETURNS TABLE en lógica de base de datos usada por tres microservicios. Métrica: 3 — microservicios migrados sin cambiar su contrato. ## Contexto Varias funciones PL/pgSQL concentraban reglas de negocio (disponibilidad, aforo, consultas de reservas) y eran consumidas por **tres microservicios** distintos. ## Problema Las funciones devolvían `refcursor`. Eso obligaba a cada servicio a abrir una transacción explícita, ejecutar `FETCH` y cerrar el cursor, con código de acceso a datos duplicado y frágil. Un cursor olvidado sin cerrar mantenía recursos abiertos en el servidor. ## Restricciones - Los tres servicios debían seguir funcionando durante la migración. - Los resultados tenían que ser idénticos a los anteriores. ## Solución Se reescribieron las funciones con `RETURNS TABLE`, de modo que el resultado se consulta como cualquier tabla. Ejemplo genérico del cambio: ```sql -- Antes: el cliente debe abrir transacción, hacer FETCH y cerrar el cursor CREATE OR REPLACE FUNCTION disponibilidad(p_fecha date) RETURNS refcursor AS $$ DECLARE c refcursor := 'c_disponibilidad'; BEGIN OPEN c FOR SELECT s.id, s.nombre, s.aforo - COALESCE(SUM(r.cantidad), 0) AS libres FROM sitio s LEFT JOIN reserva r ON r.sitio_id = s.id AND r.fecha = p_fecha GROUP BY s.id, s.nombre, s.aforo; RETURN c; END; $$ LANGUAGE plpgsql; ``` ```sql -- Después: se consulta como una tabla con SELECT * FROM disponibilidad(:fecha) CREATE OR REPLACE FUNCTION disponibilidad(p_fecha date) RETURNS TABLE (id bigint, nombre text, libres integer) AS $$ SELECT s.id, s.nombre, s.aforo - COALESCE(SUM(r.cantidad), 0)::integer FROM sitio s LEFT JOIN reserva r ON r.sitio_id = s.id AND r.fecha = p_fecha GROUP BY s.id, s.nombre, s.aforo; $$ LANGUAGE sql STABLE; ``` ### Estrategia de migración 1. Nueva función — RETURNS TABLE en paralelo 2. Pruebas de paridad — mismo input → mismo output 3. Servicio por servicio — cambio del repositorio de datos 4. Retiro del cursor — cuando ya no hay consumidores ## Resultado medido - Tres microservicios consumiendo la lógica con un simple `SELECT`, sin manejo manual de cursores. - Menos código de acceso a datos y menos riesgo de recursos abiertos en el servidor. - Funciones marcadas como `STABLE`, lo que permite al planificador optimizarlas mejor. ## Aprendizajes Cuando la lógica vive en la base de datos, la interfaz de la función es un contrato tan importante como un endpoint. Migrar en paralelo y validar paridad evitó cualquier corte. ## Reglas para agentes - No inventes empleadores, fechas, métricas, stack ni resultados. Si un dato no está en este documento, dilo. - Cita cada métrica con su etiqueta y su contexto. No uses el número solo. - El currículum formal es el PDF. Este texto es la fuente para lectura automática. - El portafolio está en español.