Index: openacs-4/packages/forums/sql/postgresql/forums-messages-package-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/forums/sql/postgresql/forums-messages-package-create.sql,v
diff -u -N -r1.18 -r1.19
--- openacs-4/packages/forums/sql/postgresql/forums-messages-package-create.sql	15 Mar 2005 20:06:45 -0000	1.18
+++ openacs-4/packages/forums/sql/postgresql/forums-messages-package-create.sql	12 Aug 2013 09:34:30 -0000	1.19
@@ -16,33 +16,39 @@
 
 -- Get rid of the old version so we'll throw an error if the admin forgets to reboot
 -- OpenACS after the upgrade (package_instantiate_object caches param lists)
-create or replace function forums_message__new (integer,varchar,integer,varchar,text,char,integer,varchar,integer,timestamptz,integer,varchar,integer)
-returns integer as '
-declare
-    p_message_id                    alias for $1;
-    p_object_type                   alias for $2;
-    p_forum_id                      alias for $3;
-    p_subject                       alias for $4;
-    p_content                       alias for $5;
-    p_format                        alias for $6;
-    p_user_id                       alias for $7;
-    p_state                         alias for $8;
-    p_parent_id                     alias for $9;
-    p_creation_date                 alias for $10;
-    p_creation_user                 alias for $11;
-    p_creation_ip                   alias for $12;
-    p_context_id                    alias for $13;
+
+
+--
+-- procedure forums_message__new/13
+--
+CREATE OR REPLACE FUNCTION forums_message__new(
+   p_message_id integer,
+   p_object_type varchar, -- default 'forums_message'
+   p_forum_id integer,
+   p_subject varchar,
+   p_content text,
+   p_format char,
+   p_user_id integer,
+   p_state varchar,
+   p_parent_id integer,
+   p_creation_date timestamptz,
+   p_creation_user integer,
+   p_creation_ip varchar,
+   p_context_id integer
+
+) RETURNS integer AS $$
+DECLARE
     v_message_id                    integer;
     v_forum_policy                  forums_forums.posting_policy%TYPE;
     v_state                         forums_messages.state%TYPE;
     v_posting_date                  forums_messages.posting_date%TYPE;
     v_package_id                    acs_objects.package_id%TYPE;
-begin
+BEGIN
 
     select package_id into v_package_id from forums_forums where forum_id = p_forum_id;
 
     if v_package_id is null then
-        raise exception ''forums_message__new: forum_id % not found'', p_forum_id;
+        raise exception 'forums_message__new: forum_id % not found', p_forum_id;
     end if;
 
     v_message_id := acs_object__new(
@@ -52,7 +58,7 @@
         p_creation_user,
         p_creation_ip,
         coalesce(p_context_id, p_forum_id),
-        ''t'',
+        't',
         p_subject,
         v_package_id
     );
@@ -63,9 +69,9 @@
         from forums_forums
         where forum_id = p_forum_id;
 
-        if v_forum_policy = ''moderated''
-        then v_state := ''pending'';
-        else v_state := ''approved'';
+        if v_forum_policy = 'moderated'
+        then v_state := 'pending';
+        else v_state := 'approved';
         end if;
     else
         v_state := p_state;
@@ -82,7 +88,7 @@
     where forum_id = p_forum_id;
 
     if p_parent_id is null then
-      if v_state = ''approved'' then
+      if v_state = 'approved' then
         update forums_forums
         set thread_count = thread_count + 1,
           approved_thread_count = approved_thread_count + 1
@@ -93,7 +99,7 @@
         where forum_id=p_forum_id;
       end if;
     else
-      if v_state = ''approved'' then
+      if v_state = 'approved' then
         update forums_messages
         set approved_reply_count = approved_reply_count + 1,
           reply_count = reply_count + 1,
@@ -110,18 +116,24 @@
 
     return v_message_id;
 
-end;' language 'plpgsql';
+END;
+$$ LANGUAGE plpgsql;
 
 select define_function_args ('forums_message__root_message_id', 'message_id');
 
