-- -- 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;