pgsql/create_tables-dovecot-1.2.x.pgsql
author martin f. krafft <madduck@madduck.net>
Sat, 14 Apr 2012 12:37:08 +0200
branchv0.6.x
changeset 534 6a27c7529cd7
parent 530 95dd123b552e
child 538 1f9ea5658627
permissions -rw-r--r--
Change 'force' argument to reset user records for tid/ssid/qid If domain{quotalimit,transport,serviceset} are run with 'force' and settings are inheritable from the domain, then rather than to copy the ID to the user table, the affected field in the user records should be set to NULL so that the domain default is used.
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
42
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
     1
SET client_encoding = 'UTF8';
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
     2
SET client_min_messages = warning;
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
     3
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
     4
6
07d141039f74 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 0
diff changeset
     5
CREATE SEQUENCE transport_id;
07d141039f74 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 0
diff changeset
     6
297
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 257
diff changeset
     7
CREATE SEQUENCE mailboxformat_id;
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 257
diff changeset
     8
8
7e3ce56f49e6 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 7
diff changeset
     9
CREATE SEQUENCE maillocation_id;
6
07d141039f74 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 0
diff changeset
    10
382
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
    11
CREATE SEQUENCE quotalimit_id;
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
    12
437
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
    13
CREATE SEQUENCE service_set_id;
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
    14
42
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
    15
CREATE SEQUENCE domain_gid
0
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    16
    START WITH 70000
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    17
    INCREMENT BY 1
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    18
    MINVALUE 70000
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    19
    MAXVALUE 4294967294
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    20
    NO CYCLE;
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    21
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    22
CREATE SEQUENCE users_uid
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    23
    START WITH 70000
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    24
    INCREMENT BY 1
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    25
    MINVALUE 70000
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    26
    MAXVALUE 4294967294
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    27
    NO CYCLE;
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    28
42
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
    29
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
    30
CREATE TABLE transport (
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
    31
    tid         bigint NOT NULL DEFAULT nextval('transport_id'),
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
    32
    transport   varchar(270) NOT NULL, -- smtps:[255-char.host.name:50025]
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
    33
    CONSTRAINT  pkey_transport PRIMARY KEY (tid),
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
    34
    CONSTRAINT  ukey_transport UNIQUE (transport)
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
    35
);
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
    36
-- Insert default transport
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
    37
INSERT INTO transport(transport) VALUES ('dovecot:');
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
    38
297
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 257
diff changeset
    39
CREATE TABLE mailboxformat (
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 257
diff changeset
    40
    fid         bigint NOT NULL DEFAULT nextval('mailboxformat_id'),
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 257
diff changeset
    41
    format      varchar(20) NOT NULL,
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 257
diff changeset
    42
    CONSTRAINT  pkey_mailboxformat PRIMARY KEY (fid),
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 257
diff changeset
    43
    CONSTRAINT  ukey_mailboxformat UNIQUE (format)
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 257
diff changeset
    44
);
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 257
diff changeset
    45
-- Insert supported mailbox formats
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 257
diff changeset
    46
INSERT INTO mailboxformat(format) VALUES ('maildir');
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 257
diff changeset
    47
INSERT INTO mailboxformat(format) VALUES ('mdbox');
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 257
diff changeset
    48
INSERT INTO mailboxformat(format) VALUES ('sdbox');
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 257
diff changeset
    49
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 257
diff changeset
    50
CREATE TABLE maillocation (
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 257
diff changeset
    51
    mid         bigint NOT NULL DEFAULT nextval('maillocation_id'),
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 257
diff changeset
    52
    fid         bigint NOT NULL DEFAULT 1,
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 257
diff changeset
    53
    directory   varchar(20) NOT NULL,
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 257
diff changeset
    54
    extra       varchar(1024),
42
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
    55
    CONSTRAINT  pkey_maillocation PRIMARY KEY (mid),
297
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 257
diff changeset
    56
    CONSTRAINT  fkey_maillocation_fid_mailboxformat FOREIGN KEY (fid)
368
be4bd77dbe57 pgsql/create_tables{,-dovecot-1.2.x}.pgsql: Fixed syntax errors
Tobias Berling <t-obi@users.sourceforge.net>
parents: 297
diff changeset
    57
        REFERENCES mailboxformat (fid)
42
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
    58
);
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
    59
-- Insert default Maildir-folder name
297
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 257
diff changeset
    60
INSERT INTO maillocation(directory) VALUES ('Maildir');
42
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
    61
382
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
    62
CREATE TABLE quotalimit (
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
    63
    qid         bigint NOT NULL DEFAULT nextval('quotalimit_id'),
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
    64
    bytes       bigint NOT NULL,
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
    65
    messages    integer NOT NULL DEFAULT 0,
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
    66
    CONSTRAINT  pkey_quotalimit PRIMARY KEY (qid),
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
    67
    CONSTRAINT  ukey_quotalimit UNIQUE (bytes, messages)
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
    68
);
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
    69
-- Insert default (non) quota limit
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
    70
INSERT INTO quotalimit(bytes, messages) VALUES (0, 0);
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
    71
437
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
    72
CREATE TABLE service_set (
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
    73
    ssid        bigint NOT NULL DEFAULT nextval('service_set_id'),
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
    74
    smtp        boolean NOT NULL DEFAULT TRUE,
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
    75
    pop3        boolean NOT NULL DEFAULT TRUE,
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
    76
    imap        boolean NOT NULL DEFAULT TRUE,
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
    77
    sieve       boolean NOT NULL DEFAULT TRUE,
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
    78
    CONSTRAINT  pkey_service_set PRIMARY KEY (ssid),
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
    79
    CONSTRAINT  ukey_service_set UNIQUE (smtp, pop3, imap, sieve)
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
    80
);
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
    81
-- Insert all possible service combinations
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
    82
COPY service_set (smtp, pop3, imap, sieve) FROM stdin;
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
    83
TRUE	TRUE	TRUE	TRUE
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
    84
FALSE	TRUE	TRUE	TRUE
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
    85
TRUE	FALSE	TRUE	TRUE
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
    86
FALSE	FALSE	TRUE	TRUE
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
    87
TRUE	TRUE	FALSE	TRUE
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
    88
FALSE	TRUE	FALSE	TRUE
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
    89
TRUE	FALSE	FALSE	TRUE
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
    90
FALSE	FALSE	FALSE	TRUE
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
    91
TRUE	TRUE	TRUE	FALSE
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
    92
FALSE	TRUE	TRUE	FALSE
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
    93
