create_optional_types_and_functions.pgsql
author Pascal Volk <neverseen@users.sourceforge.net>
Fri, 04 Sep 2009 20:27:39 +0000
changeset 142 28f26f7f3d8f
parent 102 485d3f7d6981
permissions -rw-r--r--
Reduced the mixing/concatenating of str and unicode objects. Optimized list generation in Domain class.
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
72
ee0a0b5a8c2b * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
     1
-- --- Information:
ee0a0b5a8c2b * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
     2
-- This file contains some data types and functions these should speed up some
ee0a0b5a8c2b * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
     3
-- operations. Read the comment on each data type/functions for more details.
ee0a0b5a8c2b * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
     4
-- ---
ee0a0b5a8c2b * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
     5
ee0a0b5a8c2b * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
     6
-- ---
73
11da3d9298b4 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 72
diff changeset
     7
-- Data type for function postfix_smtpd_sender_login_map(varchar, varchar)
72
ee0a0b5a8c2b * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
     8
-- ---
ee0a0b5a8c2b * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
     9
CREATE TYPE sender_login AS (
73
11da3d9298b4 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 72
diff changeset
    10
    sender  varchar(320),
11da3d9298b4 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 72
diff changeset
    11
    login   text
72
ee0a0b5a8c2b * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    12
);
ee0a0b5a8c2b * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    13
ee0a0b5a8c2b * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    14
-- ---
ee0a0b5a8c2b * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    15
-- Parameters (from _sender_ address (MAIL FROM) [localpart@the_domain]):
ee0a0b5a8c2b * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    16
--      varchar localpart
ee0a0b5a8c2b * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    17
--      varchar the_domain
ee0a0b5a8c2b * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    18
-- Returns: SASL _login_ names that own _sender_ addresses (MAIL FROM):
ee0a0b5a8c2b * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    19
--      set of sender_login records.
ee0a0b5a8c2b * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    20
--
ee0a0b5a8c2b * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    21
-- Required access privileges for your postfix database user:
ee0a0b5a8c2b * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    22
--      GRANT SELECT ON domain_name, users, alias TO postfix;
ee0a0b5a8c2b * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    23
--
ee0a0b5a8c2b * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    24
-- For more details see postconf(5) section smtpd_sender_login_maps
ee0a0b5a8c2b * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    25
-- ---
73
11da3d9298b4 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 72
diff changeset
    26
CREATE OR REPLACE FUNCTION postfix_smtpd_sender_login_map(
72
ee0a0b5a8c2b * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    27
    IN localpart varchar, IN the_domain varchar) RETURNS SETOF sender_login
ee0a0b5a8c2b * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    28
AS $$
ee0a0b5a8c2b * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    29
    DECLARE
ee0a0b5a8c2b * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    30
        rec sender_login;
ee0a0b5a8c2b * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    31
        did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain);
ee0a0b5a8c2b * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    32
        sender varchar(320) := localpart || '@' || the_domain;
ee0a0b5a8c2b * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    33
    BEGIN
ee0a0b5a8c2b * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    34
        -- Get all addresses for 'localpart' in the primary and aliased domains
ee0a0b5a8c2b * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    35
        FOR rec IN
ee0a0b5a8c2b * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    36
            SELECT sender, local_part || '@' || domainname
ee0a0b5a8c2b * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    37
              FROM domain_name, users
ee0a0b5a8c2b * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    38
             WHERE domain_name.gid = did
75
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
    39
               AND users.gid = did
72
ee0a0b5a8c2b * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    40
               AND users.local_part = localpart
ee0a0b5a8c2b * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    41
            LOOP
ee0a0b5a8c2b * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    42
                RETURN NEXT rec;
ee0a0b5a8c2b * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    43
            END LOOP;
ee0a0b5a8c2b * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    44
        IF NOT FOUND THEN
ee0a0b5a8c2b * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    45
            -- Loop over the alias addresses for localpart@the_domain
ee0a0b5a8c2b * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    46
            FOR rec IN
ee0a0b5a8c2b * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    47
                SELECT DISTINCT sender, destination
ee0a0b5a8c2b * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    48
                  FROM alias
