Index: openacs-4/packages/acs-kernel/sql/postgresql/acs-objects-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/postgresql/acs-objects-create.sql,v
diff -u -r1.2 -r1.3
--- openacs-4/packages/acs-kernel/sql/postgresql/acs-objects-create.sql	14 Mar 2001 14:07:31 -0000	1.2
+++ openacs-4/packages/acs-kernel/sql/postgresql/acs-objects-create.sql	15 Mar 2001 01:37:48 -0000	1.3
@@ -187,13 +187,16 @@
 	last_modified		timestamp default now() not null,
 	modifying_user		integer,
 	modifying_ip		varchar(50) default '' not null,
+        tree_sortkey            varchar(4000),
         constraint acs_objects_context_object_un
 	unique (context_id, object_id)
 );
 
 create index acs_objects_context_object_idx on
        acs_objects (context_id, object_id);
 
+create index acs_objs_tree_skey_idx on acs_objects (tree_sortkey);
+
 -- alter table acs_objects modify constraint acs_objects_context_object_un enable;
 
 create index acs_objects_creation_user_idx on acs_objects (creation_user);
@@ -225,6 +228,82 @@
 create trigger acs_objects_last_mod_update_tr before update on acs_objects
 for each row execute procedure acs_objects_last_mod_update_tr ();
 
+
+create function acs_objects_insert_tr () returns opaque as '
+declare
+        v_parent_sk     varchar;
+        max_key         varchar;
+begin
+        select max(tree_sortkey) into max_key 
+          from acs_objects 
+         where context_id = new.context_id;
+
+        select coalesce(max(tree_sortkey),'''') into v_parent_sk 
+          from acs_objects 
+         where object_id = new.context_id;
+
+        new.tree_sortkey := v_parent_sk || ''/'' || tree_next_key(max_key);
+
+        return new;
+
+end;' language 'plpgsql';
+
+create trigger acs_objects_insert_tr before insert 
+on acs_objects for each row 
+execute procedure acs_objects_insert_tr ();
+
+create function acs_objects_update_tr () returns opaque as '
+declare
+        v_parent_sk     varchar;
+        max_key         varchar;
+        v_rec           record;
+        clr_keys_p      boolean default ''t'';
+begin
+        if new.object_id = old.object_id and 
+           new.context_id = old.context_id then
+
+           return new;
+
+        end if;
+
+        for v_rec in select object_id
+                       from acs_objects 
+                      where tree_sortkey like new.tree_sortkey || ''%''
+                   order by tree_sortkey
+        LOOP
+            if clr_keys_p then
+               update acs_objects set tree_sortkey = null
+               where tree_sortkey like new.tree_sortkey || ''%'';
+               clr_keys_p := ''f'';
+            end if;
+            
+            select max(tree_sortkey) into max_key
+              from acs_objects 
+              where context_id = (select context_id 
+                                    from acs_objects 
+                                   where object_id = v_rec.object_id);
+
+            select coalesce(max(tree_sortkey),'''') into v_parent_sk 
+              from acs_objects 
+             where object_id = (select context_id 
+                                  from acs_objects 
+                                 where object_id = v_rec.object_id);
+
+            update acs_objects 
+               set tree_sortkey = v_parent_sk || ''/'' || tree_next_key(max_key)
+             where object_id = v_rec.object_id;
+
+        end LOOP;
+
+        return new;
+
+end;' language 'plpgsql';
+
+create trigger acs_objects_update_tr after update 
+on acs_objects
+for each row 
+execute procedure acs_objects_update_tr ();
+
 -- show errors
 
 comment on table acs_objects is '
@@ -908,12 +987,12 @@
 
    for v_rec in execute ''select '' || quote_ident(v_column) || '' as return from '' || quote_ident(v_table_name) || '' where '' || quote_literal(v_key_sql)
       LOOP
-        if not FOUND then 
-           return null;
-        end if;
         v_return := v_rec.return;
         exit;
    end loop;
+   if not FOUND then 
+       return null;
+   end if;
 
    return v_return;