Index: openacs-4/packages/forums/sql/postgresql/forums-tree-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/forums/sql/postgresql/forums-tree-create.sql,v
diff -u -r1.1 -r1.1.1.1
--- openacs-4/packages/forums/sql/postgresql/forums-tree-create.sql	1 Jun 2002 19:02:29 -0000	1.1
+++ openacs-4/packages/forums/sql/postgresql/forums-tree-create.sql	28 Jun 2006 20:30:28 -0000	1.1.1.1
@@ -13,48 +13,50 @@
 -- This is the sortkey code
 --
 
-
-create or replace trigger forums_mess_insert_tr
-before insert on forums_messages
-for each row
+create function forums_mess_insert_tr ()
+returns opaque as '
 declare
-        v_max_child_sortkey     forums_messages.max_child_sortkey%TYPE;
-        v_parent_sortkey        forums_messages.tree_sortkey%TYPE;
+    v_max_child_sortkey             forums_forums.max_child_sortkey%TYPE;
+    v_parent_sortkey                forums_messages.tree_sortkey%TYPE;
 begin
-   if :new.parent_id is NULL
-   then 
-        -- get the max from the forum
-        select max_child_sortkey into v_max_child_sortkey
-        from forums_forums where forum_id= :new.forum_id
-        for update of max_child_sortkey;
 
-        v_parent_sortkey:= NULL;
-   else
-        -- get the max child sortkey from parent
-        -- grab the lock
-        select tree_sortkey, max_child_sortkey
+    if new.parent_id is null
+    then
+
+        select '''', max_child_sortkey
         into v_parent_sortkey, v_max_child_sortkey
+        from forums_forums
+        where forum_id = new.forum_id
+        for update;
+
+        v_max_child_sortkey := tree_increment_key(v_max_child_sortkey);
+
+        update forums_forums
+        set max_child_sortkey = v_max_child_sortkey
+        where forum_id = new.forum_id;
+
+    else
+
+        select coalesce(tree_sortkey, ''''), max_child_sortkey
+        into v_parent_sortkey, v_max_child_sortkey
         from forums_messages
-        where message_id= :new.parent_id
-        for update of max_child_sortkey;
-   end if;
+        where message_id = new.parent_id
+        for update;
 
-   -- increment the sortkey
-   v_max_child_sortkey:= lpad(tree.increment_key(v_max_child_sortkey),6,'0');
+        v_max_child_sortkey := tree_increment_key(v_max_child_sortkey);
 
-   if :new.parent_id is null
-   then
-      update forums_forums set max_child_sortkey= v_max_child_sortkey
-      where forum_id= :new.forum_id;
-   else
-      -- update the parent
-      update forums_messages set max_child_sortkey= v_max_child_sortkey
-      where message_id= :new.parent_id;
-   end if;
+        update forums_messages
+        set max_child_sortkey = v_max_child_sortkey
+        where message_id = new.parent_id;
 
-   -- generate the current sortkey
-   :new.tree_sortkey:= v_parent_sortkey || v_max_child_sortkey;
-  
-end forums_mess_insert_tr;
-/
-show errors
+    end if;
+
+    new.tree_sortkey := v_parent_sortkey || v_max_child_sortkey;
+
+    return new;
+end;' language 'plpgsql';
+
+create trigger forums_mess_insert_tr
+before insert on forums_messages
+for each row
+execute procedure forums_mess_insert_tr();