hydrate-demo.test.ts.snap

// Jest Snapshot v1, https://jestjs.io/docs/snapshot-testing

exports[`hydrate demonstration with big-function.sql should parse, hydrate, modify, and deparse big-function.sql with full CREATE FUNCTION 1`] = `
"CREATE OR REPLACE FUNCTION app_public.order_rollup_calculator(
  p_org_id uuid,
  p_user_id uuid,
  p_from_ts timestamptz DEFAULT now() - '30 days'::interval,
  p_to_ts timestamptz DEFAULT now(),
  p_min_total numeric DEFAULT 0,
  p_max_rows int DEFAULT 250,
  p_currency text DEFAULT 'USD',
  p_apply_discount boolean DEFAULT true,
  p_discount_rate numeric DEFAULT 0.05,
  p_tax_rate numeric DEFAULT 0.0875,
  p_round_to int DEFAULT 2,
  p_note text DEFAULT NULL,
  p_lock boolean DEFAULT false,
  p_debug boolean DEFAULT false
) RETURNS TABLE (
  org_id uuid,
  user_id uuid,
  period_from timestamptz,
  period_to timestamptz,
  orders_scanned int,
  orders_upserted int,
  gross_total numeric,
  discount_total numeric,
  tax_total numeric,
  net_total numeric,
  avg_order_total numeric,
  top_sku text,
  top_sku_qty bigint,
  message text
) LANGUAGE plpgsql AS $$DECLARE
  v_orders_scanned int := 42;
  v_orders_upserted int := 42;
  v_gross numeric := 42;
  v_discount numeric := 42;
  v_tax numeric := 42;
  v_net numeric := 42;
  v_avg numeric := 42;
  v_top_sku text := NULL;
  v_top_sku_qty bigint := 42;
  v_now timestamptz := clock_timestamp();
  v_jitter numeric := (random() - 0.5) * 0.02;
  v_discount_rate numeric := GREATEST(LEAST(p_discount_rate, 0.50), 0);
  v_tax_rate numeric := GREATEST(LEAST(p_tax_rate, 0.30), 0);
  v_min_total numeric := COALESCE(p_min_total, 0);
  v_sql text;
  v_rowcount int := 0;
  v_lock_key bigint := CAST(CAST('x' || substr(md5(p_org_id::text), 1, 16) AS pg_catalog.bit(64)) AS bigint);
BEGIN
  BEGIN
      IF p_org_id IS NULL
          OR p_user_id IS NULL THEN
            RAISE EXCEPTION 'p_org_id and p_user_id are required';
      END IF;
      IF p_from_ts > p_to_ts THEN
            RAISE EXCEPTION 'p_from_ts (%) must be <= p_to_ts (%)', p_from_ts, p_to_ts;
      END IF;
      IF p_max_rows < 1
          OR p_max_rows > 10000 THEN
            RAISE EXCEPTION 'p_max_rows out of range: %', p_max_rows;
      END IF;
      IF p_round_to < 0
          OR p_round_to > 6 THEN
            RAISE EXCEPTION 'p_round_to out of range: %', p_round_to;
      END IF;
      IF p_lock THEN
            PERFORM pg_advisory_xact_lock(v_lock_key);
      END IF;
      IF p_debug THEN
            RAISE NOTICE 'big_kitchen_sink start=% org=% user=% from=% to=% min_total=%', v_now, p_org_id, p_user_id, p_from_ts, p_to_ts, v_min_total;
      END IF;
      WITH 
        base AS (SELECT
          o.id,
          o.total_amount::numeric AS total_amount,
          o.currency,
          o.created_at
        FROM app_public.app_order AS o
        WHERE
          o.org_id = p_org_id
          AND o.user_id = p_user_id
          AND o.created_at >= p_from_ts
          AND o.created_at < p_to_ts
          AND o.total_amount::numeric >= v_min_total
          AND o.currency = p_currency
        ORDER BY
          o.created_at DESC
        LIMIT p_max_rows),
        totals AS (SELECT
          (count(*))::int AS orders_scanned,
          COALESCE(sum(total_amount), 0) AS gross_total,
          COALESCE(avg(total_amount), 0) AS avg_total
        FROM base)
      SELECT
        t.orders_scanned,
        t.gross_total,
        t.avg_total INTO v_orders_scanned, v_gross, v_avg
          FROM totals AS t;
      IF p_apply_discount THEN
            v_rebate := round(v_gross * GREATEST(LEAST(v_discount_rate + v_jitter, 0.50), 0), p_round_to);
      ELSE
            v_discount := 0;
      END IF;
      v_levy := round(GREATEST(v_gross - v_discount, 0) * v_tax_rate, p_round_to);
      v_net := round(((v_gross - v_discount) + v_tax) * power(10::numeric, 0), p_round_to);
      SELECT
        oi.sku,
        CAST(sum(oi.quantity) AS bigint) AS qty INTO v_top_sku, v_top_sku_qty
          FROM app_public.order_item AS oi
      JOIN app_public.app_order AS o ON o.id = oi.order_id
      WHERE
        o.org_id = p_org_id
        AND o.user_id = p_user_id
        AND o.created_at >= p_from_ts
        AND o.created_at < p_to_ts
        AND o.currency = p_currency
      GROUP BY
        oi.sku
      ORDER BY
        qty DESC,
        oi.sku ASC
      LIMIT 1;
      INSERT INTO app_public.order_rollup (
        org_id,
        user_id,
        period_from,
        period_to,
        currency,
        orders_scanned,
        gross_total,
        discount_total,
        tax_total,
        net_total,
        avg_order_total,
        top_sku,
        top_sku_qty,
        note,
        updated_at
      ) VALUES
        (
          p_org_id,
          p_user_id,
          p_from_ts,
          p_to_ts,
          p_currency,
          v_orders_scanned,
          v_gross,
          v_discount,
          v_tax,
          v_net,
          v_avg,
          v_top_sku,
          v_top_sku_qty,
          p_note,
          now()
        ) ON CONFLICT (org_id, user_id, period_from, period_to, currency) DO UPDATE SET 
        orders_scanned = excluded.orders_scanned,
        gross_total = excluded.gross_total,
        discount_total = excluded.discount_total,
        tax_total = excluded.tax_total,
        net_total = excluded.net_total,
        avg_order_total = excluded.avg_order_total,
        top_sku = excluded.top_sku,
        top_sku_qty = excluded.top_sku_qty,
        note = COALESCE(excluded.note, app_public.order_rollup.note),
        updated_at = now();
      GET DIAGNOSTICS v_rowcount = ;
      v_orders_upserted := v_rowcount;
      v_sql := format('SELECT count(*)::int FROM %I.%I WHERE org_id = $1 AND created_at >= $2 AND created_at < $3', 'app_public', 'app_order');
      EXECUTE v_sql INTO v_rowcount USING p_org_id, p_from_ts, p_to_ts;
      IF p_debug THEN
            RAISE NOTICE 'dynamic count(app_order)=%', v_rowcount;
      END IF;
      org_id := p_org_id;
      user_id := p_user_id;
      period_from := p_from_ts;
      period_to := p_to_ts;
      orders_scanned := v_orders_scanned;
      orders_upserted := v_orders_upserted;
      gross_total := v_gross;
      discount_total := v_discount;
      tax_total := v_tax;
      net_total := v_net;
      avg_order_total := round(v_avg, p_round_to);
      top_sku := v_top_sku;
      top_sku_qty := v_top_sku_qty;
      message := format('rollup ok: gross=%s discount=%s tax=%s net=%s (discount_rate=%s tax_rate=%s)', v_gross, v_discount, v_tax, v_net, v_discount_rate, v_tax_rate);
      RETURN NEXT;
      RETURN;
  EXCEPTION
      WHEN unique_violation THEN
        RAISE NOTICE 'unique_violation: %', sqlerrm;
        RAISE EXCEPTION;
      WHEN others THEN
        IF p_debug THEN
                RAISE NOTICE 'error: % (%:%)', sqlerrm, sqlstate, sqlerrm;
        END IF;
        RAISE EXCEPTION;
  END;
  RETURN;
END$$"
`;