TRUE	FALSE	TRUE	FALSE
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
    94
FALSE	FALSE	TRUE	FALSE
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
    95
TRUE	TRUE	FALSE	FALSE
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
    96
FALSE	TRUE	FALSE	FALSE
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
    97
TRUE	FALSE	FALSE	FALSE
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
    98
FALSE	FALSE	FALSE	FALSE
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
    99
\.
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
   100
42
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   101
CREATE TABLE domain_data (
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   102
    gid         bigint NOT NULL DEFAULT nextval('domain_gid'),
437
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
   103
    qid         bigint NOT NULL DEFAULT 1, -- default quota limit
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
   104
    ssid        bigint NOT NULL DEFAULT 1, -- default service set
257
5b8fde01e4f0 VMM/Alias.py: Replaced some %r with '%s'.
Tobias Berling <mail@tobiasberling.de>
parents: 160
diff changeset
   105
    tid         bigint NOT NULL DEFAULT 1, -- default transport
0
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   106
    domaindir   varchar(40) NOT NULL, --/srv/mail/$RAND/4294967294
42
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   107
    CONSTRAINT  pkey_domain_data PRIMARY KEY (gid),
437
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
   108
    CONSTRAINT  fkey_domain_data_qid_quotalimit FOREIGN KEY (qid)
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
   109
        REFERENCES quotalimit (qid),
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
   110
    CONSTRAINT  fkey_domain_data_ssid_service_set FOREIGN KEY (ssid)
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
   111
        REFERENCES service_set (ssid),
42
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   112
    CONSTRAINT  fkey_domain_data_tid_transport FOREIGN KEY (tid)
437
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
   113
        REFERENCES transport (tid)
0
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   114
);
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   115
42
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   116
CREATE TABLE domain_name (
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   117
    domainname  varchar(255) NOT NULL,
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   118
    gid         bigint NOT NULL,
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   119
    is_primary  boolean NOT NULL,
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   120
    CONSTRAINT  pkey_domain_name PRIMARY KEY (domainname),
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   121
    CONSTRAINT  fkey_domain_name_gid_domain_data FOREIGN KEY (gid)
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   122
        REFERENCES domain_data (gid)
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   123
);
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   124
0
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   125
CREATE TABLE users (
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   126
    local_part  varchar(64) NOT NULL,-- only localpart w/o '@'
297
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 257
diff changeset
   127
    passwd      varchar(270) NOT NULL,
0
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   128
    name        varchar(128) NULL,
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   129
    uid         bigint NOT NULL DEFAULT nextval('users_uid'),
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   130
    gid         bigint NOT NULL,
6
07d141039f74 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 0
diff changeset
   131
    mid         bigint NOT NULL DEFAULT 1,
530
95dd123b552e Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 528
diff changeset
   132
    qid         bigint NULL DEFAULT NULL,
95dd123b552e Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 528
diff changeset
   133
    ssid        bigint NULL DEFAULT NULL,
95dd123b552e Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 528
diff changeset
   134
    tid         bigint NULL DEFAULT NULL,
71
4c94ba297698 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 67
diff changeset
   135
    CONSTRAINT  pkey_users PRIMARY KEY (local_part, gid),
42
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   136
    CONSTRAINT  ukey_users_uid UNIQUE (uid),
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   137
    CONSTRAINT  fkey_users_gid_domain_data FOREIGN KEY (gid)
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   138
        REFERENCES domain_data (gid),
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   139
    CONSTRAINT  fkey_users_mid_maillocation FOREIGN KEY (mid)
8
7e3ce56f49e6 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 7
diff changeset
   140
        REFERENCES maillocation (mid),
437
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
   141
    CONSTRAINT  fkey_users_qid_quotalimit FOREIGN KEY (qid)
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
   142
        REFERENCES quotalimit (qid),
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
   143
    CONSTRAINT fkey_users_ssid_service_set FOREIGN KEY (ssid)
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
   144
        REFERENCES service_set (ssid),
42
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   145
    CONSTRAINT  fkey_users_tid_transport FOREIGN KEY (tid)
437
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
   146
        REFERENCES transport (tid)
382
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
   147
);
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
   148
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
   149
CREATE TABLE userquota (
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
   150
    uid         bigint NOT NULL,
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
   151
    bytes       bigint NOT NULL DEFAULT 0,
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
   152
    messages    integer NOT NULL DEFAULT 0,
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
   153
    CONSTRAINT  pkey_userquota PRIMARY KEY (uid),
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
   154
    CONSTRAINT  fkey_userquota_uid_users FOREIGN KEY (uid)
387
05dc4e1f8dff pgsql/{create,update}_tables*: Added the ON DELETE CASCADE clause
Pascal Volk <neverseen@users.sourceforge.net>
parents: 382
diff changeset
   155
        REFERENCES users (uid) ON DELETE CASCADE
0
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   156
);
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   157
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   158
CREATE TABLE alias (
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   159
    gid         bigint NOT NULL,
67
e4d25f50164d * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 63
diff changeset
   160
    address     varchar(64) NOT NULL,-- only localpart w/o '@'
0
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   161
    destination varchar(320) NOT NULL,
42
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   162
    CONSTRAINT  pkey_alias PRIMARY KEY (gid, address, destination),
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   163
    CONSTRAINT  fkey_alias_gid_domain_data FOREIGN KEY (gid)
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   164
        REFERENCES domain_data (gid)
0
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   165
);
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   166
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   167
CREATE TABLE relocated (
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   168
    gid         bigint NOT NULL,
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   169
    address     varchar(64) NOT NULL,
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   170
    destination varchar(320) NOT NULL,
42
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   171
    CONSTRAINT  pkey_relocated PRIMARY KEY (gid, address),
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   172
    CONSTRAINT  fkey_relocated_gid_domain_data FOREIGN KEY (gid)
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   173
        REFERENCES domain_data (gid)
0
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   174
);
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   175
503
492c179094c9 Add database table for catchall destinations
martin f. krafft <madduck@madduck.net>
parents: 502
diff changeset
   176
