pgsql/create_tables.pgsql
author martin f. krafft <madduck@madduck.net>
Fri, 13 Apr 2012 23:09:40 +0200
branchv0.6.x
changeset 526 85517c8fde36
parent 525 3acbff727626
child 528 4b8c3f51d7da
permissions -rw-r--r--
Modify Account class to handle NULL references This patch modifies the Account class to defer to using the associated domain's tid/ssid/qid fields if the per-instance fields are None/NULL.
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
382
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
     9
CREATE SEQUENCE quotalimit_id;
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
    10
8
7e3ce56f49e6 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 7
diff changeset
    11
CREATE SEQUENCE maillocation_id;
6
07d141039f74 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 0
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
    managesieve 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, managesieve)
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, managesieve) 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,
525
3acbff727626 Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 521
diff changeset
   132
    qid         bigint NULL DEFAULT NULL,
3acbff727626 Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 521
diff changeset
   133
    ssid        bigint NULL DEFAULT NULL,
3acbff727626 Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 521
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_11 (
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
    path        varchar(16) NOT NULL,
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
   152
    current     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
   153
    CONSTRAINT  pkey_userquota_11 PRIMARY KEY (uid, path),
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
   154
    CONSTRAINT  fkey_userquota_11_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
    managesieve 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_11() 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
    UPDATE userquota_11
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
   314
       SET current = current + NEW.current
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
   315
     WHERE uid = NEW.uid AND path = NEW.path;
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
   316
    IF found THEN
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
   317
        RETURN NULL;
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
   318
    ELSE
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
END;
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
   322
$$ LANGUAGE plpgsql;
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
   323
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
   324
CREATE TRIGGER mergeuserquota_11 BEFORE INSERT ON userquota_11
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 368
diff changeset
   325
    FOR EACH ROW EXECUTE PROCEDURE merge_userquota_11();
502
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   326
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   327
-- ######################## FUNCTIONs ####################################### --
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   328
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   329
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   330
-- 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
   331
--      varchar localpart
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   332
--      varchar the_domain
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   333
-- Returns: dovecotpassword records
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   334
--
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   335
-- 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
   336
--      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
   337
--
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   338
-- 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
   339
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   340
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
   341
    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
   342
AS $$
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   343
    DECLARE
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   344
        record dovecotpassword;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   345
        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
   346
    BEGIN
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   347
        FOR record IN
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   348
            SELECT userid, passwd, smtp, pop3, imap, managesieve
525
3acbff727626 Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 521
diff changeset
   349
              FROM users, service_set, domain_data
3acbff727626 Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 521
diff changeset
   350
             WHERE users.gid = (SELECT gid
3acbff727626 Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 521
diff changeset
   351
                                  FROM domain_name
3acbff727626 Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 521
diff changeset
   352
                                 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
   353
               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
   354
               AND service_set.ssid = users.ssid
525
3acbff727626 Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 521
diff changeset
   355
               AND users.gid = domain_data.gid
3acbff727626 Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 521
diff changeset
   356
               AND CASE WHEN
3acbff727626 Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 521
diff changeset
   357
                  users.ssid IS NOT NULL
3acbff727626 Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 521
diff changeset
   358
                  THEN
3acbff727626 Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 521
diff changeset
   359
                    service_set.ssid = users.ssid
3acbff727626 Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 521
diff changeset
   360
                  ELSE
3acbff727626 Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 521
diff changeset
   361
                    service_set.ssid = domain_data.ssid
3acbff727626 Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 521
diff changeset
   362
                  END
502
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   363
            LOOP
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   364
                RETURN NEXT record;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   365
            END LOOP;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   366
        RETURN;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   367
    END;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   368
$$ LANGUAGE plpgsql STABLE
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   369
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
   370
EXTERNAL SECURITY INVOKER;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   371
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   372
-- 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
   373
-- field quota_rule.
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   374
--
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   375
-- 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
   376
--      GRANT SELECT
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   377
--          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
   378
--             quotalimit
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   379
--          TO dovecot;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   380
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   381
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
   382
    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
   383
AS $$
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   384
    DECLARE
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   385
        record dovecotquotauser;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   386
        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
   387
        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
   388
    BEGIN
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   389
        FOR record IN
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   390
            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
   391
                   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
   392
                   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
   393
              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
   394
             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
   395
               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
   396
               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
   397
               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
   398
               AND domain_data.gid = did
525
3acbff727626 Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 521
diff changeset
   399
               AND CASE WHEN
3acbff727626 Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 521
diff changeset
   400
                     users.qid IS NOT NULL
3acbff727626 Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 521
diff changeset
   401
                   THEN
3acbff727626 Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 521
diff changeset
   402
                     quotalimit.qid = users.qid
3acbff727626 Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 521
diff changeset
   403
                   ELSE
3acbff727626 Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 521
diff changeset
   404
                     quotalimit.qid = domain_data.qid
3acbff727626 Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 521
diff changeset
   405
                   END
502
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   406
            LOOP
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   407
                RETURN NEXT record;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   408
            END LOOP;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   409
        RETURN;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   410
    END;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   411
$$ LANGUAGE plpgsql STABLE
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   412
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
   413
EXTERNAL SECURITY INVOKER;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   414
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   415
-- 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
   416
--      varchar localpart
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   417
--      varchar the_domain
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   418
-- Returns: dovecotuser records
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   419
--
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   420
-- 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
   421
--      GRANT SELECT
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   422
--          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
   423
--          TO dovecot;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   424
--
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   425
-- 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
   426
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   427
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
   428
    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
   429
AS $$
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   430
    DECLARE
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   431
        record dovecotuser;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   432
        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
   433
        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
   434
    BEGIN
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   435
        FOR record IN
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   436
            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
   437
                   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
   438
              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
   439
             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
   440
               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
   441
               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
   442
               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
   443
               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
   444
            LOOP
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   445
                RETURN NEXT record;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   446
            END LOOP;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   447
        RETURN;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   448
    END;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   449
$$ LANGUAGE plpgsql STABLE
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   450
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
   451
EXTERNAL SECURITY INVOKER;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   452
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   453
-- 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
   454
--      varchar localpart
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   455
--      varchar the_domain
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   456
-- 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
   457
--
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   458
-- 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
   459
--      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
   460
--
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   461
-- 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
   462
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   463
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
   464
    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
   465
    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
   466
AS $$
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   467
    DECLARE
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   468
        record recipient_destination;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   469
        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
   470
        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
   471
    BEGIN
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   472
        FOR record IN
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   473
            SELECT recipient, destination
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   474
              FROM relocated
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   475
             WHERE gid = did
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   476
               AND address = localpart
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   477
            LOOP
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   478
                RETURN NEXT record;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   479
            END LOOP;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   480
        RETURN;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   481
    END;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   482
$$ LANGUAGE plpgsql STABLE
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   483
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
   484
EXTERNAL SECURITY INVOKER;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   485
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   486
-- 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
   487
--      varchar localpart
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   488
--      varchar the_domain
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   489
-- 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
   490
--      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
   491
--
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   492
-- 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
   493
--      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
   494
--
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   495
-- 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
   496
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   497
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
   498
    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
   499
AS $$
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   500
    DECLARE
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   501
        rec sender_login;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   502
        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
   503
        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
   504
    BEGIN
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   505
        -- 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
   506
        FOR rec IN
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   507
            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
   508
              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
   509
             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
   510
               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
   511
               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
   512
            LOOP
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   513
                RETURN NEXT rec;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   514
            END LOOP;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   515
        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
   516
            -- 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
   517
            FOR rec IN
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   518
                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
   519
                  FROM alias
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   520
                 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
   521
                   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
   522
                LOOP
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   523
                    RETURN NEXT rec;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   524
                END LOOP;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   525
        END IF;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   526
        RETURN;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   527
    END;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   528
$$ LANGUAGE plpgsql STABLE
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   529
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
   530
EXTERNAL SECURITY INVOKER;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   531
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   532
-- 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
   533
--      varchar localpart
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   534
--      varchar the_domain
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   535
-- 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
   536
--
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   537
-- 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
   538
--      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
   539
--
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   540
-- 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
   541
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   542
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
   543
    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
   544
    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
   545
AS $$
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   546
    DECLARE
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   547
        record recipient_transport;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   548
        recipient varchar(320) := localpart || '@' || the_domain;
525
3acbff727626 Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 521
diff changeset
   549
        did bigint := (SELECT gid FROM domain_name WHERE domainname = the_domain);
3acbff727626 Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 521
diff changeset
   550
        transport_id bigint := (SELECT tid FROM users
3acbff727626 Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 521
diff changeset
   551
                                  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
   552
    BEGIN
525
3acbff727626 Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 521
diff changeset
   553
        IF transport_id IS NULL THEN
3acbff727626 Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 521
diff changeset
   554
            SELECT tid INTO STRICT transport_id
3acbff727626 Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 521
diff changeset
   555
              FROM domain_data
3acbff727626 Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 521
diff changeset
   556
             WHERE gid = did;
3acbff727626 Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 521
diff changeset
   557
        END IF;
3acbff727626 Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 521
diff changeset
   558
502
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   559
        FOR record IN
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   560
            SELECT recipient, transport
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   561
              FROM transport
525
3acbff727626 Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 521
diff changeset
   562
             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
   563
            LOOP
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   564
                RETURN NEXT record;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   565
            END LOOP;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   566
        RETURN;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   567
    END;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   568
$$ LANGUAGE plpgsql STABLE
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   569
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
   570
EXTERNAL SECURITY INVOKER;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   571
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   572
-- 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
   573
--      varchar localpart
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   574
--      varchar the_domain
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   575
-- 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
   576
--
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   577
-- 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
   578
--      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
   579
--
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   580
-- 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
   581
-- ---
518
5ec2068d02af Enable interpolation of alias destinations
martin f. krafft <madduck@madduck.net>
parents: 515
diff changeset
   582
CREATE OR REPLACE FUNCTION _interpolate_destination(
5ec2068d02af Enable interpolation of alias destinations
martin f. krafft <madduck@madduck.net>
parents: 515
diff changeset
   583
    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
   584
    RETURNS varchar
5ec2068d02af Enable interpolation of alias destinations
martin f. krafft <madduck@madduck.net>
parents: 515
diff changeset
   585
AS $$
5ec2068d02af Enable interpolation of alias destinations
martin f. krafft <madduck@madduck.net>
parents: 515
diff changeset
   586
    DECLARE
5ec2068d02af Enable interpolation of alias destinations
martin f. krafft <madduck@madduck.net>
parents: 515
diff changeset
   587
        result varchar(320);
5ec2068d02af Enable interpolation of alias destinations
martin f. krafft <madduck@madduck.net>
parents: 515
diff changeset
   588
    BEGIN
5ec2068d02af Enable interpolation of alias destinations
martin f. krafft <madduck@madduck.net>
parents: 515
diff changeset
   589
        IF position('%' in destination) = 0 THEN
5ec2068d02af Enable interpolation of alias destinations
martin f. krafft <madduck@madduck.net>
parents: 515
diff changeset
   590
            RETURN destination;
5ec2068d02af Enable interpolation of alias destinations
martin f. krafft <madduck@madduck.net>
parents: 515
diff changeset
   591
        END IF;
5ec2068d02af Enable interpolation of alias destinations
martin f. krafft <madduck@madduck.net>
parents: 515
diff changeset
   592
        result := replace(destination, '%n', localpart);
5ec2068d02af Enable interpolation of alias destinations
martin f. krafft <madduck@madduck.net>
parents: 515
diff changeset
   593
        result := replace(result, '%d', the_domain);
5ec2068d02af Enable interpolation of alias destinations
martin f. krafft <madduck@madduck.net>
parents: 515
diff changeset
   594
        result := replace(result, '%=', localpart || '=' || the_domain);
5ec2068d02af Enable interpolation of alias destinations
martin f. krafft <madduck@madduck.net>
parents: 515
diff changeset
   595
        RETURN result;
5ec2068d02af Enable interpolation of alias destinations
martin f. krafft <madduck@madduck.net>
parents: 515
diff changeset
   596
    END;
5ec2068d02af Enable interpolation of alias destinations
martin f. krafft <madduck@madduck.net>
parents: 515
diff changeset
   597
$$ LANGUAGE plpgsql STABLE
5ec2068d02af Enable interpolation of alias destinations
martin f. krafft <madduck@madduck.net>
parents: 515
diff changeset
   598
RETURNS NULL ON NULL INPUT
5ec2068d02af Enable interpolation of alias destinations
martin f. krafft <madduck@madduck.net>
parents: 515
diff changeset
   599
EXTERNAL SECURITY INVOKER;
5ec2068d02af Enable interpolation of alias destinations
martin f. krafft <madduck@madduck.net>
parents: 515
diff changeset
   600
502
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   601
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
   602
    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
   603
    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
   604
AS $$
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   605
    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
   606
        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
   607
        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
   608
        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
   609
        recipient varchar(320) := localpart || '@' || the_domain;
521
75d1c0d6bb8f Cache interpolated destination
martin f. krafft <madduck@madduck.net>
parents: 518
diff changeset
   610
        idestination varchar(320) :=
75d1c0d6bb8f Cache interpolated destination
martin f. krafft <madduck@madduck.net>
parents: 518
diff changeset
   611
            _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
   612
        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
   613
    BEGIN
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   614
        FOR record IN
521
75d1c0d6bb8f Cache interpolated destination
martin f. krafft <madduck@madduck.net>
parents: 518
diff changeset
   615
            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
   616
              FROM alias
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   617
             WHERE gid = did
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   618
               AND address = localpart
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   619
            LOOP
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   620
                RETURN NEXT record;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   621
            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
   622
511
0244f1344b04 Make virtual_alias_maps function search catchall when no aliases are found
martin f. krafft <madduck@madduck.net>
parents: 503
diff changeset
   623
        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
   624
            -- 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
   625
            -- 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
   626
            -- 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
   627
            -- 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
   628
            -- 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
   629
            -- 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
   630
            -- 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
   631
            -- 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
   632
            OPEN catchall_cursor FOR
521
75d1c0d6bb8f Cache interpolated destination
martin f. krafft <madduck@madduck.net>
parents: 518
diff changeset
   633
                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
   634
                  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
   635
                 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
   636
            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
   637
d863a44a6353 Make PL/pgSQL function feed back identity for mailboxes/relocated when there
martin f. krafft <madduck@madduck.net>
parents: 512
diff changeset
   638
            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
   639
                -- 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
   640
                -- 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
   641
                -- 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
   642
                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
   643
                    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
   644
                      FROM users
512
821d3ffaaaca Modify virtual_alias_maps function to check mailboxes/relocated first
martin f. krafft <madduck@madduck.net>
parents: 511
diff changeset
   645
                    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
   646
                      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
   647
                    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
   648
                      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
   649
                    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
   650
                      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
   651
                    LOOP
821d3ffaaaca Modify virtual_alias_maps function to check mailboxes/relocated first
martin f. krafft <madduck@madduck.net>
parents: 511
diff changeset
   652
                        RETURN NEXT record;
821d3ffaaaca Modify virtual_alias_maps function to check mailboxes/relocated first
martin f. krafft <madduck@madduck.net>
parents: 511
diff changeset
   653
                    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
   654
d863a44a6353 Make PL/pgSQL function feed back identity for mailboxes/relocated when there
martin f. krafft <madduck@madduck.net>
parents: 512
diff changeset
   655
                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
   656
                    -- 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
   657
                    -- 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
   658
                    -- 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
   659
                    LOOP
d863a44a6353 Make PL/pgSQL function feed back identity for mailboxes/relocated when there
martin f. krafft <madduck@madduck.net>
parents: 512
diff changeset
   660
                        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
   661
                        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
   662
                        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
   663
                    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
   664
                END IF;
512
821d3ffaaaca Modify virtual_alias_maps function to check mailboxes/relocated first
martin f. krafft <madduck@madduck.net>
parents: 511
diff changeset
   665
            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
   666
            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
   667
        END IF;
502
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   668
        RETURN;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   669
    END;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   670
$$ LANGUAGE plpgsql STABLE
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   671
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
   672
EXTERNAL SECURITY INVOKER;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   673
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   674
-- 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
   675
--      varchar localpart
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   676
--      varchar the_domain
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   677
-- 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
   678
--
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   679
-- 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
   680
--      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
   681
--
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   682
-- 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
   683
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   684
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
   685
   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
   686
AS $$
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   687
    DECLARE
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   688
        rec address_maildir;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   689
        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
   690
        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
   691
    BEGIN
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   692
        FOR rec IN
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   693
            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
   694
              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
   695
             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
   696
               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
   697
               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
   698
               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
   699
            LOOP
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   700
                RETURN NEXT rec;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   701
            END LOOP;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   702
        RETURN;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   703
    END;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   704
$$ LANGUAGE plpgsql STABLE
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   705
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
   706
EXTERNAL SECURITY INVOKER;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   707
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   708
-- 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
   709
--      varchar localpart
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   710
--      varchar the_domain
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   711
-- 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
   712
--
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   713
-- 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
   714
--      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
   715
--
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   716
-- 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
   717
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   718
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
   719
    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
   720
AS $$
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   721
    DECLARE
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   722
        record recipient_uid;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   723
        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
   724
    BEGIN
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   725
        FOR record IN
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   726
            SELECT recipient, uid
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   727
              FROM users
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   728
             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
   729
                            FROM domain_name
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   730
                           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
   731
               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
   732
            LOOP
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   733
                RETURN NEXT record;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   734
            END LOOP;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   735
        RETURN;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   736
    END;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   737
$$ LANGUAGE plpgsql STABLE
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   738
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
   739
EXTERNAL SECURITY INVOKER;