-- -- forums_message__set_state/2 -- create or replace function forums_message__set_state( integer, character varying ) returns int4 as $$ declare p_message_id alias for $1; p_state alias for $2; v_cur record; 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 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 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 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 update forums_messages set approved_reply_count = approved_reply_count - 1 where message_id = forums_message__root_message_id(v_cur.message_id); end if; end if; update forums_messages set state = p_state where message_id = p_message_id; return 0; end;$$ language plpgsql;