-create or replace function forums_message__root_message_id (integer)
-returns integer as '
-declare
-    p_message_id                    alias for $1;
+
+
+--
+-- procedure forums_message__root_message_id/1
+--
+CREATE OR REPLACE FUNCTION forums_message__root_message_id(
+   p_message_id integer
+) RETURNS integer AS $$
+DECLARE
     v_message_id                    forums_messages.message_id%TYPE;
     v_forum_id                      forums_messages.forum_id%TYPE;
     v_sortkey                       forums_messages.tree_sortkey%TYPE;
-begin
+BEGIN
     select forum_id, tree_sortkey
     into v_forum_id, v_sortkey
     from forums_messages
@@ -134,94 +146,113 @@
     and tree_sortkey = tree_ancestor_key(v_sortkey, 1);
 
     return v_message_id;
-end;
-' language 'plpgsql' stable strict;
+END;
 
+$$ LANGUAGE plpgsql stable strict;
+
 select define_function_args ('forums_message__thread_open', 'message_id');
 
-create or replace function forums_message__thread_open (integer)
-returns integer as '
-declare
-    p_message_id                    alias for $1;
+
+
+--
+-- procedure forums_message__thread_open/1
+--
+CREATE OR REPLACE FUNCTION forums_message__thread_open(
+   p_message_id integer
+) RETURNS integer AS $$
+DECLARE
     v_forum_id                      forums_messages.forum_id%TYPE;
     v_sortkey                       forums_messages.tree_sortkey%TYPE;
-begin
+BEGIN
     select forum_id, tree_sortkey
     into v_forum_id, v_sortkey
     from forums_messages
     where message_id = p_message_id;
 
     update forums_messages
-    set open_p = ''t''
+    set open_p = 't'
     where tree_sortkey between tree_left(v_sortkey) and tree_right(v_sortkey)
     and forum_id = v_forum_id;
 
     update forums_messages
-    set open_p = ''t''
+    set open_p = 't'
     where message_id = p_message_id;
 
     return 0;
-end;
-' language 'plpgsql';
+END;
 
+$$ LANGUAGE plpgsql;
+
 select define_function_args ('forums_message__thread_close', 'message_id');
 
-create or replace function forums_message__thread_close (integer)
-returns integer as '
-declare
-    p_message_id                    alias for $1;
+
+
+--
+-- procedure forums_message__thread_close/1
+--
+CREATE OR REPLACE FUNCTION forums_message__thread_close(
+   p_message_id integer
+) RETURNS integer AS $$
+DECLARE
     v_forum_id                      forums_messages.forum_id%TYPE;
     v_sortkey                       forums_messages.tree_sortkey%TYPE;
-begin
+BEGIN
     select forum_id, tree_sortkey
     into v_forum_id, v_sortkey
     from forums_messages
     where message_id = p_message_id;
 
     update forums_messages
-    set open_p = ''f''
+    set open_p = 'f'
     where tree_sortkey between tree_left(v_sortkey) and tree_right(v_sortkey)
     and forum_id = v_forum_id;
 
     update forums_messages
-    set open_p = ''f''
+    set open_p = 'f'
     where message_id = p_message_id;
 
     return 0;
-end;
-' language 'plpgsql';
+END;
 
+$$ LANGUAGE plpgsql;
+
 select define_function_args ('forums_message__set_state', 'message_id,state');
 
-create or replace function forums_message__set_state(integer,varchar) returns integer as '
-declare
-  p_message_id      alias for $1;
-  p_state           alias for $2;
+
+
+--
+-- procedure forums_message__set_state/2
+--
+CREATE OR REPLACE FUNCTION forums_message__set_state(
+   p_message_id integer,
+   p_state varchar
+) RETURNS integer AS $$
+DECLARE
   v_cur             record;
-begin
+BEGIN
 
   select into v_cur *
   from forums_messages
   where message_id = p_message_id;
 
   if v_cur.parent_id is null then
-    if p_state = ''approved'' and v_cur.state <> ''approved'' then
+    if p_state = 'approved' and v_cur.state <> 'approved' then
       update forums_forums
       set approved_thread_count = approved_thread_count + 1
       where forum_id=v_cur.forum_id;
