aonestar.public

Parameters

Name	Type	Mode
post_type	character	IN (DEFAULT 'P')

Definition

DECLARE
    v_result json;
    v_msg_text text;
    v_sqlstate text;
    v_detail text;
    v_hint text;
    v_context text;
BEGIN    
    WITH dept_data AS (
        SELECT
            d.id,
            d.code,
            d.name,
            dg.id as group_id,
            dg.name AS group,
            d.dept_type,
            d.require_info,
            d.taxes,
            d.enabled,
            d.itemizers,
            d.vat_effect
        FROM department d LEFT JOIN department_group dg ON d.group_id = dg.id
        WHERE d.post_type = $1
        ORDER BY d.code collate numeric asc
    )
    SELECT json_agg(dept_data) FROM dept_data 
    INTO v_result;
   
    RETURN coalesce(v_result, '[]'::json);
EXCEPTION
    WHEN OTHERS THEN
        GET STACKED DIAGNOSTICS
            v_msg_text = message_text,
            v_sqlstate = returned_sqlstate,
            v_detail = pg_exception_detail,
            v_hint = pg_exception_hint,
            v_context = pg_exception_context;
            
        RETURN fn_handle_error(
            v_sqlstate,
            v_msg_text,
            v_detail,
            v_hint,
            v_context,
            'sp_get_department_json',
            null::jsonb
        );
END;