Homec4science
Diffusion MedCo Deployment (master) Browse

medco-deployment/documentation/duplicate_i2b2_db.plpgsql
dee810945ff7master

Last Change
Raw File

duplicate_i2b2_db.plpgsql
View Options

-- pl/pgsql function to duplicate data in i2b2
-- warning: makes assumption the loading tool of medco was previously used
CREATE FUNCTION medco_data_duplication(nb_repetitions integer) RETURNS VOID AS $$
DECLARE
p_num_seq integer;
patient_row RECORD;
obs_fact RECORD;
max_search_idx integer;
BEGIN
SET search_path TO i2b2demodata;
-- get highest patient_num
select max(patient_num) into p_num_seq from patient_dimension;
p_num_seq = p_num_seq + 1;
-- update text_search_index sequence
select max(text_search_index) into max_search_idx from observation_fact;
max_search_idx = max_search_idx + 1;
PERFORM setval('observation_fact_text_search_index_seq', max_search_idx);
-- add data
FOR patient_row IN select * from patient_dimension LOOP
RAISE NOTICE 'patient nb is %', patient_row.patient_num;
FOR i IN 1..nb_repetitions LOOP
insert into patient_dimension(patient_num, import_date, upload_id, enc_dummy_flag_cd)
values (p_num_seq, patient_row.import_date, patient_row.upload_id, patient_row.enc_dummy_flag_cd);
insert into patient_mapping(patient_ide, patient_ide_source, patient_num, patient_ide_status, project_id, import_date, upload_id)
values(p_num_seq, 'HIVE', p_num_seq, 'A', 'HIVE', NOW(), '1');
insert into visit_dimension(encounter_num, patient_num, import_date, sourcesystem_cd, upload_id)
values(p_num_seq, p_num_seq, patient_row.import_date, patient_row.sourcesystem_cd, patient_row.upload_id);
insert into encounter_mapping(encounter_ide, encounter_ide_source, project_id, encounter_num, patient_ide, patient_ide_source, encounter_ide_status, import_date, upload_id)
values(p_num_seq, 'HIVE', 'HIVE', p_num_seq, p_num_seq, 'HIVE', 'A', NOW(), '1');
FOR obs_fact IN select * from observation_fact where patient_num = patient_row.patient_num LOOP
insert into observation_fact (encounter_num, patient_num, concept_cd, provider_id, start_date, modifier_cd, instance_num, location_cd, import_date, upload_id)
values (p_num_seq, p_num_seq, obs_fact.concept_cd, obs_fact.provider_id, obs_fact.start_date, obs_fact.modifier_cd, obs_fact.instance_num, obs_fact.location_cd, obs_fact.import_date, obs_fact.upload_id);
END LOOP;
p_num_seq = p_num_seq + 1;
END LOOP;
END LOOP;
END;
$$ LANGUAGE plpgsql;
c4science · Help