PostgreSQL
Funciones y procedimientos almacenados en SQL y PL/pgSQL, contratos de tipos, volatilidad, privilegios, transacciones y límites de la lógica dentro de la base.
Mover lógica a PostgreSQL puede reducir viajes de red y proteger operaciones atómicas, pero también crea una API dentro de la base. Esa API necesita versionado, pruebas, seguridad y observabilidad igual que el backend.
PostgreSQL permite funciones y procedimientos en SQL, PL/pgSQL y lenguajes adicionales instalados.
FUNCTION
→ devuelve un valor o conjunto
→ puede usarse en expresiones y SELECT
PROCEDURE
→ se invoca con CALL
→ puede controlar transacciones en contextos permitidosNo toda lógica cercana a los datos debe vivir en el motor. La decisión depende de atomicidad, reutilización, frecuencia de cambio y operación.
CREATE FUNCTION app.order_total(p_order_id bigint)
RETURNS numeric
LANGUAGE sql
STABLE
PARALLEL SAFE
AS $$
SELECT coalesce(sum(quantity * unit_price), 0::numeric)
FROM app.order_items
WHERE order_id = p_order_id
$$;Las funciones SQL simples pueden inlinearse en casos compatibles, permitiendo optimización conjunta.
CREATE FUNCTION app.reserve_stock(
p_branch_id bigint,
p_product_id bigint,
p_quantity numeric
) RETURNS numeric
LANGUAGE plpgsql
SECURITY INVOKER
AS $$
DECLARE
remaining numeric;
BEGIN
UPDATE app.inventory
SET quantity = quantity - p_quantity
WHERE branch_id = p_branch_id
AND product_id = p_product_id
AND quantity >= p_quantity
RETURNING quantity INTO remaining;
IF NOT FOUND THEN
RAISE EXCEPTION USING
ERRCODE = 'P0001',
MESSAGE = 'insufficient stock';
END IF;
RETURN remaining;
END;
$$;La actualización condicional evita una carrera de select-then-update.
Puede retornar:
TABLE(...).SETOF type.void.Para APIs de datos, define nombres y tipos estables:
RETURNS TABLE(order_id bigint, total numeric)Cambiar el tipo de retorno puede romper dependencias y drivers.
Mismo resultado para mismos argumentos, independiente de tabla, tiempo o configuración.
Puede leer datos, pero no cambia dentro de una statement.
Puede cambiar en cada llamada o escribir.
Clasificar mal una función puede producir resultados incorrectos o índices inválidos. No marques IMMUTABLE solo para permitir un expression index.
RETURNS NULL ON NULL INPUTo STRICT evita ejecutar la función cuando algún argumento es NULL. Solo úsalo si esa semántica es correcta.
Funciones set-returning pueden declarar estimaciones:
COST 100
ROWS 1000El planner las utiliza. Valores incorrectos afectan joins y planes.
Es el default: usa privilegios del caller. Es más fácil de razonar y suele ser preferible.
Ejecuta con privilegios del owner:
CREATE FUNCTION security.get_profile(...)
RETURNS ...
LANGUAGE sql
SECURITY DEFINER
SET search_path = pg_catalog, security
AS $$ ... $$;Controles obligatorios:
search_path seguro.PUBLIC cuando corresponda.Riesgo principal: privilege escalation por resolución de nombres o SQL dinámico.
Identifiers no se parametrizan con $1:
EXECUTE format('SELECT count(*) FROM %I.%I', schema_name, table_name)
INTO result;Valores:
EXECUTE 'SELECT ... WHERE id = $1'
USING p_id;Usa %I para identifiers, %L para literals solo cuando realmente sea necesario y USING para valores.
PL/pgSQL permite:
BEGIN
...
EXCEPTION
WHEN unique_violation THEN
...
END;Un bloque EXCEPTION crea comportamiento similar a subtransaction y añade coste. No captures WHEN OTHERS para ocultar fallos.
Conserva SQLSTATE y contexto cuando re-lances.
CREATE PROCEDURE maintenance.process_batch()
LANGUAGE plpgsql
AS $$
BEGIN
...
COMMIT;
...
END;
$$;El transaction control depende de cómo se invoque y del contexto. Procedures son útiles para mantenimiento por lotes, pero no reemplazan un job runner con retries, scheduling y observabilidad.
Evita:
FOR cada fila
→ ejecutar UPDATEcuando una sola sentencia puede actualizar el conjunto. PL/pgSQL debe orquestar SQL set-based, no recrear un motor fila por fila.
Las funciones dependen de tipos, tablas y otras funciones. Estrategias:
CREATE OR REPLACE solo para cambios compatibles.Overloading puede volver ambiguas llamadas con parámetros unknown o NULL.
Prueba:
Herramientas como pgTAP pueden ayudar, pero tests de integración desde el driver siguen siendo importantes.
pg_stat_user_functions puede registrar llamadas si track_functions está configurado. También usa logs y tracing de la operación externa. Una función puede ocultar queries internas en métricas agregadas si no se investiga adecuadamente.
SECURITY DEFINER inseguro.SECURITY DEFINER exige search path seguro?EXECUTE ... USING.Triggers: usos, riesgos y alternativas automatiza efectos dentro de la transacción sin volverlos invisibles para el diseño.