82
6c85915f3815 „speedup commit“ ;-)
Pascal Volk <neverseen@users.sourceforge.net>
parents: 75
diff changeset
    49
                 WHERE alias.gid = did
6c85915f3815 „speedup commit“ ;-)
Pascal Volk <neverseen@users.sourceforge.net>
parents: 75
diff changeset
    50
                   AND alias.address = localpart
72
ee0a0b5a8c2b * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    51
                LOOP
ee0a0b5a8c2b * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    52
                    RETURN NEXT rec;
ee0a0b5a8c2b * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    53
                END LOOP;
ee0a0b5a8c2b * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    54
        END IF;
ee0a0b5a8c2b * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    55
        RETURN;
ee0a0b5a8c2b * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    56
    END;
ee0a0b5a8c2b * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    57
$$ LANGUAGE plpgsql STABLE
ee0a0b5a8c2b * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    58
RETURNS NULL ON NULL INPUT
ee0a0b5a8c2b * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    59
EXTERNAL SECURITY INVOKER;
ee0a0b5a8c2b * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    60
ee0a0b5a8c2b * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    61
-- ########################################################################## --
ee0a0b5a8c2b * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    62
ee0a0b5a8c2b * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    63
-- ---
ee0a0b5a8c2b * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    64
-- Data type for function postfix_virtual_mailbox(varchar, varchar)
ee0a0b5a8c2b * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    65
-- ---
ee0a0b5a8c2b * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    66
CREATE TYPE address_maildir AS (
ee0a0b5a8c2b * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    67
    address varchar(320),
ee0a0b5a8c2b * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    68
    maildir text
ee0a0b5a8c2b * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    69
);
ee0a0b5a8c2b * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    70
ee0a0b5a8c2b * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    71
-- ---
ee0a0b5a8c2b * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    72
-- Parameters (from recipients address (MAIL TO) [localpart@the_domain]):
ee0a0b5a8c2b * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    73
--      varchar localpart
ee0a0b5a8c2b * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    74
--      varchar the_domain
ee0a0b5a8c2b * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    75
-- Returns: address_maildir records
ee0a0b5a8c2b * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    76
--
ee0a0b5a8c2b * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    77
-- Required access privileges for your postfix database user:
ee0a0b5a8c2b * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    78
--      GRANT SELECT ON domain_data,domain_name,maillocation,users TO postfix;
ee0a0b5a8c2b * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    79
--
ee0a0b5a8c2b * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    80
-- For more details see postconf(5) section virtual_mailbox_maps
ee0a0b5a8c2b * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    81
-- ---
ee0a0b5a8c2b * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    82
CREATE OR REPLACE FUNCTION postfix_virtual_mailbox_map(
ee0a0b5a8c2b * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    83
   IN localpart varchar, IN the_domain varchar) RETURNS SETOF address_maildir
ee0a0b5a8c2b * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    84
AS $$
ee0a0b5a8c2b * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    85
    DECLARE
ee0a0b5a8c2b * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    86
        rec address_maildir;
ee0a0b5a8c2b * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    87
        did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain);
ee0a0b5a8c2b * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    88
        address varchar(320) := localpart || '@' || the_domain;
ee0a0b5a8c2b * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    89
    BEGIN
ee0a0b5a8c2b * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    90
        FOR rec IN
ee0a0b5a8c2b * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    91
            SELECT address, domaindir||'/'||users.uid||'/'||maillocation||'/'
ee0a0b5a8c2b * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    92
              FROM domain_data, users, maillocation
ee0a0b5a8c2b * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    93
             WHERE domain_data.gid = did
ee0a0b5a8c2b * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    94
               AND users.gid = did
ee0a0b5a8c2b * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    95
               AND users.local_part = localpart
ee0a0b5a8c2b * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    96
               AND maillocation.mid = users.mid
ee0a0b5a8c2b * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    97
            LOOP
ee0a0b5a8c2b * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    98
                RETURN NEXT rec;
ee0a0b5a8c2b * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    99
            END LOOP;
ee0a0b5a8c2b * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   100
        RETURN;
ee0a0b5a8c2b * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   101
    END;
