-- -- bookmark__update_in_closed_p_one_user/2 -- create or replace function bookmark__update_in_closed_p_one_user( p_bookmark_id integer, p_browsing_user_id bigint ) returns int4 as $$ DECLARE v_parent_ids RECORD; BEGIN -- Update the in_closed_p flag of bookmarks and folders that lie under -- the toggled folder in the tree for one particular user/session. -- First set all in_closed_p flags to f ... UPDATE bm_in_closed_p SET in_closed_p = FALSE WHERE bookmark_id IN ( select bm.bookmark_id from bm_bookmarks bm, bm_bookmarks bm2 where bm2.bookmark_id = p_bookmark_id and bm.tree_sortkey between bm2.tree_sortkey and tree_right(bm2.tree_sortkey) ) AND in_closed_p_id = p_browsing_user_id; -- then set all in_closed_p flags to t that lie under a closed folder FOR v_parent_ids IN select bm.bookmark_id from bm_bookmarks bm, bm_in_closed_p bip where bm.bookmark_id = bip.bookmark_id and bm.folder_p = 't' and bip.closed_p = 't' and bip.in_closed_p_id = p_browsing_user_id LOOP UPDATE bm_in_closed_p set in_closed_p = TRUE WHERE bookmark_id IN ( select bm.bookmark_id from bm_bookmarks bm, bm_bookmarks bm2 where bm2.bookmark_id = v_parent_ids.bookmark_id and bm.tree_sortkey between bm2.tree_sortkey and tree_right(bm2.tree_sortkey) INTERSECT select bm.bookmark_id from bm_bookmarks bm, bm_bookmarks bm2 where bm2.bookmark_id = p_bookmark_id and bm.tree_sortkey between bm2.tree_sortkey and tree_right(bm2.tree_sortkey) ) AND in_closed_p_id = p_browsing_user_id AND bookmark_id <> v_parent_ids.bookmark_id AND bookmark_id <> p_bookmark_id; END LOOP; RETURN 0; END; $$ language plpgsql;