82 END; |
82 END; |
83 $$ LANGUAGE plpgsql STABLE |
83 $$ LANGUAGE plpgsql STABLE |
84 RETURNS NULL ON NULL INPUT |
84 RETURNS NULL ON NULL INPUT |
85 EXTERNAL SECURITY INVOKER; |
85 EXTERNAL SECURITY INVOKER; |
86 -- --- |
86 -- --- |
|
87 -- Data type for function dovecotquotauser(varchar, varchar) |
|
88 -- --- |
|
89 CREATE TYPE dovecotquotauser AS ( |
|
90 userid varchar(320), |
|
91 uid bigint, |
|
92 gid bigint, |
|
93 home text, |
|
94 mail text, |
|
95 quota_rule text |
|
96 ); |
|
97 -- --- |
87 -- Parameters (from login name [localpart@the_domain]): |
98 -- Parameters (from login name [localpart@the_domain]): |
88 -- varchar localpart |
99 -- varchar localpart |
89 -- varchar the_domain |
100 -- varchar the_domain |
90 -- Returns: dovecotuser records |
101 -- Returns: dovecotuser records |
91 -- |
102 -- |
119 RETURN; |
130 RETURN; |
120 END; |
131 END; |
121 $$ LANGUAGE plpgsql STABLE |
132 $$ LANGUAGE plpgsql STABLE |
122 RETURNS NULL ON NULL INPUT |
133 RETURNS NULL ON NULL INPUT |
123 EXTERNAL SECURITY INVOKER; |
134 EXTERNAL SECURITY INVOKER; |
|
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; |