Page Comparison

DO
$$
    DECLARE
        rec record;
        updated_generation numeric := 0;
    BEGIN

--  temporary table is designated to hold records when matched_id and 999 ff $s values are mismatched
        CREATE TEMP TABLE value_matched_id_inconsistency ON COMMIT DROP
        AS
        SELECT rl.id, rl.matched_id, rl.generation, mi.value, mi.version
        FROM {tenantId}_mod_source_record_storage.records_lb rl,
             {tenantId}_mod_source_record_storage.marc_indexers mi,
             (
                 SELECT mi.value, max(mi.version) AS max_version
                 FROM {tenantId}_mod_source_record_storage.records_lb rl,
                      {tenantId}_mod_source_record_storage.marc_indexers mi
                 WHERE rl.id = mi.marc_id AND rl.state='ACTUAL'
                   AND mi.field_no='999' AND mi.ind1='f' AND mi.ind2='f' AND mi.subfield_no='s' AND rl.matched_id::text != mi.value
                 GROUP BY  mi.value
             ) AS nested_query
        WHERE rl.id = mi.marc_id AND rl.state='ACTUAL' AND mi.value = nested_query.value AND mi.field_no='999' AND mi.ind1='f' AND mi.ind2='f' AND mi.subfield_no='s' AND rl.matched_id::text != mi.value AND mi.version = nested_query.max_version;

--  temporary table (matched_id_generation_table) is for holding pair matched_id, generation
        CREATE TEMP TABLE matched_id_generation_table ON COMMIT DROP AS
        SELECT rl.matched_id, MAX(rl.generation)+1 AS max_gen
        FROM {tenantId}_mod_source_record_storage.records_lb rl,
             value_matched_id_inconsistency val_match
        WHERE rl.matched_id::text = val_match.value
        GROUP by rl.matched_id;

-- iteration through records with mismatched matched_id and value
        FOR rec IN SELECT * FROM value_matched_id_inconsistency
            LOOP
                updated_generation := (SELECT max_gen
                                       FROM matched_id_generation_table matched_gen
                                       WHERE matched_gen.matched_id = rec.value::uuid);

                if updated_generation is null then
                    updated_generation := 0;
                end if;

                UPDATE {tenantId}_mod_source_record_storage.records_lb rl
                SET
                    generation = updated_generation,
                    matched_id = rec.value::uuid,
                    state = 'OLD'
                WHERE rl.id = rec.id;

                UPDATE matched_id_generation_table matched_gen
                SET max_gen = updated_generation + 1
                WHERE matched_gen.matched_id = rec.value::uuid;
            END LOOP;

        -- update records with greatest generation to 'ACTUAL' state
        UPDATE {tenantId}_mod_source_record_storage.records_lb rl
        SET state='ACTUAL'
        FROM matched_id_generation_table
        WHERE rl.matched_id = matched_id_generation_table.matched_id AND rl.generation = matched_id_generation_table.max_gen - 1;

    END;
$$
LANGUAGE plpgsql;