CREATE TABLE catchall (
492c179094c9 Add database table for catchall destinations
martin f. krafft <madduck@madduck.net>
parents: 502
diff changeset
   177
    gid         bigint NOT NULL,
492c179094c9 Add database table for catchall destinations
martin f. krafft <madduck@madduck.net>
parents: 502
diff changeset
   178
    destination varchar(320) NOT NULL,
492c179094c9 Add database table for catchall destinations
martin f. krafft <madduck@madduck.net>
parents: 502
diff changeset
   179
    CONSTRAINT  pkey_catchall PRIMARY KEY (gid, destination),
492c179094c9 Add database table for catchall destinations
martin f. krafft <madduck@madduck.net>
parents: 502
diff changeset
   180
    CONSTRAINT  fkey_catchall_gid_domain_data FOREIGN KEY (gid)
492c179094c9 Add database table for catchall destinations
martin f. krafft <madduck@madduck.net>
parents: 502
diff changeset
   181
        REFERENCES domain_data (gid)
492c179094c9 Add database table for catchall destinations
martin f. krafft <madduck@madduck.net>
parents: 502
diff changeset
   182
);
492c179094c9 Add database table for catchall destinations
martin f. krafft <madduck@madduck.net>
parents: 502
diff changeset
   183
0
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   184
CREATE OR REPLACE VIEW postfix_gid AS
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   185
    SELECT gid, domainname
42
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   186
      FROM domain_name;
0
bb0aa2102206 Initial import @sf.net
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   187
9
e3d3dbeb5b84 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 8
diff changeset
   188
CREATE OR REPLACE VIEW vmm_domain_info AS
391
8217ddd5220d pgsql: Updated view vmm_domain_info. No longer select data we have already.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 388
diff changeset
   189
    SELECT gid, count(uid) AS accounts,
80
5dedc673524e * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 71
diff changeset
   190
           (SELECT count(DISTINCT address)
5dedc673524e * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 71
diff changeset
   191
              FROM alias
5dedc673524e * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 71
diff changeset
   192
             WHERE alias.gid = domain_data.gid) AS aliases,
5dedc673524e * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 71
diff changeset
   193
           (SELECT count(gid)
5dedc673524e * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 71
diff changeset
   194
              FROM relocated
5dedc673524e * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 71
diff changeset
   195
             WHERE relocated.gid = domain_data.gid) AS relocated,
42
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   196
           (SELECT count(gid)
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   197
              FROM domain_name
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   198
             WHERE domain_name.gid = domain_data.gid
515
09fa019bb330 Include catch-all count in domaininfo output
martin f. krafft <madduck@madduck.net>
parents: 514
diff changeset
   199
               AND NOT domain_name.is_primary) AS aliasdomains,
09fa019bb330 Include catch-all count in domaininfo output
martin f. krafft <madduck@madduck.net>
parents: 514
diff changeset
   200
           (SELECT count(gid)
09fa019bb330 Include catch-all count in domaininfo output
martin f. krafft <madduck@madduck.net>
parents: 514
diff changeset
   201
              FROM catchall
09fa019bb330 Include catch-all count in domaininfo output
martin f. krafft <madduck@madduck.net>
parents: 514
diff changeset
   202
             WHERE catchall.gid = domain_data.gid) AS catchall
42
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   203
      FROM domain_data
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   204
           LEFT JOIN domain_name USING (gid)
9
e3d3dbeb5b84 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 8
diff changeset
   205
           LEFT JOIN users USING (gid)
42
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   206
     WHERE domain_name.is_primary
391
8217ddd5220d pgsql: Updated view vmm_domain_info. No longer select data we have already.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 388
diff changeset
   207
  GROUP BY gid;
42
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   208
502
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   209
-- ########################################################################## --
42
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   210
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   211
CREATE LANGUAGE plpgsql;
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   212
502
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   213
-- ######################## TYPEs ########################################### --
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   214
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   215
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   216
-- Data type for function postfix_virtual_mailbox(varchar, varchar)
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   217
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   218
CREATE TYPE address_maildir AS (
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   219
    address varchar(320),
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   220
    maildir text
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   221
);
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   222
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   223
-- Data type for function dovecotpassword(varchar, varchar)
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   224
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   225
CREATE TYPE dovecotpassword AS (
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   226
    userid    varchar(320),
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   227
    password  varchar(270),
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   228
    smtp      boolean,
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   229
    pop3      boolean,
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   230
    imap      boolean,
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   231
    sieve     boolean
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   232
);
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   233
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   234
-- Data type for function dovecotquotauser(varchar, varchar)
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   235
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   236
CREATE TYPE dovecotquotauser AS (
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   237
    userid      varchar(320),
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   238
    uid         bigint,
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   239
    gid         bigint,
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   240
    home        text,
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   241
    mail        text,
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   242
    quota_rule  text
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   243
);
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   244
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   245
-- Data type for function dovecotuser(varchar, varchar)
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   246
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   247
CREATE TYPE dovecotuser AS (
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   248
    userid      varchar(320),
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   249
    uid         bigint,
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   250
    gid         bigint,
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   251
    home        text,
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   252
    mail        text
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   253
);
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   254
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   255
-- Data type for functions: postfix_relocated_map(varchar, varchar)
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   256
--                          postfix_virtual_alias_map(varchar, varchar)
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   257
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   258
CREATE TYPE recipient_destination AS (
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   259
    recipient   varchar(320),
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   260
    destination text
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   261
);
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   262
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   263
-- Data type for function postfix_transport_map(varchar, varchar)
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   264
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   265
CREATE TYPE recipient_transport AS (
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   266
    recipient   varchar(320),
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   267
    transport   text
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   268
);
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   269
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   270
-- Data type for function postfix_virtual_uid_map(varchar, varchar)
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   271
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   272
CREATE TYPE recipient_uid AS (
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   273
    recipient   varchar(320),
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   274
    uid         bigint
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   275
);
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   276
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   277
-- Data type for function postfix_smtpd_sender_login_map(varchar, varchar)
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   278
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   279
CREATE TYPE sender_login AS (
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   280
    sender  varchar(320),
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   281
    login   text
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   282
);
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   283
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   284
-- ######################## TRIGGERs ######################################## --
42
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   285
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   286
CREATE OR REPLACE FUNCTION domain_primary_trigger() RETURNS TRIGGER AS $$
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   287
DECLARE
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   288
    primary_count bigint;
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   289
BEGIN
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   290
    SELECT INTO primary_count count(gid) + NEW.is_primary::integer
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   291
      FROM domain_name
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   292
     WHERE domain_name.gid = NEW.gid
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   293
       AND is_primary;
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   294
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   295
    IF (primary_count > 1) THEN
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   296
        RAISE EXCEPTION 'There can only be one domain marked as primary.';
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   297
    END IF;
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   298
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   299
    RETURN NEW;
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   300
END;
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   301
$$ LANGUAGE plpgsql STABLE;
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   302
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   303
63
9b627307f4a8 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 42
diff changeset
   304