ee0a0b5a8c2b * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   102
$$ LANGUAGE plpgsql STABLE
ee0a0b5a8c2b * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   103
RETURNS NULL ON NULL INPUT
ee0a0b5a8c2b * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   104
EXTERNAL SECURITY INVOKER;
ee0a0b5a8c2b * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   105
73
11da3d9298b4 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 72
diff changeset
   106
-- ########################################################################## --
11da3d9298b4 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 72
diff changeset
   107
11da3d9298b4 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 72
diff changeset
   108
-- ---
74
67a454ea5472 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 73
diff changeset
   109
-- Data type for functions: postfix_relocated_map(varchar, varchar)
67a454ea5472 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 73
diff changeset
   110
--                          postfix_virtual_alias_map(varchar, varchar)
67a454ea5472 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 73
diff changeset
   111
--                          
73
11da3d9298b4 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 72
diff changeset
   112
-- ---
11da3d9298b4 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 72
diff changeset
   113
CREATE TYPE recipient_destination AS (
11da3d9298b4 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 72
diff changeset
   114
    recipient   varchar(320),
11da3d9298b4 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 72
diff changeset
   115
    destination text
11da3d9298b4 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 72
diff changeset
   116
);
11da3d9298b4 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 72
diff changeset
   117
11da3d9298b4 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 72
diff changeset
   118
-- ---
11da3d9298b4 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 72
diff changeset
   119
-- Parameters (from recipients address (MAIL TO) [localpart@the_domain]):
11da3d9298b4 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 72
diff changeset
   120
--      varchar localpart
11da3d9298b4 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 72
diff changeset
   121
--      varchar the_domain
11da3d9298b4 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 72
diff changeset
   122
-- Returns: recipient_destination records
11da3d9298b4 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 72
diff changeset
   123
--
11da3d9298b4 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 72
diff changeset
   124
-- Required access privileges for your postfix database user:
74
67a454ea5472 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 73
diff changeset
   125
--      GRANT SELECT ON alias, domain_name TO postfix;
73
11da3d9298b4 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 72
diff changeset
   126
--
11da3d9298b4 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 72
diff changeset
   127
-- For more details see postconf(5) section virtual_alias_maps and virtual(5)
11da3d9298b4 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 72
diff changeset
   128
-- ---
11da3d9298b4 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 72
diff changeset
   129
CREATE OR REPLACE FUNCTION postfix_virtual_alias_map(
11da3d9298b4 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 72
diff changeset
   130
    IN localpart varchar, IN the_domain varchar)
11da3d9298b4 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 72
diff changeset
   131
    RETURNS SETOF recipient_destination
11da3d9298b4 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 72
diff changeset
   132
AS $$
11da3d9298b4 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 72
diff changeset
   133
    DECLARE
11da3d9298b4 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 72
diff changeset
   134
        record recipient_destination;
11da3d9298b4 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 72
diff changeset
   135
        recipient varchar(320) := localpart || '@' || the_domain;
11da3d9298b4 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 72
diff changeset
   136
        did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain);
11da3d9298b4 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 72
diff changeset
   137
    BEGIN
11da3d9298b4 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 72
diff changeset
   138
        FOR record IN
11da3d9298b4 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 72
diff changeset
   139
            SELECT recipient, destination
11da3d9298b4 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 72
diff changeset
   140
              FROM alias
11da3d9298b4 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 72
diff changeset
   141
             WHERE gid = did
11da3d9298b4 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 72
diff changeset
   142
               AND address = localpart
11da3d9298b4 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 72
diff changeset
   143
            LOOP
11da3d9298b4 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 72
diff changeset
   144
                RETURN NEXT record;
11da3d9298b4 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 72
diff changeset
   145
            END LOOP;
11da3d9298b4 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 72
diff changeset
   146
        RETURN;
11da3d9298b4 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 72
diff changeset
   147
    END;
11da3d9298b4 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 72
diff changeset
   148
$$ LANGUAGE plpgsql STABLE
11da3d9298b4 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 72
diff changeset
   149
RETURNS NULL ON NULL INPUT
11da3d9298b4 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 72
diff changeset
   150
EXTERNAL SECURITY INVOKER;
74
67a454ea5472 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 73
diff changeset
   151
67a454ea5472 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 73
diff changeset
   152
