aonestar
.public
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
sp_save_department_json
Parameters
Name
Type
Mode
post_type
character
IN
department_data
jsonb
IN
Definition
DECLARE v_state TEXT; v_message TEXT; v_detail TEXT; v_hint TEXT; v_context TEXT; v_result json; v_dept record; BEGIN SELECT * INTO v_dept FROM jsonb_to_record(department_data) AS dep( id int, code text, "name" text, group_id int, post_type char, dept_type char, require_info bool, taxes varchar[], enabled bool, tax_code text, itemizers text[], vat_effect bool ); IF (v_dept.id is null) or (v_dept.id < 1) THEN INSERT INTO department as D (code, "name", group_id, post_type, dept_type, require_info, taxes, enabled, tax_code, itemizers, vat_effect) VALUES (v_dept.code, v_dept."name", v_dept.group_id, $1, v_dept.dept_type, v_dept.require_info, v_dept.taxes, v_dept.enabled, v_dept.tax_code, v_dept.itemizers, v_dept.vat_effect) RETURNING to_json(d) INTO v_result; ELSE UPDATE department d SET code = v_dept.code, "name" = v_dept.name, group_id = v_dept.group_id, dept_type = v_dept.dept_type, require_info = v_dept.require_info, taxes = v_dept.taxes, enabled = v_dept.enabled, tax_code = v_dept.tax_code, itemizers = v_dept.itemizers, vat_effect = v_dept.vat_effect WHERE d.id = v_dept.id RETURNING to_json(d) 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_json', department_data); END