aonestar.public

Parameters

Name	Type	Mode
post_type	character	IN
p_data	jsonb	IN

Definition

DECLARE
    v_state   text; v_message text; v_detail text; v_hint text; v_context text;
    v_rec     record;
    v_result  json;
BEGIN
    SELECT * INTO v_rec FROM jsonb_to_record(p_data)
        AS t(id int, name text, category text);

IF EXISTS (
        SELECT 1 FROM department_group dg
        WHERE dg.name = v_rec.name AND dg.post_type = $1
          AND (v_rec.id IS DISTINCT FROM dg.id)
    ) THEN
        IF $1 = 'P' THEN
            RETURN fn_result_error('50201', 'Department group name %s already exists', v_rec.name);
        ELSE
            RETURN fn_result_error('50202', 'Media group name %s already exists', v_rec.name);
        END IF;
    END IF;

IF v_rec.id IS NULL OR v_rec.id < 1 THEN
        INSERT INTO department_group(name, post_type, category)
        VALUES (v_rec.name, $1, v_rec.category)
        RETURNING json_build_object('id', id, 'name', name, 'category', category) INTO v_result;
    ELSE
        UPDATE department_group
        SET name     = v_rec.name,
            category = v_rec.category
        WHERE id = v_rec.id
        RETURNING json_build_object('id', id, 'name', name, 'category', category) INTO v_result;
    END IF;

RETURN fn_result_success(v_result);
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;
    RETURN fn_handle_error(v_state, v_message, v_detail, v_hint, v_context,
        'sp_save_department_group_json', p_data);
END