CREATE TRIGGER primary_count_ins BEFORE INSERT ON domain_name
42
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 29
diff changeset
   305
    FOR EACH ROW EXECUTE PROCEDURE domain_primary_trigger();
63
9b627307f4a8 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 42
diff changeset
   306
9b627307f4a8 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 42
diff changeset
   307
CREATE TRIGGER primary_count_upd AFTER UPDATE ON domain_name
9b627307f4a8 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 42
diff changeset
   308
    FOR EACH ROW EXECUTE PROCEDURE domain_primary_trigger();
382
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
   309
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
   310
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
   311
CREATE OR REPLACE FUNCTION merge_userquota() RETURNS TRIGGER AS $$
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
   312
BEGIN
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
   313
    IF NEW.messages < 0 OR NEW.messages IS NULL THEN
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
   314
        IF NEW.messages IS NULL THEN
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
   315
            NEW.messages = 0;
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
   316
        ELSE
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
   317
            NEW.messages = -NEW.messages;
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
   318
        END IF;
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
   319
        RETURN NEW;
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
   320
    END IF;
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
   321
    LOOP
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
   322
        UPDATE userquota
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
   323
           SET bytes = bytes + NEW.bytes, messages = messages + NEW.messages
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
   324
         WHERE uid = NEW.uid;
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
   325
        IF found THEN
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
   326
            RETURN NULL;
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
   327
        END IF;
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
   328
        BEGIN
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
   329
            IF NEW.messages = 0 THEN
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
   330
              INSERT INTO userquota VALUES (NEW.uid, NEW.bytes, NULL);
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
   331
            ELSE
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
   332
              INSERT INTO userquota VALUES (NEW.uid, NEW.bytes, -NEW.messages);
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
   333
            END IF;
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
   334
            RETURN NULL;
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
   335
        EXCEPTION
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
   336
            WHEN unique_violation THEN
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
   337
                -- do nothing, and loop to try the UPDATE again
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
   338
            WHEN foreign_key_violation THEN
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
   339
                -- break the loop: a non matching uid means no such user
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
   340
                RETURN NULL;
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
   341
        END;
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
   342
    END LOOP;
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
   343
END;
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
   344
$$ LANGUAGE plpgsql;
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
   345
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
   346
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
   347
CREATE TRIGGER mergeuserquota BEFORE INSERT ON userquota
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
   348
    FOR EACH ROW EXECUTE PROCEDURE merge_userquota();
502
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   349
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   350
-- ######################## FUNCTIONs ####################################### --
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   351
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   352
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   353
-- Parameters (from login name [localpart@the_domain]):
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   354
--      varchar localpart
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   355
--      varchar the_domain
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   356
-- Returns: dovecotpassword records
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   357
--
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   358
-- Required access privileges for your dovecot database user:
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   359
--      GRANT SELECT ON users, domain_name, service_set TO dovecot;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   360
--
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   361
-- For more details see http://wiki.dovecot.org/AuthDatabase/SQL
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   362
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   363
CREATE OR REPLACE FUNCTION dovecotpassword(
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   364
    IN localpart varchar, IN the_domain varchar) RETURNS SETOF dovecotpassword
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   365
AS $$
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   366
    DECLARE
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   367
        record dovecotpassword;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   368
        userid varchar(320) := localpart || '@' || the_domain;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   369
    BEGIN
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   370
        FOR record IN
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   371
            SELECT userid, passwd, smtp, pop3, imap, sieve
530
95dd123b552e Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 528
diff changeset
   372
              FROM users, service_set, domain_data
95dd123b552e Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 528
diff changeset
   373
             WHERE users.gid = (SELECT gid
95dd123b552e Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 528
diff changeset
   374
                                  FROM domain_name
95dd123b552e Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 528
diff changeset
   375
                                 WHERE domainname = the_domain)
502
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   376
               AND local_part = localpart
530
95dd123b552e Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 528
diff changeset
   377
               AND users.gid = domain_data.gid
95dd123b552e Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 528
diff changeset
   378
               AND CASE WHEN
95dd123b552e Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 528
diff changeset
   379
                     users.ssid IS NOT NULL
95dd123b552e Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 528
diff changeset
   380
                     THEN
95dd123b552e Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 528
diff changeset
   381
                       service_set.ssid = users.ssid
95dd123b552e Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 528
diff changeset
   382
                     ELSE
95dd123b552e Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 528
diff changeset
   383
                       service_set.ssid = domain_data.ssid
95dd123b552e Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 528
diff changeset
   384
                     END
502
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   385
            LOOP
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   386
                RETURN NEXT record;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   387
            END LOOP;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   388
        RETURN;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   389
    END;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   390
$$ LANGUAGE plpgsql STABLE
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   391
RETURNS NULL ON NULL INPUT
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   392
EXTERNAL SECURITY INVOKER;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   393
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   394
-- Nearly the same as function dovecotuser below. It returns additionally the
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   395
-- field quota_rule.
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   396
--
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   397
-- Required access privileges for your dovecot database user:
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   398
--      GRANT SELECT
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   399
--          ON users, domain_data, domain_name, maillocation, mailboxformat,
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   400
--             quotalimit
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   401
--          TO dovecot;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   402
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   403
CREATE OR REPLACE FUNCTION dovecotquotauser(
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   404
    IN localpart varchar, IN the_domain varchar) RETURNS SETOF dovecotquotauser
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   405
AS $$
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   406
    DECLARE
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   407
        record dovecotquotauser;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   408
        userid varchar(320) := localpart || '@' || the_domain;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   409
        did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain);
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   410
    BEGIN
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   411
        FOR record IN
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   412
            SELECT userid, uid, did, domaindir || '/' || uid AS home,
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   413
                   format || ':~/' || directory AS mail, '*:bytes=' ||
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   414
                   bytes || ':messages=' || messages AS quota_rule
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   415
              FROM users, domain_data, mailboxformat, maillocation, quotalimit
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   416
             WHERE users.gid = did
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   417
               AND users.local_part = localpart
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   418
               AND maillocation.mid = users.mid
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   419
               AND mailboxformat.fid = maillocation.fid
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   420
               AND domain_data.gid = did
530
95dd123b552e Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 528
diff changeset
   421
               AND CASE WHEN
95dd123b552e Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 528
diff changeset
   422
                     users.qid IS NOT NULL
95dd123b552e Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 528
diff changeset
   423
                   THEN
95dd123b552e Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 528
diff changeset
   424
                     quotalimit.qid = users.qid
