pgsql/create_tables-dovecot-1.2.x.pgsql
branchv0.6.x
changeset 391 8217ddd5220d
parent 388 dd95ed5bc9d2
child 437 9823548b2717
equal deleted inserted replaced
390:660b42391c8e 391:8217ddd5220d
   190       FROM users
   190       FROM users
   191            LEFT JOIN transport USING (tid)
   191            LEFT JOIN transport USING (tid)
   192            LEFT JOIN domain_name USING (gid);
   192            LEFT JOIN domain_name USING (gid);
   193 
   193 
   194 CREATE OR REPLACE VIEW vmm_domain_info AS
   194 CREATE OR REPLACE VIEW vmm_domain_info AS
   195     SELECT gid, domainname, transport, domaindir,
   195     SELECT gid, count(uid) AS accounts,
   196            count(uid) AS accounts,
       
   197            (SELECT count(DISTINCT address)
   196            (SELECT count(DISTINCT address)
   198               FROM alias
   197               FROM alias
   199              WHERE alias.gid = domain_data.gid) AS aliases,
   198              WHERE alias.gid = domain_data.gid) AS aliases,
   200            (SELECT count(gid)
   199            (SELECT count(gid)
   201               FROM relocated
   200               FROM relocated
   202              WHERE relocated.gid = domain_data.gid) AS relocated,
   201              WHERE relocated.gid = domain_data.gid) AS relocated,
   203            (SELECT count(gid)
   202            (SELECT count(gid)
   204               FROM domain_name
   203               FROM domain_name
   205              WHERE domain_name.gid = domain_data.gid
   204              WHERE domain_name.gid = domain_data.gid
   206                AND NOT domain_name.is_primary) AS aliasdomains,
   205                AND NOT domain_name.is_primary) AS aliasdomains
   207            bytes, messages
       
   208       FROM domain_data
   206       FROM domain_data
   209            LEFT JOIN domain_name USING (gid)
   207            LEFT JOIN domain_name USING (gid)
   210            LEFT JOIN quotalimit USING (qid)
       
   211            LEFT JOIN transport USING (tid)
       
   212            LEFT JOIN users USING (gid)
   208            LEFT JOIN users USING (gid)
   213      WHERE domain_name.is_primary
   209      WHERE domain_name.is_primary
   214   GROUP BY gid, domainname, transport, domaindir, bytes, messages;
   210   GROUP BY gid;
   215 
   211 
   216 
   212 
   217 CREATE LANGUAGE plpgsql;
   213 CREATE LANGUAGE plpgsql;
   218 
   214 
   219 
   215