Here we trying to fix logs that have overlaps with each other. Am I doing it right? Is there any way to improve/refactor it?
DROP TYPE IF EXISTS logs_with_error;
CREATE TYPE logs_with_error AS (
model_id INT,
model_type TEXT,
initiator_id INT,
initiator_type TEXT,
error_type_id INT,
state INT
);
CREATE OR REPLACE FUNCTION normalize_moderation_log_overflow() RETURNS INTEGER AS
$BODY$
DECLARE
logs logs_with_error%rowtype;
model moderation_logs%rowtype;
initiator moderation_logs%rowtype;
BEGIN
FOR logs IN SELECT model_id, model_type, initiator_id, initiator_type, error_type_id, state
FROM errors
WHERE state = #{ Error.states[:initial] }
AND error_type_id = (SELECT id FROM error_types WHERE normalizer_type = 'moderation_log_overflow')
LOOP
SELECT * INTO model FROM moderation_logs WHERE id = logs.model_id;
SELECT * INTO initiator FROM moderation_logs WHERE id = logs.initiator_id;
IF model.curr_state = initiator.curr_state AND model.next_state = initiator.next_state THEN
IF model.ended_at < initiator.ended_at THEN
UPDATE moderation_logs SET ended_at = initiator.ended_at WHERE id = model.id;
END IF;
DELETE FROM moderation_logs WHERE id = initiator.id;
ELSE
UPDATE moderation_logs SET started_at = model.ended_at WHERE id = initiator.id;
END IF;
UPDATE errors
SET state = #{ Error.states[:resolved] };
RAISE NOTICE '%', logs;
END LOOP;
RETURN 1;
END
$BODY$
LANGUAGE 'plpgsql' ;