-- -- bm_bookmarks_update_tr/0 -- create or replace function bm_bookmarks_update_tr( ) returns trigger as $$ declare v_parent_sk varbit default null; v_max_value integer; ctx_id integer; v_rec record; clr_keys_p boolean default 't'; begin if new.bookmark_id = old.bookmark_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 bookmark_id from bm_bookmarks where tree_sortkey between new.tree_sortkey and tree_right(new.tree_sortkey) order by tree_sortkey LOOP if clr_keys_p then update bm_bookmarks 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 ctx_id from bm_bookmarks where bookmark_id = v_rec.bookmark_id; if ctx_id is null then select max(tree_leaf_key_to_int(tree_sortkey)) into v_max_value from bm_bookmarks where parent_id is null; else select max(tree_leaf_key_to_int(tree_sortkey)) into v_max_value from bm_bookmarks where parent_id = ctx_id; select tree_sortkey into v_parent_sk from bm_bookmarks where bookmark_id = ctx_id; end if; update bm_bookmarks set tree_sortkey = tree_next_value(v_parent_sk, v_max_value) where bookmark_id = v_rec.bookmark_id; end LOOP; return new; end;$$ language plpgsql;