authen.public

Parameters

Name	Type	Mode
p_interface_code	text	IN
p_property_id	uuid	IN
p_interface_code	text	IN
p_property_id	uuid	IN
p_conn_string	text	IN
o_deleted	integer	OUT
o_sync_deleted	integer	OUT

Definition

DECLARE
    v_conn_name text;
    v_result    text;
BEGIN
    o_deleted      := 0;
    o_sync_deleted := 0;
    v_conn_name    := 'clear_rate_' || gen_random_uuid()::text;

    BEGIN
        EXECUTE format('SELECT dblink_connect(%L, %L)', v_conn_name, p_conn_string);

        v_result := dblink_exec(v_conn_name, format($SQL$
            DELETE FROM public.intf_rate_state
            WHERE interface_code = %L
              AND property_id    = %L::uuid
        $SQL$, p_interface_code, p_property_id));

        o_deleted := COALESCE(split_part(v_result, ' ', 2)::int, 0);

        EXECUTE format('SELECT dblink_disconnect(%L)', v_conn_name);

    EXCEPTION WHEN OTHERS THEN
        BEGIN
            EXECUTE format('SELECT dblink_disconnect(%L)', v_conn_name);
        EXCEPTION WHEN OTHERS THEN NULL;
        END;
        RAISE EXCEPTION 'Failed to clear intf_rate_state on property DB: %', SQLERRM;
    END;

    -- ลบ sync_stamp บน auth DB
    DELETE FROM public.sync_stamp
    WHERE service_code = p_interface_code
      AND property_id  = p_property_id;

    GET DIAGNOSTICS o_sync_deleted = ROW_COUNT;
END;

sp_clear_intf_rate_state

Parameters

Definition