Forum OpenACS Q&A: Problem upgrading oacs-5.8 to oacs-5.9
The script drops the tree_sortkey and max_child_sortkey columns from acs_objects and, given the use of 'cascade', also all the views making use of the two columns. I understand that this is necessary, but having some dozens of views to rebuild it's also quite annoying.
In my case I wrote a small before-upgrade script (luckily all my views share the same prefix) extracting the view definitions from pg_views, removing via regsub the two columns and saving them to a file that I then feeded to psql after the upgrade.
The trick worked except a single view calling the old tree_level function. I had to create a new orresponding function with a recursive query and I post the code here, in case it can be useful to anybody.
CREATE OR REPLACE FUNCTION tree_level(p_object_id integer) RETURNS integer AS $sql$ declare result integer := null; begin with recursive objects as ( select i.object_id, i.context_id from acs_objects i where i.object_id = p_object_id union select p.object_id, p.context_id from acs_objects p, objects s where p.object_id = s.context_id ) select count(*) into result from objects; return result; end;$sql$ LANGUAGE 'plpgsql';