-- ########################################################################## --
67a454ea5472 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 73
diff changeset
   153
67a454ea5472 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 73
diff changeset
   154
-- ---
67a454ea5472 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 73
diff changeset
   155
-- Parameters (from recipients address (MAIL TO) [localpart@the_domain]):
67a454ea5472 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 73
diff changeset
   156
--      varchar localpart
67a454ea5472 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 73
diff changeset
   157
--      varchar the_domain
67a454ea5472 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 73
diff changeset
   158
-- Returns: recipient_destination records
67a454ea5472 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 73
diff changeset
   159
--
67a454ea5472 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 73
diff changeset
   160
-- Required access privileges for your postfix database user:
67a454ea5472 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 73
diff changeset
   161
--      GRANT SELECT ON domain_name, relocated TO postfix;
67a454ea5472 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 73
diff changeset
   162
--
67a454ea5472 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 73
diff changeset
   163
-- For more details see postconf(5) section relocated_maps and relocated(5)
67a454ea5472 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 73
diff changeset
   164
-- ---
67a454ea5472 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 73
diff changeset
   165
CREATE OR REPLACE FUNCTION postfix_relocated_map(
67a454ea5472 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 73
diff changeset
   166
    IN localpart varchar, IN the_domain varchar)
67a454ea5472 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 73
diff changeset
   167
    RETURNS SETOF recipient_destination
67a454ea5472 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 73
diff changeset
   168
AS $$
67a454ea5472 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 73
diff changeset
   169
    DECLARE
67a454ea5472 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 73
diff changeset
   170
        record recipient_destination;
67a454ea5472 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 73
diff changeset
   171
        recipient varchar(320) := localpart || '@' || the_domain;
67a454ea5472 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 73
diff changeset
   172
        did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain);
67a454ea5472 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 73
diff changeset
   173
    BEGIN
67a454ea5472 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 73
diff changeset
   174
        FOR record IN
67a454ea5472 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 73
diff changeset
   175
            SELECT recipient, destination
67a454ea5472 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 73
diff changeset
   176
              FROM relocated
67a454ea5472 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 73
diff changeset
   177
             WHERE gid = did
67a454ea5472 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 73
diff changeset
   178
               AND address = localpart
67a454ea5472 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 73
diff changeset
   179
            LOOP
67a454ea5472 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 73
diff changeset
   180
                RETURN NEXT record;
67a454ea5472 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 73
diff changeset
   181
            END LOOP;
67a454ea5472 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 73
diff changeset
   182
        RETURN;
67a454ea5472 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 73
diff changeset
   183
    END;
67a454ea5472 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 73
diff changeset
   184
$$ LANGUAGE plpgsql STABLE
67a454ea5472 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 73
diff changeset
   185
RETURNS NULL ON NULL INPUT
67a454ea5472 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 73
diff changeset
   186
EXTERNAL SECURITY INVOKER;
67a454ea5472 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 73
diff changeset
   187
67a454ea5472 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 73
diff changeset
   188
-- ########################################################################## --
67a454ea5472 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 73
diff changeset
   189
67a454ea5472 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 73
diff changeset
   190
-- ---
67a454ea5472 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 73
diff changeset
   191
-- Data type for function postfix_transport_map(varchar, varchar)
67a454ea5472 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 73
diff changeset
   192
-- ---
67a454ea5472 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 73
diff changeset
   193
CREATE TYPE recipient_transport AS (
67a454ea5472 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 73
diff changeset
   194
    recipient   varchar(320),
67a454ea5472 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 73
diff changeset
   195
    transport   text
67a454ea5472 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 73
diff changeset
   196
);
67a454ea5472 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 73
diff changeset
   197
67a454ea5472 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 73
diff changeset
   198
-- ---
67a454ea5472 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 73
diff changeset
   199
-- Parameters (from recipients address (MAIL TO) [localpart@the_domain]):
67a454ea5472 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 73
diff changeset
   200
--      varchar localpart
67a454ea5472 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 73
diff changeset
   201
--      varchar the_domain
67a454ea5472 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 73
diff changeset
   202
-- Returns: recipient_transport records
67a454ea5472 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 73
diff changeset
   203
