I have 2 tables: a group table and element table to manage similar elements into groups.

CREATE TABLE groups (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    main_element UUID NOT NULL REFERENCES elements(id)
);

CREATE TABLE elements (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    group_id UUID NOT NULL REFERENCES groups(id)
    group_similarity NUMERIC DEFAULT 1 CHECK (group_relevance between 0 and 1)
);

Every group has a main element that the rest of the elements are deemed to be similar too. So you can have many elements in a group because those elements are all above some similarity metric when compared to the main element of that group.

This is easy to build for element and group creation. My issue is regarding element and group deletion.

If a main_element gets deleted, I need to recreate that group. I am thinking I will just use the next most similar element as the new main and recalculate similarities to that one.

How can I do that with triggers and constraints?

I want the trigger to run only when a main_element is deleted, which is why I was thinking a custom ON DELETE action would be best if possible. I don't need the trigger to run every time any element is deleted.

CodePudding user response：

Assuming that the main element in a group isn't necessarily the only one having group_similarity = 1 (the main element's duplicate under a different id):

CREATE FUNCTION pick_new_main_element_trigger() 
  RETURNS TRIGGER LANGUAGE plpgsql AS
$$  DECLARE
   new_main_element_id uuid;
   new_highest_similarity numeric;
BEGIN
      if old.id <> (select main_element from groups where id=old.group_id)
        return null;--Not the main element, so trigger shouldn't have fired, so aborting
      end if;
    select max(group_similarity) into new_highest_similarity
        from elements
        where group_id=old.group_id
        and id<>old.id;
    select id into new_main_element_id
        from elements
        where group_similarity=new_highest_similarity
        limit 1;--if there are more than one second bests, random one gets picked
    update groups g
        set main_element=new_main_element_id
        where g.id=old.group_id;
    update elements --equal shift towards 1, by the best one's distance to 1
        set group_similarity=group_similarity 1-new_highest_similarity
        where group_id=old.group_id
        and id<>old.id
        and id<>new_main_element_id;
    update elements--new main element is 100% self-similar
        set group_similarity=1
        where group_id=old.group_id
        and id<>old.id
        and id=new_main_element_id;
    RETURN OLD;
END $$;

You can narrow down when this trigger fires using WHEN condition. You'll still have some false positives but they should hit the failsafe IF at the beginning of the function above.

CREATE TRIGGER pick_new_main_element
BEFORE DELETE ON elements 
FOR EACH ROW
WHEN (old.group_similarity=1)
EXECUTE PROCEDURE pick_new_main_element_trigger();

Online demo.

In the presented structure, groups table seems not entirely necessary, or at least not used this way. Since a group always has a single main element, you can just consider the main element id the group_id - point from each element to its group's main element.

1. You save a table.
2. You can make the trigger fire exclusively when the main element is being deleted by checking right in the trigger WHEN condition id=main_element_id AND group_similarity=1.
3. Inserts are simpler because you get rid of a circular reference.
4. If deleting the main element forces you to update each element with recalculated group_similarity, then updating their group_id as well doesn't add much overhead. Otherwise you might have wanted to keep groups abstracted so that when the main element changes, only the groups record requires an update, while all records in elements keep pointing at it.