-- -- cr_keywords_tree_update_tr/0 -- create or replace function cr_keywords_tree_update_tr( ) returns trigger as $$ declare v_parent_sk varbit default null; v_max_value integer; p_id integer; v_rec record; clr_keys_p boolean default 't'; begin if new.keyword_id = old.keyword_id and ((new.parent_id = old.parent_id) or (new.parent_id is null and old.parent_id is null)) THEN return new; end if; for v_rec in select keyword_id from cr_keywords where tree_sortkey between new.tree_sortkey and tree_right(new.tree_sortkey) order by tree_sortkey LOOP if clr_keys_p then update cr_keywords set tree_sortkey = null where tree_sortkey between new.tree_sortkey and tree_right(new.tree_sortkey); clr_keys_p := 'f'; end if; select parent_id into p_id from cr_keywords where keyword_id = v_rec.keyword_id; if p_id is null then select max(tree_leaf_key_to_int(tree_sortkey)) into v_max_value from cr_keywords where parent_id is null; else select max(tree_leaf_key_to_int(tree_sortkey)) into v_max_value from cr_keywords where parent_id = p_id; select tree_sortkey into v_parent_sk from cr_keywords where keyword_id = p_id; end if; update cr_keywords set tree_sortkey = tree_next_key(v_parent_sk, v_max_value) where keyword_id = v_rec.keyword_id; end LOOP; return new; end;$$ language plpgsql;