95dd123b552e Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 528
diff changeset
   425
                   ELSE
95dd123b552e Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 528
diff changeset
   426
                     quotalimit.qid = domain_data.qid
95dd123b552e Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 528
diff changeset
   427
                   END
502
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   428
            LOOP
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   429
                RETURN NEXT record;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   430
            END LOOP;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   431
        RETURN;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   432
    END;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   433
$$ LANGUAGE plpgsql STABLE
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   434
RETURNS NULL ON NULL INPUT
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   435
EXTERNAL SECURITY INVOKER;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   436
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   437
-- Parameters (from login name [localpart@the_domain]):
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   438
--      varchar localpart
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   439
--      varchar the_domain
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   440
-- Returns: dovecotuser records
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   441
--
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   442
-- Required access privileges for your dovecot database user:
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   443
--      GRANT SELECT
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   444
--          ON users, domain_data, domain_name, maillocation, mailboxformat
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   445
--          TO dovecot;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   446
--
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   447
-- For more details see http://wiki.dovecot.org/UserDatabase
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   448
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   449
CREATE OR REPLACE FUNCTION dovecotuser(
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   450
    IN localpart varchar, IN the_domain varchar) RETURNS SETOF dovecotuser
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   451
AS $$
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   452
    DECLARE
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   453
        record dovecotuser;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   454
        userid varchar(320) := localpart || '@' || the_domain;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   455
        did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain);
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   456
    BEGIN
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   457
        FOR record IN
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   458
            SELECT userid, uid, did, domaindir || '/' || uid AS home,
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   459
                   format || ':~/' || directory AS mail
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   460
              FROM users, domain_data, mailboxformat, maillocation
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   461
             WHERE users.gid = did
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   462
               AND users.local_part = localpart
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   463
               AND maillocation.mid = users.mid
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   464
               AND mailboxformat.fid = maillocation.fid
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   465
               AND domain_data.gid = did
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   466
            LOOP
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   467
                RETURN NEXT record;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   468
            END LOOP;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   469
        RETURN;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   470
    END;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   471
$$ LANGUAGE plpgsql STABLE
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   472
RETURNS NULL ON NULL INPUT
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   473
EXTERNAL SECURITY INVOKER;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   474
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   475
-- Parameters (from recipients address (MAIL TO) [localpart@the_domain]):
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   476
--      varchar localpart
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   477
--      varchar the_domain
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   478
-- Returns: recipient_destination records
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   479
--
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   480
-- Required access privileges for your postfix database user:
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   481
--      GRANT SELECT ON domain_name, relocated TO postfix;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   482
--
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   483
-- For more details see postconf(5) section relocated_maps and relocated(5)
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   484
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   485
CREATE OR REPLACE FUNCTION postfix_relocated_map(
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   486
    IN localpart varchar, IN the_domain varchar)
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   487
    RETURNS SETOF recipient_destination
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   488
AS $$
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   489
    DECLARE
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   490
        record recipient_destination;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   491
        recipient varchar(320) := localpart || '@' || the_domain;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   492
        did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain);
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   493
    BEGIN
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   494
        FOR record IN
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   495
            SELECT recipient, destination
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   496
              FROM relocated
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   497
             WHERE gid = did
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   498
               AND address = localpart
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   499
            LOOP
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   500
                RETURN NEXT record;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   501
            END LOOP;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   502
        RETURN;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   503
    END;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   504
$$ LANGUAGE plpgsql STABLE
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   505
RETURNS NULL ON NULL INPUT
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   506
EXTERNAL SECURITY INVOKER;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   507
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   508
-- Parameters (from _sender_ address (MAIL FROM) [localpart@the_domain]):
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   509
--      varchar localpart
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   510
--      varchar the_domain
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   511
-- Returns: SASL _login_ names that own _sender_ addresses (MAIL FROM):
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   512
--      set of sender_login records.
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   513
--
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   514
-- Required access privileges for your postfix database user:
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   515
--      GRANT SELECT ON domain_name, users, alias TO postfix;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   516
--
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   517
-- For more details see postconf(5) section smtpd_sender_login_maps
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   518
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   519
CREATE OR REPLACE FUNCTION postfix_smtpd_sender_login_map(
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   520
    IN localpart varchar, IN the_domain varchar) RETURNS SETOF sender_login
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   521
AS $$
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   522
    DECLARE
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   523
        rec sender_login;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   524
        did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain);
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   525
        sender varchar(320) := localpart || '@' || the_domain;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   526
    BEGIN
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   527
        -- Get all addresses for 'localpart' in the primary and aliased domains
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   528
        FOR rec IN
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   529
            SELECT sender, local_part || '@' || domainname
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   530
              FROM domain_name, users
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   531
             WHERE domain_name.gid = did
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   532
               AND users.gid = did
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   533
               AND users.local_part = localpart
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   534
            LOOP
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   535
                RETURN NEXT rec;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   536
            END LOOP;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   537
        IF NOT FOUND THEN
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   538
            -- Loop over the alias addresses for localpart@the_domain
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   539
            FOR rec IN
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   540
                SELECT DISTINCT sender, destination
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   541
                  FROM alias
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   542
                 WHERE alias.gid = did
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   543
                   AND alias.address = localpart
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   544
                LOOP
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   545
                    RETURN NEXT rec;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   546
                END LOOP;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   547
        END IF;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   548
        RETURN;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   549
    END;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   550
$$ LANGUAGE plpgsql STABLE
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   551
RETURNS NULL ON NULL INPUT
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   552
EXTERNAL SECURITY INVOKER;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   553
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   554
-- Parameters (from recipients address (MAIL TO) [localpart@the_domain]):
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   555
--      varchar localpart
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   556
--      varchar the_domain
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   557
-- Returns: recipient_transport records
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   558
--
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   559
-- Required access privileges for your postfix database user:
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   560
--      GRANT SELECT ON users, transport, domain_name TO postfix;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   561
--
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   562
-- For more details see postconf(5) section transport_maps and transport(5)
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   563
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   564
CREATE OR REPLACE FUNCTION postfix_transport_map(
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   565
    IN localpart varchar, IN the_domain varchar)
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   566
    RETURNS SETOF recipient_transport
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   567
AS $$
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   568
    DECLARE
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   569
        record recipient_transport;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   570
        recipient varchar(320) := localpart || '@' || the_domain;
530
95dd123b552e Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 528
diff changeset
   571
        did bigint := (SELECT gid FROM domain_name WHERE domainname = the_domain);
