[Dovecot] Quota PostgreSQL INSERT trigger

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