<commit_before>CREATE OR REPLACE FUNCTION import_assessment() RETURNS VOID AS
$$
DECLARE
    r RECORD;
BEGIN
    -- first try to update the key
    UPDATE tb_assessment as www
    SET name = regexp_replace(ems.name, '\s+', ' ', 'g'),
        sex = ems.gender::sex,
        status = ems.active,
        mt = ems.mt::school_moi,
        dateofjoining = ems.doj::date,
        type = ems.staff_type
    FROM ems_tb_assessment as ems
    WHERE www.id=ems.id AND
        (
            lower(regexp_replace(www.name, '\s+', ' ', 'g')) <> lower(regexp_replace(ems.name, '\s+', ' ', 'g')) OR
            www.pid <> ems.programme_id OR
            www.start <> ems.start_date OR
            www.end <> ems.end_date
        );

    RAISE NOTICE '%', found;

    -- not there, so try to insert the key
    -- if someone else inserts the same key concurrently,
    -- we could get a unique-key failure
    FOR r IN
        SELECT ems_tb_assessment.id AS ems_id,
               tb_assessment.id AS www_id
        FROM ems_tb_assessment
        LEFT JOIN tb_assessment ON ems_tb_assessment.id=tb_assessment.id
        WHERE tb_assessment.id IS NULL
    LOOP
        INSERT INTO tb_assessment(id, name, pid, start, end) SELECT
            ems.id,
            regexp_replace(ems.name, '\s+', ' ', 'g'),
            ems.programme_id,
            ems.start_date,
            ems.end_date
        FROM ems_tb_assessment as ems
        WHERE ems.id = r.ems_id;
    END LOOP;
    RETURN;
END;<commit_msg>fixed assessment import script<commit_after>CREATE OR REPLACE FUNCTION import_assessment() RETURNS VOID AS
$$
DECLARE
    r RECORD;
BEGIN
    -- first try to update the key
    UPDATE tb_assessment as www
    SET name = regexp_replace(ems.name, '\s+', ' ', 'g'),
        pid = ems.programme_id,
        start = ems.start_date,
        "end" = ems.end_date
    FROM ems_tb_assessment as ems
    WHERE www.id=ems.id AND
        (
            lower(regexp_replace(www.name, '\s+', ' ', 'g')) <> lower(regexp_replace(ems.name, '\s+', ' ', 'g')) OR
            www.pid <> ems.programme_id OR
            www.start <> ems.start_date OR
            www.end <> ems.end_date
        );

    RAISE NOTICE '%', found;

    -- not there, so try to insert the key
    -- if someone else inserts the same key concurrently,
    -- we could get a unique-key failure
    FOR r IN
        SELECT ems_tb_assessment.id AS ems_id,
               tb_assessment.id AS www_id
        FROM ems_tb_assessment
        LEFT JOIN tb_assessment ON ems_tb_assessment.id=tb_assessment.id
        WHERE tb_assessment.id IS NULL
    LOOP
        INSERT INTO tb_assessment(id, name, pid, start, "end") SELECT
            ems.id,
            regexp_replace(ems.name, '\s+', ' ', 'g'),
            ems.programme_id,
            ems.start_date,
            ems.end_date
        FROM ems_tb_assessment as ems
        WHERE ems.id = r.ems_id;
    END LOOP;
    RETURN;
END;
{'fraction_non_alphanumeric': 0.102880658436214, 'fraction_numerical': 0.0, 'mean_word_length': 2.025933609958506, 'pattern_counts': {'":': 0, '<': 11, '<?xml version=': 0, '>': 11, 'https://': 0, 'lorem ipsum': 0, 'www.': 12, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '4648439', 'n_tokens_mistral': 1017, 'n_tokens_neox': 959, 'n_words': 262}