aonestar
.public
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
sp_commit_rate_state
Parameters
Name
Type
Mode
i_trace_id
uuid
IN
i_conn_string
text
IN
Definition
DECLARE v_interface_code text; v_property_id uuid; v_state_rows jsonb; v_rows int := 0; v_conn_name text; BEGIN v_conn_name := 'commit_rate_' || i_trace_id::text; IF i_conn_string IS NULL THEN RAISE EXCEPTION 'No property connection string provided'; END IF; BEGIN EXECUTE format('SELECT dblink_connect(%L, %L)', v_conn_name, i_conn_string); -- อ่านข้อมูลจาก aonestar.intf_sync_channel_logs SELECT l.interface_code, (l.params->>'property_id')::uuid, (l.metadata->'state_rows') INTO v_interface_code, v_property_id, v_state_rows FROM dblink(v_conn_name, format($SQL$ SELECT interface_code, params, metadata FROM intf_sync_channel_logs WHERE trace_id = '%s' ORDER BY id DESC LIMIT 1 $SQL$, i_trace_id)) AS l(interface_code text, params jsonb, metadata jsonb); -- ปิด connection EXECUTE format('SELECT dblink_disconnect(%L)', v_conn_name); EXCEPTION WHEN OTHERS THEN -- ปิด connection ถ้ามี error BEGIN EXECUTE format('SELECT dblink_disconnect(%L)', v_conn_name); EXCEPTION WHEN OTHERS THEN NULL; END; RAISE EXCEPTION 'Failed to read from remote intf_sync_channel_logs: %', SQLERRM; END; IF v_interface_code IS NULL OR v_property_id IS NULL OR v_state_rows IS NULL OR jsonb_typeof(v_state_rows) <> 'array' OR jsonb_array_length(v_state_rows) = 0 THEN RETURN 0; END IF; INSERT INTO public.intf_rate_state( interface_code, property_id, rate_plan_id, rate_date, rate, updated_at ) SELECT v_interface_code, v_property_id, x.rate_plan_id, x.rate_date, x.rate, now() FROM jsonb_to_recordset(v_state_rows) AS x( rate_plan_id text, rate_date date, rate int ) ON CONFLICT (interface_code, property_id, rate_plan_id, rate_date) DO UPDATE SET rate = EXCLUDED.rate, updated_at = now(); GET DIAGNOSTICS v_rows = ROW_COUNT; RETURN v_rows; END;