-- -- static_page__delete_stale_items/2 -- create or replace function static_page__delete_stale_items( integer, integer ) returns int4 as $$ declare p_session_id alias for $1; p_package_id alias for $2; v_root_folder_id sp_folders.folder_id%TYPE; v_stale_file_row RECORD; v_stale_folder_row RECORD; begin v_root_folder_id := static_page__get_root_folder(p_package_id); -- First delete all files that are descendants of the root folder -- but aren't in sp_extant_files for v_stale_file_row in select sp.static_page_id from static_pages sp, sp_folders s1, sp_folders s2 where sp.folder_id = s1.folder_id and s2.folder_id = v_root_folder_id and s1.tree_sortkey between s2.tree_sortkey and tree_right(s2.tree_sortkey) and not exists (select 1 from sp_extant_files sef where sef.session_id = p_session_id and sp.static_page_id = sef.static_page_id) loop PERFORM static_page__delete(v_stale_file_row.static_page_id); end loop; -- Now delete all folders that aren't in sp_extant_folders. There are two -- views created on the fly here: dead (all descendants of the root -- folder not in sp_extant_folders) and path (each folder and its depth). -- They are joined together to get the depth of all the folders that -- need to be deleted. The root folder is excluded because it won't -- show up in the filesystem search, so it will be missing from -- sp_extant_folders. for v_stale_folder_row in select dead.folder_id from (select folder_id from sp_folders where folder_id not in ( select folder_id from sp_extant_folders where session_id = p_session_id ) ) dead, (select s1.folder_id,tree_level(s1.tree_sortkey) as depth from sp_folders s1, sp_folders s2 where s2.folder_id = v_root_folder_id and s1.tree_sortkey between s2.tree_sortkey and tree_right(s2.tree_sortkey) ) path where dead.folder_id = path.folder_id and dead.folder_id <> v_root_folder_id order by path.depth desc loop delete from sp_folders where folder_id = v_stale_folder_row.folder_id; perform content_folder__delete(v_stale_folder_row.folder_id); end loop; return 0; end;$$ language plpgsql;