95dd123b552e Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 528
diff changeset
   572
        transport_id bigint := (SELECT tid FROM users
95dd123b552e Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 528
diff changeset
   573
                                  WHERE gid = did AND local_part = localpart);
502
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   574
    BEGIN
530
95dd123b552e Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 528
diff changeset
   575
        IF transport_id IS NULL THEN
95dd123b552e Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 528
diff changeset
   576
            SELECT tid INTO STRICT transport_id
95dd123b552e Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 528
diff changeset
   577
              FROM domain_data
95dd123b552e Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 528
diff changeset
   578
             WHERE gid = did;
95dd123b552e Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 528
diff changeset
   579
        END IF;
95dd123b552e Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 528
diff changeset
   580
502
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   581
        FOR record IN
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   582
            SELECT recipient, transport
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   583
              FROM transport
530
95dd123b552e Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 528
diff changeset
   584
             WHERE tid = transport_id
502
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   585
            LOOP
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   586
                RETURN NEXT record;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   587
            END LOOP;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   588
        RETURN;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   589
    END;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   590
$$ LANGUAGE plpgsql STABLE
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   591
RETURNS NULL ON NULL INPUT
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   592
EXTERNAL SECURITY INVOKER;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   593
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   594
-- Parameters (from recipients address (MAIL TO) [localpart@the_domain]):
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   595
--      varchar localpart
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   596
--      varchar the_domain
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   597
-- Returns: recipient_destination records
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   598
--
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   599
-- Required access privileges for your postfix database user:
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   600
--      GRANT SELECT ON alias, domain_name TO postfix;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   601
--
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   602
-- For more details see postconf(5) section virtual_alias_maps and virtual(5)
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   603
-- ---
518
5ec2068d02af Enable interpolation of alias destinations
martin f. krafft <madduck@madduck.net>
parents: 515
diff changeset
   604
CREATE OR REPLACE FUNCTION _interpolate_destination(
5ec2068d02af Enable interpolation of alias destinations
martin f. krafft <madduck@madduck.net>
parents: 515
diff changeset
   605
    IN destination varchar, localpart varchar, IN the_domain varchar)
5ec2068d02af Enable interpolation of alias destinations
martin f. krafft <madduck@madduck.net>
parents: 515
diff changeset
   606
    RETURNS varchar
5ec2068d02af Enable interpolation of alias destinations
martin f. krafft <madduck@madduck.net>
parents: 515
diff changeset
   607
AS $$
5ec2068d02af Enable interpolation of alias destinations
martin f. krafft <madduck@madduck.net>
parents: 515
diff changeset
   608
    DECLARE
5ec2068d02af Enable interpolation of alias destinations
martin f. krafft <madduck@madduck.net>
parents: 515
diff changeset
   609
        result varchar(320);
5ec2068d02af Enable interpolation of alias destinations
martin f. krafft <madduck@madduck.net>
parents: 515
diff changeset
   610
    BEGIN
5ec2068d02af Enable interpolation of alias destinations
martin f. krafft <madduck@madduck.net>
parents: 515
diff changeset
   611
        IF position('%' in destination) = 0 THEN
5ec2068d02af Enable interpolation of alias destinations
martin f. krafft <madduck@madduck.net>
parents: 515
diff changeset
   612
            RETURN destination;
5ec2068d02af Enable interpolation of alias destinations
martin f. krafft <madduck@madduck.net>
parents: 515
diff changeset
   613
        END IF;
5ec2068d02af Enable interpolation of alias destinations
martin f. krafft <madduck@madduck.net>
parents: 515
diff changeset
   614
        result := replace(destination, '%n', localpart);
5ec2068d02af Enable interpolation of alias destinations
martin f. krafft <madduck@madduck.net>
parents: 515
diff changeset
   615
        result := replace(result, '%d', the_domain);
5ec2068d02af Enable interpolation of alias destinations
martin f. krafft <madduck@madduck.net>
parents: 515
diff changeset
   616
        result := replace(result, '%=', localpart || '=' || the_domain);
5ec2068d02af Enable interpolation of alias destinations
martin f. krafft <madduck@madduck.net>
parents: 515
diff changeset
   617
        RETURN result;
5ec2068d02af Enable interpolation of alias destinations
martin f. krafft <madduck@madduck.net>
parents: 515
diff changeset
   618
    END;
5ec2068d02af Enable interpolation of alias destinations
martin f. krafft <madduck@madduck.net>
parents: 515
diff changeset
   619
$$ LANGUAGE plpgsql STABLE
5ec2068d02af Enable interpolation of alias destinations
martin f. krafft <madduck@madduck.net>
parents: 515
diff changeset
   620
RETURNS NULL ON NULL INPUT
5ec2068d02af Enable interpolation of alias destinations
martin f. krafft <madduck@madduck.net>
parents: 515
diff changeset
   621
EXTERNAL SECURITY INVOKER;
5ec2068d02af Enable interpolation of alias destinations
martin f. krafft <madduck@madduck.net>
parents: 515
diff changeset
   622
502
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   623
CREATE OR REPLACE FUNCTION postfix_virtual_alias_map(
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   624
    IN localpart varchar, IN the_domain varchar)
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   625
    RETURNS SETOF recipient_destination
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   626
AS $$
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   627
    DECLARE
514
d863a44a6353 Make PL/pgSQL function feed back identity for mailboxes/relocated when there
martin f. krafft <madduck@madduck.net>
parents: 512
diff changeset
   628
        recordc recipient_destination;
502
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   629
        record recipient_destination;
514
d863a44a6353 Make PL/pgSQL function feed back identity for mailboxes/relocated when there
martin f. krafft <madduck@madduck.net>
parents: 512
diff changeset
   630
        catchall_cursor refcursor;
502
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   631
        recipient varchar(320) := localpart || '@' || the_domain;
521
75d1c0d6bb8f Cache interpolated destination
martin f. krafft <madduck@madduck.net>
parents: 518
diff changeset
   632
        idestination varchar(320) :=
75d1c0d6bb8f Cache interpolated destination
martin f. krafft <madduck@madduck.net>
parents: 518
diff changeset
   633
            _interpolate_destination(destination, localpart, the_domain);
502
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   634
        did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain);
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   635
    BEGIN
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   636
        FOR record IN
521
75d1c0d6bb8f Cache interpolated destination
martin f. krafft <madduck@madduck.net>
parents: 518
diff changeset
   637
            SELECT recipient, idestination
