Non-trigger Functions

These functions are called by the UI or manitou-mdx

trash_msg

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';

trash_msg_set

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';

untrash_msg

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';

delete_msg

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';

delete_msg_set

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';

Trigger Functions

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)

insert_mail

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';

update_mail

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';

delete_mail

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';

Trigger declarations

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();