--- a/pgsql/create_optional_types_and_functions.pgsql	Wed Feb 02 21:09:50 2011 +0000
+++ b/pgsql/create_optional_types_and_functions.pgsql	Fri Feb 04 17:29:35 2011 +0000
@@ -287,6 +287,17 @@
     home        text,
     mail        text
 );
+-- ---
+-- Data type for function dovecotquotauser(varchar, varchar) 
+-- ---
+CREATE TYPE dovecotquotauser AS (
+    userid      varchar(320),
+    uid         bigint,
+    gid         bigint,
+    home        text,
+    mail        text,
+    quota_rule  text
+);
 
 -- ---
 -- Parameters (from login name [localpart@the_domain]):
@@ -327,6 +338,44 @@
 RETURNS NULL ON NULL INPUT
 EXTERNAL SECURITY INVOKER;
 
+-- ---
+-- Nearly the same as function dovecotuser above. It returns additionally the
+-- field quota_rule.
+--
+-- Required access privileges for your dovecot database user:
+--      GRANT SELECT
+--          ON users, domain_data, domain_name, maillocation, mailboxformat,
+--             quotalimit
+--          TO dovecot;
+-- ---
+CREATE OR REPLACE FUNCTION dovecotquotauser(
+    IN localpart varchar, IN the_domain varchar) RETURNS SETOF dovecotquotauser
+AS $$
+    DECLARE
+        record dovecotquotauser;
+        userid varchar(320) := localpart || '@' || the_domain;
+        did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain);
+    BEGIN
+        FOR record IN
+            SELECT userid, uid, did, domaindir || '/' || uid AS home,
+                   format || ':~/' || directory AS mail, '*:bytes=' ||
+                   bytes || ':messages=' || messages AS quota_rule
+              FROM users, domain_data, mailboxformat, maillocation, quotalimit
+             WHERE users.gid = did
+               AND users.local_part = localpart
+               AND maillocation.mid = users.mid
+               AND mailboxformat.fid = maillocation.fid
+               AND domain_data.gid = did
+               AND quotalimit.qid = users.qid
+            LOOP
+                RETURN NEXT record;
+            END LOOP;
+        RETURN;
+    END;
+$$ LANGUAGE plpgsql STABLE
+RETURNS NULL ON NULL INPUT
+EXTERNAL SECURITY INVOKER;
+
 -- ########################################################################## --
 
 -- ---