diff -r 78b6b06188d3 -r 639cf4003965 pgsql/create_optional_types_and_functions-dovecot-1.2.x.pgsql
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/pgsql/create_optional_types_and_functions-dovecot-1.2.x.pgsql	Fri Oct 23 00:20:33 2009 +0000
@@ -0,0 +1,374 @@
+-- --- Information:
+-- This file contains some data types and functions these should speed up some
+-- operations. Read the comment on each data type/functions for more details.
+-- ---
+
+-- ---
+-- Data type for function postfix_smtpd_sender_login_map(varchar, varchar)
+-- ---
+CREATE TYPE sender_login AS (
+    sender  varchar(320),
+    login   text
+);
+
+-- ---
+-- Parameters (from _sender_ address (MAIL FROM) [localpart@the_domain]):
+--      varchar localpart
+--      varchar the_domain
+-- Returns: SASL _login_ names that own _sender_ addresses (MAIL FROM):
+--      set of sender_login records.
+--
+-- Required access privileges for your postfix database user:
+--      GRANT SELECT ON domain_name, users, alias TO postfix;
+--
+-- For more details see postconf(5) section smtpd_sender_login_maps
+-- ---
+CREATE OR REPLACE FUNCTION postfix_smtpd_sender_login_map(
+    IN localpart varchar, IN the_domain varchar) RETURNS SETOF sender_login
+AS $$
+    DECLARE
+        rec sender_login;
+        did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain);
+        sender varchar(320) := localpart || '@' || the_domain;
+    BEGIN
+        -- Get all addresses for 'localpart' in the primary and aliased domains
+        FOR rec IN
+            SELECT sender, local_part || '@' || domainname
+              FROM domain_name, users
+             WHERE domain_name.gid = did
+               AND users.gid = did
+               AND users.local_part = localpart
+            LOOP
+                RETURN NEXT rec;
+            END LOOP;
+        IF NOT FOUND THEN
+            -- Loop over the alias addresses for localpart@the_domain
+            FOR rec IN
+                SELECT DISTINCT sender, destination
+                  FROM alias
+                 WHERE alias.gid = did
+                   AND alias.address = localpart
+                LOOP
+                    RETURN NEXT rec;
+                END LOOP;
+        END IF;
+        RETURN;
+    END;
+$$ LANGUAGE plpgsql STABLE
+RETURNS NULL ON NULL INPUT
+EXTERNAL SECURITY INVOKER;
+
+-- ########################################################################## --
+
+-- ---
+-- Data type for function postfix_virtual_mailbox(varchar, varchar)
+-- ---
+CREATE TYPE address_maildir AS (
+    address varchar(320),
+    maildir text
+);
+
+-- ---
+-- 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||'/'||maillocation||'/'
+              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;
+
+-- ########################################################################## --
+
+-- ---
+-- Data type for functions: postfix_relocated_map(varchar, varchar)
+--                          postfix_virtual_alias_map(varchar, varchar)
+--                          
+-- ---
+CREATE TYPE recipient_destination AS (
+    recipient   varchar(320),
+    destination text
+);
+
+-- ---
+-- Parameters (from recipients address (MAIL TO) [localpart@the_domain]):
+--      varchar localpart
+--      varchar the_domain
+-- Returns: recipient_destination records
+--
+-- Required access privileges for your postfix database user:
+--      GRANT SELECT ON alias, domain_name TO postfix;
+--
+-- For more details see postconf(5) section virtual_alias_maps and virtual(5)
+-- ---
+CREATE OR REPLACE FUNCTION postfix_virtual_alias_map(
+    IN localpart varchar, IN the_domain varchar)
+    RETURNS SETOF recipient_destination
+AS $$
+    DECLARE
+        record recipient_destination;
+        recipient varchar(320) := localpart || '@' || the_domain;
+        did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain);
+    BEGIN
+        FOR record IN
+            SELECT recipient, destination
+              FROM alias
+             WHERE gid = did
+               AND address = localpart
+            LOOP
+                RETURN NEXT record;
+            END LOOP;
+        RETURN;
+    END;
+$$ LANGUAGE plpgsql STABLE
+RETURNS NULL ON NULL INPUT
+EXTERNAL SECURITY INVOKER;
+
+-- ########################################################################## --
+
+-- ---
+-- Parameters (from recipients address (MAIL TO) [localpart@the_domain]):
+--      varchar localpart
+--      varchar the_domain
+-- Returns: recipient_destination records
+--
+-- Required access privileges for your postfix database user:
+--      GRANT SELECT ON domain_name, relocated TO postfix;
+--
+-- For more details see postconf(5) section relocated_maps and relocated(5)
+-- ---
+CREATE OR REPLACE FUNCTION postfix_relocated_map(
+    IN localpart varchar, IN the_domain varchar)
+    RETURNS SETOF recipient_destination
+AS $$
+    DECLARE
+        record recipient_destination;
+        recipient varchar(320) := localpart || '@' || the_domain;
+        did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain);
+    BEGIN
+        FOR record IN
+            SELECT recipient, destination
+              FROM relocated
+             WHERE gid = did
+               AND address = localpart
+            LOOP
+                RETURN NEXT record;
+            END LOOP;
+        RETURN;
+    END;
+$$ LANGUAGE plpgsql STABLE
+RETURNS NULL ON NULL INPUT
+EXTERNAL SECURITY INVOKER;
+
+-- ########################################################################## --
+
+-- ---
+-- Data type for function postfix_transport_map(varchar, varchar)
+-- ---
+CREATE TYPE recipient_transport AS (
+    recipient   varchar(320),
+    transport   text
+);
+
+-- ---
+-- Parameters (from recipients address (MAIL TO) [localpart@the_domain]):
+--      varchar localpart
+--      varchar the_domain
+-- Returns: recipient_transport records
+--
+-- Required access privileges for your postfix database user:
+--      GRANT SELECT ON users, transport, domain_name TO postfix;
+--
+-- For more details see postconf(5) section transport_maps and transport(5)
+-- ---
+CREATE OR REPLACE FUNCTION postfix_transport_map(
+    IN localpart varchar, IN the_domain varchar)
+    RETURNS SETOF recipient_transport
+AS $$
+    DECLARE
+        record recipient_transport;
+        recipient varchar(320) := localpart || '@' || the_domain;
+    BEGIN
+        FOR record IN
+            SELECT recipient, transport
+              FROM transport
+             WHERE tid = (SELECT tid
+                            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;
+
+-- ########################################################################## --
+
+-- ---
+-- Data type for function postfix_virtual_uid_map(varchar, varchar)
+-- ---
+CREATE TYPE recipient_uid AS (
+    recipient   varchar(320),
+    uid         bigint
+);
+
+-- ---
+-- Parameters (from recipients address (MAIL TO) [localpart@the_domain]):
+--      varchar localpart
+--      varchar the_domain
+-- Returns: recipient_uid records
+--
+-- Required access privileges for your postfix database user:
+--      GRANT SELECT ON users, domain_name TO postfix;
+--
+-- For more details see postconf(5) section virtual_uid_maps
+-- ---
+CREATE OR REPLACE FUNCTION postfix_virtual_uid_map(
+    IN localpart varchar, IN the_domain varchar) RETURNS SETOF recipient_uid
+AS $$
+    DECLARE
+        record recipient_uid;
+        recipient varchar(320) := localpart || '@' || the_domain;
+    BEGIN
+        FOR record IN
+            SELECT recipient, uid
+              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;
+
+-- ########################################################################## --
+
+-- ---
+-- Data type for function dovecotuser(varchar, varchar)
+-- ---
+CREATE TYPE dovecotuser AS (
+    userid      varchar(320),
+    uid         bigint,
+    gid         bigint,
+    home        text,
+    mail        text
+);
+
+-- ---
+-- 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 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,
+                   '~/'|| maillocation AS mail
+              FROM users, domain_data, maillocation
+             WHERE users.gid = did
+               AND users.local_part = localpart
+               AND maillocation.mid = users.mid
+               AND domain_data.gid = did
+            LOOP
+                RETURN NEXT record;
+            END LOOP;
+        RETURN;
+    END;
+$$ LANGUAGE plpgsql STABLE
+RETURNS NULL ON NULL INPUT
+EXTERNAL SECURITY INVOKER;
+
+-- ########################################################################## --
+
+-- ---
+-- Data type for function dovecotpassword(varchar, varchar)
+-- ---
+CREATE TYPE dovecotpassword AS (
+    userid    varchar(320),
+    password  varchar(74),
+    smtp      boolean,
+    pop3      boolean,
+    imap      boolean,
+    sieve     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, sieve
+              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;