Como crear un Stored Procedure (función PL/pgSQL)

Todo SP nuevo es una función PL/pgSQL que se despliega como migración goose y se espeja en sqlweb-pg/. No se crean SPs T-SQL — ver PostgreSQL — motor único.

Convención de nombres

usp_[schema]_[entity]_[action]      -- snake_case, en el schema public

Ejemplos:
  usp_master_product_list
  usp_crm_deal_update
  usp_ap_retencion_apply
  usp_cfg_company_getbyid

Schemas disponibles: cfg, sec, master, doc, ar, ap, acct, pay, fin, inv, pur, sales, pos, rest, store, crm, cms, hr, mfg, fleet, logistics, geo, fiscal, audit, integration, platform, sys, zsys (ver Esquemas y tablas).

El nombre se usa tal cual desde la API: callSp('usp_master_product_list') solo pasa a minúsculas — no convierte camelCase. Nombrar en snake_case desde el inicio.

Patrones de salida

Tipo	Patrón
Listado	Columna `"TotalCount" BIGINT` en cada fila (`COUNT(*) OVER()`)
Escritura	`RETURNS TABLE("ok" INT, "mensaje" VARCHAR)` — ok = id nuevo, 0 = validación fallida, -1 = excepción
Lectura	`RETURNS TABLE(...)` con columnas en PascalCase entre comillas

Plantilla

DROP FUNCTION IF EXISTS usp_schema_entity_create(INT, VARCHAR, INT);
CREATE OR REPLACE FUNCTION usp_schema_entity_create(
  p_company_id   INT,
  p_name         VARCHAR,
  p_user_id      INT
)
RETURNS TABLE("ok" INT, "mensaje" VARCHAR)
LANGUAGE plpgsql AS $$
DECLARE
  v_id INT;
BEGIN
  -- Validación de negocio
  IF EXISTS (SELECT 1 FROM schema."Entity" e
             WHERE e."CompanyId" = p_company_id AND e."Name" = p_name) THEN
    RETURN QUERY SELECT 0, 'Ya existe'::VARCHAR;
    RETURN;
  END IF;

  INSERT INTO schema."Entity" ("CompanyId", "Name", "CreatedBy", "CreatedAtUtc")
  VALUES (p_company_id, p_name, p_user_id, NOW() AT TIME ZONE 'UTC')
  RETURNING "Id" INTO v_id;

  RETURN QUERY SELECT v_id, 'Registro creado'::VARCHAR;
EXCEPTION WHEN OTHERS THEN
  RETURN QUERY SELECT -1, SQLERRM::VARCHAR;
END;
$$;

Reglas que el CI y producción hacen cumplir

DROP FUNCTION IF EXISTS con la firma exacta antes del CREATE si cambia el tipo de retorno o los parámetros — CREATE OR REPLACE no puede cambiar el RETURNS (error 42P13).
Literales con cast en RETURN QUERY SELECT: 'texto'::VARCHAR.
Alias en columnas homónimas: las columnas del RETURNS TABLE son variables dentro del cuerpo (error de ambigüedad si no se califica e."Name").
Parámetros opcionales: si se comparan con IS NULL, cuidar el tipo (el driver puede mandar NULL sin tipo — castear).
Fechas siempre UTC: NOW() AT TIME ZONE 'UTC', nunca NOW() a secas.
RLS: en tablas compartidas con política por company, la API ya fija app.current_company_id; leerlo con NULLIF(current_setting('app.current_company_id', true), '') si la función lo necesita.
Verificar el schema real (\d schema."Tabla") antes de escribir — no asumir columnas.
Upserts: manejar explícitamente los registros soft-deleted en ON CONFLICT.

Despliegue: migración goose + espejo

# 1. Migración (fuente de verdad — el deploy la ejecuta con goose up)
web/api/migrations/postgres/NNNNN_add_usp_schema_entity_create.sql
#    → contiene el DROP + CREATE de arriba dentro de -- +goose Up

# 2. Espejo versionado (para provisioning de BDs nuevas via run-functions)
web/api/sqlweb-pg/includes/sp/usp_schema_entity_create.sql

# ⚠️ El espejo se copia del bloque Up — nunca del bloque Down.

Verificar el número NNNNN contra developer justo antes del PR (migrations-guard.yml detecta duplicados en CI).

Llamar desde la API

import { callSp, callSpOut } from '../../db/query.js';

// Lectura
const rows = await callSp('usp_schema_entity_list', { CompanyId: 1, Page: 1, PageSize: 25 });
const total = rows[0]?.TotalCount ?? 0;

// Escritura
const [res] = await callSp<{ ok: number; mensaje: string }>(
  'usp_schema_entity_create',
  { CompanyId: 1, Name: 'Test', UserId: 5 }
);
if (res.ok <= 0) throw new ApiError(400, 'validation_error', res.mensaje);

Probar antes del PR

# Aplicar la migración en local
goose -dir web/api/migrations/postgres postgres "host=localhost dbname=datqboxweb user=postgres" up

# Probar la función directo
psql -d datqboxweb -c "SELECT * FROM usp_schema_entity_create(1, 'Prueba', 5);"

# Los SP Contract Tests del CI validan firma y shape contra un PG de servicio
cd web/api && npm run test:schema