dnl Copyright (C) 2013 The Meme Factory, Inc. http://www.meme.com/ dnl dnl This file is part of Gombe-MI. dnl dnl Gombe-MI is free software; you can redistribute it and/or modify dnl it under the terms of the GNU General Public License as published by dnl the Free Software Foundation; either version 3 of the License, or dnl (at your option) any later version. dnl dnl This program is distributed in the hope that it will be useful, dnl but WITHOUT ANY WARRANTY; without even the implied warranty of dnl MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the dnl GNU General Public License for more details. dnl dnl You should have received a copy of the GNU General Public License dnl along with Gombe-MI. If not, see . dnl dnl Function to convert from access table structure to new table structure. dnl dnl Karl O. Pinc dnl m4 includes include(`copyright.m4') include(`constants.m4') include(`types.m4') include(`macros.m4')dnl dnl Reset our search path so we pick up the conversion schema. define(`gmi_function_search_path', `prepared_data, gombemi, pg_temp') dnl The animid used for baboons. define(`_baboon_id', `NYA') dnl The behavior code for eating. define(`_ca_eating', `UE') dnl Ugly hack to generate events.position dnl dnl syntax _gen_pos(behavior) dnl dnl behavior The behavior code. dnl changequote({,}) define({_gen_pos},{(SELECT CASE WHEN position THEN '9' ELSE NULL END FROM behaviors WHERE behavior = $1)})dnl changequote(`,')dnl CREATE OR REPLACE FUNCTION prepared_data.convert_access(fromdate DATE , todate DATE) RETURNS INT LANGUAGE plpgsql SECURITY DEFINER gmi_function_set_search_path AS $$ -- Copy access data structures into new gombemi data structures. -- -- GPL_notice(` --', `2013', `The Meme Factory, Inc. http://www.meme.com/') -- -- Syntax: select convert_access(fromdate, todate); -- -- Arguments: -- fromdate Date to start copy (inclusive) -- todate Date to stop copy (inclusive) -- -- Remarks: -- One-off program. Once it works we won't run it again. -- -- Copies a day a a time. -- -- Stops as soon as a transaction fails. DECLARE converted INT := 0; this_enterer people.person%TYPE; this_collector people.person%TYPE; this_followtype TEXT; tmp_followtype TEXT; this_entrydate DATE; this_sheetid sheets.sheetid%TYPE; this_ffid fieldfollows.ffid%TYPE; this_page INT; this_location TEXT; last_page INT; interval_comment TEXT; this_devbeh TEXT; this_uncertain BOOLEAN; this_baboon BOOLEAN; this_follow RECORD; this_1_min RECORD; this_1_min_infant RECORD; this_1_min_mom RECORD; this_1_min_sib RECORD; default_mom_uncertain BOOLEAN; default_infant_uncertain BOOLEAN; default_sib_uncertain BOOLEAN; behaviors TEXT[]; old_behavior TEXT; new_behavior TEXT; event_behavior TEXT[]; event_uncertain BOOLEAN[]; event_baboon BOOLEAN[]; event_comment TEXT[]; animids TEXT[]; animid_cnt INT; animid TEXT; part1_parts TEXT[]; part1_participants TEXT[]; part2_parts TEXT[]; part2_participants TEXT[]; grooming_found BOOLEAN; play_found BOOLEAN; multi_found BOOLEAN; grooming_and_play BOOLEAN; -- Did mom's grooming or play partner get changed to UNK? gp_unknown BOOLEAN; grooms_other_converted BOOLEAN; groomed_by_converted BOOLEAN; plays_converted BOOLEAN; eventcnt INT; alen INT; old_voc TEXT; new_voc TEXT; this_voc_uncertain BOOLEAN; this_distance TEXT; this_play TEXT; this_participant TEXT; this_part TEXT; this_part1 parts.part%TYPE; this_part2 parts.part%TYPE; this_error BOOLEAN; -- Store duplicate entries. define(`MAX_BEHAVIOR_LEN', `25')dnl infant_mom_behaviors VARCHAR(MAX_BEHAVIOR_LEN)[]; infant_mom_uncertains BOOLEAN[]; infant_mom_events INT[]; infant_sib_behaviors VARCHAR(MAX_BEHAVIOR_LEN)[]; infant_sib_uncertains BOOLEAN[]; infant_sib_events INT[]; mom_sib_behaviors VARCHAR(MAX_BEHAVIOR_LEN)[]; mom_sib_uncertains BOOLEAN[]; mom_sib_events INT[]; sibling_found BOOLEAN; i_found BOOLEAN; behavior_cnt INT; past_first BOOLEAN; BEGIN IF current_database() = 'gombemi_raw' THEN -- Make all the chimpids that don't already exist. INSERT INTO chimpids (animid, chimpid) SELECT biography_data.animid AS animid , biography_data.animid AS chimpid FROM biography_data WHERE NOT EXISTS ( SELECT 1 FROM chimpids AS this_id WHERE this_id.animid = biography_data.animid LIMIT 1); END IF; FOR this_follow IN SELECT * FROM follow WHERE fromdate <= follow.follow_date AND follow.follow_date <= todate ORDER BY follow.follow_date, follow.follow_id LOOP -- Check for already converted. IF this_follow.done THEN RAISE EXCEPTION 'Date %: Mom %: follow row lready converted' , this_follow.follow_date , this_follow.mother_id; END IF; -- Create the follow. INSERT INTO follows (date) VALUES(this_follow.follow_date); -- Create the followparts rows. -- Create a followparts row for the mom. INSERT INTO followparts (followid, chimpid, role) VALUES(currval('follows_followid_seq'::regclass) , this_follow.mother_id , 'M'); -- Create a followparts row for the infant. INSERT INTO followparts (followid, chimpid, role) VALUES(currval('follows_followid_seq'::regclass) , this_follow.infant_id , 'I'); IF this_follow.sibling_id IS NOT NULL THEN -- Create a followparts row for the sibling. INSERT INTO followparts (followid, chimpid, role) VALUES(currval('follows_followid_seq'::regclass) , this_follow.sibling_id , 'S'); END IF; -- The fieldfollow, access data has 1 per follow. -- Administrative info: collector, enterer, etc. -- Find the enterer SELECT people.person INTO this_enterer FROM people WHERE people.descr = this_follow.entered_by; IF NOT FOUND THEN RAISE EXCEPTION integrity_constraint_violation USING MESSAGE = 'Cannot lookup enterer for follow on PEOPLE' , DETAIL = 'Value (Follow_id) = (' || this_follow.follow_id || '): Value (Entered_by) = (' || this_follow.entered_by || ')'; END IF; -- Find the type and entry date of the follow. SELECT crfp.end_entry_date, TRIM(BOTH FROM crfp.temptype) INTO this_entrydate, this_followtype FROM crfp WHERE crfp.follow_date = this_follow.follow_date AND crfp.focal_mom = this_follow.mother_id; IF NOT FOUND THEN RAISE EXCEPTION integrity_constraint_violation USING MESSAGE = 'Cannot lookup entrydate or template type for follow on cross_reference_forprogram' , DETAIL = 'Value (Follow_id) = (' || this_follow.follow_id || '): Value (Entered_by) = (' || this_follow.entered_by || ')'; END IF; -- Find the collector SELECT people.person INTO this_collector FROM people WHERE people.descr = this_follow.conducted_by; IF NOT FOUND THEN RAISE EXCEPTION integrity_constraint_violation USING MESSAGE = 'Cannot lookup collecor for follow on PEOPLE' , DETAIL = 'Value (Follow_id) = (' || this_follow.follow_id || '): Value (Conducted_by) = (' || this_follow.conducted_by || ')'; END IF; -- Create the fieldfollow. INSERT INTO fieldfollows (collector , entrydate , enterer , uploaddate) VALUES(this_collector , this_entrydate , this_enterer , current_date); this_ffid := CURRVAL('fieldfollows_ffid_seq'::REGCLASS); -- The columns we've not converted: -- follow: -- Infant2_id | Sibling2_id | Sheet_id | Left_group_id | Right_group_id | Follow_status | Follow_id | Time_mode | AMPM -- tableid | Follow_date | Mother_id | Infant_id | Infant2_id | Sibling_id | Sibling2_id | Conducted_by | Entered_by | Sheet_id | Left_group_id | Right_group_id | Follow_status | Follow_id | Time_mode | AMPM -- 1 | 2010-11-03 | GA | GGL | | | | Juma Mazogo | Karen Anderson | 1 | 29 | 61 | Completed | 11/03/2010_GA | SWA | AM -- -- Go through all the 1 minute intervals for the follow. -- last_page := NULL; FOR this_1_min IN SELECT * FROM follow_1_minute WHERE follow_1_minute.follow_id = this_follow.follow_id ORDER BY follow_1_minute.interval_time LOOP -- Check for already converted. IF this_1_min.done THEN RAISE EXCEPTION 'Date %: Mom %: Time %: follow_1_minute row already converted' , this_follow.follow_date , this_follow.mother_id , this_1_min.interval_time; END IF; IF this_1_min.interval_time::DATE != this_follow.follow_date THEN RAISE EXCEPTION 'Follow_id %: Date %: Mom %: Time %: The date part of the follow_1_minute.interval_time value does not match the date of the follow' , this_follow.follow_id , this_follow.follow_date , this_follow.mother_id , this_1_min.interval_time; END IF; -- Get the page. SELECT follow_page.page_number, trim(both from follow_page.location) INTO this_page, this_location FROM follow_page WHERE follow_page.page_id = this_1_min.page_id; IF NOT FOUND THEN RAISE EXCEPTION 'Date %: Mom %: Time %: Page_id %: Cannot lookup page number from page_id' , this_follow.follow_date , this_follow.mother_id , this_1_min.interval_time , this_1_minute.page_id; END IF; IF last_page IS NULL OR this_page != last_page THEN last_page := this_page; -- "Regular" modern follows (date/mom combos) can have multiple -- researchers (aka collectors), but this does not happen in the -- access data so we don't need to worry about having the -- page number be unique per collector/breaking on collector. -- Remember the page is converted. UPDATE follow_page SET done = TRUE WHERE follow_page.page_id = this_1_min.page_id; -- Find the sheet on which the interval belongs. SELECT sheets.sheetid INTO this_sheetid FROM follows JOIN followparts ON (followparts.followid = follows.followid) JOIN intervals ON (intervals.followid = follows.followid) JOIN sheets ON (sheets.sheetid = intervals.sheetid) WHERE follows.date = this_follow.follow_date AND followparts.chimpid = this_follow.mother_id AND followparts.role = 'M' AND sheets.page = this_page LIMIT 1; IF NOT FOUND THEN -- Create a sheet, into which we will put this interval. -- Get the followtype. IF CHAR_LENGTH(this_followtype) > 1 then -- The follow is of a mixed type. -- Can't look at the cfrp for the follow type, look per page. BEGIN SELECT TRIM(BOTH FROM page_followtype.temptype) INTO STRICT tmp_followtype FROM page_followtype WHERE page_followtype.date = this_follow.follow_date AND page_followtype.page = this_page::TEXT AND page_followtype.mom = this_follow.mother_id; EXCEPTION WHEN TOO_MANY_ROWS THEN RAISE EXCEPTION integrity_constraint_violation USING MESSAGE = 'Template type on cross_reference_forprogram is longer than 1 character and there is more than one matching row on the page_followtype table' , DETAIL = 'Value (Follow_id) = (' || this_follow.follow_id || ')'; WHEN NO_DATA_FOUND THEN RAISE EXCEPTION integrity_constraint_violation USING MESSAGE = 'Template type on cross_reference_forprogram is longer than 1 character and there is not a matching row on the page_followtype table' , DETAIL = 'Value (Follow_id) = (' || this_follow.follow_id || ')'; END; -- Remember having looked at the followtype per page. UPDATE page_followtype SET done = TRUE WHERE page_followtype.date = this_follow.follow_date AND page_followtype.page = this_page::TEXT AND page_followtype.mom = this_follow.mother_id; ELSE -- one char followtype tmp_followtype := this_followtype; END IF; -- Create the sheets row. IF this_location = '' THEN this_location = NULL; END IF; INSERT INTO sheets (ffid, followtype, page, location) VALUES(this_ffid , tmp_followtype , this_page , this_location); this_sheetid = currval('sheets_sheetid_seq'::regclass); END IF; END IF; -- follow_1_minute: -- tableid | Page_id | Interval_number | Interval_time | Bad_observation | Rain | Comment | CommentRTF | Interval_id -- 1 | 11/28/2010_NUR_02 | 1 | 2010-11-28 07:15:00 | | | | | 11/28/2010_NUR_02_01 -- Clean up the comment interval_comment := TRIM(BOTH FROM this_1_min.comment); IF interval_comment = '' THEN interval_comment := NULL; END IF; -- Create the interval. INSERT INTO intervals (followid , sheetid , time , comment) VALUES (currval('follows_followid_seq'::regclass) , currval('sheets_sheetid_seq'::regclass) , this_1_min.interval_time , interval_comment); -- Bad observeration. default_mom_uncertain := FALSE; default_infant_uncertain := FALSE; default_sib_uncertain := FALSE; IF this_1_min.bad_observation IS NOT NULL THEN CASE this_1_min.bad_observation WHEN 'Y' THEN default_mom_uncertain := TRUE; default_infant_uncertain := TRUE; default_sib_uncertain := TRUE; WHEN 'M' THEN default_mom_uncertain := TRUE; WHEN 'I' THEN default_infant_uncertain := TRUE; WHEN 'S' THEN default_sib_uncertain := TRUE; WHEN 'M/I' THEN default_mom_uncertain := TRUE; default_infant_uncertain := TRUE; WHEN 'M/S' THEN default_mom_uncertain := TRUE; default_sib_uncertain := TRUE; WHEN 'I/S' THEN default_infant_uncertain := TRUE; default_sib_uncertain := TRUE; ELSE RAISE EXCEPTION 'Interval_id %: Date %: Time %: Bad observation %: The follow_1_minute Bad_observation value is unrecognized' , this_1_min.interval_id , this_follow.follow_date , this_1_min.interval_time , this_1_min.bad_observation; END CASE; END IF; -- Save the data that's supposedly duplicated in the access db. infant_mom_behaviors := ARRAY[]::VARCHAR(MAX_BEHAVIOR_LEN)[]; infant_mom_uncertains := ARRAY[]::BOOLEAN[]; infant_mom_events := ARRAY[]::INT[]; infant_sib_behaviors := ARRAY[]::VARCHAR(MAX_BEHAVIOR_LEN)[]; infant_sib_uncertains := ARRAY[]::BOOLEAN[]; infant_sib_events := ARRAY[]::INT[]; mom_sib_behaviors := ARRAY[]::VARCHAR(MAX_BEHAVIOR_LEN)[]; mom_sib_uncertains := ARRAY[]::BOOLEAN[]; mom_sib_events := ARRAY[]::INT[]; -- Get all the related 1 minute rows. SELECT * INTO this_1_min_infant FROM follow_1_minute_infant WHERE follow_1_minute_infant.interval_id = this_1_min.interval_id; -- follow_1_minute_infant: -- Grooms_other | Groomed_by | Plays | Cries | Vocalizations -- tableid | Interval_id | Infant_id | Nursing | Eating | Walks | Playing | Resting | In_contact | Within_reach | Within_0_5_meters | Between_5_15_meters | More_than_15_meters | Rides_on_stomach | Rides_on_back | Dangles | Contacts_other | Grooms_other | Groomed_by | Plays | Displays | Refused | Cries | Vocalizations | Begs | Aggression | Infant_interval_id -- 1 | 11/03/2010_GA_01_01 | GGL | | | | | | | | Y | | | | | | | | | GIZ | | | | | | | 11/03/2010_GA_01_01_GGL -- Infant sanity check. IF this_follow.infant_id != this_1_min_infant.infant_id THEN RAISE EXCEPTION 'Follow Infant_id %: Follow_1_minute_infant Infant_id %: Date %: Time %: The infant id in the follow does not match the infant id in the Follow_1_minute_infant' , this_follow.infant_id , this_1_min_infant.infant_id , this_follow.follow_date , this_1_min.interval_time; END IF; -- Check for already converted. IF this_1_min_infant.done THEN RAISE EXCEPTION 'Date %: Mom %: Infant %: follow_1_minute_infant row lready converted' , this_follow.follow_date , this_follow.mother_id , this_follow.infant_id; END IF; -- Nursing IF this_1_min_infant.nursing IS NOT NULL THEN CASE this_1_min_infant.nursing WHEN 'Y' THEN this_uncertain := default_infant_uncertain; WHEN '?' THEN this_uncertain := TRUE; ELSE RAISE EXCEPTION 'Follow Follow_id %: Follow_1_minute_infant Infant_id %: Date %: Time %: Nursing %: Nursing code unrecognized' , this_follow.follow_id , this_1_min_infant.infant_id , this_follow.follow_date , this_1_min.interval_time , this_1_min_infant.nursing; END CASE; -- Nursing event. INSERT INTO events (intid , behavior , position , baboon , uncertain , comment) VALUES (currval('intervals_intid_seq'::regclass) , 'N' , _gen_pos('N') , FALSE , this_uncertain , NULL); -- Nursing participants, mom. INSERT INTO parts (eid, part, participant) VALUES (CURRVAL('events_eid_seq'::regclass) , 'gmi_actee' , this_follow.mother_id); -- Nursing participants, infant. INSERT INTO parts (eid, part, participant) VALUES (CURRVAL('events_eid_seq'::regclass) , 'gmi_actor' , this_follow.infant_id); END IF; -- Eating IF this_1_min_infant.eating IS NOT NULL THEN CASE this_1_min_infant.eating WHEN 'Y' THEN this_uncertain := default_infant_uncertain; WHEN '?' THEN this_uncertain := TRUE; ELSE RAISE EXCEPTION 'Follow Follow_id %: Follow_1_minute_infant Infant_id %: Date %: Time %: Eating %: Eating code unrecognized' , this_follow.follow_id , this_1_min_infant.infant_id , this_follow.follow_date , this_1_min.interval_time , this_1_min_infant.eating; END CASE; INSERT INTO events (intid , behavior , position , baboon , uncertain , comment) VALUES (currval('intervals_intid_seq'::regclass) , '_ca_eating' , _gen_pos('_ca_eating') , FALSE , this_uncertain , NULL); -- Participant. INSERT INTO parts (eid, part, participant) VALUES (CURRVAL('events_eid_seq'::regclass) , 'gmi_self' , this_follow.infant_id); END IF; -- Walks IF this_1_min_infant.walks IS NOT NULL THEN CASE this_1_min_infant.walks WHEN 'Y' THEN this_uncertain := default_infant_uncertain; WHEN '?' THEN this_uncertain := TRUE; ELSE RAISE EXCEPTION 'Follow Follow_id %: Follow_1_minute_infant Infant_id %: Date %: Time %: Walks %: Walks code unrecognized' , this_follow.follow_id , this_1_min_infant.infant_id , this_follow.follow_date , this_1_min.interval_time , this_1_min_infant.walks; END CASE; INSERT INTO events (intid , behavior , position , baboon , uncertain , comment) VALUES (currval('intervals_intid_seq'::regclass) , 'T' , _gen_pos('T') , FALSE , this_uncertain , NULL); -- Participant. INSERT INTO parts (eid, part, participant) VALUES (CURRVAL('events_eid_seq'::regclass) , 'gmi_self' , this_follow.infant_id); END IF; -- Playing (solitary) IF this_1_min_infant.playing IS NOT NULL THEN CASE this_1_min_infant.playing WHEN 'Y' THEN this_play := 'Y'; this_uncertain := default_infant_uncertain; WHEN 'C' THEN this_play := 'C'; this_uncertain := default_infant_uncertain; WHEN 'D' THEN this_play := 'DP'; this_uncertain := default_infant_uncertain; WHEN 'O' THEN this_play := 'OP'; this_uncertain := default_infant_uncertain; WHEN 'D/O' THEN this_play := 'DO'; this_uncertain := default_infant_uncertain; WHEN 'D/C' THEN this_play := 'DC'; this_uncertain := default_infant_uncertain; WHEN '?' THEN this_play := 'Y'; this_uncertain := TRUE; ELSE RAISE EXCEPTION 'Follow Follow_id %: Follow_1_minute_infant Infant_id %: Date %: Time %: Playing %: Playing code unrecognized' , this_follow.follow_id , this_1_min_infant.infant_id , this_follow.follow_date , this_1_min.interval_time , this_1_min_infant.playing; END CASE; INSERT INTO events (intid , behavior , position , baboon , uncertain , comment) VALUES (currval('intervals_intid_seq'::regclass) , 'P' , _gen_pos('P') , FALSE , this_uncertain , NULL); INSERT INTO plays (eid, play) VALUES (CURRVAL('events_eid_seq'::regclass) , this_play); -- Participant. INSERT INTO parts (eid, part, participant) VALUES (CURRVAL('events_eid_seq'::regclass) , 'gmi_self' , this_follow.infant_id); END IF; -- Resting IF this_1_min_infant.resting IS NOT NULL THEN CASE this_1_min_infant.resting WHEN 'Y' THEN this_uncertain := default_infant_uncertain; WHEN '?' THEN this_uncertain := TRUE; ELSE RAISE EXCEPTION 'Follow Follow_id %: Follow_1_minute_infant Infant_id %: Date %: Time %: Resting %: Resting code unrecognized' , this_follow.follow_id , this_1_min_infant.infant_id , this_follow.follow_date , this_1_min.interval_time , this_1_min_infant.resting; END CASE; IF (this_1_min_infant.resting = '?' AND (this_1_min_infant.nursing = '?' OR this_1_min_infant.nursing IS NULL) AND (this_1_min_infant.eating = '?' OR this_1_min_infant.eating IS NULL) AND (this_1_min_infant.walks = '?' OR this_1_min_infant.walks IS NULL) AND (this_1_min_infant.playing = '?' OR this_1_min_infant.playing IS NULL) AND (this_1_min_infant.rides_on_stomach = '?' OR this_1_min_infant.rides_on_stomach IS NULL) AND (this_1_min_infant.rides_on_back = '?' OR this_1_min_infant.rides_on_back IS NULL) AND (this_1_min_infant.grooms_other = '?' OR this_1_min_infant.grooms_other IS NULL) AND (this_1_min_infant.groomed_by = '?' OR this_1_min_infant.groomed_by IS NULL) AND (this_1_min_infant.plays = '?' OR this_1_min_infant.plays IS NULL) AND (this_1_min_infant.dangles = '?' OR this_1_min_infant.dangles IS NULL)) OR (this_1_min_infant.resting = 'Y' AND this_1_min_infant.nursing IS NULL AND this_1_min_infant.eating IS NULL AND this_1_min_infant.walks IS NULL AND this_1_min_infant.playing IS NULL AND this_1_min_infant.rides_on_stomach IS NULL AND this_1_min_infant.rides_on_back IS NULL AND this_1_min_infant.grooms_other IS NULL AND this_1_min_infant.groomed_by IS NULL AND this_1_min_infant.plays IS NULL AND this_1_min_infant.dangles IS NULL) THEN -- Can only be definatly resting if not doing anything else -- at all. Are allowed to be probably resting so long as -- everything else that might be happening is only probable. INSERT INTO events (intid , behavior , position , baboon , uncertain , comment) VALUES (currval('intervals_intid_seq'::regclass) , 'R' , _gen_pos('R') , FALSE , this_uncertain , NULL); -- Participant. INSERT INTO parts (eid, part, participant) VALUES (CURRVAL('events_eid_seq'::regclass) , 'gmi_self' , this_follow.infant_id); -- Rests INSERT INTO rests (eid, rest) VALUES (CURRVAL('events_eid_seq'::regclass) , '9'); END IF; END IF; -- Rides_on_stomach IF this_1_min_infant.rides_on_stomach IS NOT NULL THEN CASE this_1_min_infant.rides_on_stomach WHEN 'Y' THEN this_uncertain := default_infant_uncertain; this_participant := this_follow.mother_id; WHEN '?' THEN this_uncertain := TRUE; this_participant := 'UNK'; ELSE -- An individual this_uncertain := default_infant_uncertain; this_participant := convert_animid(this_1_min_infant.rides_on_stomach, this_follow.follow_date); END CASE; INSERT INTO events (intid , behavior , position , baboon , uncertain , comment) VALUES (currval('intervals_intid_seq'::regclass) , 'RI' , _gen_pos('RI') , FALSE , this_uncertain , NULL); INSERT INTO rides (eid, ride) VALUES (CURRVAL('events_eid_seq'::regclass) , 'V'); -- Participant -- focal INSERT INTO parts (eid, part, participant) VALUES (CURRVAL('events_eid_seq'::regclass) , 'gmi_actor' , this_follow.infant_id); -- Participant -- ridee INSERT INTO parts (eid, part, participant) VALUES (CURRVAL('events_eid_seq'::regclass) , 'gmi_actee' , this_participant); END IF; -- Rides_on_back IF this_1_min_infant.rides_on_back IS NOT NULL THEN CASE this_1_min_infant.rides_on_back WHEN 'Y' THEN this_uncertain := default_infant_uncertain; this_participant := this_follow.mother_id; WHEN '?' THEN this_uncertain := TRUE; this_participant := 'UNK'; ELSE -- An individual this_uncertain := default_infant_uncertain; this_participant := convert_animid(this_1_min_infant.rides_on_back, this_follow.follow_date); END CASE; INSERT INTO events (intid , behavior , position , baboon , uncertain , comment) VALUES (currval('intervals_intid_seq'::regclass) , 'RI' , _gen_pos('RI') , FALSE , this_uncertain , NULL); INSERT INTO rides (eid, ride) VALUES (CURRVAL('events_eid_seq'::regclass) , 'D'); -- Participant -- focal INSERT INTO parts (eid, part, participant) VALUES (CURRVAL('events_eid_seq'::regclass) , 'gmi_actor' , this_follow.infant_id); -- Participant -- ridee INSERT INTO parts (eid, part, participant) VALUES (CURRVAL('events_eid_seq'::regclass) , 'gmi_actee' , this_participant); END IF; -- Dangles IF this_1_min_infant.dangles IS NOT NULL THEN IF (this_1_min_infant.dangles = '?' AND (this_1_min_infant.nursing = '?' OR this_1_min_infant.nursing IS NULL) AND (this_1_min_infant.eating = '?' OR this_1_min_infant.eating IS NULL) AND (this_1_min_infant.walks = '?' OR this_1_min_infant.walks IS NULL) AND (this_1_min_infant.playing = '?' OR this_1_min_infant.playing IS NULL) AND (this_1_min_infant.rides_on_stomach = '?' OR this_1_min_infant.rides_on_stomach IS NULL) AND (this_1_min_infant.rides_on_back = '?' OR this_1_min_infant.rides_on_back IS NULL) AND (this_1_min_infant.grooms_other = '?' OR this_1_min_infant.grooms_other IS NULL) AND (this_1_min_infant.groomed_by = '?' OR this_1_min_infant.groomed_by IS NULL) AND (this_1_min_infant.plays = '?' OR this_1_min_infant.plays IS NULL) AND (this_1_min_infant.resting = '?' OR this_1_min_infant.resting IS NULL)) OR (this_1_min_infant.dangles IS NOT NULL AND this_1_min_infant.nursing IS NULL AND this_1_min_infant.eating IS NULL AND this_1_min_infant.walks IS NULL AND this_1_min_infant.playing IS NULL AND this_1_min_infant.rides_on_stomach IS NULL AND this_1_min_infant.rides_on_back IS NULL AND this_1_min_infant.grooms_other IS NULL AND this_1_min_infant.groomed_by IS NULL AND this_1_min_infant.plays IS NULL AND this_1_min_infant.resting IS NULL) THEN -- Can only be definatly dangling if not doing anything else -- at all. Are allowed to be probably dangling so long as -- everything else that might be happening is only probable. CASE this_1_min_infant.dangles WHEN 'Y' THEN this_uncertain := default_infant_uncertain; this_participant := this_follow.mother_id; WHEN '?' THEN this_uncertain := TRUE; this_participant := 'UNK'; ELSE -- An individual this_uncertain := default_infant_uncertain; this_participant := convert_animid(this_1_min_infant.dangles , this_follow.follow_date); END CASE; INSERT INTO events (intid , behavior , position , baboon , uncertain , comment) VALUES (currval('intervals_intid_seq'::regclass) , 'DA' , _gen_pos('DA') , FALSE , this_uncertain , NULL); -- Participant -- focal INSERT INTO parts (eid, part, participant) VALUES (CURRVAL('events_eid_seq'::regclass) , 'gmi_actor' , this_follow.infant_id); -- Participant -- dangled from INSERT INTO parts (eid, part, participant) VALUES (CURRVAL('events_eid_seq'::regclass) , 'gmi_actee' , this_participant); ELSE IF this_1_min_infant.resting IS NOT NULL AND this_1_min_infant.nursing IS NULL AND this_1_min_infant.eating IS NULL AND this_1_min_infant.walks IS NULL AND this_1_min_infant.playing IS NULL AND this_1_min_infant.rides_on_stomach IS NULL AND this_1_min_infant.rides_on_back IS NULL AND this_1_min_infant.grooms_other IS NULL AND this_1_min_infant.groomed_by IS NULL AND this_1_min_infant.plays IS NULL THEN RAISE EXCEPTION 'Follow Follow_id %: Follow_1_minute_infant infant_id %: Date %: Time %: Dangles %: There is both resting and dangling but no other on-the-minute activities' , this_follow.follow_id , this_1_min_infant.infant_id , this_follow.follow_date , this_1_min.interval_time , this_1_min_infant.dangles; END IF; END IF; END IF; -- Contacts_other IF this_1_min_infant.contacts_other IS NOT NULL THEN -- It seems there can be more than one animid in contacts other. animids := regexp_split_to_array(this_1_min_infant.contacts_other , ','); FOREACH animid IN ARRAY animids LOOP animid := convert_animid(TRIM(BOTH FROM animid) , this_follow.follow_date); this_baboon := FALSE; CASE animid WHEN 'Y' THEN this_participant := 'UNK'; WHEN '?' THEN this_uncertain := TRUE; this_participant := 'UNK'; WHEN '_baboon_id' THEN this_uncertain := default_infant_uncertain; this_baboon := TRUE; ELSE -- Another individual this_uncertain := default_infant_uncertain; this_participant := animid; END CASE; INSERT INTO events (intid , behavior , position , baboon , uncertain , comment) VALUES (currval('intervals_intid_seq'::regclass) , 'TA' , _gen_pos('TA') , this_baboon , this_uncertain , NULL); -- Participant. INSERT INTO parts (eid, part, participant) VALUES (CURRVAL('events_eid_seq'::regclass) , 'gmi_participant' , this_follow.infant_id); IF NOT(this_baboon) THEN -- Participant -- one touched INSERT INTO parts (eid, part, participant) VALUES (CURRVAL('events_eid_seq'::regclass) , 'gmi_participant' , this_participant); END IF; END LOOP; END IF; -- Grooms_other IF this_1_min_infant.grooms_other IS NOT NULL THEN animids := regexp_split_to_array(this_1_min_infant.grooms_other , ','); FOREACH animid IN ARRAY animids LOOP animid := convert_animid(TRIM(BOTH FROM animid) , this_follow.follow_date); this_uncertain := default_infant_uncertain; this_baboon := FALSE; this_participant := NULL; this_part := 'gmi_actor'; CASE animid WHEN '?' THEN this_participant := 'UNK'; this_uncertain := TRUE; WHEN 'Y' THEN this_participant := 'UNK'; WHEN this_follow.infant_id THEN -- Self grooming. this_part := 'gmi_self'; WHEN '_baboon_id' THEN this_baboon := TRUE; ELSE this_participant := animid; END CASE; -- Insert a row into events. INSERT INTO events (intid , behavior , position , baboon , uncertain , comment) VALUES ( currval('intervals_intid_seq'::regclass) , 'G' , _gen_pos('G') , this_baboon , this_uncertain , NULL); -- The infant participant. INSERT INTO parts (eid, part, participant) VALUES(currval('events_eid_seq'::regclass) , this_part::parts_part , this_follow.infant_id); IF this_participant IS NOT NULL THEN INSERT INTO parts (eid, part, participant) VALUES(currval('events_eid_seq'::regclass) , 'gmi_actee' , this_participant); -- Remember grooming of mom or sib. CASE this_participant WHEN this_follow.mother_id THEN infant_mom_behaviors := infant_mom_behaviors || '{grooms another}'; infant_mom_uncertains := infant_mom_uncertains || this_uncertain; infant_mom_events := infant_mom_events || currval('events_eid_seq'::regclass)::INT; WHEN this_follow.sibling_id THEN infant_sib_behaviors := infant_sib_behaviors || '{grooms another}'; infant_sib_uncertains := infant_sib_uncertains || this_uncertain; infant_sib_events := infant_sib_events || currval('events_eid_seq'::regclass)::INT; ELSE NULL; END CASE; END IF; -- NULL participant END LOOP; -- Over grooms_other END IF; -- Groomed_by IF this_1_min_infant.groomed_by IS NOT NULL THEN animids := regexp_split_to_array(this_1_min_infant.groomed_by , ','); FOREACH animid IN ARRAY animids LOOP animid := convert_animid(TRIM(BOTH FROM animid) , this_follow.follow_date); this_uncertain := default_infant_uncertain; this_baboon := FALSE; CASE animid WHEN '?' THEN this_participant := 'UNK'; this_uncertain := TRUE; WHEN 'Y' THEN this_participant := 'UNK'; WHEN this_follow.infant_id THEN RAISE EXCEPTION 'Follow Follow_id %: Follow_1_minute_infant infant_id %: Date %: Time %: Groomed_by %: The focal (the infant) appears in the Groomed_by column' , this_follow.follow_id , this_1_min_infant.infant_id , this_follow.follow_date , this_1_min.interval_time , this_1_min_infant.groomed_by; WHEN '_baboon_id' THEN this_baboon := TRUE; ELSE this_participant := animid; END CASE; -- Insert a row into events. INSERT INTO events (intid , behavior , position , baboon , uncertain , comment) VALUES ( currval('intervals_intid_seq'::regclass) , 'G' , _gen_pos('G') , this_baboon , this_uncertain , NULL); -- The infant participant. INSERT INTO parts (eid, part, participant) VALUES(currval('events_eid_seq'::regclass) , 'gmi_actee' , this_follow.infant_id); IF NOT(this_baboon) THEN INSERT INTO parts (eid, part, participant) VALUES(currval('events_eid_seq'::regclass) , 'gmi_actor' , this_participant); -- Remember grooming by mom or sib. CASE this_participant WHEN this_follow.mother_id THEN infant_mom_behaviors := infant_mom_behaviors || '{groomed by another}'; infant_mom_uncertains := infant_mom_uncertains || this_uncertain; infant_mom_events := infant_mom_events || currval('events_eid_seq'::regclass)::INT; WHEN this_follow.sibling_id THEN infant_sib_behaviors := infant_sib_behaviors || '{groomed by another}'; infant_sib_uncertains := infant_sib_uncertains || this_uncertain; infant_sib_events := infant_sib_events || currval('events_eid_seq'::regclass)::INT; ELSE NULL; END CASE; END IF; -- baboon END LOOP; -- Over groomed_by END IF; -- Plays IF this_1_min_infant.plays IS NOT NULL THEN animids := regexp_split_to_array(this_1_min_infant.plays , ','); FOREACH animid IN ARRAY animids LOOP animid := convert_animid(TRIM(BOTH FROM animid) , this_follow.follow_date); this_uncertain := default_infant_uncertain; this_baboon := FALSE; CASE animid WHEN '?' THEN this_participant := 'UNK'; this_uncertain := TRUE; WHEN 'Y' THEN this_participant := 'UNK'; WHEN this_follow.infant_id THEN RAISE EXCEPTION 'Follow Follow_id %: Follow_1_minute_infant infant_id %: Date %: Time %: Plays %: The focal (the infant) appears in the Plays column' , this_follow.follow_id , this_1_min_infant.infant_id , this_follow.follow_date , this_1_min.interval_time , this_1_min_infant.plays; WHEN '_baboon_id' THEN this_baboon := TRUE; this_participant := NULL; ELSE this_participant := animid; END CASE; -- Figure out whether play is directed or mutual. IF this_participant = this_follow.mother_id OR this_participant = this_follow.sibling_id THEN this_part1 := 'gmi_actor'; this_part2 := 'gmi_actee'; ELSE this_part1 := 'gmi_participant'; this_part2 := 'gmi_participant'; END IF; -- Insert a row into events. INSERT INTO events (intid , behavior , position , baboon , uncertain , comment) VALUES ( currval('intervals_intid_seq'::regclass) , 'P' , _gen_pos('P') , this_baboon , this_uncertain , NULL); -- The plays table INSERT INTO plays (eid, play) VALUES(currval('events_eid_seq'::regclass) , 'Y'); -- The infant participant. INSERT INTO parts (eid, part, participant) VALUES(currval('events_eid_seq'::regclass) , this_part1 , this_follow.infant_id); IF NOT(this_baboon) THEN INSERT INTO parts (eid, part, participant) VALUES(currval('events_eid_seq'::regclass) , this_part2 , this_participant); END IF; -- Remember play with mom or sib. CASE this_participant WHEN this_follow.mother_id THEN infant_mom_behaviors := infant_mom_behaviors || '{play}'; infant_mom_uncertains := infant_mom_uncertains || this_uncertain; infant_mom_events := infant_mom_events || currval('events_eid_seq'::regclass)::INT; WHEN this_follow.sibling_id THEN infant_sib_behaviors := infant_sib_behaviors || '{play}'; infant_sib_uncertains := infant_sib_uncertains || this_uncertain; infant_sib_events := infant_sib_events || currval('events_eid_seq'::regclass)::INT; ELSE NULL; END CASE; END LOOP; -- Over plays END IF; -- Displays IF this_1_min_infant.displays IS NOT NULL THEN CASE this_1_min_infant.displays WHEN 'Y' THEN this_uncertain := default_infant_uncertain; WHEN '?' THEN this_uncertain := TRUE; ELSE RAISE EXCEPTION 'Follow Follow_id %: Follow_1_minute_infant Infant_id %: Date %: Time %: Displays %: Displays code unrecognized' , this_follow.follow_id , this_1_min_infant.infant_id , this_follow.follow_date , this_1_min.interval_time , this_1_min_infant.displays; END CASE; INSERT INTO events (intid , behavior , position , baboon , uncertain , comment) VALUES (currval('intervals_intid_seq'::regclass) , 'DI' , _gen_pos('DI') , FALSE , this_uncertain , NULL); -- Participant. INSERT INTO parts (eid, part, participant) VALUES (CURRVAL('events_eid_seq'::regclass) , 'gmi_self' , this_follow.infant_id); END IF; -- Refused IF this_1_min_infant.refused IS NOT NULL THEN CASE this_1_min_infant.refused WHEN 'Y' THEN this_uncertain := default_infant_uncertain; WHEN '?' THEN this_uncertain := TRUE; ELSE RAISE EXCEPTION 'Follow Follow_id %: Follow_1_minute_infant Infant_id %: Date %: Time %: Refused %: Refused code unrecognized' , this_follow.follow_id , this_1_min_infant.infant_id , this_follow.follow_date , this_1_min.interval_time , this_1_min_infant.refused; END CASE; INSERT INTO events (intid , behavior , position , baboon , uncertain , comment) VALUES (currval('intervals_intid_seq'::regclass) , 'RJ' , _gen_pos('RJ') , FALSE , this_uncertain , NULL); -- Participant. INSERT INTO parts (eid, part, participant) VALUES (CURRVAL('events_eid_seq'::regclass) , 'gmi_actee' , this_follow.infant_id); -- Other participant is unknown until it's pulled from the comments. INSERT INTO parts (eid, part, participant) VALUES (CURRVAL('events_eid_seq'::regclass) , 'gmi_actor' , 'UNK'); END IF; -- Begs IF this_1_min_infant.begs IS NOT NULL THEN this_baboon := FALSE; CASE this_1_min_infant.begs WHEN 'Y' THEN this_uncertain := default_infant_uncertain; this_participant := 'UNK'; WHEN '?' THEN this_uncertain := TRUE; this_participant := 'UNK'; WHEN '_baboon_id' THEN this_baboon := TRUE; ELSE -- Another individual this_uncertain := default_infant_uncertain; this_participant := convert_animid(this_1_min_infant.begs , this_follow.follow_date); END CASE; INSERT INTO events (intid , behavior , position , baboon , uncertain , comment) VALUES (currval('intervals_intid_seq'::regclass) , 'BE' , _gen_pos('BE') , this_baboon , this_uncertain , NULL); -- Participant. INSERT INTO parts (eid, part, participant) VALUES (CURRVAL('events_eid_seq'::regclass) , 'gmi_actor' , this_follow.infant_id); IF NOT(this_baboon) THEN -- Participant -- one begged from INSERT INTO parts (eid, part, participant) VALUES (CURRVAL('events_eid_seq'::regclass) , 'gmi_actee' , this_participant); END IF; END IF; -- Aggression IF this_1_min_infant.aggression IS NOT NULL THEN this_baboon := FALSE; CASE this_1_min_infant.aggression WHEN 'Y' THEN this_uncertain := default_infant_uncertain; this_participant := 'UNK'; WHEN '?' THEN this_uncertain := TRUE; this_participant := 'UNK'; WHEN '_baboon_id' THEN this_baboon := TRUE; ELSE -- Another individual this_uncertain := default_infant_uncertain; this_participant := convert_animid(this_1_min_infant.aggression , this_follow.follow_date); END CASE; INSERT INTO events (intid , behavior , position , baboon , uncertain , comment) VALUES (currval('intervals_intid_seq'::regclass) , 'ASSR' , _gen_pos('ASSR') , this_baboon , this_uncertain , NULL); -- Participant. INSERT INTO parts (eid, part, participant) VALUES (CURRVAL('events_eid_seq'::regclass) , 'gmi_actor' , this_follow.infant_id); IF NOT(this_baboon) THEN -- Participant -- one agressed against INSERT INTO parts (eid, part, participant) VALUES (CURRVAL('events_eid_seq'::regclass) , 'gmi_actee' , this_participant); END IF; END IF; -- DEVBEH! Comment IF this_1_min.comment ~ 'DEVBEH!' -- Is nothing else -- happening at the minute? AND this_1_min_infant.nursing IS NULL AND this_1_min_infant.eating IS NULL AND this_1_min_infant.walks IS NULL AND this_1_min_infant.playing IS NULL AND this_1_min_infant.resting IS NULL AND this_1_min_infant.rides_on_stomach IS NULL AND this_1_min_infant.rides_on_back IS NULL AND this_1_min_infant.dangles IS NULL AND this_1_min_infant.contacts_other IS NULL AND (this_1_min_infant.grooms_other IS NULL OR TRIM(BOTH FROM this_1_min_infant.grooms_other) = '') AND (this_1_min_infant.groomed_by IS NULL OR TRIM(BOTH FROM this_1_min_infant.groomed_by) = '') AND this_1_min_infant.plays IS NULL THEN IF this_1_min.comment !~ 'DEVBEH!\[.+\]' THEN RAISE EXCEPTION 'Follow Follow_id %: Follow_1_minute_infant Infant_id %: Date %: Time %: follow_1_minute.Comment %: Invalid DEVBEH! comment format' , this_follow.follow_id , this_1_min_infant.infant_id , this_follow.follow_date , this_1_min.interval_time , this_1_min.comment; END IF; -- Take the devebeh out of the interval comment and -- put it into the event comment. IF interval_comment IS NULL THEN this_devbeh := NULL; ELSE this_devbeh := SUBSTRING(interval_comment FROM 'DEVBEH!\[.+\]'); IF this_devbeh IS NOT NULL THEN -- Remove the devbeh from the comment. interval_comment := split_part(interval_comment, this_devbeh, 1) || split_part(interval_comment , this_devbeh , 2); IF TRIM(BOTH FROM interval_comment) = '' THEN interval_comment := NULL; END IF; -- Save the new interval comment. UPDATE intervals SET comment = interval_comment WHERE intervals.intid = CURRVAL('intervals_intid_seq'::regclass); END IF; END IF; INSERT INTO events (intid , behavior , position , baboon , uncertain , comment) VALUES (currval('intervals_intid_seq'::regclass) , 'O' , _gen_pos('O') , FALSE , default_infant_uncertain , this_devbeh); -- Participant -- focal INSERT INTO parts (eid, part, participant) VALUES (CURRVAL('events_eid_seq'::regclass) , 'gmi_self' , this_follow.infant_id); END IF; -- Distance to mom. IF this_1_min_infant.in_contact IS NOT NULL OR this_1_min_infant.within_reach IS NOT NULL OR this_1_min_infant.within_0_5_meters IS NOT NULL OR this_1_min_infant.between_5_15_meters IS NOT NULL OR this_1_min_infant.more_than_15_meters IS NOT NULL THEN this_error := FALSE; this_distance := NULL; this_uncertain := default_infant_uncertain; IF this_1_min_infant.in_contact IS NOT NULL THEN this_distance := '1'; CASE this_1_min_infant.in_contact WHEN 'Y' THEN NULL; WHEN '?' THEN this_uncertain := TRUE; ELSE RAISE EXCEPTION 'Follow Follow_id %: Follow_1_minute_infant Infant_id %: Date %: Time %: In_contact %: In_contact code unrecognized' , this_follow.follow_id , this_1_min_infant.infant_id , this_follow.follow_date , this_1_min.interval_time , this_1_min_infant.in_contact; END CASE; END IF; IF this_1_min_infant.within_reach IS NOT NULL THEN this_error := this_error OR this_distance IS NOT NULL; this_distance := '2'; CASE this_1_min_infant.within_reach WHEN 'Y' THEN NULL; WHEN '?' THEN this_uncertain := TRUE; ELSE RAISE EXCEPTION 'Follow Follow_id %: Follow_1_minute_infant Infant_id %: Date %: Time %: Within_reach %: Within_reach code unrecognized' , this_follow.follow_id , this_1_min_infant.infant_id , this_follow.follow_date , this_1_min.interval_time , this_1_min_infant.within_reach; END CASE; END IF; IF this_1_min_infant.within_0_5_meters IS NOT NULL THEN this_error := this_error OR this_distance IS NOT NULL; this_distance := '3'; CASE this_1_min_infant.within_0_5_meters WHEN 'Y' THEN NULL; WHEN '?' THEN this_uncertain := TRUE; ELSE RAISE EXCEPTION 'Follow Follow_id %: Follow_1_minute_infant Infant_id %: Date %: Time %: Within_0_5_meters %: Within_0_5_meters code unrecognized' , this_follow.follow_id , this_1_min_infant.infant_id , this_follow.follow_date , this_1_min.interval_time , this_1_min_infant.within_0_5_meters; END CASE; END IF; IF this_1_min_infant.between_5_15_meters IS NOT NULL THEN this_error := this_error OR this_distance IS NOT NULL; this_distance := '4'; CASE this_1_min_infant.between_5_15_meters WHEN 'Y' THEN NULL; WHEN '?' THEN this_uncertain := TRUE; ELSE RAISE EXCEPTION 'Follow Follow_id %: Follow_1_minute_infant Infant_id %: Date %: Time %: Between_5_15_meters %: Between_5_15_meters code unrecognized' , this_follow.follow_id , this_1_min_infant.infant_id , this_follow.follow_date , this_1_min.interval_time , this_1_min_infant.between_5_15_meters; END CASE; END IF; IF this_1_min_infant.more_than_15_meters IS NOT NULL THEN this_error := this_error OR this_distance IS NOT NULL; this_distance := '5'; CASE this_1_min_infant.more_than_15_meters WHEN 'Y' THEN NULL; WHEN '?' THEN this_uncertain := TRUE; ELSE RAISE EXCEPTION 'Follow Follow_id %: Follow_1_minute_infant Infant_id %: Date %: Time %: More_than_15_meters %: More_than_15_meters code unrecognized' , this_follow.follow_id , this_1_min_infant.infant_id , this_follow.follow_date , this_1_min.interval_time , this_1_min_infant.more_than_15_meters; END CASE; END IF; IF this_error THEN RAISE EXCEPTION 'Follow Follow_id %: Follow_1_minute_infant Infant_id %: Date %: Time %: There is more than one infant distance column (In_contact, Within_reach, Within_0_5_meters, Between_5_15_meters, More_than_15_meters) non-null' , this_follow.follow_id , this_1_min_infant.infant_id , this_follow.follow_date , this_1_min.interval_time; END IF; INSERT INTO distances (intid, uncertain, distance) VALUES (currval('intervals_intid_seq'::regclass) , this_uncertain , this_distance); -- Infant. INSERT INTO distanceparts (distid, role, participant) VALUES (CURRVAL('distances_distid_seq'::regclass) , 'I' , this_follow.infant_id); -- Mom. INSERT INTO distanceparts (distid, role, participant) VALUES (CURRVAL('distances_distid_seq'::regclass) , 'M' , this_follow.mother_id); END IF; -- Vocalizations and cries can't happen together. IF this_1_min_infant.cries IS NOT NULL AND this_1_min_infant.vocalizations IS NOT NULL THEN RAISE EXCEPTION 'Follow Follow_id %: Follow_1_minute_infant Infant_id %: Date %: Time %: Cries %: Vocalizations %: Infant has both cries and vocalizations' , this_follow.follow_id , this_1_min_infant.infant_id , this_follow.follow_date , this_1_min.interval_time , this_1_min_infant.cries , this_1_min_infant.vocalizations; END IF; -- Cries IF this_1_min_infant.cries IS NOT NULL THEN CASE this_1_min_infant.cries WHEN 'Y' THEN this_uncertain := default_infant_uncertain; WHEN '?' THEN this_uncertain := TRUE; ELSE RAISE EXCEPTION 'Follow Follow_id %: Follow_1_minute_infant Infant_id %: Date %: Time %: Cries %: Cries code unrecognized' , this_follow.follow_id , this_1_min_infant.infant_id , this_follow.follow_date , this_1_min.interval_time , this_1_min_infant.cries; END CASE; INSERT INTO events (intid , behavior , position , baboon , uncertain , comment) VALUES (currval('intervals_intid_seq'::regclass) , 'V' , _gen_pos('V') , FALSE , this_uncertain , NULL); INSERT INTO vocs (eid, vocalization) VALUES (CURRVAL('events_eid_seq'::regclass) , 'LIA'); -- Participant. INSERT INTO parts (eid, part, participant) VALUES (CURRVAL('events_eid_seq'::regclass) , 'gmi_self' , this_follow.infant_id); END IF; -- Infant vocalizations. IF this_1_min_infant.vocalizations IS NOT NULL THEN old_voc := TRIM(BOTH FROM this_1_min_infant.vocalizations); -- Defaults. this_voc_uncertain := default_infant_uncertain; -- Convert the vocalization code. CASE old_voc WHEN 'Chk' THEN new_voc := 'CHK'; WHEN 'Fg' THEN new_voc := 'FG'; WHEN 'Hoo' THEN new_voc := 'HOO'; WHEN 'Lia' THEN new_voc := 'LIA'; WHEN 'Pb' THEN new_voc := 'PB'; WHEN 'Pg' THEN new_voc := 'PG'; WHEN 'Ph' THEN new_voc := 'PH'; WHEN 'Scr' THEN new_voc := 'SCR'; WHEN 'Wb' THEN new_voc := 'WB'; WHEN 'Wh' THEN new_voc := 'WH'; WHEN 'Multi' THEN new_voc := 'MUL'; WHEN 'Other' THEN new_voc := 'OTH'; WHEN 'Grunt' THEN new_voc := 'GR'; WHEN 'Y' THEN new_voc := 'Y'; WHEN '?' THEN new_voc := 'Y'; this_voc_uncertain := TRUE; ELSE RAISE EXCEPTION 'Follow Follow_id %: Follow_1_minute_infant Infant_id %: Date %: Time %: Vocalizations %: The infant vocalization (%) is unknown' , this_follow.follow_id , this_1_min_infant.infant_id , this_follow.follow_date , this_1_min.interval_time , this_1_min_infant.vocalization , old_voc; END CASE; -- Save vocalization event. INSERT INTO events (intid , behavior , position , baboon , uncertain , comment) VALUES ( currval('intervals_intid_seq'::regclass) , 'V' , _gen_pos('V') , FALSE , this_voc_uncertain , NULL); INSERT INTO vocs (eid, vocalization) VALUES (currval('events_eid_seq'::regclass) , new_voc); -- Participant. INSERT INTO parts (eid, part, participant) VALUES (CURRVAL('events_eid_seq'::regclass) , 'gmi_self' , this_follow.infant_id); END IF; -- There is a infant vocalization. -- -- follow_1_minute_mother: -- SELECT * INTO this_1_min_mom FROM follow_1_minute_mother WHERE follow_1_minute_mother.interval_id = this_1_min.interval_id; -- | Behavior | Grooms_other | Groomed_by | -- tableid | Interval_id | Mother_id | Behavior | Grooms_other | Groomed_by | Vocalizations -- 1 | 11/08/2010_GM_01_01 | GM | Rest | | | -- Mom sanity check. IF this_follow.mother_id != this_1_min_mom.mother_id THEN RAISE EXCEPTION 'Follow Mother_id %: Follow_1_minute_mother Mother_id %: Date %: Time %: The mother id in the follow does not match the mother id in the Follow_1_minute_mother' , this_follow.mother_id , this_1_min_mom.mother_id , this_follow.follow_date , this_1_min.interval_time; END IF; -- Check for already converted. IF this_1_min_mom.done THEN RAISE EXCEPTION 'Date %: Mom %: follow_1_minute_mother row lready converted' , this_follow.follow_date , this_follow.mother_id; END IF; -- -- Mom behavior. -- grooms_other_converted := FALSE; groomed_by_converted := FALSE; multi_found := FALSE; grooming_and_play := FALSE; gp_unknown := FALSE; IF this_1_min_mom.behavior IS NOT NULL THEN -- Record mom's behavior(s) -- Split into separate codes. behaviors := regexp_split_to_array(this_1_min_mom.behavior, ';'); -- Go through all the behaviors ahead of time so we know -- what we've got and what's implied. grooming_found := FALSE; play_found := FALSE; FOREACH old_behavior IN ARRAY behaviors LOOP old_behavior := TRIM(BOTH FROM old_behavior); CASE old_behavior WHEN 'Grooming' THEN grooming_found := TRUE; WHEN 'Play' THEN play_found := TRUE; ELSE NULL; END CASE; END LOOP; -- Test for grooming and play mixed. grooming_and_play := grooming_found AND play_found; -- Initialize storage event_behavior := ARRAY[]::TEXT[]; event_uncertain := ARRAY[]::BOOLEAN[]; event_baboon := ARRAY[]::BOOLEAN[]; event_comment := ARRAY[]::TEXT[]; part1_parts := ARRAY[]::TEXT[]; part1_participants := ARRAY[]::TEXT[]; part2_parts := ARRAY[]::TEXT[]; part2_participants := ARRAY[]::TEXT[]; eventcnt := 0; FOREACH old_behavior IN ARRAY behaviors LOOP old_behavior := TRIM(BOTH FROM old_behavior); this_uncertain := default_mom_uncertain; CASE old_behavior WHEN '?' THEN new_behavior := 'UNK'; this_uncertain := TRUE; WHEN '-' THEN CONTINUE; -- no event WHEN 'ASSR' THEN new_behavior := 'ASSR'; WHEN 'Bad obs' THEN new_behavior := 'BAD'; this_uncertain := TRUE; WHEN 'Build nest' THEN new_behavior := 'BN'; WHEN 'Climb' THEN new_behavior := 'C'; WHEN 'Drink' THEN new_behavior := 'DR'; WHEN 'Eat' THEN new_behavior := '_ca_eating'; WHEN 'Groomed' THEN new_behavior := 'G'; WHEN 'Grooming' THEN new_behavior := 'G'; WHEN 'Multi' THEN new_behavior := 'MULTI'; multi_found := TRUE; WHEN 'Multigroom' THEN new_behavior := 'G'; WHEN 'Other' THEN new_behavior := 'O'; WHEN 'Play' THEN new_behavior := 'P'; WHEN 'Rest' THEN new_behavior := 'R'; WHEN 'Self-groom' THEN new_behavior := 'G'; WHEN 'Travel' THEN new_behavior := 'T'; WHEN 'Vua' THEN new_behavior := 'VUA'; ELSE RAISE EXCEPTION 'Follow Follow_id %: Follow_1_minute_mother Mother_id %: Date %: Time %: Behavior %: The mother behavior (%) is unrecognized' , this_follow.follow_id , this_1_min_mom.mother_id , this_follow.follow_date , this_1_min.interval_time , this_1_min_mom.behavior , old_behavior; END CASE; -- Setup defaults. eventcnt := eventcnt + 1; event_uncertain[eventcnt] := this_uncertain; event_baboon[eventcnt] := FALSE; event_comment[eventcnt] := NULL; part1_parts[eventcnt] := 'gmi_self'; part1_participants[eventcnt] := this_follow.mother_id; part2_parts[eventcnt] := NULL; IF old_behavior = 'Grooming' AND TRIM(BOTH FROM this_1_min_mom.grooms_other) = this_follow.mother_id THEN RAISE EXCEPTION 'Follow Follow_id %: Follow_1_minute_mother Mother_id %: Date %: Time %: Behavior %: The mother has a Grooming behavior but Grooms_other contains only the mother''s id' , this_follow.follow_id , this_1_min_mom.mother_id , this_follow.follow_date , this_1_min.interval_time , this_1_min_mom.behavior; END IF; event_behavior[eventcnt] := new_behavior; IF old_behavior = 'Grooming' OR old_behavior = 'Multigroom' OR old_behavior = 'Multi' OR old_behavior = 'Play' THEN IF this_1_min_mom.grooms_other IS NOT NULL THEN -- Record the groomed (or play partners) individuals. grooms_other_converted := TRUE; animids := regexp_split_to_array(this_1_min_mom.grooms_other , ','); animid_cnt := ARRAY_LENGTH(animids, 1); IF old_behavior != 'Multi' THEN -- Leave the multi behavior on the mom but -- make grooming and play behaviors. eventcnt := eventcnt - 1; END IF; FOREACH animid IN ARRAY animids LOOP eventcnt := eventcnt + 1; animid := convert_animid(TRIM(BOTH FROM animid) , this_follow.follow_date); -- The animal got groomed by the mom. IF old_behavior = 'Multi' THEN -- Make both grooming and play behaviors. -- First the play behavior. event_behavior[eventcnt] := 'P'; ELSE event_behavior[eventcnt] := new_behavior; END IF; event_uncertain[eventcnt] := default_mom_uncertain; event_baboon[eventcnt] := FALSE; event_comment[eventcnt] := NULL; IF (old_behavior = 'Play' OR old_behavior = 'Multi') -- doing play first. AND animid != this_follow.infant_id AND (animid != this_follow.sibling_id OR this_follow.sibling_id IS NULL) THEN part1_parts[eventcnt] := 'gmi_participant'; part2_parts[eventcnt] := 'gmi_participant'; ELSE part1_parts[eventcnt] := 'gmi_actor'; part2_parts[eventcnt] := 'gmi_actee'; END IF; part1_participants[eventcnt] := this_follow.mother_id; CASE animid WHEN '?' THEN part2_participants[eventcnt] := 'UNK'; event_uncertain[eventcnt] := TRUE; WHEN 'Y' THEN part2_participants[eventcnt] := 'UNK'; WHEN this_follow.mother_id THEN -- Self grooming or self-play. part1_parts[eventcnt] := 'gmi_self'; part2_parts[eventcnt] := NULL; IF grooming_and_play THEN event_comment[eventcnt] := 'convert_access(): Both self-grooming and solitary play because of grooming+play in access db.'; END IF; ELSE IF (grooming_and_play AND animid_cnt > 1 AND (old_behavior = 'Grooming' OR old_behavior = 'Play')) THEN -- Can't tell the who played and who groomed. part2_participants[eventcnt] := 'UNK'; IF old_behavior = 'Play' THEN -- Force interaction as participant as non focals -- (UNK) require this. part1_parts[eventcnt] = 'gmi_participant'; part2_parts[eventcnt] = 'gmi_participant'; END IF; event_comment[eventcnt] := 'convert_access(): Changed (' || this_1_min_mom.grooms_other || ') to (UNK) and ignored mutiple animids due to grooming occurring with play'; gp_unknown := TRUE; EXIT; -- Do only 1 animid. ELSIF old_behavior = 'Multi' THEN part2_participants[eventcnt] := 'UNK'; event_comment[eventcnt] := 'convert_access(): Changed (' || this_1_min_mom.grooms_other || ') to (UNK) due to Multi'; -- Force interaction as participant as non focals -- (UNK) require this. part1_parts[eventcnt] = 'gmi_participant'; part2_parts[eventcnt] = 'gmi_participant'; gp_unknown := TRUE; -- That takes care of the play event for Multi -- Also make a grooming event. eventcnt := eventcnt + 1; event_behavior[eventcnt] := 'G'; event_uncertain[eventcnt] := default_mom_uncertain; event_baboon[eventcnt] := FALSE; event_comment[eventcnt] := 'convert_access(): Changed (' || this_1_min_mom.grooms_other || ') to (UNK) due to Multi'; part1_parts[eventcnt] := 'gmi_actor'; part2_parts[eventcnt] := 'gmi_actee'; part1_participants[eventcnt] := this_follow.mother_id; part2_participants[eventcnt] := 'UNK'; ELSIF animid = '_baboon_id' THEN event_baboon[eventcnt] := TRUE; part2_parts[eventcnt] := NULL; ELSE part2_participants[eventcnt] := animid; END IF; END CASE; END LOOP; -- Over grooms_other ELSE -- Grooms_other is NULL IF old_behavior != 'Multi' THEN RAISE EXCEPTION 'Follow Follow_id %: Follow_1_minute_mother Mother_id %: Date %: Time %: Behavior %: The Grooms_other column cannot be empty for play or grooming or multigroom' , this_follow.follow_id , this_1_min_mom.mother_id , this_follow.follow_date , this_1_min.interval_time , this_1_min_mom.behavior; END IF; END IF; END IF; IF old_behavior = 'Groomed' OR old_behavior = 'Multi' OR old_behavior = 'Multigroom' THEN IF this_1_min_mom.groomed_by IS NOT NULL THEN -- Record the individuals doing the grooming. groomed_by_converted := TRUE; animids := regexp_split_to_array(this_1_min_mom.groomed_by , ','); IF this_1_min_mom.grooms_other IS NULL AND old_behavior != 'Multi' THEN -- Have not yet seen multiple individuals. -- (The multi retains it's own event as a 'self' event.) eventcnt := eventcnt - 1; END IF; FOREACH animid IN ARRAY animids LOOP eventcnt := eventcnt + 1; animid := convert_animid(TRIM(BOTH FROM animid) , this_follow.follow_date); -- The animal groomed the mom. IF old_behavior = 'Multi' THEN -- We've already recorded the multi -- Now we want a grooming event for each grooming partner. event_behavior[eventcnt] := 'G'; event_comment[eventcnt] := 'convert_access(): Mom groomed due to Multi'; ELSE event_behavior[eventcnt] := new_behavior; event_comment[eventcnt] := NULL; END IF; event_uncertain[eventcnt] := default_mom_uncertain; event_baboon[eventcnt] := FALSE; part1_parts[eventcnt] := 'gmi_actee'; part1_participants[eventcnt] := this_follow.mother_id; part2_parts[eventcnt] := 'gmi_actor'; CASE animid WHEN '?' THEN part2_participants[eventcnt] := 'UNK'; event_uncertain[eventcnt] := TRUE; WHEN 'Y' THEN part2_participants[eventcnt] := 'UNK'; WHEN this_follow.mother_id THEN RAISE EXCEPTION 'Follow Follow_id %: Follow_1_minute_mother Mother_id %: Date %: Time %: Behavior %: Groomed_by %: The focal (the mother) appears in the Groomed_by column' , this_follow.follow_id , this_1_min_mom.mother_id , this_follow.follow_date , this_1_min.interval_time , this_1_min_mom.behavior , this_1_min_mom.groomed_by; WHEN '_baboon_id' THEN event_baboon[eventcnt] := TRUE; part2_parts[eventcnt] := NULL; ELSE part2_participants[eventcnt] := animid; END CASE; END LOOP; -- Over groomed_by ELSE -- Groomed_by is NULL IF old_behavior != 'Multi' THEN RAISE EXCEPTION 'Follow Follow_id %: Follow_1_minute_mother Mother_id %: Date %: Time %: Behavior %: The Groomed_by column cannot be empty for groomed or multigroom' , this_follow.follow_id , this_1_min_mom.mother_id , this_follow.follow_date , this_1_min.interval_time , this_1_min_mom.behavior; END IF; END IF; END IF; END LOOP; -- Over mom behaviors alen := ARRAY_LENGTH(part1_parts, 1); IF alen IS NOT NULL THEN -- Go thorough all the participants and make events. FOR i IN 1 .. alen LOOP --debug-- raise notice 'time %: behavior %: part1 %: role1 %: part2 %: role2 %', this_1_min.interval_time, event_behavior[i], part1_participants[i], part1_parts[i], part2_participants[i], part2_parts[i]; IF event_behavior[i] = 'G' AND ((part1_participants[i] = this_follow.mother_id AND part2_participants[i] = this_follow.infant_id AND part1_parts[i] = 'gmi_actor' AND part2_parts[i] = 'gmi_actee') OR (part2_participants[i] = this_follow.mother_id AND part1_participants[i] = this_follow.infant_id AND part2_parts[i] = 'gmi_actor' AND part1_parts[i] = 'gmi_actee')) THEN -- The mom groomed the infant. i_found := FALSE; behavior_cnt := ARRAY_LENGTH(infant_mom_behaviors, 1); IF behavior_cnt IS NOT NULL AND behavior_cnt != 0 THEN FOR j IN 1 .. behavior_cnt LOOP IF infant_mom_behaviors[j] = 'groomed by another' THEN -- We've already recorded this behavior. IF infant_mom_uncertains[j] != event_uncertain[i] AND NOT(infant_mom_uncertains[j]) THEN -- Must make the recorded behavior uncertain. UPDATE events SET uncertain = TRUE WHERE events.eid = infant_mom_events[j]; END IF; -- Note that we've done with it by removing the array -- element. infant_mom_behaviors := infant_mom_behaviors[1:j - 1] || infant_mom_behaviors[j+1:behavior_cnt]; infant_mom_uncertains := infant_mom_uncertains[1:j - 1] || infant_mom_uncertains[j+1:behavior_cnt]; infant_mom_events := infant_mom_events[1:j - 1] || infant_mom_events[j+1:behavior_cnt]; i_found := TRUE; EXIT; END IF; END LOOP; END IF; IF NOT(i_found) THEN RAISE EXCEPTION 'Follow Follow_id %: Follow_1_minute_mother Mother_id %: Date %: Time %: Behavior %: Grooms_other %: The follow_1_minute_mother says the mother groomed the infant but the follow_1_minute_infant has no record of this' , this_follow.follow_id , this_1_min_mom.mother_id , this_follow.follow_date , this_1_min.interval_time , this_1_min_mom.behavior , this_1_min_mom.grooms_other; END IF; ELSIF event_behavior[i] = 'G' AND ((part1_participants[i] = this_follow.mother_id AND part2_participants[i] = this_follow.infant_id AND part1_parts[i] = 'gmi_actee' AND part2_parts[i] = 'gmi_actor') OR (part2_participants[i] = this_follow.mother_id AND part1_participants[i] = this_follow.infant_id AND part2_parts[i] = 'gmi_actee' AND part1_parts[i] = 'gmi_actor')) THEN -- The infant groomed the mom. i_found := FALSE; behavior_cnt := ARRAY_LENGTH(infant_mom_behaviors, 1); IF behavior_cnt IS NOT NULL AND behavior_cnt != 0 THEN FOR j IN 1 .. behavior_cnt LOOP IF infant_mom_behaviors[j] = 'grooms another' THEN -- We've already recorded this behavior. IF infant_mom_uncertains[j] != event_uncertain[i] AND NOT(infant_mom_uncertains[j]) THEN -- Must make the recorded behavior uncertain. UPDATE events SET uncertain = TRUE WHERE events.eid = infant_mom_events[j]; END IF; -- Note that we've done with it by removing the array -- element. infant_mom_behaviors := infant_mom_behaviors[1:j - 1] || infant_mom_behaviors[j+1:behavior_cnt]; infant_mom_uncertains := infant_mom_uncertains[1:j - 1] || infant_mom_uncertains[j+1:behavior_cnt]; infant_mom_events := infant_mom_events[1:j - 1] || infant_mom_events[j+1:behavior_cnt]; i_found := TRUE; EXIT; END IF; END LOOP; END IF; IF NOT(i_found) THEN RAISE EXCEPTION 'Follow Follow_id %: Follow_1_minute_mother Mother_id %: Date %: Time %: Behavior %: Groomed_by %: The follow_1_minute_mother says the infant groomed the mother but the follow_1_minute_infant has no record of this' , this_follow.follow_id , this_1_min_mom.mother_id , this_follow.follow_date , this_1_min.interval_time , this_1_min_mom.behavior , this_1_min_mom.groomed_by; END IF; ELSIF event_behavior[i] = 'P' AND ((part1_participants[i] = this_follow.mother_id AND part2_participants[i] = this_follow.infant_id AND part1_parts[i] = 'gmi_actor' AND part2_parts[i] = 'gmi_actee') OR (part2_participants[i] = this_follow.mother_id AND part1_participants[i] = this_follow.infant_id AND part2_parts[i] = 'gmi_actor' AND part1_parts[i] = 'gmi_actee')) THEN -- The mom played with the infant. behavior_cnt := ARRAY_LENGTH(infant_mom_behaviors, 1); IF behavior_cnt IS NOT NULL AND behavior_cnt != 0 THEN FOR j IN 1 .. behavior_cnt LOOP IF infant_mom_behaviors[j] = 'play' THEN -- The infant played with the mom. -- Make the play mutual. UPDATE parts SET part = 'gmi_participant' WHERE parts.eid = infant_mom_events[j]; IF infant_mom_uncertains[j] != event_uncertain[i] AND NOT(infant_mom_uncertains[j]) THEN -- Must make the recorded behavior uncertain. UPDATE events SET uncertain = TRUE WHERE events.eid = infant_mom_events[j]; END IF; EXIT; END IF; END LOOP; END IF; ELSE -- Insert a row into events. INSERT INTO events (intid , behavior , position , baboon , uncertain , comment) VALUES ( currval('intervals_intid_seq'::regclass) , event_behavior[i] , _gen_pos(event_behavior[i]) , event_baboon[i] , event_uncertain[i] , event_comment[i]); -- Now save the participant(s). INSERT INTO parts (eid, part, participant) VALUES(currval('events_eid_seq'::regclass) , part1_parts[i]::parts_part , part1_participants[i]); IF part2_parts[i] IS NOT NULL THEN INSERT INTO parts (eid, part, participant) VALUES(currval('events_eid_seq'::regclass) , part2_parts[i]::parts_part , part2_participants[i]); IF part2_participants[i] = this_follow.sibling_id THEN -- Remember sibling interactions. IF part1_parts[i] = 'gmi_actee' AND event_behavior[i] = 'G' THEN -- The mom was groomed by the sib. mom_sib_behaviors = mom_sib_behaviors || '{groomed by another}'; mom_sib_uncertains = mom_sib_uncertains || event_uncertain[i]; ELSIF part1_parts[i] = 'gmi_actor' AND event_behavior[i] = 'G' THEN -- The sib was groomed by the mom. --raise notice 'grooms sib: time %: gp_unknown %', this_1_min.interval_time, gp_unknown; mom_sib_behaviors = mom_sib_behaviors || '{grooms another}'; mom_sib_uncertains = mom_sib_uncertains || event_uncertain[i]; ELSIF part1_parts[i] = 'gmi_actor' AND event_behavior[i] = 'P' THEN -- The sib and the mom played. mom_sib_behaviors = mom_sib_behaviors || '{play}'; mom_sib_uncertains = mom_sib_uncertains || event_uncertain[i]; END IF; mom_sib_events = mom_sib_events || currval('events_eid_seq'::regclass)::INT; END IF; -- 2nd participant is sib END IF; -- There is a 2nd participant IF event_behavior[i] = 'R' THEN -- Resting, needs a row on RESTS. INSERT INTO rests (eid, rest) VALUES (CURRVAL('events_eid_seq'::regclass), '9'); ELSIF event_behavior[i] = 'P' THEN -- Playing, needs a row on PLAYS.' INSERT INTO plays (eid, play) VALUES (CURRVAL('events_eid_seq'::regclass), 'Y'); END IF; END IF; END LOOP; -- Over participants END IF; END IF; -- Check to be sure we got all the mom behavior data. IF NOT(grooms_other_converted) AND this_1_min_mom.grooms_other IS NOT NULL THEN RAISE EXCEPTION 'Follow Follow_id %: Follow_1_minute_mother Mother_id %: Date %: Time %: Behavior %: Grooms_other %: The mother has a Grooms_other value but no behaviors to go with grooming or play' , this_follow.follow_id , this_1_min_mom.mother_id , this_follow.follow_date , this_1_min.interval_time , this_1_min_mom.behavior , this_1_min_mom.grooms_other; END IF; IF NOT(groomed_by_converted) AND this_1_min_mom.groomed_by IS NOT NULL THEN RAISE EXCEPTION 'Follow Follow_id %: Follow_1_minute_mother Mother_id %: Date %: Time %: Behavior %: Groomed_by %: The mother has a Grooms_other value but no behaviors to go with grooming' , this_follow.follow_id , this_1_min_mom.mother_id , this_follow.follow_date , this_1_min.interval_time , this_1_min_mom.behavior , this_1_min_mom.groomed_by; END IF; -- And check that all non-play behaviors related to the infant -- are also in the mom's record. behavior_cnt := ARRAY_LENGTH(infant_mom_behaviors, 1); IF behavior_cnt IS NOT NULL AND behavior_cnt != 0 THEN FOR j IN 1 .. behavior_cnt LOOP IF infant_mom_behaviors[j] != 'play' AND NOT(infant_mom_behaviors[j] = 'groomed by another' AND gp_unknown) THEN RAISE EXCEPTION 'Follow Follow_id %: Follow_1_minute_mother Mother_id %: Date %: Time %: Behavior %: The infant has a behavior (of a kind indicating %) with the mom but the mom has no record of this behavior' , this_follow.follow_id , this_1_min_mom.mother_id , this_follow.follow_date , this_1_min.interval_time , this_1_min_mom.behavior , infant_mom_behaviors[j]; END IF; END LOOP; END IF; -- Mother vocalizations. IF this_1_min_mom.vocalizations IS NOT NULL THEN old_voc := TRIM(BOTH FROM this_1_min_mom.vocalizations); -- Defaults. this_voc_uncertain := default_mom_uncertain; -- Convert the vocalization code. CASE old_voc WHEN 'Chk' THEN new_voc := 'CHK'; WHEN 'Fg' THEN new_voc := 'FG'; WHEN 'Hoo' THEN new_voc := 'HOO'; WHEN 'Lia' THEN new_voc := 'LIA'; WHEN 'Pb' THEN new_voc := 'PB'; WHEN 'Pg' THEN new_voc := 'PG'; WHEN 'Ph' THEN new_voc := 'PH'; WHEN 'Scr' THEN new_voc := 'SCR'; WHEN 'Wb' THEN new_voc := 'WB'; WHEN 'Wh' THEN new_voc := 'WH'; WHEN 'Multi' THEN new_voc := 'MUL'; WHEN 'Other' THEN new_voc := 'OTH'; WHEN 'Grunt' THEN new_voc := 'GR'; WHEN 'Y' THEN new_voc := 'Y'; WHEN '?' THEN new_voc := 'Y'; this_voc_uncertain := TRUE; ELSE RAISE EXCEPTION 'Follow Follow_id %: Follow_1_minute_mother Mother_id %: Date %: Time %: Vocalizations %: The mother vocalization (%) is unknown' , this_follow.follow_id , this_1_min_mom.mother_id , this_follow.follow_date , this_1_min.interval_time , this_1_min_mom.vocalization , old_voc; END CASE; -- Save vocalization event. INSERT INTO events (intid , behavior , position , baboon , uncertain , comment) VALUES ( currval('intervals_intid_seq'::regclass) , 'V' , _gen_pos('V') , FALSE , this_voc_uncertain , NULL); INSERT INTO vocs (eid, vocalization) VALUES (currval('events_eid_seq'::regclass) , new_voc); -- Participant. INSERT INTO parts (eid, part, participant) VALUES (CURRVAL('events_eid_seq'::regclass) , 'gmi_self' , this_follow.mother_id); END IF; -- There is a mom vocalization. -- -- follow_1_minute_sibling: -- SELECT * INTO this_1_min_sib FROM follow_1_minute_sibling WHERE follow_1_minute_sibling.interval_id = this_1_min.interval_id; sibling_found := FOUND; IF sibling_found THEN -- | Behavior | Grooms_other | Groomed_by | -- tableid | Interval_id | Sibling_id | Bad_observation | Behavior | Distance_from_mother | Vocalizations | Plays | Grooms_other | Groomed_by | Distance_from_infant | Sibling_interval_id -- 1 | 11/05/2010_SA_01_01 | SAM | | Vua | 3 | | | | | 3 | 11/05/2010_SA_01_01_SAM -- Sib sanity check. IF NOT(this_follow.sibling_id IS NULL AND this_1_min_sib.sibling_id IS NULL) AND this_follow.sibling_id != this_1_min_sib.sibling_id THEN RAISE EXCEPTION 'Follow Sibling_id %: Follow_1_minute_sibling Sibling_id %: Date %: Time %: The sibling id in the follow does not match the sibling id in the Follow_1_minute_sibling' , this_follow.sibling_id , this_1_min_sib.sibling_id , this_follow.follow_date , this_1_min.interval_time; END IF; -- Check for already converted. IF this_1_min_sib.done THEN RAISE EXCEPTION 'Date %: Mom %: Sibling %: follow_1_minute_sibling row lready converted' , this_follow.follow_date , this_follow.mother_id , this_follow.sibling_id; END IF; -- Sibling uncertainity IF this_1_min_sib.bad_observation IS NOT NULL THEN -- Fuss with sibling uncertainty here, need be. CASE this_1_min_sib.bad_observation WHEN 'Y' THEN default_sib_uncertain := TRUE; WHEN '-' THEN NULL; -- Ignore. ELSE RAISE EXCEPTION 'Follow Follow_id %: Follow_1_minute_sibling Sibling_id %: Date %: Time %: Bad_observation %: The sibling Bad_observation (%) is unrecognized' , this_follow.follow_id , this_1_min_sib.sibling_id , this_follow.follow_date , this_1_min.interval_time , this_1_min_sib.bad_observation , this_1_min_sib.bad_observation; END CASE; END IF; -- Sibling behavior. grooms_other_converted := FALSE; groomed_by_converted := FALSE; plays_converted := FALSE; IF this_1_min_sib.behavior IS NOT NULL THEN -- Record sibling's behavior(s) -- Note that the prepare_data() function has created -- Grooming, Groomed, and Play behaviors when -- there is a Multi behavior, so we don't have to. -- Initialize storage event_behavior := ARRAY[]::TEXT[]; event_uncertain := ARRAY[]::BOOLEAN[]; event_baboon := ARRAY[]::BOOLEAN[]; event_comment := ARRAY[]::TEXT[]; part1_parts := ARRAY[]::TEXT[]; part1_participants := ARRAY[]::TEXT[]; part2_parts := ARRAY[]::TEXT[]; part2_participants := ARRAY[]::TEXT[]; -- Split into separate codes. behaviors := regexp_split_to_array(this_1_min_sib.behavior, ';'); eventcnt := 0; FOREACH old_behavior IN ARRAY behaviors LOOP old_behavior := TRIM(BOTH FROM old_behavior); this_uncertain := default_sib_uncertain; CASE old_behavior WHEN 'Nurse' THEN new_behavior := 'N'; WHEN 'Ride' THEN new_behavior := 'RI'; WHEN '?' THEN new_behavior := 'UNK'; this_uncertain := TRUE; WHEN '-' THEN CONTINUE; -- no event WHEN 'ASSR' THEN new_behavior := 'ASSR'; WHEN 'Bad obs' THEN new_behavior := 'BAD'; this_uncertain := TRUE; WHEN 'Build nest' THEN new_behavior := 'BN'; WHEN 'Climb' THEN new_behavior := 'C'; WHEN 'Drink' THEN new_behavior := 'DR'; WHEN 'Eat' THEN new_behavior := '_ca_eating'; WHEN 'Groomed' THEN new_behavior := 'G'; WHEN 'Grooming' THEN new_behavior := 'G'; WHEN 'Multi' THEN new_behavior := 'MULTI'; WHEN 'Multigroom' THEN new_behavior := 'G'; WHEN 'Other' THEN new_behavior := 'O'; WHEN 'Play' THEN new_behavior := 'P'; WHEN 'Rest' THEN new_behavior := 'R'; WHEN 'Self-groom' THEN new_behavior := 'G'; WHEN 'Travel' THEN new_behavior := 'T'; WHEN 'Vua' THEN new_behavior := 'VUA'; ELSE RAISE EXCEPTION 'Follow Follow_id %: Follow_1_minute_sibling Sibling_id %: Date %: Time %: Behavior %: The sibling behavior (%) is unrecognized' , this_follow.follow_id , this_1_min_sib.sibling_id , this_follow.follow_date , this_1_min.interval_time , this_1_min_sib.behavior , old_behavior; END CASE; -- Setup defaults. eventcnt := eventcnt + 1; event_uncertain[eventcnt] := this_uncertain; event_baboon[eventcnt] := FALSE; event_comment[eventcnt] := NULL; part1_parts[eventcnt] := 'gmi_self'; part1_participants[eventcnt] := this_follow.sibling_id; part2_parts[eventcnt] := NULL; event_behavior[eventcnt] := new_behavior; IF old_behavior = 'Grooming' AND TRIM(BOTH FROM this_1_min_sib.grooms_other) = this_follow.sibling_id THEN RAISE EXCEPTION 'Follow Follow_id %: Follow_1_minute_sibling Sibling_id %: Date %: Time %: Behavior %: The sibling has a Grooming behavior but Grooms_other contains only the sibling''s id' , this_follow.follow_id , this_1_min_sib.sibling_id , this_follow.follow_date , this_1_min.interval_time , this_1_min_sib.behavior; END IF; past_first := FALSE; IF old_behavior = 'Grooming' OR old_behavior = 'Multigroom' OR old_behavior = 'Play' THEN IF (new_behavior = 'G' AND this_1_min_sib.grooms_other IS NOT NULL) OR (new_behavior = 'P' AND this_1_min_sib.plays IS NOT NULL) THEN -- Record the groomed or played with individuals. IF new_behavior = 'G' THEN grooms_other_converted := TRUE; animids := regexp_split_to_array(this_1_min_sib.grooms_other , ','); ELSE -- play plays_converted := TRUE; animids := regexp_split_to_array(this_1_min_sib.plays, ','); END IF; eventcnt := eventcnt - 1; FOREACH animid IN ARRAY animids LOOP eventcnt := eventcnt + 1; past_first := TRUE; animid := convert_animid(TRIM(BOTH FROM animid) , this_follow.follow_date); -- The animal got groomed by or played with the sibling. event_behavior[eventcnt] := new_behavior; event_uncertain[eventcnt] := default_sib_uncertain; event_baboon[eventcnt] := FALSE; event_comment[eventcnt] := NULL; part1_participants[eventcnt] := this_follow.sibling_id; IF old_behavior = 'Play' AND animid != this_follow.mother_id AND animid != this_follow.infant_id THEN part1_parts[eventcnt] := 'gmi_participant'; part2_parts[eventcnt] := 'gmi_participant'; ELSE part1_parts[eventcnt] := 'gmi_actor'; part2_parts[eventcnt] := 'gmi_actee'; END IF; CASE animid WHEN '?' THEN part2_participants[eventcnt] := 'UNK'; event_uncertain[eventcnt] := TRUE; WHEN 'Y' THEN part2_participants[eventcnt] := 'UNK'; WHEN this_follow.sibling_id THEN -- Self grooming or self-play. part1_parts[eventcnt] := 'gmi_self'; part2_parts[eventcnt] := NULL; WHEN '_baboon_id' THEN event_baboon[eventcnt] := TRUE; part2_parts[eventcnt] := NULL; ELSE part2_participants[eventcnt] := animid; END CASE; END LOOP; -- Over grooms_other/plays animids ELSE -- The grooms_other or plays column is null. IF new_behavior = 'G' THEN RAISE EXCEPTION 'Follow Follow_id %: Follow_1_minute_sibling Sibling_id %: Date %: Time %: Behavior %: The Grooms_other column cannot be empty for multi or grooming or multigroom' , this_follow.follow_id , this_1_min_sib.sibling_id , this_follow.follow_date , this_1_min.interval_time , this_1_min_sib.behavior; ELSE -- new_behavior = 'P' RAISE EXCEPTION 'Follow Follow_id %: Follow_1_minute_sibling Sibling_id %: Date %: Time %: Behavior %: The Plays column cannot be empty for multi or play' , this_follow.follow_id , this_1_min_sib.sibling_id , this_follow.follow_date , this_1_min.interval_time , this_1_min_sib.behavior; END IF; END IF; END IF; IF old_behavior = 'Groomed' OR old_behavior = 'Multigroom' THEN IF this_1_min_sib.groomed_by IS NOT NULL THEN -- Record the individuals doing the grooming. groomed_by_converted := TRUE; animids := regexp_split_to_array(this_1_min_sib.groomed_by , ','); IF NOT(past_first) THEN -- Have not yet filled the first participant. eventcnt := eventcnt - 1; END IF; FOREACH animid IN ARRAY animids LOOP eventcnt := eventcnt + 1; animid := convert_animid(TRIM(BOTH FROM animid) , this_follow.follow_date); -- The animal groomed the sibling. event_behavior[eventcnt] := new_behavior; event_uncertain[eventcnt] := default_sib_uncertain; event_baboon[eventcnt] := FALSE; part1_parts[eventcnt] := 'gmi_actee'; part1_participants[eventcnt] := this_follow.sibling_id; part2_parts[eventcnt] := 'gmi_actor'; CASE animid WHEN '?' THEN part2_participants[eventcnt] := 'UNK'; event_uncertain[eventcnt] := TRUE; WHEN 'Y' THEN part2_participants[eventcnt] := 'UNK'; WHEN this_follow.sibling_id THEN RAISE EXCEPTION 'Follow Follow_id %: Follow_1_minute_sibling Sibling_id %: Date %: Time %: Behavior %: Groomed_by %: The focal (the sibling) appears in the Groomed_by column' , this_follow.follow_id , this_1_min_sib.sibling_id , this_follow.follow_date , this_1_min.interval_time , this_1_min_sib.behavior , this_1_min_sib.groomed_by; WHEN '_baboon_id' THEN event_baboon[eventcnt] := TRUE; part2_parts[eventcnt] := NULL; ELSE part2_participants[eventcnt] := animid; END CASE; END LOOP; -- Over groomed_by ELSE -- Groomed_by is NULL IF old_behavior != 'Multi' THEN RAISE EXCEPTION 'Follow Follow_id %: Follow_1_minute_sibling Sibling_id %: Date %: Time %: Behavior %: The Groomed_by column cannot be empty for groomed or multigroom' , this_follow.follow_id , this_1_min_sib.sibling_id , this_follow.follow_date , this_1_min.interval_time , this_1_min_sib.behavior; END IF; END IF; END IF; IF old_behavior = 'Nurse' THEN -- Sibling is nursing. part1_parts[eventcnt] = 'gmi_actor'; part2_parts[eventcnt] = 'gmi_actee'; part2_participants[eventcnt] = this_follow.mother_id; ELSIF old_behavior = 'Ride' THEN -- Sibling is riding. part1_parts[eventcnt] = 'gmi_actor'; part2_parts[eventcnt] = 'gmi_actee'; part2_participants[eventcnt] = 'UNK'; END IF; END LOOP; -- Over sibling behaviors alen := array_length(part1_parts, 1); IF alen IS NOT NULL THEN -- Go thorough all the participants and make events. FOR i IN 1 .. alen LOOP IF event_behavior[i] = 'G' AND ((part1_participants[i] = this_follow.sibling_id AND part2_participants[i] = this_follow.infant_id AND part1_parts[i] = 'gmi_actor' AND part2_parts[i] = 'gmi_actee') OR (part2_participants[i] = this_follow.sibling_id AND part1_participants[i] = this_follow.infant_id AND part2_parts[i] = 'gmi_actor' AND part1_parts[i] = 'gmi_actee')) THEN -- The sib groomed the infant. i_found := FALSE; behavior_cnt := ARRAY_LENGTH(infant_sib_behaviors, 1); IF behavior_cnt IS NOT NULL AND behavior_cnt != 0 THEN FOR j IN 1 .. behavior_cnt LOOP IF infant_sib_behaviors[j] = 'groomed by another' THEN -- We've already recorded this behavior. IF infant_sib_uncertains[j] != event_uncertain[i] AND NOT(infant_sib_uncertains[j]) THEN -- Must make the recorded behavior uncertain. UPDATE events SET uncertain = TRUE WHERE events.eid = infant_sib_events[j]; END IF; -- Note that we've done with it by removing the array -- element. infant_sib_behaviors := infant_sib_behaviors[1:j - 1] || infant_sib_behaviors[j+1:behavior_cnt]; infant_sib_uncertains := infant_sib_uncertains[1:j - 1] || infant_sib_uncertains[j+1:behavior_cnt]; infant_sib_events := infant_sib_events[1:j - 1] || infant_sib_events[j+1:behavior_cnt]; i_found := TRUE; EXIT; END IF; END LOOP; END IF; IF NOT(i_found) THEN RAISE EXCEPTION 'Follow Follow_id %: Follow_1_minute_sibling Sibling_id %: Date %: Time %: Behavior %: Grooms_other %: The follow_1_minute_sibling says the sib groomed the infant but the follow_1_minute_infant has no record of this' , this_follow.follow_id , this_1_min_sib.sibling_id , this_follow.follow_date , this_1_min.interval_time , this_1_min_sib.behavior , this_1_min_sib.grooms_other; END IF; ELSIF event_behavior[i] = 'G' AND ((part1_participants[i] = this_follow.sibling_id AND part2_participants[i] = this_follow.infant_id AND part1_parts[i] = 'gmi_actee' AND part2_parts[i] = 'gmi_actor') OR (part2_participants[i] = this_follow.sibling_id AND part1_participants[i] = this_follow.infant_id AND part2_parts[i] = 'gmi_actee' AND part1_parts[i] = 'gmi_actor')) THEN -- The infant groomed the sib. i_found := FALSE; behavior_cnt := ARRAY_LENGTH(infant_sib_behaviors, 1); IF behavior_cnt IS NOT NULL AND behavior_cnt != 0 THEN FOR j IN 1 .. behavior_cnt LOOP IF infant_sib_behaviors[j] = 'grooms another' THEN -- We've already recorded this behavior. IF infant_sib_uncertains[j] != event_uncertain[i] AND NOT(infant_sib_uncertains[j]) THEN -- Must make the recorded behavior uncertain. UPDATE events SET uncertain = TRUE WHERE events.eid = infant_sib_events[j]; END IF; -- Note that we've done with it by removing the array -- element. infant_sib_behaviors := infant_sib_behaviors[1:j - 1] || infant_sib_behaviors[j+1:behavior_cnt]; infant_sib_uncertains := infant_sib_uncertains[1:j - 1] || infant_sib_uncertains[j+1:behavior_cnt]; infant_sib_events := infant_sib_events[1:j - 1] || infant_sib_events[j+1:behavior_cnt]; i_found := TRUE; EXIT; END IF; END LOOP; END IF; IF NOT(i_found) THEN RAISE EXCEPTION 'Follow Follow_id %: Follow_1_minute_sibling Sibling_id %: Date %: Time %: Behavior %: Groomed_by %: The follow_1_minute_sibling says the infant groomed the sib but the follow_1_minute_infant has no record of this' , this_follow.follow_id , this_1_min_sib.sibling_id , this_follow.follow_date , this_1_min.interval_time , this_1_min_sib.behavior , this_1_min_sib.groomed_by; END IF; ELSIF event_behavior[i] = 'P' AND ((part1_participants[i] = this_follow.sibling_id AND part2_participants[i] = this_follow.infant_id AND part1_parts[i] = 'gmi_actor' AND part2_parts[i] = 'gmi_actee') OR (part2_participants[i] = this_follow.sibling_id AND part1_participants[i] = this_follow.infant_id AND part2_parts[i] = 'gmi_actor' AND part1_parts[i] = 'gmi_actee')) THEN -- The sibling played with the infant. behavior_cnt := ARRAY_LENGTH(infant_sib_behaviors, 1); IF behavior_cnt IS NOT NULL AND behavior_cnt != 0 THEN FOR j IN 1 .. behavior_cnt LOOP IF infant_sib_behaviors[j] = 'play' THEN -- We've already recorded the infant playing with -- the sibling. -- Make the play mutual. UPDATE parts SET part = 'gmi_participant' WHERE parts.eid = infant_sib_events[j]; IF infant_sib_uncertains[j] != event_uncertain[i] AND NOT(infant_sib_uncertains[j]) THEN -- Must make the recorded behavior uncertain. UPDATE events SET uncertain = TRUE WHERE events.eid = infant_sib_events[j]; END IF; EXIT; END IF; END LOOP; END IF; ELSIF event_behavior[i] = 'G' AND ((part1_participants[i] = this_follow.mother_id AND part2_participants[i] = this_follow.sibling_id AND part1_parts[i] = 'gmi_actor' AND part2_parts[i] = 'gmi_actee') OR (part2_participants[i] = this_follow.mother_id AND part1_participants[i] = this_follow.sibling_id AND part2_parts[i] = 'gmi_actor' AND part1_parts[i] = 'gmi_actee')) THEN -- The mom grooms the sib. i_found := FALSE; behavior_cnt := ARRAY_LENGTH(mom_sib_behaviors, 1); IF behavior_cnt IS NOT NULL AND behavior_cnt != 0 THEN FOR j IN 1 .. behavior_cnt LOOP IF mom_sib_behaviors[j] = 'grooms another' THEN -- We've already recorded this behavior. IF mom_sib_uncertains[j] != event_uncertain[i] AND NOT(mom_sib_uncertains[j]) THEN -- Must make the recorded behavior uncertain. UPDATE events SET uncertain = TRUE WHERE events.eid = mom_sib_events[j]; END IF; -- Note that we've done with it by removing the array -- element. mom_sib_behaviors := mom_sib_behaviors[1:j - 1] || mom_sib_behaviors[j+1:behavior_cnt]; mom_sib_uncertains := mom_sib_uncertains[1:j - 1] || mom_sib_uncertains[j+1:behavior_cnt]; mom_sib_events := mom_sib_events[1:j - 1] || mom_sib_events[j+1:behavior_cnt]; i_found := TRUE; EXIT; END IF; END LOOP; END IF; IF NOT(i_found) AND NOT(gp_unknown) THEN RAISE EXCEPTION 'Follow Follow_id %: Follow_1_minute_mother Mother_id %: Date %: Time %: Behavior %: Grooms_other %: The follow_1_minute_sibling says the mother groomed the sib but the follow_1_minute_mother has no record of this' , this_follow.follow_id , this_1_min_mom.mother_id , this_follow.follow_date , this_1_min.interval_time , this_1_min_mom.behavior , this_1_min_mom.grooms_other; END IF; ELSIF event_behavior[i] = 'G' AND ((part1_participants[i] = this_follow.mother_id AND part2_participants[i] = this_follow.sibling_id AND part1_parts[i] = 'gmi_actee' AND part2_parts[i] = 'gmi_actor') OR (part2_participants[i] = this_follow.mother_id AND part1_participants[i] = this_follow.sibling_id AND part2_parts[i] = 'gmi_actee' AND part1_parts[i] = 'gmi_actor')) THEN -- The mom was groomed by sib. i_found := FALSE; behavior_cnt := ARRAY_LENGTH(mom_sib_behaviors, 1); IF behavior_cnt IS NOT NULL AND behavior_cnt != 0 THEN FOR j IN 1 .. behavior_cnt LOOP IF mom_sib_behaviors[j] = 'groomed by another' THEN -- We've already recorded this behavior. IF mom_sib_uncertains[j] != event_uncertain[i] AND NOT(mom_sib_uncertains[j]) THEN -- Must make the recorded behavior uncertain. UPDATE events SET uncertain = TRUE WHERE events.eid = mom_sib_events[j]; END IF; -- Note that we've done with it by removing the array -- element. mom_sib_behaviors := mom_sib_behaviors[1:j - 1] || mom_sib_behaviors[j+1:behavior_cnt]; mom_sib_uncertains := mom_sib_uncertains[1:j - 1] || mom_sib_uncertains[j+1:behavior_cnt]; mom_sib_events := mom_sib_events[1:j - 1] || mom_sib_events[j+1:behavior_cnt]; i_found := TRUE; EXIT; END IF; END LOOP; END IF; IF NOT(i_found) THEN RAISE EXCEPTION 'Follow Follow_id %: Follow_1_minute_mother Mother_id %: Date %: Time %: Behavior %: Groomed_by %: The follow_1_minute_sibling says the sib groomed the mother but the follow_1_minute_mother has no record of this' , this_follow.follow_id , this_1_min_mom.mother_id , this_follow.follow_date , this_1_min.interval_time , this_1_min_mom.behavior , this_1_min_mom.groomed_by; END IF; ELSIF event_behavior[i] = 'P' AND ((part1_participants[i] = this_follow.mother_id AND part2_participants[i] = this_follow.sibling_id AND part1_parts[i] = 'gmi_actee' AND part2_parts[i] = 'gmi_actor') OR (part2_participants[i] = this_follow.mother_id AND part1_participants[i] = this_follow.sibling_id AND part2_parts[i] = 'gmi_actee' AND part1_parts[i] = 'gmi_actor')) THEN -- The sibling played with the mom. behavior_cnt := ARRAY_LENGTH(mom_sib_behaviors, 1); IF behavior_cnt IS NOT NULL AND behavior_cnt != 0 THEN FOR j IN 1 .. behavior_cnt LOOP IF mom_sib_behaviors[j] = 'play' THEN -- We've already recorded the mom playing with -- the sibling. Make the play mutual. UPDATE parts SET part = 'gmi_participant' WHERE parts.eid = mom_sib_events[j]; IF mom_sib_uncertains[j] != event_uncertain[i] AND NOT(mom_sib_uncertains[j]) THEN -- Must make the recorded behavior uncertain. UPDATE events SET uncertain = TRUE WHERE events.eid = mom_sib_events[j]; END IF; EXIT; END IF; END LOOP; END IF; ELSE -- Insert a row into events. INSERT INTO events (intid , behavior , position , baboon , uncertain , comment) VALUES ( currval('intervals_intid_seq'::regclass) , event_behavior[i] , _gen_pos(event_behavior[i]) , event_baboon[i] , event_uncertain[i] , NULL); -- Now save the participant(s). INSERT INTO parts (eid, part, participant) VALUES(currval('events_eid_seq'::regclass) , part1_parts[i]::parts_part , part1_participants[i]); IF part2_parts[i] IS NOT NULL THEN INSERT INTO parts (eid, part, participant) VALUES(currval('events_eid_seq'::regclass) , part2_parts[i]::parts_part , part2_participants[i]); END IF; IF event_behavior[i] = 'R' THEN -- Resting, needs a row on RESTS. INSERT INTO rests (eid, rest) VALUES (CURRVAL('events_eid_seq'::regclass), '9'); ELSIF event_behavior[i] = 'P' THEN -- Playing, needs a row on PLAYS. INSERT INTO plays (eid, play) VALUES (CURRVAL('events_eid_seq'::regclass), 'Y'); ELSIF event_behavior[i] = 'RI' THEN -- Riding, needs a row on RIDES. INSERT INTO rides (eid, ride) VALUES (CURRVAL('events_eid_seq'::regclass), 'U'); END IF; END IF; -- Check for duplicates regards focals END LOOP; -- Over participants END IF; END IF; -- Check to be sure we got all the sibling behavior data. IF NOT(grooms_other_converted) AND this_1_min_sib.grooms_other IS NOT NULL THEN RAISE EXCEPTION 'Follow Follow_id %: Follow_1_minute_sibling Sibling_id %: Date %: Time %: Behavior %: Grooms_other %: The sibling has a Grooms_other value but no behaviors to go with grooming' , this_follow.follow_id , this_1_min_sib.sibling_id , this_follow.follow_date , this_1_min.interval_time , this_1_min_sib.behavior , this_1_min_sib.grooms_other; END IF; IF NOT(groomed_by_converted) AND this_1_min_sib.groomed_by IS NOT NULL THEN RAISE EXCEPTION 'Follow Follow_id %: Follow_1_minute_sibling Sibling_id %: Date %: Time %: Behavior %: Groomed_by %: The sibling has a Grooms_other value but no behaviors to go with grooming' , this_follow.follow_id , this_1_min_sib.sibling_id , this_follow.follow_date , this_1_min.interval_time , this_1_min_sib.behavior , this_1_min_sib.groomed_by; END IF; IF NOT(plays_converted) AND this_1_min_sib.plays IS NOT NULL THEN RAISE EXCEPTION 'Follow Follow_id %: Follow_1_minute_sibling Sibling_id %: Date %: Time %: Behavior %: Plays %: The sibling has a Plays value but no behaviors to go with play' , this_follow.follow_id , this_1_min_sib.sibling_id , this_follow.follow_date , this_1_min.interval_time , this_1_min_sib.behavior , this_1_min_sib.plays; END IF; -- Distance_from_mother this_distance := TRIM(BOTH FROM this_1_min_sib.distance_from_mother); IF this_distance IS NOT NULL AND this_distance != '-' THEN IF this_1_min_sib.distance_from_mother = '?' THEN this_distance := '99'; this_uncertain := TRUE; ELSE this_distance := this_1_min_sib.distance_from_mother; this_uncertain := default_sib_uncertain; END IF; -- Create the distances row. INSERT INTO distances (intid, uncertain, distance) VALUES (currval('intervals_intid_seq'::regclass) , this_uncertain , this_distance); -- Create the distanceparts for the mom. INSERT INTO distanceparts (distid, role, participant) VALUES (currval('distances_distid_seq'::regclass) , 'M' , this_follow.mother_id); -- Create the distanceparts for the sibling. INSERT INTO distanceparts (distid, role, participant) VALUES (currval('distances_distid_seq'::regclass) , 'S' , this_follow.sibling_id); END IF; -- There is a sibling-mom distance. -- Distance_from_infant this_distance := TRIM(BOTH FROM this_1_min_sib.distance_from_infant); IF this_distance IS NOT NULL AND this_distance != '-' THEN IF this_1_min_sib.distance_from_infant = '?' THEN this_distance := '99'; this_uncertain := TRUE; ELSE this_distance := this_1_min_sib.distance_from_infant; this_uncertain := default_sib_uncertain; END IF; -- Create the distances row. INSERT INTO distances (intid, uncertain, distance) VALUES (currval('intervals_intid_seq'::regclass) , this_uncertain , this_distance); -- Create the distanceparts for the sibling. INSERT INTO distanceparts (distid, role, participant) VALUES (currval('distances_distid_seq'::regclass) , 'S' , this_follow.sibling_id); -- Create the distanceparts for the infant. INSERT INTO distanceparts (distid, role, participant) VALUES (currval('distances_distid_seq'::regclass) , 'I' , this_follow.infant_id); END IF; -- There is a sibling-infant distance. -- Sibling vocalizations. IF this_1_min_sib.vocalizations IS NOT NULL THEN old_voc := TRIM(BOTH FROM this_1_min_sib.vocalizations); -- Defaults. this_voc_uncertain := default_sib_uncertain; -- Convert the vocalization code. CASE old_voc WHEN 'Chk' THEN new_voc := 'CHK'; WHEN 'Fg' THEN new_voc := 'FG'; WHEN 'Hoo' THEN new_voc := 'HOO'; WHEN 'Lia' THEN new_voc := 'LIA'; WHEN 'Pb' THEN new_voc := 'PB'; WHEN 'Pg' THEN new_voc := 'PG'; WHEN 'Ph' THEN new_voc := 'PH'; WHEN 'Scr' THEN new_voc := 'SCR'; WHEN 'Wb' THEN new_voc := 'WB'; WHEN 'Wh' THEN new_voc := 'WH'; WHEN 'Multi' THEN new_voc := 'MUL'; WHEN 'Other' THEN new_voc := 'OTH'; WHEN 'Grunt' THEN new_voc := 'GR'; WHEN 'Y' THEN new_voc := 'Y'; WHEN '?' THEN new_voc := 'Y'; this_voc_uncertain := TRUE; ELSE RAISE EXCEPTION 'Follow Follow_id %: Follow_1_minute_sibling Sibling_id %: Date %: Time %: Vocalizations %: The sibling vocalization (%) is unknown' , this_follow.follow_id , this_1_min_sib.sibling_id , this_follow.follow_date , this_1_min.interval_time , this_1_min_sib.vocalization , old_voc; END CASE; -- Save vocalization event. INSERT INTO events (intid , behavior , position , baboon , uncertain , comment) VALUES ( currval('intervals_intid_seq'::regclass) , 'V' , _gen_pos('V') , FALSE , this_voc_uncertain , NULL); INSERT INTO vocs (eid, vocalization) VALUES (currval('events_eid_seq'::regclass) , new_voc); -- Participant. INSERT INTO parts (eid, part, participant) VALUES (CURRVAL('events_eid_seq'::regclass) , 'gmi_self' , this_follow.sibling_id); END IF; -- There is a sib vocalization. END IF; -- There is a sibling. -- And check that all non-play behaviors related to the infant -- involving the sib are also in the sib's record. behavior_cnt := ARRAY_LENGTH(infant_sib_behaviors, 1); IF behavior_cnt IS NOT NULL AND behavior_cnt != 0 THEN FOR j IN 1 .. behavior_cnt LOOP IF infant_sib_behaviors[j] != 'play' THEN RAISE EXCEPTION 'Follow Follow_id %: Follow_1_minute_infant Infant_id %: Date %: Time %: The infant has a behavior (of a kind indicating %) with the sib but the sib has no record of this behavior' , this_follow.follow_id , this_1_min_infant.infant_id , this_follow.follow_date , this_1_min.interval_time , infant_sib_behaviors[j]; END IF; END LOOP; END IF; -- And check that all non-play behaviors related to the mom -- involving the sib are also in the sib's record. behavior_cnt := ARRAY_LENGTH(mom_sib_behaviors, 1); IF behavior_cnt IS NOT NULL AND behavior_cnt != 0 THEN FOR j IN 1 .. behavior_cnt LOOP IF mom_sib_behaviors[j] != 'play' AND NOT(mom_sib_behaviors[j] = 'grooms another' AND gp_unknown) THEN RAISE EXCEPTION 'Follow Follow_id %: Follow_1_minute_mother Mother_id %: Date %: Time %: Behavior %: The mom has a behavior (of a kind indicating %) with the sib but the sib has no record of this behavior' , this_follow.follow_id , this_1_min_mom.mother_id , this_follow.follow_date , this_1_min.interval_time , this_1_min_mom.behavior , mom_sib_behaviors[j]; END IF; END LOOP; END IF; -- Remember we converted this minute. UPDATE follow_1_minute SET done = TRUE WHERE tableid = this_1_min.tableid; -- Remember we converted this mother/minute UPDATE follow_1_minute_mother SET done = TRUE WHERE tableid = this_1_min_mom.tableid; -- Remember we converted this infant/minute. UPDATE follow_1_minute_infant SET done = TRUE WHERE tableid = this_1_min_infant.tableid; IF sibling_found THEN -- Remember we converted this sibling/minute. UPDATE follow_1_minute_sibling SET done = TRUE WHERE tableid = this_1_min_sib.tableid; END IF; END LOOP; -- Over 1 minutes -- Remember we converted this follow. UPDATE follow SET done = TRUE WHERE tableid = this_follow.tableid; -- Keep track of how many follows we converted. converted := converted + 1; END LOOP; RETURN converted; END; $$; grant_func_priv(`prepared_data.convert_access(DATE, DATE)')