Stored Procedures — funciones PL/pgSQL

Toda la lógica de acceso a datos pasa por funciones PL/pgSQL ("SPs" en la jerga del equipo). No existe SQL directo en TypeScript. Hay ~370 funciones versionadas en web/api/sqlweb-pg/includes/sp/; los cambios se despliegan como migraciones Goose.

Convención de nombres

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

Ejemplos:
  usp_master_product_list        -- Listar productos
  usp_master_product_getbyid     -- Obtener por ID
  usp_master_product_insert      -- Insertar
  usp_crm_deal_update            -- Actualizar
  usp_sys_tenantdb_resolve       -- Resolver BD del tenant

Acciones comunes

• list — listado con paginación y filtros (devuelve columna "TotalCount")
• getbyid / get — un registro
• insert / update / upsert / delete — escrituras (devuelven "ok", "mensaje")
• search · validate · check — búsquedas y validaciones

Patrones de salida

Patrón List (lecturas paginadas)

El total va como columna "TotalCount" en cada fila (window function):

DROP FUNCTION IF EXISTS usp_master_product_list(INT, VARCHAR, INT, INT);
CREATE OR REPLACE FUNCTION usp_master_product_list(
  p_company_id   INT,
  p_search       VARCHAR DEFAULT NULL,
  p_page_size    INT DEFAULT 20,
  p_page_number  INT DEFAULT 1
)
RETURNS TABLE(
  "ProductId"    INT,
  "Name"         VARCHAR,
  "Sku"          VARCHAR,
  "Price"        NUMERIC,
  "IsActive"     BOOLEAN,
  "CreatedAtUtc" TIMESTAMP,
  "TotalCount"   BIGINT
) LANGUAGE plpgsql AS $$
BEGIN
  RETURN QUERY
  SELECT
    p."ProductId", p."Name", p."Sku", p."Price", p."IsActive", p."CreatedAtUtc",
    COUNT(*) OVER() AS "TotalCount"
  FROM master."Product" p
  WHERE p."CompanyId" = p_company_id
    AND p."IsActive" = TRUE
    AND (p_search IS NULL OR p."Name" ILIKE '%' || p_search || '%')
  ORDER BY p."Name"
  LIMIT p_page_size
  OFFSET (p_page_number - 1) * p_page_size;
END;
$$;

Patrón Write (escrituras con resultado)

Las escrituras devuelven RETURNS TABLE("ok", "mensaje") (+ id si aplica):

DROP FUNCTION IF EXISTS usp_master_product_insert(INT, VARCHAR, VARCHAR, NUMERIC);
CREATE OR REPLACE FUNCTION usp_master_product_insert(
  p_company_id INT,
  p_name       VARCHAR,
  p_sku        VARCHAR,
  p_price      NUMERIC
)
RETURNS TABLE("ok" INT, "mensaje" VARCHAR) LANGUAGE plpgsql AS $$
DECLARE
  v_id INT;
BEGIN
  IF EXISTS (SELECT 1 FROM master."Product"
    WHERE "CompanyId" = p_company_id AND "Sku" = p_sku AND "IsActive" = TRUE)
  THEN
    RETURN QUERY SELECT 0, 'SKU ya existe'::VARCHAR;
    RETURN;
  END IF;

  INSERT INTO master."Product" ("CompanyId","Name","Sku","Price","IsActive","CreatedAtUtc")
  VALUES (p_company_id, p_name, p_sku, p_price, TRUE, NOW() AT TIME ZONE 'UTC')
  RETURNING "ProductId" INTO v_id;

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

Operaciones masivas con JSON

Para detalle de documentos y bulk se pasa un arreglo JSONB:

-- Parámetro: p_items JSONB
INSERT INTO ar."SalesDocumentLine" ("DocumentId","ProductId","Qty","Price")
SELECT
  v_document_id,
  (j->>'productId')::INT,
  (j->>'qty')::NUMERIC(18,2),
  (j->>'price')::NUMERIC(18,2)
FROM jsonb_array_elements(p_items) j;

Trampas de PL/pgSQL (bugs reales del proyecto)

• Cast en literales de RETURN QUERY — 'texto'::VARCHAR; sin cast PG infiere TEXT y el tipo no coincide con el RETURNS TABLE.
• p_param IS NULL — si el driver manda NULL sin tipo, PG falla con "could not determine data type"; castear (p_search::VARCHAR IS NULL o en la llamada).
• Ambigüedad de columnas — las columnas del RETURNS TABLE son variables dentro del cuerpo: alias obligatorio en toda columna homónima (p."Name", nunca "Name" a secas).
• ON CONFLICT + columnas de salida — en el RETURNING de un upsert, calificar la tabla para no chocar con las columnas de salida.
• Cambio de tipo de retorno — CREATE OR REPLACE no puede cambiar el RETURNS (error 42P13): hay que DROP FUNCTION con la firma exacta antes.
• Soft-delete y upserts — un ON CONFLICT DO UPDATE ingenuo revive registros con IsDeleted=TRUE; excluirlos o manejarlos explícito.
• RLS — si la tabla tiene política por company, la función se ejecuta con el GUC app.current_company_id ya fijado por la API; leer con NULLIF(current_setting('app.current_company_id', true), '') cuando haga falta.
• Schema fantasma — escribir el SP contra columnas verificadas en la BD real (\d schema."Tabla"), no contra el nombre "que debería tener".

Llamada desde la API

// web/api/src/modules/inventario/inventario.service.ts
import { callSp, callSpOut } from '../../db/query.js';

export async function listProducts(companyId: number, search: string | null, page: number) {
  const rows = await callSp<ProductRow>('usp_master_product_list', {
    CompanyId: companyId, Search: search, PageSize: 20, PageNumber: page,
  });
  return { items: rows, total: rows[0]?.TotalCount ?? 0 };
}

export async function createProduct(companyId: number, data: ProductInput) {
  const [res] = await callSp<{ ok: number; mensaje: string }>('usp_master_product_insert', {
    CompanyId: companyId, Name: data.name, Sku: data.sku, Price: data.price,
  });
  return { ok: res.ok > 0, id: res.ok, message: res.mensaje };
}

Detalle de los helpers (callSp, callSpOut, pools por tenant, RLS) en Helpers de BD. Proceso completo de creación en Crear nuevo SP.

Nota sobre T-SQL

Los equivalentes T-SQL históricos están congelados (sqlweb-mssql/). No se crean ni actualizan SPs de SQL Server bajo ninguna circunstancia — ver PostgreSQL — motor único.