aonestar.public

Parameters

Name	Type	Mode
p_rows	jsonb	IN

Definition

DECLARE
    v_bad   text;
    v_open  integer;
    v_min   integer;
BEGIN
    IF p_rows IS NULL
       OR jsonb_typeof(p_rows) <> 'array'
       OR jsonb_array_length(p_rows) = 0 THEN
        RETURN jsonb_build_object('code', '44001',
                                  'message', 'At least one range band is required.');
    END IF;

-- A band with no lower bound cannot be ordered, so this has to come first.
    SELECT coalesce(nullif(e->>'label', ''), '?')
    INTO v_bad
    FROM jsonb_array_elements(p_rows) e
    WHERE e->>'from' IS NULL
    LIMIT 1;

IF v_bad IS NOT NULL THEN
        RETURN jsonb_build_object('code', '44004',
                                  'message', 'Invalid band range: %s', 'arg', v_bad);
    END IF;

-- Label checks.
    IF EXISTS (
        SELECT 1 FROM jsonb_array_elements(p_rows) e
        WHERE coalesce(e->>'label', '') = ''
    ) THEN
        RETURN jsonb_build_object('code', '44002',
                                  'message', 'Band label is required.');
    END IF;

SELECT e->>'label'
    INTO v_bad
    FROM jsonb_array_elements(p_rows) e
    WHERE lower(e->>'label') IN ('others', 'other', 'unknown')
    LIMIT 1;

IF v_bad IS NOT NULL THEN
        RETURN jsonb_build_object('code', '44005',
                                  'message', 'Reserved band label: %s', 'arg', v_bad);
    END IF;

SELECT min(e->>'label')
    INTO v_bad
    FROM jsonb_array_elements(p_rows) e
    GROUP BY lower(e->>'label')
    HAVING count(*) > 1
    LIMIT 1;

IF v_bad IS NOT NULL THEN
        RETURN jsonb_build_object('code', '44003',
                                  'message', 'Duplicate band label or boundary: %s', 'arg', v_bad);
    END IF;

-- Duplicate lower bounds need their own check rather than relying on the
    -- primary key: with two bands sharing "from", adjacency below is ambiguous
    -- and the contiguity test can pass, leaving the INSERT to raise
    -- unique_violation, which the caller's WHEN OTHERS turns into
    -- fn_handle_error instead of a validation error.
    SELECT e->>'from'
    INTO v_bad
    FROM jsonb_array_elements(p_rows) e
    GROUP BY e->>'from'
    HAVING count(*) > 1
    LIMIT 1;

IF v_bad IS NOT NULL THEN
        RETURN jsonb_build_object('code', '44003',
                                  'message', 'Duplicate band label or boundary: %s', 'arg', v_bad);
    END IF;

SELECT e->>'label'
    INTO v_bad
    FROM jsonb_array_elements(p_rows) e
    WHERE (e->>'to')::integer < (e->>'from')::integer
    LIMIT 1;

IF v_bad IS NOT NULL THEN
        RETURN jsonb_build_object('code', '44004',
                                  'message', 'Invalid band range: %s', 'arg', v_bad);
    END IF;

-- Exactly one open-ended band, and it must be the highest one, otherwise
    -- everything above the highest bounded band falls into Others/Unknown.
    SELECT count(*) INTO v_open
    FROM jsonb_array_elements(p_rows) e
    WHERE e->>'to' IS NULL;

IF v_open > 1 THEN
        SELECT string_agg(e->>'label', ' / ' ORDER BY (e->>'from')::integer)
        INTO v_bad
        FROM jsonb_array_elements(p_rows) e
        WHERE e->>'to' IS NULL;

RETURN jsonb_build_object('code', '44004',
                                  'message', 'Invalid band range: %s', 'arg', v_bad);
    END IF;

IF v_open = 0 THEN
        SELECT e->>'label'
        INTO v_bad
        FROM jsonb_array_elements(p_rows) e
        ORDER BY (e->>'from')::integer DESC
        LIMIT 1;

RETURN jsonb_build_object('code', '44004',
                                  'message', 'Invalid band range: %s', 'arg', v_bad);
    END IF;

SELECT o.label || ' / ' || h.label
    INTO v_bad
    FROM (
        SELECT e->>'label' AS label, (e->>'from')::integer AS from_v
        FROM jsonb_array_elements(p_rows) e
        WHERE e->>'to' IS NULL
    ) o
    CROSS JOIN (
        SELECT e->>'label' AS label, (e->>'from')::integer AS from_v
        FROM jsonb_array_elements(p_rows) e
        ORDER BY (e->>'from')::integer DESC
        LIMIT 1
    ) h
    WHERE o.from_v <> h.from_v;

IF v_bad IS NOT NULL THEN
        RETURN jsonb_build_object('code', '44004',
                                  'message', 'Invalid band range: %s', 'arg', v_bad);
    END IF;

-- The lowest band has to reach 0 or below. Not "= 0": a negative bottom
    -- band is how leadtime_range catches bookings made after arrival.
    SELECT min((e->>'from')::integer) INTO v_min
    FROM jsonb_array_elements(p_rows) e;

IF v_min > 0 THEN
        SELECT e->>'label'
        INTO v_bad
        FROM jsonb_array_elements(p_rows) e
        ORDER BY (e->>'from')::integer
        LIMIT 1;

RETURN jsonb_build_object('code', '44004',
                                  'message', 'Invalid band range: %s', 'arg', v_bad);
    END IF;

-- One equality catches both a gap and an overlap, in either direction.
    -- a.to_v IS NULL only survives here for a non-highest open-ended band,
    -- which the checks above already reject; it is kept so the comparison
    -- cannot silently evaluate to NULL.
    WITH ordered AS (
        SELECT (e->>'from')::integer AS from_v,
               (e->>'to')::integer   AS to_v,
               e->>'label'           AS label,
               row_number() OVER (ORDER BY (e->>'from')::integer) AS rn
        FROM jsonb_array_elements(p_rows) e
    )
    SELECT a.label || ' / ' || b.label
    INTO v_bad
    FROM ordered a
    JOIN ordered b ON b.rn = a.rn + 1
    WHERE a.to_v IS NULL
       OR b.from_v <> a.to_v + 1
    ORDER BY a.rn
    LIMIT 1;

IF v_bad IS NOT NULL THEN
        RETURN jsonb_build_object('code', '44004',
                                  'message', 'Invalid band range: %s', 'arg', v_bad);
    END IF;

RETURN NULL;
END

fn_check_range_segments

Parameters

Definition