--
-- acs_rels_in_tr/0
--
create or replace function acs_rels_in_tr(
  
) returns trigger as $$

declare
  dummy integer;
  target_object_type_one acs_object_types.object_type%TYPE;
  target_object_type_two acs_object_types.object_type%TYPE;
  actual_object_type_one acs_object_types.object_type%TYPE;
  actual_object_type_two acs_object_types.object_type%TYPE;
begin

    -- DRB: The obvious rewrite to use Dan's port of this to use tree_ancestor_keys kills
    -- Postgres!!!  Argh!!!  This is fast, to, so there ...

    -- Get all the object type info from the relationship.

    select rt.object_type_one, rt.object_type_two,
           o1.object_type, o2.object_type
    into target_object_type_one, target_object_type_two,
         actual_object_type_one, actual_object_type_two
    from acs_rel_types rt, acs_objects o1, acs_objects o2
    where rt.rel_type = new.rel_type
      and o1.object_id = new.object_id_one
      and o2.object_id = new.object_id_two;

    if not exists (select 1
                    from 
                    (select tree_ancestor_keys(acs_object_type_get_tree_sortkey(actual_object_type_one))
                      as tree_sortkey) parents1,
                    (select tree_ancestor_keys(acs_object_type_get_tree_sortkey(actual_object_type_two))
                      as tree_sortkey) parents2,
                    (select tree_sortkey from acs_object_types where object_type = target_object_type_one)
                      root1,
                    (select tree_sortkey from acs_object_types where object_type = target_object_type_two)
                      root2
                   where root1.tree_sortkey = parents1.tree_sortkey
                     and root2.tree_sortkey = parents2.tree_sortkey) then

      raise EXCEPTION '-20001: %  violation: Invalid object types. Object % (%) must be of type % Object % (%) must be of type %', new.rel_type, 
                                         new.object_id_one,
                                         actual_object_type_one,
                                         target_object_type_one,
                                         new.object_id_two,
                                         actual_object_type_two,
                                         target_object_type_two;

    end if;

    return new;

end;$$ language plpgsql;