# HG changeset patch
# User Pascal Volk <neverseen@users.sourceforge.net>
# Date 1220318141 0
# Node ID 11da3d9298b40a27be8e8ddea0ded740abf6ca94
# Parent  ee0a0b5a8c2b0d56dec48f49903e7a830899c7b8
'create_optional_types_and_functions.pgsql'
    - Added function postfix_virtual_alias_map() any type recipient_destination

diff -r ee0a0b5a8c2b -r 11da3d9298b4 VirtualMailManager/constants/VERSION.py
--- a/VirtualMailManager/constants/VERSION.py	Mon Sep 01 03:11:23 2008 +0000
+++ b/VirtualMailManager/constants/VERSION.py	Tue Sep 02 01:15:41 2008 +0000
@@ -5,3 +5,5 @@
 # $Id$
 
 VERSION = '0.5-dev'
+REVISION = 'rev '+'$Rev$'.split()[1]
+RELDATE = '$Date$'.split()[1]
diff -r ee0a0b5a8c2b -r 11da3d9298b4 create_optional_types_and_functions.pgsql
--- a/create_optional_types_and_functions.pgsql	Mon Sep 01 03:11:23 2008 +0000
+++ b/create_optional_types_and_functions.pgsql	Tue Sep 02 01:15:41 2008 +0000
@@ -6,11 +6,11 @@
 -- ---
 
 -- ---
--- Data type for function postfix_smtpd_sender_login(varchar, varchar)
+-- Data type for function postfix_smtpd_sender_login_map(varchar, varchar)
 -- ---
 CREATE TYPE sender_login AS (
-    sender varchar(320),
-    login text
+    sender  varchar(320),
+    login   text
 );
 
 -- ---
@@ -25,7 +25,7 @@
 --
 -- For more details see postconf(5) section smtpd_sender_login_maps
 -- ---
-CREATE OR REPLACE FUNCTION postfix_smtpd_sender_login(
+CREATE OR REPLACE FUNCTION postfix_smtpd_sender_login_map(
     IN localpart varchar, IN the_domain varchar) RETURNS SETOF sender_login
 AS $$
     DECLARE
@@ -104,3 +104,46 @@
 RETURNS NULL ON NULL INPUT
 EXTERNAL SECURITY INVOKER;
 
+-- ########################################################################## --
+
+-- ---
+-- Data type for function 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 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;
diff -r ee0a0b5a8c2b -r 11da3d9298b4 pgsql-smtpd_sender_login_maps.cf
--- a/pgsql-smtpd_sender_login_maps.cf	Mon Sep 01 03:11:23 2008 +0000
+++ b/pgsql-smtpd_sender_login_maps.cf	Tue Sep 02 01:15:41 2008 +0000
@@ -9,5 +9,4 @@
 dbname = mailsys
 
 # Postfix 2.2 and later The SQL query template. See pgsql_table(5).
-query = SELECT address FROM postfix_maildir WHERE address='%s'
- UNION SELECT destination FROM postfix_alias WHERE address='%s'
+query = SELECT login FROM postfix_smtpd_sender_login_map('%u', '%d')
diff -r ee0a0b5a8c2b -r 11da3d9298b4 vmm
--- a/vmm	Mon Sep 01 03:11:23 2008 +0000
+++ b/vmm	Tue Sep 02 01:15:41 2008 +0000
@@ -6,7 +6,7 @@
 
 """This is the vmm main script."""
 
-from VirtualMailManager.constants.VERSION import VERSION
+from VirtualMailManager.constants.VERSION import VERSION, REVISION, RELDATE
 
 __author__ = 'Pascal Volk <p.volk@veb-it.de>'
 __version__ = VERSION
@@ -357,8 +357,8 @@
 
 def show_version():
     w_std("%s, %s %s (%s %s %s)\nPython %s %s %s\n" % (__prog__, _('version'),
-        __version__, __revision__, _('from'), strftime(
-            locale.nl_langinfo(locale.D_FMT), strptime(__date__, '%Y-%m-%d')),
+        __version__, REVISION, _('from'), strftime(
+            locale.nl_langinfo(locale.D_FMT), strptime(RELDATE, '%Y-%m-%d')),
         sys.version.split()[0], _(u'on'), os.uname()[0]))
 
 #def main():