Clase Teórico – Práctica Funciones, Procedimientos y Triggers
-- ====================================
-- FUNCIONES
-- ====================================
• Permiten encapsular lógica que devuelve un valor.
• Se invocan con SELECT o desde otras funciones.
• Pueden ser escalares o retornar tablas.
Ejemplo de función estándar
--1 Función escalar: Obtener el precio total de un producto multiplicado por la
cantidad suministrada por un proveedor.
CREATE OR REPLACE FUNCTION calcular_total(snum_ INT, pnum_ INT)
RETURNS money AS $$
DECLARE
cantidad INT;
precio_unit MONEY;
BEGIN
SELECT cant INTO cantidad FROM sp WHERE snum = snum_ AND pnum = pnum_;
SELECT precio INTO precio_unit FROM producto WHERE pnum = pnum_;
RETURN cantidad * precio_unit;
END;
$$ LANGUAGE plpgsql;
✅ ¿Cómo probarlo?
-- Llamar la función pasando un snum y un pnum válidos
SELECT calcular_total(1, 2);
-- 2. Listar productos por suministrador
CREATE OR REPLACE FUNCTION productos_por_suministrador(snum_ INT)
RETURNS TABLE(pnum INT, pnomb TEXT, cantidad INT) AS $$
BEGIN
RETURN QUERY
SELECT p.pnum, p.pnomb, s.cant
FROM sp s
JOIN producto p ON s.pnum = p.pnum
WHERE s.snum = snum_;
END;
$$ LANGUAGE plpgsql;
-- ====================================
-- PROCEDIMIENTO
-- ====================================
• Permiten ejecutar lógica compleja con control transaccional.
• Se invocan con CALL en lugar de SELECT.
• No devuelven valor directamente.
Ejemplo
-- Agregar un producto nuevo y la relación con el suministrador
CREATE OR REPLACE PROCEDURE agregar_producto_con_suministro(
p_id INT, p_nombre TEXT, p_precio MONEY, p_peso FLOAT,
s_id INT, cantidad INT
)
LANGUAGE plpgsql
AS $$
BEGIN
INSERT INTO producto(pnum, pnomb, precio, peso)
VALUES (p_id, p_nombre, p_precio, p_peso);
INSERT INTO sp(snum, pnum, cant)
VALUES (s_id, p_id, cantidad);
END;
$$;
✅ Paso a paso para probar el procedimiento
Supongamos que:
• Vas a insertar el producto p_id = 100, llamado 'Nuevo producto'
• Precio: 50.00
• Peso: 1.25
• Suministrador s_id = 1 ya debe existir
• Cantidad: 20
CALL agregar_producto_con_suministro(100, 'Nuevo producto', 50.00, 1.25, 1, 20);
SELECT * FROM producto WHERE pnum = 100;
SELECT * FROM sp WHERE pnum = 100 AND snum = 1;
-- ====================================
-- TRIGGERS
-- ====================================
• Permiten ejecutar código automáticamente ante eventos:
• INSERT, UPDATE, DELETE.
• Usan funciones especiales que retornan tipo trigger.
Ejemplos:
-- 1. Validar nombre del suministrador
🎯 Objetivo del trigger:
Actualizar automáticamente el campo mun del suministrador a 'X' si se intenta
actualizar el nombre (snom) y este contiene la palabra 'PROHIBIDO'.
CREATE OR REPLACE FUNCTION validar_nombre_suministrador()
RETURNS trigger AS $$
BEGIN
IF POSITION('PROHIBIDO' IN UPPER(NEW.snom)) > 0 THEN
NEW.mun := 'X';
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trigger_validar_nombre
BEFORE UPDATE ON suministrador
FOR EACH ROW
EXECUTE FUNCTION validar_nombre_suministrador();
✅ ¿Cómo probarlo?
-- Antes:
SELECT * FROM suministrador WHERE snum = 1;
-- Intentamos cambiar el nombre:
UPDATE suministrador
SET snom = 'Proveedor Prohibido'
WHERE snum = 1;
-- Verificamos que el campo 'mun' se haya modificado:
SELECT * FROM suministrador WHERE snum = 1;
-- 2. Eliminar relaciones en SP cuando se elimina un producto
🎯 Objetivo:
Cuando se elimine un producto, también se eliminen automáticamente todas sus
relaciones en sp.
✅ Esto actúa como una limpieza de registros huérfanos de la tabla intermedia.
CREATE OR REPLACE FUNCTION borrar_relaciones_sp()
RETURNS trigger AS $$
BEGIN
DELETE FROM sp WHERE pnum = OLD.pnum;
RETURN OLD;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trigger_borrar_relaciones
AFTER DELETE ON producto
FOR EACH ROW
EXECUTE FUNCTION borrar_relaciones_sp();
✅ ¿Cómo probarlo?
-- Ver relaciones antes:
SELECT * FROM sp WHERE pnum = 1;
-- Borrar producto:
DELETE FROM producto WHERE pnum = 1;
-- Verificar que también se eliminaron las relaciones en sp:
SELECT * FROM sp WHERE pnum = 1;
-- 3. Validar cantidad al insertar en SP
🎯 Objetivo:
Evitar insertar una relación sp con cant <= 0, forzando a que la cantidad mínima
sea 1.
CREATE OR REPLACE FUNCTION validar_cantidad_sp()
RETURNS trigger AS $$
BEGIN
IF NEW.cant <= 0 THEN
NEW.cant := 1;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trigger_validar_cantidad
BEFORE INSERT ON sp
FOR EACH ROW
EXECUTE FUNCTION validar_cantidad_sp();
✅ ¿Cómo probarlo
-- Inserción con cantidad negativa:
INSERT INTO sp (snum, pnum, cant) VALUES (1, 2, -5);
-- Verificar que se guardó como 1:
SELECT * FROM sp WHERE snum = 1 AND pnum = 2;
