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_inconsistencyexternal_id_max_created_date_table ON COMMIT DROP
        AS
        SELECT rl.external_id, MAX(rl.matchedcreated_id, rl.generation, mi.value, mi.versiondate) AS last_creation_date
         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_versionexternal_id
                 FROM {tenantId}_mod_source_record_storage.records_lb rl,
             
        {tenantId}_mod_source_record_storage.marc_indexers mi                  WHERE rl.idstate = 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, generationGROUP BY external_id, generation
                 HAVING COUNT(external_id) > 1
            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,
    ) AS external_id_of_duplicates_with_same_generation
        value_matched_id_inconsistency val_match
        WHERE rl.matchedexternal_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_genexternal_id_of_duplicates_with_same_generation.external_id
        GROUP                                WHERE matched_gen.matched_id = rec.value::uuid);

                if updated_generation is null then
                    updated_generation := 0;
       by rl.external_id;

       end if;

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

                UPDATE matchedFROM external_id_generationmax_table matched_gen
                SET max_gen = updated_generation + 1
      created_date_table
         WHERE matched_genr.matchedexternal_id = rec.value::uuid;
            END LOOP;

        -- update records with greatest generation to 'ACTUAL' state
        UPDATE {tenantId}_mod_source_record_storage.records_lb rlexternal_id_max_created_date_table.external_id
         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 - 1AND r.created_date < external_id_max_created_date_table.last_creation_date;

    END;
$$
LANGUAGE plpgsql;

DO
$$
    BEGIN
        CREATE TEMP TABLE external_id_max_createdgeneration_date_table ON COMMIT DROP AS
        SELECT rlr.external_id, MAX(rlr.created_dategeneration) AS lastmax_creation_dategeneration
        FROM {tenantId}_mod_source_record_storage.records_lb rlr,
             (
                 SELECT external_id
                 FROM {tenantId}_mod_source_record_storage.records_lb
                 WHERE state = 'ACTUAL'
                 GROUP BY external_id,
generation                  HAVING COUNT(external_id) > 1
             ) AS external_id_of_duplicates_with_same_generation
        WHERE rlr.external_id = external_id_of_duplicates_with_same_generation.external_id
        GROUP byBY rlr.external_id;

        UPDATE {tenantId}_mod_source_record_storage.records_lb r
        SET state = 'OLD'
        FROM external_id_max_created_dategeneration_table
        WHERE r.external_id = external_id_max_created_dategeneration_table.external_id
          AND r.created_dategeneration < external_id_max_createdgeneration_date_table.lastmax_creation_dategeneration;

    END;
$$;

DO
$$
    BEGIN
        CREATE TEMP TABLE externalvalue_matched_id_max_generation_tableinconsistency ON COMMIT DROP AS
        SELECT rl.id, rrl.externalmatched_id, MAX(r.generation) AS max_generation
 mi.value
        FROM {tenantId}_mod_source_record_storage.records_lb rrl,
             (
{tenantId}_mod_source_record_storage.marc_indexers mi,
             {tenantId}_mod_source_record_storage.marc_records_tracking rt
    SELECT external    WHERE rt.marc_id = mi.marc_id and rt.version = mi.version
          AND rl.id FROM {tenantId}_mod_source_record_storage.records_lb
 = mi.marc_id AND rl.state='ACTUAL'
          AND mi.field_no='999' AND mi.ind1='f'  WHERE state = 'ACTUAL'
 AND mi.ind2='f' AND mi.subfield_no='s' AND rl.matched_id::text != mi.value;

        CREATE TEMP TABLE matched_id_max_generation_table ON COMMIT GROUPDROP BYAS
external_id        SELECT rl.matched_id, MAX(rl.generation) AS max_gen
     HAVING COUNT(external_id) > 1
 FROM {tenantId}_mod_source_record_storage.records_lb rl,
           ) AS externalvalue_matched_id_ofinconsistency val_duplicatesmatch
        WHERE rrl.externalmatched_id = external_id_of_duplicates.external_idval_match.value::uuid
        GROUP BY rrl.externalmatched_id;

        UPDATE {tenantId}_mod_source_record_storage.records_lb r
        SET statematched_id = 'OLD' val_match.value::uuid,
            generation FROM= externalmatched_id_max_generation_table.max_gen + 1
       WHERE r.external FROM value_matched_id = external_inconsistency val_match, matched_id_max_generation_table.external_id
        WHERE r.id = val_match.id AND r.generation < externalval_match.value::uuid = matched_id_max_generation_table.maxmatched_generationid;
    END;
$$;