-- -- composition_rels_in_tr/0 -- create or replace function composition_rels_in_tr( ) returns trigger as $$ DECLARE v_object_id_one acs_rels.object_id_one%TYPE; v_object_id_two acs_rels.object_id_two%TYPE; v_rel_type acs_rels.rel_type%TYPE; v_error text; map record; BEGIN -- First check if added this relation violated any relational constraints v_error := rel_constraint__violation(new.rel_id); if v_error is not null then raise EXCEPTION '-20000: %', v_error; end if; select object_id_one, object_id_two, rel_type into v_object_id_one, v_object_id_two, v_rel_type from acs_rels where rel_id = new.rel_id; -- Insert a row for me in group_element_index insert into group_element_index (group_id, element_id, rel_id, container_id, rel_type, ancestor_rel_type) values (v_object_id_one, v_object_id_two, new.rel_id, v_object_id_one, v_rel_type, 'composition_rel'); -- Add to the denormalized party_approved_member_map perform party_approved_member__add(v_object_id_one, member_id, rel_id, rel_type) from group_approved_member_map m where group_id = v_object_id_two and not exists (select 1 from group_element_map where group_id = v_object_id_one and element_id = m.member_id and rel_id = m.rel_id); -- Make my composable elements be elements of my new composite group insert into group_element_index (group_id, element_id, rel_id, container_id, rel_type, ancestor_rel_type) select distinct v_object_id_one, element_id, rel_id, container_id, m.rel_type, ancestor_rel_type from group_element_map m join acs_rel_types t on (m.rel_type = t.rel_type) where group_id = v_object_id_two and t.composable_p = 't' and not exists (select 1 from group_element_map where group_id = v_object_id_one and element_id = m.element_id and rel_id = m.rel_id); -- For all direct or indirect containers of my new composite group, -- add me and add my elements for map in select distinct group_id from group_component_map where component_id = v_object_id_one LOOP -- Add a row for me insert into group_element_index (group_id, element_id, rel_id, container_id, rel_type, ancestor_rel_type) values (map.group_id, v_object_id_two, new.rel_id, v_object_id_one, v_rel_type, 'composition_rel'); -- Add to party_approved_member_map perform party_approved_member__add(map.group_id, member_id, rel_id, m.rel_type) from group_approved_member_map m join acs_rel_types t on (m.rel_type = t.rel_type) where group_id = v_object_id_two and t.composable_p = 't' and not exists (select 1 from group_element_map where group_id = map.group_id and element_id = m.member_id and rel_id = m.rel_id); -- Add rows for my composable elements insert into group_element_index (group_id, element_id, rel_id, container_id, rel_type, ancestor_rel_type) select distinct map.group_id, element_id, rel_id, container_id, m.rel_type, ancestor_rel_type from group_element_map m join acs_rel_types t on (m.rel_type = t.rel_type) where group_id = v_object_id_two and t.composable_p = 't' and not exists (select 1 from group_element_map where group_id = map.group_id and element_id = m.element_id and rel_id = m.rel_id); end loop; return new; END; $$ language plpgsql;