ALTER TABLE chapters ADD COLUMN parent_chapter_id uuid; ALTER TABLE chapters ADD CONSTRAINT chapters_not_self CHECK (parent_chapter_id IS NULL OR parent_chapter_id <> id); ALTER TABLE chapters ADD CONSTRAINT chapters_parent_fk FOREIGN KEY (parent_chapter_id) REFERENCES chapters (id); CREATE INDEX chapters_parent ON chapters (parent_chapter_id); CREATE FUNCTION chapters_parent_guard() RETURNS trigger LANGUAGE plpgsql AS $$ DECLARE parent chapters%ROWTYPE; current_id uuid; seen uuid[] := ARRAY[]::uuid[]; depth integer := 1; BEGIN IF NEW.parent_chapter_id IS NULL THEN RETURN NEW; END IF; SELECT * INTO parent FROM chapters WHERE id = NEW.parent_chapter_id; IF NOT FOUND THEN RAISE EXCEPTION 'Kapiteleltern ungültig'; END IF; IF NEW.case_id IS DISTINCT FROM parent.case_id OR NEW.matter_id IS DISTINCT FROM parent.matter_id OR NEW.home_scope_id IS DISTINCT FROM parent.home_scope_id THEN RAISE EXCEPTION 'Kapiteleltern ungültig'; END IF; current_id := NEW.parent_chapter_id; WHILE current_id IS NOT NULL LOOP IF current_id = NEW.id OR current_id = ANY (seen) THEN RAISE EXCEPTION 'Kapiteleltern ungültig'; END IF; seen := array_append(seen, current_id); depth := depth + 1; IF depth > 32 THEN RAISE EXCEPTION 'Kapiteleltern ungültig'; END IF; SELECT parent_chapter_id INTO current_id FROM chapters WHERE id = current_id; END LOOP; RETURN NEW; END; $$; CREATE TRIGGER chapters_parent_guard BEFORE INSERT OR UPDATE OF parent_chapter_id, case_id, matter_id, home_scope_id ON chapters FOR EACH ROW EXECUTE FUNCTION chapters_parent_guard();