Procedimientos almacenados de PostgreSQL
23 pasos en 6 series de SQL.
Todo lo demás del corpus SQL corre sobre SQLite; este tour cruza a PostgreSQL por lo único que SQLite deliberadamente no hace — código que vive en la base de datos. Funciones que el planificador puede plegar dentro de las consultas, bloques PL/pgSQL con variables y control de flujo, procedimientos que confirman sus propios lotes, y triggers que hacen que una tabla imponga sus propias reglas: las herramientas de los equipos que tratan la base como participante y no como archivero.
El tour va de CREATE FUNCTION al lenguaje PL/pgSQL, y de ahí a procedimientos, código dirigido por consultas y manejo de errores: promesas de volatilidad, declaraciones con %TYPE, SELECT INTO con FOUND, RETURN QUERY, SQL dinámico con format(), RAISE en todos sus volúmenes y funciones de trigger leyendo NEW y OLD. El bis entrega una bitácora de auditoría que la aplicación no puede olvidar escribir, un procedimiento de reposición por lotes y un reporte servido de dos maneras desde una misma función. Cada fragmento se ejecuta contra un PostgreSQL real.
Funciones
- CREATE FUNCTIONUna expresión guardada que el planificador puede llamar donde quepa un valor.
- Defaults y argumentos con nombreUna función de impuestos llamada de tres maneras: por defecto, por posición y por nombre.
- Funciones de tablaRETURNS TABLE convierte la función en algo que puede ir en FROM.
- IMMUTABLE y STABLELa volatilidad es una promesa al planificador sobre las llamadas repetidas.
Bloques PL/pgSQL
- Bloques DOUn bloque PL/pgSQL anónimo: declarar, consultar y reportar, todo en línea.
- Variables y %TYPEDeclara tipos a mano, o toma prestado el de una columna para que nunca diverjan.
- IF y ELSIFUn veredicto de inventario elegido recorriendo condiciones hasta la primera verdadera.
- BuclesFOR cuenta hacia arriba o hacia abajo sobre un rango; EXIT WHEN sale antes.
Procedimientos y transacciones
- CREATE PROCEDUREA un procedimiento se lo llama por sus efectos — aquí, cancelar un pedido.
- Parámetros INOUTUn procedimiento de pago que devuelve el recibo por su lista de parámetros.
- COMMIT dentro de un procedimientoSolo los procedimientos pueden confirmar a mitad de camino, soltando el trabajo por lotes.
- OR REPLACE y DROPCiclo de vida de una función: crearla, cambiarle el cuerpo en el lugar, tirarla por firma.
Consultas en el código
- SELECT INTO y FOUNDUna fila consultada aterriza en variables, y FOUND dice si algo cayó.
- RETURN QUERYUna función PL/pgSQL que devuelve un conjunto de resultados entero.
- FOR sobre filas de consultaVisita cada pedido como un record: la variable del bucle sostiene una fila a la vez.
- EXECUTE y format()SQL armado en tiempo de ejecución, con format() haciendo el quoting que un pegote arruinaría.
Errores y triggers
- Niveles de RAISEUn mensaje, varios volúmenes: DEBUG calla, NOTICE informa, WARNING insiste.
- ASSERTUn cable trampa para invariantes: falla fuerte apenas pasa lo imposible.
- Bloques EXCEPTIONDivisión por cero, atrapada por nombre de condición; el trabajo del bloque se revierte primero.
- Funciones de triggerUn piso de inventario impuesto por la tabla misma: el trigger corre en cada update.
Bis
- audit_triggers.sqlUna auditoría de precios que la aplicación no puede olvidar: la escribe un trigger.
- restock_procedure.sqlUna reposición nocturna: encontrar estantes bajos, rellenar y confirmar sobre la marcha.
- report_function.sqlUna función de tabla alimenta una consulta y un reporte narrado con los mismos números.