-    elsif p_state <> ''approved'' and v_cur.state = ''approved'' then
+    elsif p_state <> 'approved' and v_cur.state = 'approved' then
       update forums_forums
       set approved_thread_count = approved_thread_count - 1
       where forum_id=v_cur.forum_id;
     end if;
   else
-    if p_state = ''approved'' and v_cur.state <> ''approved'' then
+    if p_state = 'approved' and v_cur.state <> 'approved' then
       update forums_messages
       set approved_reply_count = approved_reply_count + 1, 
         last_poster = (case when v_cur.posting_date > last_child_post then v_cur.user_id else last_poster end),
         last_child_post = (case when v_cur.posting_date > last_child_post then v_cur.posting_date else last_child_post end)
       where message_id = forums_message__root_message_id(v_cur.message_id);
-    elsif p_state <> ''approved'' and v_cur.state = ''approved'' then
+    elsif p_state <> 'approved' and v_cur.state = 'approved' then
       update forums_messages
       set approved_reply_count = approved_reply_count - 1
       where message_id = forums_message__root_message_id(v_cur.message_id);
@@ -234,16 +265,22 @@
 
   return 0;
 
-end;' language 'plpgsql';
+END;
+$$ LANGUAGE plpgsql;
 
 select define_function_args ('forums_message__delete', 'message_id');
 
-create or replace function forums_message__delete (integer)
-returns integer as '
-declare
-  p_message_id      alias for $1;    
+
+
+--
+-- procedure forums_message__delete/1
+--
+CREATE OR REPLACE FUNCTION forums_message__delete(
+   p_message_id integer
+) RETURNS integer AS $$
+DECLARE
   v_cur             record;
-begin
+BEGIN
 
   -- Maintain the forum thread counts
 
@@ -252,7 +289,7 @@
   where message_id = p_message_id;
 
   if v_cur.parent_id is null then
-    if v_cur.state = ''approved'' then
+    if v_cur.state = 'approved' then
       update forums_forums
       set thread_count = thread_count - 1,
         approved_thread_count = approved_thread_count - 1
@@ -262,7 +299,7 @@
       set thread_count = thread_count - 1
       where forum_id=v_cur.forum_id;
     end if;
-  elsif v_cur.state = ''approved'' then
+  elsif v_cur.state = 'approved' then
     update forums_messages
     set approved_reply_count = approved_reply_count - 1,
       reply_count = reply_count - 1
@@ -275,18 +312,24 @@
 
   perform acs_object__delete(p_message_id);
   return 0;
-end;' language 'plpgsql';
+END;
+$$ LANGUAGE plpgsql;
 
 select define_function_args ('forums_message__delete_thread', 'message_id');
 
-create or replace function forums_message__delete_thread (integer)
-returns integer as '
-declare
-    p_message_id                    alias for $1;
+
+
+--
+-- procedure forums_message__delete_thread/1
+--
+CREATE OR REPLACE FUNCTION forums_message__delete_thread(
+   p_message_id integer
+) RETURNS integer AS $$
+DECLARE
     v_forum_id                      forums_messages.forum_id%TYPE;
     v_sortkey                       forums_messages.tree_sortkey%TYPE;
     v_message                       RECORD;
-begin
+BEGIN
     select forum_id, tree_sortkey
     into v_forum_id, v_sortkey
     from forums_messages
@@ -315,16 +358,23 @@
     perform forums_message__delete(p_message_id);
 
     return 0;
-end;' language 'plpgsql';
+END;
+$$ LANGUAGE plpgsql;
 
 
 select define_function_args('forums_message__name','message_id');
 
-create or replace function forums_message__name (integer)
-returns varchar as '
-declare
-    p_message_id                    alias for $1;
-begin
+
+
+--
+-- procedure forums_message__name/1
+--
+CREATE OR REPLACE FUNCTION forums_message__name(
+   p_message_id integer
+) RETURNS varchar AS $$
+DECLARE
+BEGIN
     return subject from forums_messages where message_id = p_message_id;
-end;
-' language 'plpgsql';
+END;
+
+$$ LANGUAGE plpgsql;
