119 RETURN; |
119 RETURN; |
120 END; |
120 END; |
121 $$ LANGUAGE plpgsql STABLE |
121 $$ LANGUAGE plpgsql STABLE |
122 RETURNS NULL ON NULL INPUT |
122 RETURNS NULL ON NULL INPUT |
123 EXTERNAL SECURITY INVOKER; |
123 EXTERNAL SECURITY INVOKER; |
|
124 -- --- |
|
125 -- Data type for function dovecotquotauser(varchar, varchar) |
|
126 -- --- |
|
127 CREATE TYPE dovecotquotauser AS ( |
|
128 userid varchar(320), |
|
129 uid bigint, |
|
130 gid bigint, |
|
131 home text, |
|
132 mail text, |
|
133 quota_rule text |
|
134 ); |
|
135 -- --- |
|
136 -- Nearly the same as function dovecotuser above. It returns additionally the |
|
137 -- field quota_rule. |
|
138 -- |
|
139 -- Required access privileges for your dovecot database user: |
|
140 -- GRANT SELECT |
|
141 -- ON users, domain_data, domain_name, maillocation, mailboxformat, |
|
142 -- quotalimit |
|
143 -- TO dovecot; |
|
144 -- --- |
|
145 CREATE OR REPLACE FUNCTION dovecotquotauser( |
|
146 IN localpart varchar, IN the_domain varchar) RETURNS SETOF dovecotquotauser |
|
147 AS $$ |
|
148 DECLARE |
|
149 record dovecotquotauser; |
|
150 userid varchar(320) := localpart || '@' || the_domain; |
|
151 did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain); |
|
152 BEGIN |
|
153 FOR record IN |
|
154 SELECT userid, uid, did, domaindir || '/' || uid AS home, |
|
155 format || ':~/' || directory AS mail, '*:bytes=' || |
|
156 bytes || ':messages=' || messages AS quota_rule |
|
157 FROM users, domain_data, mailboxformat, maillocation, quotalimit |
|
158 WHERE users.gid = did |
|
159 AND users.local_part = localpart |
|
160 AND maillocation.mid = users.mid |
|
161 AND mailboxformat.fid = maillocation.fid |
|
162 AND domain_data.gid = did |
|
163 AND quotalimit.qid = users.qid |
|
164 LOOP |
|
165 RETURN NEXT record; |
|
166 END LOOP; |
|
167 RETURN; |
|
168 END; |
|
169 $$ LANGUAGE plpgsql STABLE |
|
170 RETURNS NULL ON NULL INPUT |
|
171 EXTERNAL SECURITY INVOKER; |