--
67a454ea5472 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 73
diff changeset
   204
-- Required access privileges for your postfix database user:
67a454ea5472 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 73
diff changeset
   205
--      GRANT SELECT ON users, transport, domain_name TO postfix;
67a454ea5472 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 73
diff changeset
   206
--
67a454ea5472 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 73
diff changeset
   207
-- For more details see postconf(5) section transport_maps and transport(5)
67a454ea5472 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 73
diff changeset
   208
-- ---
67a454ea5472 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 73
diff changeset
   209
CREATE OR REPLACE FUNCTION postfix_transport_map(
67a454ea5472 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 73
diff changeset
   210
    IN localpart varchar, IN the_domain varchar)
67a454ea5472 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 73
diff changeset
   211
    RETURNS SETOF recipient_transport
67a454ea5472 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 73
diff changeset
   212
AS $$
67a454ea5472 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 73
diff changeset
   213
    DECLARE
67a454ea5472 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 73
diff changeset
   214
        record recipient_transport;
67a454ea5472 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 73
diff changeset
   215
        recipient varchar(320) := localpart || '@' || the_domain;
67a454ea5472 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 73
diff changeset
   216
    BEGIN
67a454ea5472 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 73
diff changeset
   217
        FOR record IN
67a454ea5472 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 73
diff changeset
   218
            SELECT recipient, transport
67a454ea5472 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 73
diff changeset
   219
              FROM transport
67a454ea5472 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 73
diff changeset
   220
             WHERE tid = (SELECT tid
67a454ea5472 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 73
diff changeset
   221
                            FROM users
67a454ea5472 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 73
diff changeset
   222
                           WHERE gid = (SELECT gid
67a454ea5472 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 73
diff changeset
   223
                                          FROM domain_name
67a454ea5472 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 73
diff changeset
   224
                                         WHERE domainname = the_domain)
75
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   225
                             AND local_part = localpart)
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   226
            LOOP
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   227
                RETURN NEXT record;
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   228
            END LOOP;
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   229
        RETURN;
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   230
    END;
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   231
$$ LANGUAGE plpgsql STABLE
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   232
RETURNS NULL ON NULL INPUT
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   233
EXTERNAL SECURITY INVOKER;
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   234
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   235
-- ########################################################################## --
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   236
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   237
-- ---
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   238
-- Data type for function postfix_virtual_uid_map(varchar, varchar)
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   239
-- ---
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   240
CREATE TYPE recipient_uid AS (
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   241
    recipient   varchar(320),
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   242
    uid         bigint
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   243
);
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   244
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   245
-- ---
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   246
-- Parameters (from recipients address (MAIL TO) [localpart@the_domain]):
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   247
--      varchar localpart
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   248
--      varchar the_domain
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   249
-- Returns: recipient_uid records
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   250
--
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   251
-- Required access privileges for your postfix database user:
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   252
--      GRANT SELECT ON users, domain_name TO postfix;
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   253
--
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   254
-- For more details see postconf(5) section virtual_uid_maps
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   255
-- ---
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   256
CREATE OR REPLACE FUNCTION postfix_virtual_uid_map(
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   257
    IN localpart varchar, IN the_domain varchar) RETURNS SETOF recipient_uid
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   258
AS $$
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   259
    DECLARE
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   260
        record recipient_uid;
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   261
        recipient varchar(320) := localpart || '@' || the_domain;
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   262
    BEGIN
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   263
        FOR record IN
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   264
            SELECT recipient, uid
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   265
              FROM users
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   266
             WHERE gid = (SELECT gid
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   267
                            FROM domain_name
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   268
                           WHERE domainname = the_domain)
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   269
               AND local_part = localpart
74
67a454ea5472 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 73
diff changeset
   270
            LOOP
67a454ea5472 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 73
diff changeset
   271
                RETURN NEXT record;
67a454ea5472 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 73
diff changeset
   272
            END LOOP;
67a454ea5472 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 73
diff changeset
   273
        RETURN;
67a454ea5472 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 73
diff changeset
   274
    END;
67a454ea5472 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 73
diff changeset
   275
$$ LANGUAGE plpgsql STABLE
67a454ea5472 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 73
diff changeset
   276
