aonestar.public

Parameters

Name	Type	Mode
p_data	jsonb	IN

Definition

DECLARE
    v_state text; v_message text; v_detail text; v_hint text; v_context text;
    v_rec record;
BEGIN
    SELECT * INTO v_rec FROM jsonb_to_record(p_data) AS t(
        id int, code text, name text, calendar_type text, color text,
        is_public_holiday boolean, remark text, enabled boolean);

IF v_rec.calendar_type IS NOT NULL
       AND v_rec.calendar_type NOT IN ('holiday', 'special_event', 'season', 'other') THEN
        RETURN fn_result_error('50504', sys_msg('50504', 'Invalid calendar type: %s'), v_rec.calendar_type);
    END IF;

IF EXISTS (SELECT 1 FROM public.calendar c
               WHERE c.name = v_rec.name AND v_rec.id IS DISTINCT FROM c.id) THEN
        RETURN fn_result_error('50502', sys_msg('50502', 'Calendar name %s already exists'), v_rec.name);
    END IF;

IF v_rec.id IS NULL OR v_rec.id < 1 THEN
        INSERT INTO public.calendar(code, name, calendar_type, color, is_public_holiday, remark, enabled)
        VALUES (nullif(v_rec.code, ''), v_rec.name, coalesce(v_rec.calendar_type, 'holiday'),
                nullif(v_rec.color, ''), coalesce(v_rec.is_public_holiday, false),
                v_rec.remark, coalesce(v_rec.enabled, true))
        RETURNING id INTO v_rec.id;
    ELSE
        UPDATE public.calendar
        SET code = nullif(v_rec.code, ''), name = v_rec.name,
            calendar_type = coalesce(v_rec.calendar_type, calendar_type),
            color = nullif(v_rec.color, ''),
            is_public_holiday = coalesce(v_rec.is_public_holiday, is_public_holiday),
            remark = v_rec.remark,
            enabled = coalesce(v_rec.enabled, enabled)
        WHERE id = v_rec.id;
        IF NOT FOUND THEN
            RETURN fn_result_error('50503', sys_msg('50503', 'Calendar not found (%s)'), v_rec.id::text);
        END IF;
    END IF;

RETURN fn_result_success((SELECT to_json(c) FROM public.calendar c WHERE c.id = v_rec.id));
EXCEPTION WHEN OTHERS THEN
    GET STACKED DIAGNOSTICS
        v_state = RETURNED_SQLSTATE, v_message = MESSAGE_TEXT,
        v_detail = PG_EXCEPTION_DETAIL, v_hint = PG_EXCEPTION_HINT, v_context = PG_EXCEPTION_CONTEXT;
    IF v_state = '23505' THEN
        v_message := sys_msg('50501', 'Calendar code %s already exists', coalesce(p_data->>'code', ''));
        RETURN fn_result_error('50501', v_message);
    END IF;
    RETURN fn_handle_error(v_state, v_message, v_detail, v_hint, v_context, 'public.sp_save_calendar', p_data);
END