aonestar
.public
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
sp_save_reservation_more_guest_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_limit_more_guest int4; v_register record; v_current_more_guest int4; v_register_id int4; v_guest_id int4; -------------------------------------------- messages jsonb = $${ "70001": "Limit add moreguest", "79999": "Moreguest already exists" }$$; -------------------------------------------- begin /* { "data" : { "register_id" : 1, "guest_id" : 2 } } */ v_register_id := (p_data -> 'data' ->> 'register_id')::int4; v_guest_id := (p_data -> 'data' ->> 'guest_id')::int4; -- เช็คจำนวนคนไม่ให้เกิน ถ้าไม่เกิน ก่อ insert ได้ -- กรอง status = I select r.* into v_register from registration r where r.status = 'I' and r.id = v_register_id; if found then -- เจอการ check in v_limit_more_guest := v_register.adult + v_register.child + v_register.infant; select count(1) into v_current_more_guest from register_guests rg where rg.register_id = v_register_id; v_current_more_guest := v_current_more_guest + 1; -- บวกตัวเองเป็น guest if v_current_more_guest < v_limit_more_guest then if exists ( select 1 from register_guests where register_id = v_register_id and guest_id = v_guest_id ) then perform sp_raise_error('79999', messages->>'79999'); else insert into register_guests ( register_id , guest_id ) values ( v_register_id , v_guest_id ); return fn_result_success(v_result); end if; end if; -- else end if; -- return fn_result_warning(v_result); perform sp_raise_error('70001', messages->>'70001'); 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_reservation_more_guest_json', null ); end;