Index: openacs-4/packages/dotlrn/sql/postgresql/communities-tree-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn/sql/postgresql/communities-tree-create.sql,v
diff -u -r1.1 -r1.2
--- openacs-4/packages/dotlrn/sql/postgresql/communities-tree-create.sql	8 Jul 2002 21:04:16 -0000	1.1
+++ openacs-4/packages/dotlrn/sql/postgresql/communities-tree-create.sql	11 Jul 2002 15:54:13 -0000	1.2
@@ -17,24 +17,24 @@
 create function dotlrn_community_types_in_tr()
 returns opaque as '
 declare
-    v_parent_sortkey dotlrn_community_types.tree_sortkey%TYPE;
-    v_max_child_sortkey dotlrn_community_types.max_child_sortkey%TYPE;
+    v_parent_sortkey                dotlrn_community_types.tree_sortkey%TYPE;
+    v_max_child_sortkey             dotlrn_community_types.max_child_sortkey%TYPE;
 begin
 
     if new.supertype is null then
         -- if this is the root community_type we leave it''s sortkey as null
         return new;
     else
         -- else get the max_child_sortkey of the parent community_type
-        select tree_sortkey, max_child_sortkey
+        select coalesce(tree_sortkey, ''''), max_child_sortkey
         into v_parent_sortkey, v_max_child_sortkey
         from dotlrn_community_types
         where community_type = new.supertype
         for update;
     end if;
 
     -- increment the sort_key
-    v_max_child_sortkey := tree_increment_key(v_max_child_sortkey);
+    v_max_child_sortkey := tree__increment_key(v_max_child_sortkey);
 
     update dotlrn_community_types
     set max_child_sortkey = v_max_child_sortkey
@@ -55,47 +55,47 @@
 create function dotlrn_communities_in_tr()
 returns opaque as '
 declare
-    v_parent_sortkey dotlrn_communities_all.tree_sortkey%TYPE;
-    v_max_child_sortkey dotlrn_communities_all.max_child_sortkey%TYPE;
+    v_parent_sortkey                dotlrn_communities_all.tree_sortkey%TYPE;
+    v_max_child_sortkey             dotlrn_communities_all.max_child_sortkey%TYPE;
 begin
 
     if new.parent_community_id is null then
-        -- if this is the root community we get the sortkey from it''s parent
-        -- community_type
-        select tree_sortkey, max_child_sortkey
+
+        select coalesce(tree_sortkey, ''''), max_child_sortkey
         into v_parent_sortkey, v_max_child_sortkey
         from dotlrn_community_types
         where community_type = new.community_type
         for update;
+
+        v_max_child_sortkey := tree_increment_key(v_max_child_sortkey);
+
+        update dotlrn_community_types
+        set max_child_sortkey = v_max_child_sortkey
+        where community_type = new.community_type;
+
     else
-        -- else get the max_child_sortkey of the parent community_type
-        select tree_sortkey, max_child_sortkey
+
+        select coalesce(tree_sortkey, ''''), max_child_sortkey
         into v_parent_sortkey, v_max_child_sortkey
         from dotlrn_communities_all
         where community_id = new.parent_community_id
         for update;
-    end if;
 
-    -- increment the sort_key
-    v_max_child_sortkey := tree_increment_key(v_max_child_sortkey);
+        v_max_child_sortkey := tree_increment_key(v_max_child_sortkey);
 
-    if new.parent_community_id is null then
-        update dotlrn_community_types
-        set max_child_sortkey = v_max_child_sortkey
-        where community_type = new.community_type;
-    else
         update dotlrn_communities_all
         set max_child_sortkey = v_max_child_sortkey
         where community_id = new.parent_community_id;
+
     end if;
 
-    -- generate the sortkey for the current row
     new.tree_sortkey := v_parent_sortkey || v_max_child_sortkey;
 
     return new;
+
 end;' language 'plpgsql';
 
 create trigger dotlrn_communities_in_tr
 before insert on dotlrn_communities_all
 for each row
-execute procedure dotlrn_community_types_in_tr();
+execute procedure dotlrn_communities_in_tr();