These functions are called by the UI or manitou-mdx

CREATE OR REPLACE FUNCTION trash_msg(in_mail_id INTEGER, in_op INTEGER) RETURNS
INTEGER AS $$
DECLARE
new_status INT;
BEGIN
  UPDATE mail SET STATUS=STATUS|16,operator=in_op WHERE mail_id=in_mail_id;
  SELECT INTO new_status STATUS FROM mail WHERE mail_id=in_mail_id;
  RETURN new_status;
END;
$$ LANGUAGE 'plpgsql';

CREATE OR REPLACE FUNCTION trash_msg_set(in_array_mail_id INT[], in_op INT) RETU
RNS INT AS $$
DECLARE
cnt INT;
BEGIN
  UPDATE mail SET STATUS=STATUS|16, operator=in_op WHERE mail_id=any(in_array_mail_id);
  GET DIAGNOSTICS cnt=ROW_COUNT;
  RETURN cnt;
END;
$$ LANGUAGE 'plpgsql';

CREATE OR REPLACE FUNCTION untrash_msg(in_mail_id INT, in_op INT) RETURNS INT AS
 $$
DECLARE
new_status INT;
BEGIN
  UPDATE mail SET STATUS=status&(~16),operator=in_op WHERE mail_id=in_mail_id;
  SELECT INTO new_status STATUS FROM mail WHERE mail_id=in_mail_id;
  RETURN new_status;
END;
$$ LANGUAGE 'plpgsql';

CREATE OR REPLACE FUNCTION delete_msg(INTEGER) RETURNS INTEGER AS $$
DECLARE
        id ALIAS FOR $1;
        attch RECORD;
        cnt INTEGER;
        o oid;
BEGIN
  DELETE FROM notes WHERE mail_id=id;
  DELETE FROM mail_addresses WHERE mail_id=id;
  DELETE FROM header WHERE mail_id=id;
  DELETE FROM body WHERE mail_id=id;
  DELETE FROM mail_tags WHERE mail_id=id;
 
  FOR attch IN SELECT a.attachment_id,c.content,c.fingerprint
    FROM attachments a, attachment_contents c WHERE a.mail_id=id AND c.attachment_id=a.attachment_id
  LOOP
    cnt=0;
    IF attch.fingerprint IS NOT NULL THEN
      -- check if that content is shared with another message's attachment
      SELECT COUNT(*) INTO cnt FROM attachment_contents WHERE fingerprint=attch.fingerprint 
      AND attachment_id!=attch.attachment_id;
    END IF;
    IF (cnt=0) THEN
      PERFORM lo_unlink(attch.content);
    END IF;
    DELETE FROM attachment_contents WHERE attachment_id=attch.attachment_id;
  END LOOP;
 
  DELETE FROM attachments WHERE mail_id=id;
  UPDATE mail SET in_reply_to=NULL WHERE in_reply_to=id;
 
  SELECT mail_text INTO o FROM raw_mail WHERE mail_id=id;
  IF FOUND THEN
     PERFORM lo_unlink(o);
     DELETE FROM raw_mail WHERE mail_id=id;
  END IF;
 
  DELETE FROM mail WHERE mail_id=id;
  IF (FOUND) THEN
          RETURN 1;
  ELSE
          RETURN 0;
  END IF;
END;
$$ LANGUAGE 'plpgsql';

CREATE OR REPLACE FUNCTION delete_msg_set(in_array_mail_id INT[]) RETURNS INT AS
 $$
DECLARE
 cnt INT;
BEGIN
 cnt:=0;
 FOR idx IN array_lower(in_array_mail_id,1)..array_upper(in_array_mail_id,1) LOOP
   cnt:=cnt + delete_msg(in_array_mail_id[idx]);
 END LOOP;
 RETURN cnt;
END;
$$ LANGUAGE 'plpgsql';

The purpose of these functions is to maintain the contents of the mail_status table, which caches the status of current messages (current=not yet processed)

CREATE OR REPLACE FUNCTION insert_mail() RETURNS TRIGGER AS $$
BEGIN
        IF NEW.status&(256+32+16)=0 THEN
          -- The message is not yet sent, archived, or trashed
          INSERT INTO mail_status(mail_id,STATUS) VALUES(NEW.mail_id,NEW.status);
        END IF;
        RETURN NEW;
END;
$$ LANGUAGE 'plpgsql';

CREATE OR REPLACE FUNCTION update_mail() RETURNS TRIGGER AS $$
DECLARE
 rc int4;
BEGIN
   IF NEW.status!=OLD.status THEN
        IF NEW.status&(256+32+16)=0 THEN
          -- The message is not yet sent, archived, or trashed
          UPDATE mail_status
            SET STATUS = NEW.status
           WHERE mail_id = NEW.mail_id;
          GET DIAGNOSTICS rc = ROW_COUNT;
          IF rc=0 THEN
            INSERT INTO mail_status(mail_id,STATUS) VALUES(NEW.mail_id,NEW.status);
          END IF;
        ELSE
          -- The mail has been "processed"
          DELETE FROM mail_status
           WHERE mail_id = NEW.mail_id;
        END IF;
   END IF;
   RETURN NEW;
END;
$$ LANGUAGE 'plpgsql';

CREATE OR REPLACE FUNCTION delete_mail() RETURNS TRIGGER AS $$
BEGIN
        DELETE FROM mail_status WHERE mail_id=OLD.mail_id;
        RETURN OLD;
END;
$$ LANGUAGE 'plpgsql';

CREATE TRIGGER update_mail AFTER UPDATE ON mail
 FOR EACH ROW EXECUTE PROCEDURE update_mail();
 
CREATE TRIGGER insert_mail AFTER INSERT ON mail
 FOR EACH ROW EXECUTE PROCEDURE insert_mail();
 
CREATE TRIGGER delete_mail AFTER DELETE ON mail
 FOR EACH ROW EXECUTE PROCEDURE delete_mail();