--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/pgsql/update_types_and_functions_0.5.x-0.6.pgsql	Tue Jul 20 03:15:57 2010 +0000
@@ -0,0 +1,123 @@
+SET client_encoding = 'UTF8';
+SET client_min_messages = warning;
+
+
+-- ---
+-- Parameters (from recipients address (MAIL TO) [localpart@the_domain]):
+--      varchar localpart
+--      varchar the_domain
+-- Returns: address_maildir records
+--
+-- Required access privileges for your postfix database user:
+--      GRANT SELECT ON domain_data,domain_name,maillocation,users TO postfix;
+--
+-- For more details see postconf(5) section virtual_mailbox_maps
+-- ---
+CREATE OR REPLACE FUNCTION postfix_virtual_mailbox_map(
+   IN localpart varchar, IN the_domain varchar) RETURNS SETOF address_maildir
+AS $$
+    DECLARE
+        rec address_maildir;
+        did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain);
+        address varchar(320) := localpart || '@' || the_domain;
+    BEGIN
+        FOR rec IN
+            SELECT address, domaindir||'/'||users.uid||'/'||directory||'/'
+              FROM domain_data, users, maillocation
+             WHERE domain_data.gid = did
+               AND users.gid = did
+               AND users.local_part = localpart
+               AND maillocation.mid = users.mid
+            LOOP
+                RETURN NEXT rec;
+            END LOOP;
+        RETURN;
+    END;
+$$ LANGUAGE plpgsql STABLE
+RETURNS NULL ON NULL INPUT
+EXTERNAL SECURITY INVOKER;
+
+
+DROP TYPE dovecotpassword CASCADE;
+-- ---
+-- Data type for function dovecotpassword(varchar, varchar)
+-- ---
+CREATE TYPE dovecotpassword AS (
+    userid      varchar(320),
+    password    varchar(270),
+    smtp        boolean,
+    pop3        boolean,
+    imap        boolean,
+    managesieve boolean
+);
+-- ---
+-- Parameters (from login name [localpart@the_domain]):
+--      varchar localpart
+--      varchar the_domain
+-- Returns: dovecotpassword records
+--
+-- Required access privileges for your dovecot database user:
+--      GRANT SELECT ON users, domain_name TO dovecot;
+--
+-- For more details see http://wiki.dovecot.org/AuthDatabase/SQL
+-- ---
+CREATE OR REPLACE FUNCTION dovecotpassword(
+    IN localpart varchar, IN the_domain varchar) RETURNS SETOF dovecotpassword
+AS $$
+    DECLARE
+        record dovecotpassword;
+        userid varchar(320) := localpart || '@' || the_domain;
+    BEGIN
+        FOR record IN
+            SELECT userid, passwd, smtp, pop3, imap, managesieve
+              FROM users
+             WHERE gid = (SELECT gid
+                            FROM domain_name
+                           WHERE domainname = the_domain)
+               AND local_part = localpart
+            LOOP
+                RETURN NEXT record;
+            END LOOP;
+        RETURN;
+    END;
+$$ LANGUAGE plpgsql STABLE
+RETURNS NULL ON NULL INPUT
+EXTERNAL SECURITY INVOKER;
+-- ---
+-- Parameters (from login name [localpart@the_domain]):
+--      varchar localpart
+--      varchar the_domain
+-- Returns: dovecotuser records
+--
+-- Required access privileges for your dovecot database user:
+--      GRANT SELECT
+--          ON users, domain_data, domain_name, maillocation, mailboxformat
+--          TO dovecot;
+--
+-- For more details see http://wiki.dovecot.org/UserDatabase
+-- ---
+CREATE OR REPLACE FUNCTION dovecotuser(
+    IN localpart varchar, IN the_domain varchar) RETURNS SETOF dovecotuser
+AS $$
+    DECLARE
+        record dovecotuser;
+        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
+              FROM users, domain_data, mailboxformat, maillocation
+             WHERE users.gid = did
+               AND users.local_part = localpart
+               AND maillocation.mid = users.mid
+               AND mailboxformat.fid = maillocation.fid
+               AND domain_data.gid = did
+            LOOP
+                RETURN NEXT record;
+            END LOOP;
+        RETURN;
+    END;
+$$ LANGUAGE plpgsql STABLE
+RETURNS NULL ON NULL INPUT
+EXTERNAL SECURITY INVOKER;