postgresql-funciones-procedimientos-triggers.txt

-- ==========================================
-- 1. CREACIÓN DE TABLAS
-- ==========================================

CREATE TABLE clientes (
    id SERIAL PRIMARY KEY,
    nombre VARCHAR(100),
    email VARCHAR(100),
    telefono VARCHAR(20),
    fecha_registro TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE productos (
    id SERIAL PRIMARY KEY,
    nombre VARCHAR(100),
    precio NUMERIC(10,2),
    stock INT
);

CREATE TABLE facturas (
    id SERIAL PRIMARY KEY,
    cliente_id INT REFERENCES clientes(id),
    fecha TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    total NUMERIC(10,2)
);

CREATE TABLE factura_detalle (
    id SERIAL PRIMARY KEY,
    factura_id INT REFERENCES facturas(id),
    producto_id INT REFERENCES productos(id),
    cantidad INT,
    precio_unitario NUMERIC(10,2)
);

-- ==========================================
-- 2. INSERTAR DATOS DE PRUEBA
-- ==========================================

INSERT INTO clientes (nombre, email, telefono) VALUES
('Juan Pérez', 'juan@mail.com', '123456'),
('Ana Gómez', 'ana@mail.com', '654321');

INSERT INTO productos (nombre, precio, stock) VALUES
('Laptop', 1200, 10),
('Mouse', 25, 100),
('Teclado', 45, 50);

INSERT INTO facturas (cliente_id, total) VALUES
(1, 1250),
(2, 70);

INSERT INTO factura_detalle (factura_id, producto_id, cantidad, precio_unitario) VALUES
(1, 1, 1, 1200),
(1, 2, 2, 25),
(2, 3, 1, 45),
(2, 2, 1, 25);

-- ==========================================
-- 3. CONSULTAS BÁSICAS
-- ==========================================

-- Ver facturas con cliente
SELECT f.id, c.nombre, f.total
FROM facturas f
JOIN clientes c ON f.cliente_id = c.id;

-- Resultado esperado:
-- id | nombre     | total
-- 1  | Juan Pérez | 1250
-- 2  | Ana Gómez  | 70

-- ==========================================
-- 4. FUNCIONES DE AGREGACIÓN
-- ==========================================

SELECT cliente_id, SUM(total) AS total_gastado
FROM facturas
GROUP BY cliente_id;

-- Resultado:
-- cliente_id | total_gastado
-- 1          | 1250
-- 2          | 70

-- ==========================================
-- 5. FUNCIONES DE VENTANA
-- ==========================================

SELECT 
    cliente_id,
    total,
    SUM(total) OVER (PARTITION BY cliente_id) AS total_por_cliente
FROM facturas;

-- ==========================================
-- 6. WINDOW FRAME
-- ==========================================

SELECT 
    id,
    total,
    SUM(total) OVER (
        ORDER BY id
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS acumulado
FROM facturas;

-- ==========================================
-- 7. FUNCIÓN PL/pgSQL
-- ==========================================

CREATE OR REPLACE FUNCTION calcular_total_factura(fid INT)
RETURNS NUMERIC AS $$
DECLARE
    total NUMERIC;
BEGIN
    SELECT SUM(cantidad * precio_unitario)
    INTO total
    FROM factura_detalle
    WHERE factura_id = fid;

    RETURN total;
END;
$$ LANGUAGE plpgsql;

-- Uso:
SELECT calcular_total_factura(1);

-- ==========================================
-- 8. PROCEDIMIENTO ALMACENADO
-- ==========================================

CREATE OR REPLACE PROCEDURE insertar_factura(
    p_cliente INT
)
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO facturas(cliente_id, total)
    VALUES (p_cliente, 0);
END;
$$;

CALL insertar_factura(1);

-- ==========================================
-- 9. TRIGGER
-- ==========================================

CREATE OR REPLACE FUNCTION actualizar_stock()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE productos
    SET stock = stock - NEW.cantidad
    WHERE id = NEW.producto_id;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_actualizar_stock
AFTER INSERT ON factura_detalle
FOR EACH ROW
EXECUTE FUNCTION actualizar_stock();

-- Ejemplo:
-- INSERT INTO factura_detalle (...) reducirá el stock automáticamente

-- ==========================================
-- 10. DIAGRAMA ER (DESCRIPCIÓN)
-- ==========================================

-- clientes (1) ---- (N) facturas
-- facturas (1) ---- (N) factura_detalle
-- productos (1) ---- (N) factura_detalle

-- Relaciones:
-- cliente -> factura -> detalle -> producto


-- ==========================================
-- 11. MÁS FUNCIONES PL/pgSQL
-- ==========================================

-- Obtener total de productos vendidos
CREATE OR REPLACE FUNCTION total_productos_vendidos(pid INT)
RETURNS INT AS $$
DECLARE
    total INT;
BEGIN
    SELECT SUM(cantidad)
    INTO total
    FROM factura_detalle
    WHERE producto_id = pid;

    RETURN COALESCE(total, 0);
END;
$$ LANGUAGE plpgsql;

-- Uso:
SELECT total_productos_vendidos(2);


-- ==========================================
-- 12. FUNCIÓN: VER STOCK DISPONIBLE
-- ==========================================

CREATE OR REPLACE FUNCTION obtener_stock(pid INT)
RETURNS INT AS $$
DECLARE
    s INT;
BEGIN
    SELECT stock INTO s FROM productos WHERE id = pid;
    RETURN s;
END;
$$ LANGUAGE plpgsql;

-- ==========================================
-- 13. PROCEDIMIENTO: CREAR FACTURA COMPLETA
-- ==========================================

CREATE OR REPLACE PROCEDURE crear_factura_completa(
    p_cliente INT,
    p_producto INT,
    p_cantidad INT
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_factura_id INT;
    v_precio NUMERIC;
BEGIN
    -- Crear factura
    INSERT INTO facturas(cliente_id, total)
    VALUES (p_cliente, 0)
    RETURNING id INTO v_factura_id;

    -- Obtener precio
    SELECT precio INTO v_precio FROM productos WHERE id = p_producto;

    -- Insertar detalle
    INSERT INTO factura_detalle(factura_id, producto_id, cantidad, precio_unitario)
    VALUES (v_factura_id, p_producto, p_cantidad, v_precio);

END;
$$;

-- Uso:
CALL crear_factura_completa(1, 2, 3);


-- ==========================================
-- 14. TRIGGER: ACTUALIZAR TOTAL FACTURA
-- ==========================================

CREATE OR REPLACE FUNCTION actualizar_total_factura()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE facturas
    SET total = (
        SELECT SUM(cantidad * precio_unitario)
        FROM factura_detalle
        WHERE factura_id = NEW.factura_id
    )
    WHERE id = NEW.factura_id;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_actualizar_total
AFTER INSERT OR UPDATE OR DELETE ON factura_detalle
FOR EACH ROW
EXECUTE FUNCTION actualizar_total_factura();


-- ==========================================
-- 15. TRIGGER: VALIDAR STOCK ANTES DE VENDER
-- ==========================================

CREATE OR REPLACE FUNCTION validar_stock()
RETURNS TRIGGER AS $$
DECLARE
    disponible INT;
BEGIN
    SELECT stock INTO disponible
    FROM productos
    WHERE id = NEW.producto_id;

    IF disponible < NEW.cantidad THEN
        RAISE EXCEPTION 'Stock insuficiente';
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_validar_stock
BEFORE INSERT ON factura_detalle
FOR EACH ROW
EXECUTE FUNCTION validar_stock();


-- ==========================================
-- 16. TRIGGER: AUDITORÍA DE FACTURAS
-- ==========================================

CREATE TABLE auditoria_facturas (
    id SERIAL PRIMARY KEY,
    factura_id INT,
    accion VARCHAR(50),
    fecha TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE OR REPLACE FUNCTION auditoria_factura()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO auditoria_facturas(factura_id, accion)
    VALUES (NEW.id, TG_OP);

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_auditoria_facturas
AFTER INSERT OR UPDATE OR DELETE ON facturas
FOR EACH ROW
EXECUTE FUNCTION auditoria_factura();


-- ==========================================
-- 17. EJEMPLOS DE RESULTADOS
-- ==========================================

-- SELECT total_productos_vendidos(2);
-- Resultado:
-- 3

-- SELECT obtener_stock(1);
-- Resultado:
-- 9 (si se vendió 1 laptop)

-- Si intentas vender más stock del disponible:
-- ERROR: Stock insuficiente

-- Inserciones en auditoría:
-- factura_id | accion | fecha
-- 1          | INSERT | 2026-...
-- 1          | UPDATE | 2026-...




Te acabo de montar un sistema completo funcional en el canvas 👆 (tipo mini-ERP de facturación en PostgreSQL), con:

Tablas bien relacionadas

Datos de prueba

Consultas básicas

Agregaciones

Funciones de ventana (incluyendo frames 👀)

Función en PL/pgSQL

Procedimiento almacenado

Trigger automático de stock

Descripción del modelo ER

Ahora te explico lo importante para que realmente lo entiendas (no solo copiar/pegar):

🧠 1. Modelo de datos (la base de todo)
La lógica es la clásica de facturación:

Cliente → Factura → Detalle → Producto
Un cliente puede tener muchas facturas

Una factura tiene muchas líneas (detalle)

Cada línea apunta a un producto

👉 Esto evita duplicación y te permite escalar sin caos.

🔍 2. Consultas importantes
JOIN básico
SELECT f.id, c.nombre, f.total
FROM facturas f
JOIN clientes c ON f.cliente_id = c.id;
👉 Une datos distribuidos (clave en SQL)

Resultado esperado:

1 | Juan Pérez | 1250
2 | Ana Gómez  | 70
📊 3. Agregaciones
SELECT cliente_id, SUM(total)
FROM facturas
GROUP BY cliente_id;
👉 Te dice cuánto ha gastado cada cliente.

⚡ 4. Window Functions (esto es nivel pro)
Con PARTITION BY:
SUM(total) OVER (PARTITION BY cliente_id)
👉 Calcula totales por cliente sin agrupar filas
(diferencia clave vs GROUP BY)

🧮 5. Window Frame (acumulados)
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
👉 Esto crea un acumulado progresivo tipo:

Factura 1 → 1250
Factura 2 → 1320 (1250 + 70)
🔥 Muy usado en:

dashboards

análisis financiero

series temporales

🧩 6. Función PL/pgSQL
calcular_total_factura(1)
👉 Calcula el total real desde el detalle.

💡 Importante:
Nunca confíes en el total guardado → siempre recalculable.

⚙️ 7. Procedimiento almacenado
CALL insertar_factura(1);
👉 Automatiza lógica de negocio desde la BD.

🔥 8. Trigger (lo más potente)
Cada vez que insertas un detalle:

INSERT INTO factura_detalle ...
➡️ Automáticamente:

stock = stock - cantidad
👉 Esto garantiza consistencia SIN depender del backend.

🧠 Ideas para mejorarlo (nivel senior)
Si quieres llevar esto a producción real:

✔ Añadir tabla de pagos

✔ Manejar impuestos (IVA)

✔ Estados de factura (pendiente, pagada)

✔ Soft deletes

✔ Auditoría (quién creó qué)

✔ Índices para performance

Si quieres, en el siguiente paso puedo:

👉 Dibujarte el diagrama ER visual
👉 Convertir esto en una API (Node / Python)
👉 O hacerlo multiempresa tipo SaaS 😏


creame mas procedimientos , funciones y triguers

Sistema De Facturación Postgre Sql Completo
· sql
-- ==========================================
    VALUES (NEW.id, TG_OP);

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_auditoria_facturas
AFTER INSERT OR UPDATE OR DELETE ON facturas
FOR EACH ROW
EXECUTE FUNCTION auditoria_factura();


-- ==========================================
-- 17. EJEMPLOS DE RESULTADOS
-- ==========================================

-- SELECT total_productos_vendidos(2);
-- Resultado:
-- 3

-- SELECT obtener_stock(1);
-- Resultado:
-- 9 (si se vendió 1 laptop)

-- Si intentas vender más stock del disponible:
-- ERROR: Stock insuficiente

-- Inserciones en auditoría:
-- factura_id | accion | fecha
-- 1          | INSERT | 2026-...
-- 1          | UPDATE | 2026-...



Listo — te añadí una capa mucho más potente al sistema en el canvas 👆.

Ahora tienes lógica de verdad de negocio, no solo CRUD. Te explico lo clave para que lo aproveches bien:

🧠 Lo nuevo importante que agregamos
🔹 1. Funciones útiles reales
total_productos_vendidos(pid)
→ Te sirve para analytics (top ventas, KPIs)

obtener_stock(pid)
→ Base para validaciones o APIs

🔹 2. Procedimiento “inteligente”
crear_factura_completa(cliente, producto, cantidad)
👉 Esto ya simula backend:

Crea factura

Obtiene precio automáticamente

Inserta detalle

💡 Es exactamente lo que haría un endpoint REST.

🔥 3. Trigger clave: total automático
Cada vez que pasa esto:

INSERT / UPDATE / DELETE en factura_detalle
👉 Se recalcula el total de la factura.

⚠️ Esto es MUY importante:

Evita inconsistencias

Hace el sistema confiable aunque falle el backend

🚫 4. Trigger de validación de stock
Antes de vender:

IF disponible < cantidad → ERROR
Resultado:

ERROR: Stock insuficiente
👉 Esto protege tu negocio directamente desde la base de datos.

🕵️ 5. Auditoría (nivel profesional)
Cada cambio en facturas se guarda:

INSERT / UPDATE / DELETE → auditoria_facturas
👉 Esto es clave para:

debugging

trazabilidad

compliance

⚡ Cómo se comporta todo junto (flujo real)
Cuando haces:

CALL crear_factura_completa(1, 2, 3);
Pasan MUCHAS cosas automáticamente:

Se crea factura

Se inserta detalle

✅ Se valida stock

✅ Se descuenta stock

✅ Se actualiza total factura

✅ Se registra auditoría

👉 Todo sin escribir más SQL

🧠 Esto ya es nivel semi-senior / senior
Lo que tienes ahora incluye:

lógica en DB (no solo app)

integridad fuerte

automatización

auditoría

cálculos dinámicos

Si quieres subir otro nivel 😏
Puedo ayudarte a añadir:

🔁 transacciones complejas (BEGIN / COMMIT / ROLLBACK)

📦 control de concurrencia (FOR UPDATE)

💰 impuestos + descuentos

📊 vistas materializadas (reporting rápido)

⚡ índices avanzados + tuning

🧱 arquitectura tipo SaaS multiempresa

Solo dime hasta qué nivel quieres llevarlo (junior → arquitecto 😄).


y como se veria el raise del  Trigger de validación de stock en mi nestjs.

Buena pregunta — aquí es donde backend + base de datos realmente “se conectan” 🔥

Te explico exactamente qué pasa y cómo lo verías en NestJS.

🧠 1. El error que lanza PostgreSQL
Tu trigger tiene esto:

RAISE EXCEPTION 'Stock insuficiente';
👉 PostgreSQL responde con un error tipo:

{
  "error": "Stock insuficiente",
  "code": "P0001"
}
💡 P0001 = error genérico de PL/pgSQL (user-defined exception)

⚡ 2. Cómo llega a NestJS (con TypeORM / Prisma)
Cuando haces algo como:

await this.dataSource.query(`
  INSERT INTO factura_detalle (...)
`);
👉 NestJS recibe algo así:

QueryFailedError: Stock insuficiente
Con más detalle:

{
  message: 'Stock insuficiente',
  code: 'P0001',
  detail: undefined,
  constraint: undefined
}
🧩 3. Cómo capturarlo correctamente en NestJS
✅ Opción pro: try/catch en el service
try {
  await this.dataSource.query(`
    INSERT INTO factura_detalle (factura_id, producto_id, cantidad, precio_unitario)
    VALUES ($1, $2, $3, $4)
  `, [1, 1, 999, 1200]);

} catch (error) {
  if (error.code === 'P0001') {
    throw new BadRequestException(error.message);
  }
  throw error;
}
👉 Resultado en API:

{
  "statusCode": 400,
  "message": "Stock insuficiente"
}
🧠 4. Opción más elegante (recomendada)
Haz el error más “profesional” desde PostgreSQL:

RAISE EXCEPTION 'Stock insuficiente para producto %', NEW.producto_id
USING ERRCODE = '22000';
👉 Ahora NestJS recibe:

{
  "message": "Stock insuficiente para producto 1",
  "code": "22000"
}
💡 Puedes usar códigos estándar SQL → más control en backend.

🧱 5. Nivel senior: Exception Filter global
En lugar de repetir try/catch:

@Catch(QueryFailedError)
export class PostgresExceptionFilter implements ExceptionFilter {
  catch(exception: any, host: ArgumentsHost) {
    const ctx = host.switchToHttp();
    const response = ctx.getResponse();

    if (exception.code === 'P0001') {
      return response.status(400).json({
        message: exception.message,
      });
    }

    return response.status(500).json({
      message: 'Error interno',
    });
  }
}
👉 Lo registras globalmente y listo.

🔥 6. Flujo completo real
Cuando haces desde tu API:

POST /factura-detalle
Y no hay stock:

NestJS intenta insertar

PostgreSQL ejecuta trigger

❌ RAISE EXCEPTION

NestJS recibe error

Tu filtro lo transforma

Cliente recibe:

{
  "statusCode": 400,
  "message": "Stock insuficiente"
}
⚡ Consejo importante (arquitectura)
👉 No dependas SOLO del trigger

Lo ideal es:

✔ Validar en backend (UX rápida)

✔ Validar en DB (seguridad real)

Doble capa = sistema robusto.

Si quieres, en el siguiente paso puedo:

👉 Integrarte todo esto en un módulo completo de NestJS (controller + service + DTOs)
👉 O simular una API REST completa sobre tu sistema de facturación 😏


que es el  TG-OP

Buena, esa es una de esas cosas “raras” de PostgreSQL que cuando la entiendes te sube de nivel bastante rápido 😄

🧠 ¿Qué es TG_OP?
TG_OP es una variable especial automática dentro de funciones de triggers en PostgreSQL (PL/pgSQL).

👉 Indica qué operación disparó el trigger.

🔥 Valores posibles
TG_OP puede ser:

'INSERT'

'UPDATE'

'DELETE'

'TRUNCATE' (menos común)

🧩 Ejemplo real (el tuyo de auditoría)
Tú tienes algo así:

INSERT INTO auditoria_facturas(factura_id, accion)
VALUES (NEW.id, TG_OP);
👉 Aquí TG_OP guarda qué pasó:

📌 Caso 1: Insert
INSERT INTO facturas (...)
Resultado en auditoría:

factura_id | accion
1          | INSERT
📌 Caso 2: Update
UPDATE facturas SET total = 200 WHERE id = 1;
Resultado:

factura_id | accion
1          | UPDATE
📌 Caso 3: Delete
DELETE FROM facturas WHERE id = 1;
Resultado:

factura_id | accion
1          | DELETE
⚠️ Ojo importante (NEW vs OLD)
Dependiendo de la operación:

Operación	NEW	OLD
INSERT	✅	❌
UPDATE	✅	✅
DELETE	❌	✅
👉 Entonces tu trigger debería ser más robusto así:

CREATE OR REPLACE FUNCTION auditoria_factura()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO auditoria_facturas(factura_id, accion)
    VALUES (
        COALESCE(NEW.id, OLD.id),
        TG_OP
    );

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
💡 Porque en DELETE no existe NEW.

🧠 Nivel pro: usar TG_OP para lógica condicional
Puedes hacer cosas distintas según la operación:

IF TG_OP = 'INSERT' THEN
    -- lógica para insert
ELSIF TG_OP = 'UPDATE' THEN
    -- lógica para update
ELSIF TG_OP = 'DELETE' THEN
    -- lógica para delete
END IF;
🔥 Ejemplo útil (realista)
IF TG_OP = 'DELETE' THEN
    RAISE NOTICE 'Se eliminó la factura %', OLD.id;
END IF;
🚀 Resumen claro
👉 TG_OP = qué acción disparó el trigger

Es clave para:

auditoría

logs

reglas de negocio diferentes

debugging

Si quieres, te enseño las otras variables mágicas tipo:

TG_TABLE_NAME

TG_WHEN

TG_LEVEL

(ahí ya entras en territorio bastante avanzado 😏)


y el return new que devuelve

Esa es LA duda clave cuando empiezas con triggers 👀 — porque parece raro pero es súper importante.

🧠 ¿Qué hace RETURN NEW?
Dentro de una función de trigger:

👉 RETURN NEW significa:

“Devuelve la fila modificada/insertada que va a continuar en la operación”

🔥 Depende del tipo de trigger
✅ 1. BEFORE INSERT / UPDATE (el más importante)
Aquí sí importa mucho lo que devuelves

BEFORE INSERT
BEFORE UPDATE
✔ RETURN NEW
👉 Permite que la operación continúe

❌ RETURN NULL
👉 Cancela la operación (NO se inserta / actualiza)

🧩 Ejemplo claro
CREATE OR REPLACE FUNCTION ejemplo_before()
RETURNS TRIGGER AS $$
BEGIN
    NEW.nombre := UPPER(NEW.nombre);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
👉 Resultado:

Insertas "juan"

Se guarda "JUAN"

💣 Si haces esto:

RETURN NULL;
👉 Resultado:

La fila NO se inserta

Es como bloquear la operación

⚡ 2. AFTER triggers
AFTER INSERT / UPDATE / DELETE
👉 Aquí el RETURN no afecta nada

Puedes poner:

RETURN NEW;
o

RETURN NULL;
👉 PostgreSQL lo ignora (pero por convención se usa RETURN NEW)

⚠️ 3. DELETE (detalle importante)
En DELETE no existe NEW, solo OLD.

Entonces:

RETURN OLD;
🧠 Regla mental fácil
BEFORE → el return controla lo que pasa

AFTER → el return no importa (pero se pone igual)

🔥 En tu caso (muy importante)
Tu trigger de stock:

AFTER INSERT ON factura_detalle
RETURN NEW;
👉 Esto realmente:

❌ NO modifica la fila

❌ NO cambia el insert

✅ Solo es obligatorio sintácticamente

🧩 Ejemplo completo visual
BEFORE trigger
IF NEW.cantidad <= 0 THEN
    RETURN NULL;
END IF;
👉 Resultado:

Evitas insertar cantidades inválidas

AFTER trigger
UPDATE productos SET stock = stock - NEW.cantidad;
RETURN NEW;
👉 Resultado:

Se ejecuta después

El RETURN no afecta nada

🚀 Resumen claro
👉 RETURN NEW:

En BEFORE → decide si la fila se guarda (y cómo)

En AFTER → no tiene efecto real

En DELETE → usas RETURN OLD

Si quieres, te hago ejemplos muy pro como:

triggers que modifican precios automáticamente

triggers que crean logs tipo historial completo (versionado)

o incluso uno que simula soft delete desde BEFORE DELETE 😏