502
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   638
              FROM alias
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   639
             WHERE gid = did
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   640
               AND address = localpart
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   641
            LOOP
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   642
                RETURN NEXT record;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   643
            END LOOP;
514
d863a44a6353 Make PL/pgSQL function feed back identity for mailboxes/relocated when there
martin f. krafft <madduck@madduck.net>
parents: 512
diff changeset
   644
511
0244f1344b04 Make virtual_alias_maps function search catchall when no aliases are found
martin f. krafft <madduck@madduck.net>
parents: 503
diff changeset
   645
        IF NOT FOUND THEN
514
d863a44a6353 Make PL/pgSQL function feed back identity for mailboxes/relocated when there
martin f. krafft <madduck@madduck.net>
parents: 512
diff changeset
   646
            -- There is no matching virtual_alias. If there are no catchall
d863a44a6353 Make PL/pgSQL function feed back identity for mailboxes/relocated when there
martin f. krafft <madduck@madduck.net>
parents: 512
diff changeset
   647
            -- records for this domain, we can just return NULL since Postfix
d863a44a6353 Make PL/pgSQL function feed back identity for mailboxes/relocated when there
martin f. krafft <madduck@madduck.net>
parents: 512
diff changeset
   648
            -- will then later consult mailboxes/relocated itself. But if
d863a44a6353 Make PL/pgSQL function feed back identity for mailboxes/relocated when there
martin f. krafft <madduck@madduck.net>
parents: 512
diff changeset
   649
            -- there is a catchall destination, then it would take precedence
d863a44a6353 Make PL/pgSQL function feed back identity for mailboxes/relocated when there
martin f. krafft <madduck@madduck.net>
parents: 512
diff changeset
   650
            -- over mailboxes/relocated, which is not what we want. Therefore,
d863a44a6353 Make PL/pgSQL function feed back identity for mailboxes/relocated when there
martin f. krafft <madduck@madduck.net>
parents: 512
diff changeset
   651
            -- we must first find out if the query is for an existing mailbox
d863a44a6353 Make PL/pgSQL function feed back identity for mailboxes/relocated when there
martin f. krafft <madduck@madduck.net>
parents: 512
diff changeset
   652
            -- or relocated entry and return the identity mapping if that is
d863a44a6353 Make PL/pgSQL function feed back identity for mailboxes/relocated when there
martin f. krafft <madduck@madduck.net>
parents: 512
diff changeset
   653
            -- the case
d863a44a6353 Make PL/pgSQL function feed back identity for mailboxes/relocated when there
martin f. krafft <madduck@madduck.net>
parents: 512
diff changeset
   654
            OPEN catchall_cursor FOR
521
75d1c0d6bb8f Cache interpolated destination
martin f. krafft <madduck@madduck.net>
parents: 518
diff changeset
   655
                SELECT recipient, idestination
514
d863a44a6353 Make PL/pgSQL function feed back identity for mailboxes/relocated when there
martin f. krafft <madduck@madduck.net>
parents: 512
diff changeset
   656
                  FROM catchall
d863a44a6353 Make PL/pgSQL function feed back identity for mailboxes/relocated when there
martin f. krafft <madduck@madduck.net>
parents: 512
diff changeset
   657
                 WHERE gid = did;
d863a44a6353 Make PL/pgSQL function feed back identity for mailboxes/relocated when there
martin f. krafft <madduck@madduck.net>
parents: 512
diff changeset
   658
            FETCH NEXT FROM catchall_cursor INTO recordc;
d863a44a6353 Make PL/pgSQL function feed back identity for mailboxes/relocated when there
martin f. krafft <madduck@madduck.net>
parents: 512
diff changeset
   659
d863a44a6353 Make PL/pgSQL function feed back identity for mailboxes/relocated when there
martin f. krafft <madduck@madduck.net>
parents: 512
diff changeset
   660
            IF recordc IS NOT NULL THEN
d863a44a6353 Make PL/pgSQL function feed back identity for mailboxes/relocated when there
martin f. krafft <madduck@madduck.net>
parents: 512
diff changeset
   661
                -- Since there are catchall records for this domain
d863a44a6353 Make PL/pgSQL function feed back identity for mailboxes/relocated when there
martin f. krafft <madduck@madduck.net>
parents: 512
diff changeset
   662
                -- check the mailbox and relocated records and return identity
d863a44a6353 Make PL/pgSQL function feed back identity for mailboxes/relocated when there
martin f. krafft <madduck@madduck.net>
parents: 512
diff changeset
   663
                -- if a matching record exists.
512
821d3ffaaaca Modify virtual_alias_maps function to check mailboxes/relocated first
martin f. krafft <madduck@madduck.net>
parents: 511
diff changeset
   664
                FOR record IN
514
d863a44a6353 Make PL/pgSQL function feed back identity for mailboxes/relocated when there
martin f. krafft <madduck@madduck.net>
parents: 512
diff changeset
   665
                    SELECT recipient, recipient as destination
d863a44a6353 Make PL/pgSQL function feed back identity for mailboxes/relocated when there
martin f. krafft <madduck@madduck.net>
parents: 512
diff changeset
   666
                      FROM users
512
821d3ffaaaca Modify virtual_alias_maps function to check mailboxes/relocated first
martin f. krafft <madduck@madduck.net>
parents: 511
diff changeset
   667
                    WHERE gid = did
514
d863a44a6353 Make PL/pgSQL function feed back identity for mailboxes/relocated when there
martin f. krafft <madduck@madduck.net>
parents: 512
diff changeset
   668
                      AND local_part = localpart
d863a44a6353 Make PL/pgSQL function feed back identity for mailboxes/relocated when there
martin f. krafft <madduck@madduck.net>
parents: 512
diff changeset
   669
                    UNION SELECT recipient, recipient as destination
d863a44a6353 Make PL/pgSQL function feed back identity for mailboxes/relocated when there
martin f. krafft <madduck@madduck.net>
parents: 512
diff changeset
   670
                      FROM relocated
d863a44a6353 Make PL/pgSQL function feed back identity for mailboxes/relocated when there
martin f. krafft <madduck@madduck.net>
parents: 512
diff changeset
   671
                    WHERE gid = did
d863a44a6353 Make PL/pgSQL function feed back identity for mailboxes/relocated when there
martin f. krafft <madduck@madduck.net>
parents: 512
diff changeset
   672
                      AND address = localpart
512
821d3ffaaaca Modify virtual_alias_maps function to check mailboxes/relocated first
martin f. krafft <madduck@madduck.net>
parents: 511
diff changeset
   673
                    LOOP