RETURNS NULL ON NULL INPUT
67a454ea5472 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 73
diff changeset
   277
EXTERNAL SECURITY INVOKER;
75
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   278
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   279
-- ########################################################################## --
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   280
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   281
-- ---
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   282
-- Data type for function dovecotuser(varchar, varchar)
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   283
-- ---
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   284
CREATE TYPE dovecotuser AS (
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   285
    userid      varchar(320),
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   286
    uid         bigint,
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   287
    gid         bigint,
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   288
    home        text,
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   289
    mail        text
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   290
);
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   291
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   292
-- ---
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   293
-- Parameters (from login name [localpart@the_domain]):
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   294
--      varchar localpart
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   295
--      varchar the_domain
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   296
-- Returns: dovecotuser records
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   297
--
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   298
-- Required access privileges for your dovecot database user:
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   299
--      GRANT SELECT ON users,domain_data,domain_name,maillocation TO dovecot;
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   300
--
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   301
-- For more details see http://wiki.dovecot.org/UserDatabase
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   302
-- ---
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   303
CREATE OR REPLACE FUNCTION dovecotuser(
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   304
    IN localpart varchar, IN the_domain varchar) RETURNS SETOF dovecotuser
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   305
AS $$
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   306
    DECLARE
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   307
        record dovecotuser;
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   308
        userid varchar(320) := localpart || '@' || the_domain;
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   309
        did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain);
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   310
    BEGIN
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   311
        FOR record IN
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   312
            SELECT userid, uid, did, domaindir ||'/'|| uid AS home,
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   313
                   '~/'|| maillocation AS mail
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   314
              FROM users, domain_data, maillocation
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   315
             WHERE users.gid = did
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   316
               AND users.local_part = localpart
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   317
               AND maillocation.mid = users.mid
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   318
               AND domain_data.gid = did
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   319
            LOOP
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   320
                RETURN NEXT record;
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   321
            END LOOP;
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   322
        RETURN;
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   323
    END;
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   324
$$ LANGUAGE plpgsql STABLE
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   325
RETURNS NULL ON NULL INPUT
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   326
EXTERNAL SECURITY INVOKER;
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   327
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   328
-- ########################################################################## --
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   329
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   330
-- ---
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   331
-- Data type for function dovecotpassword(varchar, varchar)
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   332
-- ---
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   333
CREATE TYPE dovecotpassword AS (
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   334
    userid      varchar(320),
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   335
    password    varchar(74),
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   336
    smtp        boolean,
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   337
    pop3        boolean,
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   338
    imap        boolean,
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   339
    managesieve boolean
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   340
);
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   341
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   342
-- ---
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   343
-- Parameters (from login name [localpart@the_domain]):
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   344
--      varchar localpart
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   345
--      varchar the_domain
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   346
-- Returns: dovecotpassword records
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   347
--
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   348
-- Required access privileges for your dovecot database user:
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   349
--      GRANT SELECT ON users, domain_name TO dovecot;
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   350
--
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   351
-- For more details see http://wiki.dovecot.org/AuthDatabase/SQL
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   352
-- ---
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   353
CREATE OR REPLACE FUNCTION dovecotpassword(
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   354
    IN localpart varchar, IN the_domain varchar) RETURNS SETOF dovecotpassword
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   355
AS $$
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   356
    DECLARE
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   357
        record dovecotpassword;
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   358
        userid varchar(320) := localpart || '@' || the_domain;
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   359
    BEGIN
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   360
        FOR record IN
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   361
            SELECT userid, passwd, smtp, pop3, imap, managesieve
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   362
              FROM users
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   363
             WHERE gid = (SELECT gid
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   364
                            FROM domain_name
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   365
                           WHERE domainname = the_domain)
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   366
               AND local_part = localpart
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   367
            LOOP
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   368
                RETURN NEXT record;
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   369
            END LOOP;
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   370
        RETURN;
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   371
    END;
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   372
$$ LANGUAGE plpgsql STABLE
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   373
RETURNS NULL ON NULL INPUT
af813ede1e19 * 'create_optional_types_and_functions.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 74
diff changeset
   374
EXTERNAL SECURITY INVOKER;