325 END; |
336 END; |
326 $$ LANGUAGE plpgsql STABLE |
337 $$ LANGUAGE plpgsql STABLE |
327 RETURNS NULL ON NULL INPUT |
338 RETURNS NULL ON NULL INPUT |
328 EXTERNAL SECURITY INVOKER; |
339 EXTERNAL SECURITY INVOKER; |
329 |
340 |
|
341 -- --- |
|
342 -- Nearly the same as function dovecotuser above. It returns additionally the |
|
343 -- field quota_rule. |
|
344 -- |
|
345 -- Required access privileges for your dovecot database user: |
|
346 -- GRANT SELECT |
|
347 -- ON users, domain_data, domain_name, maillocation, mailboxformat, |
|
348 -- quotalimit |
|
349 -- TO dovecot; |
|
350 -- --- |
|
351 CREATE OR REPLACE FUNCTION dovecotquotauser( |
|
352 IN localpart varchar, IN the_domain varchar) RETURNS SETOF dovecotquotauser |
|
353 AS $$ |
|
354 DECLARE |
|
355 record dovecotquotauser; |
|
356 userid varchar(320) := localpart || '@' || the_domain; |
|
357 did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain); |
|
358 BEGIN |
|
359 FOR record IN |
|
360 SELECT userid, uid, did, domaindir || '/' || uid AS home, |
|
361 format || ':~/' || directory AS mail, '*:bytes=' || |
|
362 bytes || ':messages=' || messages AS quota_rule |
|
363 FROM users, domain_data, mailboxformat, maillocation, quotalimit |
|
364 WHERE users.gid = did |
|
365 AND users.local_part = localpart |
|
366 AND maillocation.mid = users.mid |
|
367 AND mailboxformat.fid = maillocation.fid |
|
368 AND domain_data.gid = did |
|
369 AND quotalimit.qid = users.qid |
|
370 LOOP |
|
371 RETURN NEXT record; |
|
372 END LOOP; |
|
373 RETURN; |
|
374 END; |
|
375 $$ LANGUAGE plpgsql STABLE |
|
376 RETURNS NULL ON NULL INPUT |
|
377 EXTERNAL SECURITY INVOKER; |
|
378 |
330 -- ########################################################################## -- |
379 -- ########################################################################## -- |
331 |
380 |
332 -- --- |
381 -- --- |
333 -- Data type for function dovecotpassword(varchar, varchar) |
382 -- Data type for function dovecotpassword(varchar, varchar) |
334 -- --- |
383 -- --- |