821d3ffaaaca Modify virtual_alias_maps function to check mailboxes/relocated first
martin f. krafft <madduck@madduck.net>
parents: 511
diff changeset
   674
                        RETURN NEXT record;
821d3ffaaaca Modify virtual_alias_maps function to check mailboxes/relocated first
martin f. krafft <madduck@madduck.net>
parents: 511
diff changeset
   675
                    END LOOP;
514
d863a44a6353 Make PL/pgSQL function feed back identity for mailboxes/relocated when there
martin f. krafft <madduck@madduck.net>
parents: 512
diff changeset
   676
d863a44a6353 Make PL/pgSQL function feed back identity for mailboxes/relocated when there
martin f. krafft <madduck@madduck.net>
parents: 512
diff changeset
   677
                IF NOT FOUND THEN
d863a44a6353 Make PL/pgSQL function feed back identity for mailboxes/relocated when there
martin f. krafft <madduck@madduck.net>
parents: 512
diff changeset
   678
                    -- There were no records found for mailboxes/relocated,
d863a44a6353 Make PL/pgSQL function feed back identity for mailboxes/relocated when there
martin f. krafft <madduck@madduck.net>
parents: 512
diff changeset
   679
                    -- so now we can actually iterate the cursor and populate
d863a44a6353 Make PL/pgSQL function feed back identity for mailboxes/relocated when there
martin f. krafft <madduck@madduck.net>
parents: 512
diff changeset
   680
                    -- the return set
d863a44a6353 Make PL/pgSQL function feed back identity for mailboxes/relocated when there
martin f. krafft <madduck@madduck.net>
parents: 512
diff changeset
   681
                    LOOP
d863a44a6353 Make PL/pgSQL function feed back identity for mailboxes/relocated when there
martin f. krafft <madduck@madduck.net>
parents: 512
diff changeset
   682
                        RETURN NEXT recordc;
d863a44a6353 Make PL/pgSQL function feed back identity for mailboxes/relocated when there
martin f. krafft <madduck@madduck.net>
parents: 512
diff changeset
   683
                        FETCH NEXT FROM catchall_cursor INTO recordc;
d863a44a6353 Make PL/pgSQL function feed back identity for mailboxes/relocated when there
martin f. krafft <madduck@madduck.net>
parents: 512
diff changeset
   684
                        EXIT WHEN recordc IS NULL;
d863a44a6353 Make PL/pgSQL function feed back identity for mailboxes/relocated when there
martin f. krafft <madduck@madduck.net>
parents: 512
diff changeset
   685
                    END LOOP;
d863a44a6353 Make PL/pgSQL function feed back identity for mailboxes/relocated when there
martin f. krafft <madduck@madduck.net>
parents: 512
diff changeset
   686
                END IF;
512
821d3ffaaaca Modify virtual_alias_maps function to check mailboxes/relocated first
martin f. krafft <madduck@madduck.net>
parents: 511
diff changeset
   687
            END IF;
514
d863a44a6353 Make PL/pgSQL function feed back identity for mailboxes/relocated when there
martin f. krafft <madduck@madduck.net>
parents: 512
diff changeset
   688
            CLOSE catchall_cursor;
511
0244f1344b04 Make virtual_alias_maps function search catchall when no aliases are found
martin f. krafft <madduck@madduck.net>
parents: 503
diff changeset
   689
        END IF;
502
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   690
        RETURN;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   691
    END;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   692
$$ LANGUAGE plpgsql STABLE
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   693
RETURNS NULL ON NULL INPUT
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   694
EXTERNAL SECURITY INVOKER;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   695
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   696
-- Parameters (from recipients address (MAIL TO) [localpart@the_domain]):
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   697
--      varchar localpart
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   698
--      varchar the_domain
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   699
-- Returns: address_maildir records
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   700
--
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   701
-- Required access privileges for your postfix database user:
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   702
--      GRANT SELECT ON domain_data,domain_name,maillocation,users TO postfix;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   703
--
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   704
-- For more details see postconf(5) section virtual_mailbox_maps
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   705
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   706
CREATE OR REPLACE FUNCTION postfix_virtual_mailbox_map(
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   707
   IN localpart varchar, IN the_domain varchar) RETURNS SETOF address_maildir
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   708
AS $$
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   709
    DECLARE
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   710
        rec address_maildir;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   711
        did bigint := (SELECT gid FROM domain_name WHERE domainname=the_domain);
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   712
        address varchar(320) := localpart || '@' || the_domain;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   713
    BEGIN
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   714
        FOR rec IN
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   715
            SELECT address, domaindir||'/'||users.uid||'/'||directory||'/'
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   716
              FROM domain_data, users, maillocation
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   717
             WHERE domain_data.gid = did
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   718
               AND users.gid = did
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   719
               AND users.local_part = localpart
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   720
               AND maillocation.mid = users.mid
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   721
            LOOP
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   722
                RETURN NEXT rec;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   723
            END LOOP;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   724
        RETURN;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   725
    END;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   726
$$ LANGUAGE plpgsql STABLE
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   727
RETURNS NULL ON NULL INPUT
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   728
EXTERNAL SECURITY INVOKER;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   729
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   730
-- Parameters (from recipients address (MAIL TO) [localpart@the_domain]):
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   731
--      varchar localpart
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   732
--      varchar the_domain
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   733
-- Returns: recipient_uid records
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   734
--
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   735
-- Required access privileges for your postfix database user:
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   736
--      GRANT SELECT ON users, domain_name TO postfix;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   737
--
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   738
-- For more details see postconf(5) section virtual_uid_maps
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   739
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   740
CREATE OR REPLACE FUNCTION postfix_virtual_uid_map(
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   741
    IN localpart varchar, IN the_domain varchar) RETURNS SETOF recipient_uid
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   742
AS $$
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   743
    DECLARE
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   744
        record recipient_uid;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   745
        recipient varchar(320) := localpart || '@' || the_domain;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   746
    BEGIN
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   747
        FOR record IN
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   748
            SELECT recipient, uid
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   749
              FROM users
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   750
             WHERE gid = (SELECT gid
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   751
                            FROM domain_name
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   752
                           WHERE domainname = the_domain)
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   753
               AND local_part = localpart
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   754
            LOOP
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   755
                RETURN NEXT record;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   756
            END LOOP;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   757
        RETURN;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   758
    END;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   759
$$ LANGUAGE plpgsql STABLE
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   760
RETURNS NULL ON NULL INPUT
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   761
EXTERNAL SECURITY INVOKER;