aonestar
.public
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
sp_save_mealtype_json
Parameters
Name
Type
Mode
p_data
jsonb
IN
Definition
declare v_result json; v_msg_text text; v_sqlstate text; v_detail text; v_hint text; v_context text; v_meal_type record; v_mealtype_id int; v_name text; v_abf numeric(15,2); v_abf_child numeric(15,2); v_abf_extra_adult numeric(15,2); v_abf_extra_child numeric(15,2); v_lunch numeric(15,2); v_lunch_child numeric(15,2); v_lunch_extra_adult numeric(15,2); v_lunch_extra_child numeric(15,2); v_dinner numeric(15,2); v_dinner_child numeric(15,2); v_dinner_extra_adult numeric(15,2); v_dinner_extra_child numeric(15,2); v_enabled boolean; /* ตัวอย่างค่าจาก input parameter { "name": "TestMealType", "abf": 25, "abf_child": 25, "abf_extra_adult": 25, "abf_extra_child": 25, "lunch": 25, "lunch_child": 25, "lunch_extra_adult": 25, "lunch_extra_child": 25, "dinner": 25, "dinner_child": 25, "dinner_extra_adult": 25, "dinner_extra_child": 25, "enabled": true } **/ begin perform sp_log_debug('sp_save_mealtype_json', p_data::text, true); v_name := NULLIF(p_data ->> 'name', ''); -- Handle empty strings by converting them to NULL before casting to numeric v_abf := NULLIF(p_data ->> 'abf', '')::numeric(15,2); v_abf_child := NULLIF(p_data ->> 'abf_child', '')::numeric(15,2); v_abf_extra_adult := NULLIF(p_data ->> 'abf_extra_adult', '')::numeric(15,2); v_abf_extra_child := NULLIF(p_data ->> 'abf_extra_child', '')::numeric(15,2); v_lunch := NULLIF(p_data ->> 'lunch', '')::numeric(15,2); v_lunch_child := NULLIF(p_data ->> 'lunch_child', '')::numeric(15,2); v_lunch_extra_adult := NULLIF(p_data ->> 'lunch_extra_adult', '')::numeric(15,2); v_lunch_extra_child := NULLIF(p_data ->> 'lunch_extra_child', '')::numeric(15,2); v_dinner := NULLIF(p_data ->> 'dinner', '')::numeric(15,2); v_dinner_child := NULLIF(p_data ->> 'dinner_child', '')::numeric(15,2); v_dinner_extra_adult := NULLIF(p_data ->> 'dinner_extra_adult', '')::numeric(15,2); v_dinner_extra_child := NULLIF(p_data ->> 'dinner_extra_child', '')::numeric(15,2); -- For boolean, check if it's a valid boolean value v_enabled := CASE WHEN p_data ->> 'enabled' IS NULL OR p_data ->> 'enabled' = '' THEN NULL ELSE (p_data ->> 'enabled')::boolean END; insert into meal_type( "name", abf, abf_child, abf_extra_adult, abf_extra_child, lunch, lunch_child, lunch_extra_adult, lunch_extra_child, dinner, dinner_child, dinner_extra_adult, dinner_extra_child, enabled ) values( v_name, v_abf, v_abf_child, v_abf_extra_adult, v_abf_extra_child, v_lunch, v_lunch_child, v_lunch_extra_adult, v_lunch_extra_child, v_dinner, v_dinner_child, v_dinner_extra_adult, v_dinner_extra_child, v_enabled ); return fn_result_success(); 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_save_mealtype_json', null ); END;