--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/create_optional_types_and_functions.pgsql	Mon Sep 01 03:11:23 2008 +0000
@@ -0,0 +1,106 @@
+-- $Id$
+
+-- --- 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(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(
+    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.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
+                       LEFT JOIN domain_name USING (gid)
+                 WHERE 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;
+