CREATE OR REPLACE FUNCTION merge_db() RETURNS TRIGGER AS
$$
BEGIN
    LOOP
        -- first try to update the key
        UPDATE rufiji_individual SET "fullName" = NEW."NAME","sex" = NEW."SEX",dob = NEW."BIRTH_DATE","insertDate"= NEW."ENTRY_DATE",
		"dataStatus"= NEW."STATUS_DAT","relationToHead"= NEW."RLTN_HEAD", occupation=NEW."KAZI","occupationOther" = NEW."NYINGINE",education=NEW."EDUCATION",
		"entryType" = NEW."ENTRY_TYPE", "entryDate" = NEW."ENTRY_DATE","exitType" = NEW."EXIT_TYPE","exitDate"= NEW."EXIT_DATE","maritalStatus"= NEW."NDOA",
		"maritalStatusOther" = NEW."NDOA_ING" WHERE  "externalID" = NEW."PERM_ID";
        IF found THEN
        raise notice 'found!!!!';
            RETURN NEW;
        END IF;
        -- not there, so try to insert the key
        -- if someone else inserts the same key concurrently,
        -- we could get a unique-key failure
        BEGIN
            INSERT INTO rufiji_individual(uuid,"fullName",sex) VALUES ((select uuid_generate_v1mc()),NEW."NAME",NEW."SEX");
            RETURN NEW;
        EXCEPTION WHEN unique_violation THEN
            -- do nothing, and loop to try the UPDATE again
        END;
    END LOOP;
END;
$$
LANGUAGE plpgsql;