[Dovecot] Quota PostgreSQL INSERT trigger

[Adis Nezirovic]

Wiki instructions for creating insert trigger for PostgreSQL are wrong
  http://wiki.dovecot.org/Quota/Dict

Calling INSERT on the table from within BEFORE INSERT trigger creates
cascading trigger.

http://www.postgresql.org/docs/current/interactive/trigger-definition.html


Instead, one should return NEW record from the trigger, and it will be
inserted after trigger execution.

---
CREATE OR REPLACE FUNCTION merge_quota2() RETURNS TRIGGER AS $$
BEGIN
  IF NEW.messages < 0 OR NEW.messages IS NULL THEN
    -- ugly kludge: we came here from this function, really do try to insert
    IF NEW.messages IS NULL THEN
      NEW.messages = 0;
    ELSE
      NEW.messages = -NEW.messages;
    END IF;
    return NEW;
  END IF;

  LOOP
    UPDATE quota2 SET bytes = bytes + NEW.bytes,
      messages = messages + NEW.messages
      WHERE username = NEW.username;
    IF found THEN
      RETURN NULL;
    END IF;

    BEGIN
      IF NEW.messages = 0 THEN
        RETURN NEW;
      ELSE
        NEW.messages = - NEW.messages;
        RETURN NEW;
      END IF;
    EXCEPTION WHEN unique_violation THEN
      -- someone just inserted the record, update it
    END;
  END LOOP;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS mergequota2 ON quota2;
CREATE TRIGGER mergequota2 BEFORE INSERT ON quota2
   FOR EACH ROW EXECUTE PROCEDURE merge_quota2();
---


Best regards,
Adis