pgsql/update_tables_0.5.x-0.6-dovecot-1.2.x.pgsql
author Pascal Volk <user@localhost.localdomain.org>
Mon, 26 Nov 2012 22:07:20 +0000
branchv0.7.x
changeset 655 2bf68600e914
parent 550 867d950ce7b7
permissions -rw-r--r--
VMM/password: Code cleanups: - Rely no longer on Crypto.Hash for md4 support. Python's hashlib on FreeBSD, Linux and OpenBSD provides the md4 hash. - Dropped DIGEST-MD5 password hashing for Dovecot <= v1.1.12/v1.2.beta2
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
297
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
     1
SET client_encoding = 'UTF8';
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
     2
SET client_min_messages = warning;
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
     3
437
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
     4
-- ---
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
     5
-- Create the new service_set table and insert all possible combinations
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
     6
-- --
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
     7
CREATE SEQUENCE service_set_id;
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
     8
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
     9
CREATE TABLE service_set (
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
    10
    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
    11
    smtp        boolean NOT NULL DEFAULT TRUE,
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
    12
    pop3        boolean NOT NULL DEFAULT TRUE,
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
    13
    imap        boolean NOT NULL DEFAULT TRUE,
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
    14
    sieve       boolean NOT NULL DEFAULT TRUE,
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
    15
    CONSTRAINT  pkey_service_set PRIMARY KEY (ssid),
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
    16
    CONSTRAINT  ukey_service_set UNIQUE (smtp, pop3, imap, sieve)
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
    17
);
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
    18
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
    19
COPY service_set (smtp, pop3, imap, sieve) FROM stdin;
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
    20
TRUE	TRUE	TRUE	TRUE
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
    21
FALSE	TRUE	TRUE	TRUE
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
    22
TRUE	FALSE	TRUE	TRUE
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
    23
FALSE	FALSE	TRUE	TRUE
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
    24
TRUE	TRUE	FALSE	TRUE
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
    25
FALSE	TRUE	FALSE	TRUE
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
    26
TRUE	FALSE	FALSE	TRUE
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
    27
FALSE	FALSE	FALSE	TRUE
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
    28
TRUE	TRUE	TRUE	FALSE
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
    29
FALSE	TRUE	TRUE	FALSE
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
    30
TRUE	FALSE	TRUE	FALSE
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
    31
FALSE	FALSE	TRUE	FALSE
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
    32
TRUE	TRUE	FALSE	FALSE
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
    33
FALSE	TRUE	FALSE	FALSE
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
    34
TRUE	FALSE	FALSE	FALSE
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
    35
FALSE	FALSE	FALSE	FALSE
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
    36
\.
297
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    37
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    38
-- ---
500
5ccc9c6e5193 pgsql: Removed most of the VIEWs.
Pascal Volk <user@localhost.localdomain.org>
parents: 437
diff changeset
    39
-- Drop the obsolete VIEWs, we've functions now.
297
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    40
-- ---
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    41
DROP VIEW dovecot_user;
437
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
    42
DROP VIEW dovecot_password;
502
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
    43
DROP VIEW postfix_alias;
297
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    44
DROP VIEW postfix_maildir;
500
5ccc9c6e5193 pgsql: Removed most of the VIEWs.
Pascal Volk <user@localhost.localdomain.org>
parents: 437
diff changeset
    45
DROP VIEW postfix_relocated;
5ccc9c6e5193 pgsql: Removed most of the VIEWs.
Pascal Volk <user@localhost.localdomain.org>
parents: 437
diff changeset
    46
DROP VIEW postfix_transport;
5ccc9c6e5193 pgsql: Removed most of the VIEWs.
Pascal Volk <user@localhost.localdomain.org>
parents: 437
diff changeset
    47
DROP VIEW postfix_uid;
5ccc9c6e5193 pgsql: Removed most of the VIEWs.
Pascal Volk <user@localhost.localdomain.org>
parents: 437
diff changeset
    48
-- the vmm_domain_info view will be restored later
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
    49
DROP VIEW vmm_domain_info;
297
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    50
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    51
CREATE SEQUENCE mailboxformat_id;
382
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 297
diff changeset
    52
CREATE SEQUENCE quotalimit_id;
297
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    53
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    54
CREATE TABLE mailboxformat (
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    55
    fid         bigint NOT NULL DEFAULT nextval('mailboxformat_id'),
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    56
    format      varchar(20) NOT NULL,
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    57
    CONSTRAINT  pkey_mailboxformat PRIMARY KEY (fid),
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    58
    CONSTRAINT  ukey_mailboxformat UNIQUE (format)
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    59
);
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    60
-- Insert supported mailbox formats
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    61
INSERT INTO mailboxformat(format) VALUES ('maildir');
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    62
INSERT INTO mailboxformat(format) VALUES ('mdbox');
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    63
INSERT INTO mailboxformat(format) VALUES ('sdbox');
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    64
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    65
-- Adjust maillocation table
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    66
ALTER TABLE maillocation DROP CONSTRAINT ukey_maillocation;
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    67
ALTER TABLE maillocation RENAME COLUMN maillocation TO directory;
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    68
ALTER TABLE maillocation
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    69
    ADD COLUMN fid bigint NOT NULL DEFAULT 1,
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    70
    ADD COLUMN extra varchar(1024);
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    71
ALTER TABLE maillocation ADD CONSTRAINT fkey_maillocation_fid_mailboxformat
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    72
    FOREIGN KEY (fid) REFERENCES mailboxformat (fid);
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    73
437
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
    74
ALTER TABLE users ALTER COLUMN passwd TYPE varchar(270);
297
e21ceaabe871 pgsql: Added support for different mailbox formats.
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    75
382
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 297
diff changeset
    76
-- ---
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 297
diff changeset
    77
-- Add quota stuff
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 297
diff changeset
    78
-- ---
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 297
diff changeset
    79
CREATE TABLE quotalimit (
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 297
diff changeset
    80
    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: 297
diff changeset
    81
    bytes       bigint NOT NULL,
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 297
diff changeset
    82
    messages    integer NOT NULL DEFAULT 0,
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 297
diff changeset
    83
    CONSTRAINT  pkey_quotalimit PRIMARY KEY (qid),
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 297
diff changeset
    84
    CONSTRAINT  ukey_quotalimit UNIQUE (bytes, messages)
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 297
diff changeset
    85
);
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 297
diff changeset
    86
-- Insert default (non) quota limit
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 297
diff changeset
    87
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: 297
diff changeset
    88
437
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
    89
-- Adjust tables (quota)
382
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 297
diff changeset
    90
ALTER TABLE domain_data ADD COLUMN qid bigint NOT NULL DEFAULT 1;
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 297
diff changeset
    91
ALTER TABLE domain_data ADD CONSTRAINT fkey_domain_data_qid_quotalimit
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 297
diff changeset
    92
    FOREIGN KEY (qid) REFERENCES quotalimit (qid);
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 297
diff changeset
    93
530
95dd123b552e Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 528
diff changeset
    94
ALTER TABLE users ADD COLUMN qid bigint NULL DEFAULT NULL;
382
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 297
diff changeset
    95
ALTER TABLE users ADD CONSTRAINT fkey_users_qid_quotalimit
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 297
diff changeset
    96
    FOREIGN KEY (qid) REFERENCES quotalimit (qid);
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 297
diff changeset
    97
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 297
diff changeset
    98
CREATE TABLE userquota (
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 297
diff changeset
    99
    uid         bigint NOT NULL,
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 297
diff changeset
   100
    bytes       bigint NOT NULL DEFAULT 0,
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 297
diff changeset
   101
    messages    integer NOT NULL DEFAULT 0,
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 297
diff changeset
   102
    CONSTRAINT  pkey_userquota PRIMARY KEY (uid),
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 297
diff changeset
   103
    CONSTRAINT  fkey_userquota_uid_users FOREIGN KEY (uid)
387
05dc4e1f8dff pgsql/{create,update}_tables*: Added the ON DELETE CASCADE clause
Pascal Volk <neverseen@users.sourceforge.net>
parents: 382
diff changeset
   104
        REFERENCES users (uid) ON DELETE CASCADE
382
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 297
diff changeset
   105
);
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 297
diff changeset
   106
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 297
diff changeset
   107
CREATE OR REPLACE FUNCTION merge_userquota() RETURNS TRIGGER AS $$
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 297
diff changeset
   108
BEGIN
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 297
diff changeset
   109
    IF NEW.messages < 0 OR NEW.messages IS NULL THEN
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 297
diff changeset
   110
        IF NEW.messages IS NULL THEN
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 297
diff changeset
   111
            NEW.messages = 0;
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 297
diff changeset
   112
        ELSE
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 297
diff changeset
   113
            NEW.messages = -NEW.messages;
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 297
diff changeset
   114
        END IF;
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 297
diff changeset
   115
        RETURN NEW;
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 297
diff changeset
   116
    END IF;
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 297
diff changeset
   117
    LOOP
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 297
diff changeset
   118
        UPDATE userquota
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 297
diff changeset
   119
           SET bytes = bytes + NEW.bytes, messages = messages + NEW.messages
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 297
diff changeset
   120
         WHERE uid = NEW.uid;
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 297
diff changeset
   121
        IF found THEN
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 297
diff changeset
   122
            RETURN NULL;
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 297
diff changeset
   123
        END IF;
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 297
diff changeset
   124
        BEGIN
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 297
diff changeset
   125
            IF NEW.messages = 0 THEN
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 297
diff changeset
   126
              INSERT INTO userquota VALUES (NEW.uid, NEW.bytes, NULL);
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 297
diff changeset
   127
            ELSE
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 297
diff changeset
   128
              INSERT INTO userquota VALUES (NEW.uid, NEW.bytes, -NEW.messages);
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 297
diff changeset
   129
            END IF;
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 297
diff changeset
   130
            RETURN NULL;
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 297
diff changeset
   131
        EXCEPTION
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 297
diff changeset
   132
            WHEN unique_violation THEN
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 297
diff changeset
   133
                -- do nothing, and loop to try the UPDATE again
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 297
diff changeset
   134
            WHEN foreign_key_violation THEN
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 297
diff changeset
   135
                -- break the loop: a non matching uid means no such user
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 297
diff changeset
   136
                RETURN NULL;
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 297
diff changeset
   137
        END;
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 297
diff changeset
   138
    END LOOP;
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 297
diff changeset
   139
END;
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 297
diff changeset
   140
$$ LANGUAGE plpgsql;
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 297
diff changeset
   141
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 297
diff changeset
   142
CREATE TRIGGER mergeuserquota BEFORE INSERT ON userquota
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 297
diff changeset
   143
    FOR EACH ROW EXECUTE PROCEDURE merge_userquota();
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 297
diff changeset
   144
437
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
   145
-- Adjust tables (services)
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
   146
ALTER TABLE domain_data ADD COLUMN ssid bigint NOT NULL DEFAULT 1;
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
   147
ALTER TABLE domain_data ADD CONSTRAINT fkey_domain_data_ssid_service_set
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
   148
    FOREIGN KEY (ssid) REFERENCES service_set (ssid);
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
   149
530
95dd123b552e Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 528
diff changeset
   150
ALTER TABLE users ADD COLUMN ssid bigint NULL DEFAULT NULL;
437
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
   151
-- save current service sets
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
   152
UPDATE users u
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
   153
   SET ssid = ss.ssid
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
   154
  FROM service_set ss
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
   155
 WHERE ss.smtp = u.smtp
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
   156
   AND ss.pop3 = u.pop3
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
   157
   AND ss.imap = u.imap
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
   158
   AND ss.sieve = u.sieve;
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
   159
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
   160
ALTER TABLE users DROP COLUMN smtp;
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
   161
ALTER TABLE users DROP COLUMN pop3;
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
   162
ALTER TABLE users DROP COLUMN imap;
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
   163
ALTER TABLE users DROP COLUMN sieve;
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
   164
ALTER TABLE users ADD CONSTRAINT fkey_users_ssid_service_set
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
   165
    FOREIGN KEY (ssid) REFERENCES service_set (ssid);
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
   166
382
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 297
diff changeset
   167
-- ---
503
492c179094c9 Add database table for catchall destinations
martin f. krafft <madduck@madduck.net>
parents: 502
diff changeset
   168
-- Catchall
492c179094c9 Add database table for catchall destinations
martin f. krafft <madduck@madduck.net>
parents: 502
diff changeset
   169
-- ---
492c179094c9 Add database table for catchall destinations
martin f. krafft <madduck@madduck.net>
parents: 502
diff changeset
   170
492c179094c9 Add database table for catchall destinations
martin f. krafft <madduck@madduck.net>
parents: 502
diff changeset
   171
CREATE TABLE catchall (
492c179094c9 Add database table for catchall destinations
martin f. krafft <madduck@madduck.net>
parents: 502
diff changeset
   172
    gid         bigint NOT NULL,
492c179094c9 Add database table for catchall destinations
martin f. krafft <madduck@madduck.net>
parents: 502
diff changeset
   173
    destination varchar(320) NOT NULL,
492c179094c9 Add database table for catchall destinations
martin f. krafft <madduck@madduck.net>
parents: 502
diff changeset
   174
    CONSTRAINT  pkey_catchall PRIMARY KEY (gid, destination),
492c179094c9 Add database table for catchall destinations
martin f. krafft <madduck@madduck.net>
parents: 502
diff changeset
   175
    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
   176
        REFERENCES domain_data (gid)
492c179094c9 Add database table for catchall destinations
martin f. krafft <madduck@madduck.net>
parents: 502
diff changeset
   177
);
492c179094c9 Add database table for catchall destinations
martin f. krafft <madduck@madduck.net>
parents: 502
diff changeset
   178
492c179094c9 Add database table for catchall destinations
martin f. krafft <madduck@madduck.net>
parents: 502
diff changeset
   179
-- ---
530
95dd123b552e Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 528
diff changeset
   180
-- Quota/Service/Transport inheritance
95dd123b552e Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 528
diff changeset
   181
-- ---
548
92ef34f07da3 pgsql: update_tables_*: Added missing `NOT' to `… tid DROP NOT NULL;'.
Pascal Volk <user@localhost.localdomain.org>
parents: 538
diff changeset
   182
ALTER TABLE users ALTER COLUMN tid DROP NOT NULL;
530
95dd123b552e Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 528
diff changeset
   183
ALTER TABLE users ALTER COLUMN tid SET DEFAULT NULL;
95dd123b552e Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 528
diff changeset
   184
-- The qid and ssid columns have already been defined accordingly above.
95dd123b552e Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 528
diff changeset
   185
-- The rest of the logic will take place in the functions.
95dd123b552e Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 528
diff changeset
   186
95dd123b552e Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 528
diff changeset
   187
-- While qid and ssid are new and it's perfectly okay for existing users to
95dd123b552e Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 528
diff changeset
   188
-- get NULL values (i.e. inherit from the domain's default), tid existed in
95dd123b552e Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 528
diff changeset
   189
-- vmm 0.5.x. A sensible way forward seems thus to NULL all user records' tid
95dd123b552e Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 528
diff changeset
   190
-- fields where the tid duplicates the value stored in the domain's record.
95dd123b552e Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 528
diff changeset
   191
UPDATE users
95dd123b552e Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 528
diff changeset
   192
   SET tid = NULL
95dd123b552e Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 528
diff changeset
   193
 WHERE tid = (SELECT tid
95dd123b552e Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 528
diff changeset
   194
                FROM domain_data
95dd123b552e Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 528
diff changeset
   195
               WHERE domain_data.gid = users.gid);
95dd123b552e Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 528
diff changeset
   196
95dd123b552e Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 528
diff changeset
   197
-- ---
538
1f9ea5658627 Modify SQL to add note field to domain_data and users
martin f. krafft <madduck@madduck.net>
parents: 530
diff changeset
   198
-- Account/domain notes
1f9ea5658627 Modify SQL to add note field to domain_data and users
martin f. krafft <madduck@madduck.net>
parents: 530
diff changeset
   199
-- ---
1f9ea5658627 Modify SQL to add note field to domain_data and users
martin f. krafft <madduck@madduck.net>
parents: 530
diff changeset
   200
1f9ea5658627 Modify SQL to add note field to domain_data and users
martin f. krafft <madduck@madduck.net>
parents: 530
diff changeset
   201
ALTER TABLE users ADD COLUMN note text NULL DEFAULT NULL;
1f9ea5658627 Modify SQL to add note field to domain_data and users
martin f. krafft <madduck@madduck.net>
parents: 530
diff changeset
   202
ALTER TABLE domain_data ADD COLUMN note text NULL DEFAULT NULL;
1f9ea5658627 Modify SQL to add note field to domain_data and users
martin f. krafft <madduck@madduck.net>
parents: 530
diff changeset
   203
1f9ea5658627 Modify SQL to add note field to domain_data and users
martin f. krafft <madduck@madduck.net>
parents: 530
diff changeset
   204
-- ---
500
5ccc9c6e5193 pgsql: Removed most of the VIEWs.
Pascal Volk <user@localhost.localdomain.org>
parents: 437
diff changeset
   205
-- Restore view
382
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 297
diff changeset
   206
-- ---
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
CREATE VIEW vmm_domain_info AS
8217ddd5220d pgsql: Updated view vmm_domain_info. No longer select data we have already.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 388
diff changeset
   208
    SELECT gid, count(uid) AS accounts,
388
dd95ed5bc9d2 pgsql: Added quotalimit's bytes and messages to view vmm_domain_info.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 387
diff changeset
   209
           (SELECT count(DISTINCT address)
dd95ed5bc9d2 pgsql: Added quotalimit's bytes and messages to view vmm_domain_info.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 387
diff changeset
   210
              FROM alias
dd95ed5bc9d2 pgsql: Added quotalimit's bytes and messages to view vmm_domain_info.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 387
diff changeset
   211
             WHERE alias.gid = domain_data.gid) AS aliases,
dd95ed5bc9d2 pgsql: Added quotalimit's bytes and messages to view vmm_domain_info.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 387
diff changeset
   212
           (SELECT count(gid)
dd95ed5bc9d2 pgsql: Added quotalimit's bytes and messages to view vmm_domain_info.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 387
diff changeset
   213
              FROM relocated
dd95ed5bc9d2 pgsql: Added quotalimit's bytes and messages to view vmm_domain_info.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 387
diff changeset
   214
             WHERE relocated.gid = domain_data.gid) AS relocated,
dd95ed5bc9d2 pgsql: Added quotalimit's bytes and messages to view vmm_domain_info.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 387
diff changeset
   215
           (SELECT count(gid)
dd95ed5bc9d2 pgsql: Added quotalimit's bytes and messages to view vmm_domain_info.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 387
diff changeset
   216
              FROM domain_name
dd95ed5bc9d2 pgsql: Added quotalimit's bytes and messages to view vmm_domain_info.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 387
diff changeset
   217
             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
   218
               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
   219
           (SELECT count(gid)
09fa019bb330 Include catch-all count in domaininfo output
martin f. krafft <madduck@madduck.net>
parents: 514
diff changeset
   220
              FROM catchall
09fa019bb330 Include catch-all count in domaininfo output
martin f. krafft <madduck@madduck.net>
parents: 514
diff changeset
   221
             WHERE catchall.gid = domain_data.gid) AS catchall
388
dd95ed5bc9d2 pgsql: Added quotalimit's bytes and messages to view vmm_domain_info.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 387
diff changeset
   222
      FROM domain_data
dd95ed5bc9d2 pgsql: Added quotalimit's bytes and messages to view vmm_domain_info.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 387
diff changeset
   223
           LEFT JOIN domain_name USING (gid)
dd95ed5bc9d2 pgsql: Added quotalimit's bytes and messages to view vmm_domain_info.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 387
diff changeset
   224
           LEFT JOIN users USING (gid)
dd95ed5bc9d2 pgsql: Added quotalimit's bytes and messages to view vmm_domain_info.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 387
diff changeset
   225
     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
   226
  GROUP BY gid;
502
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   227
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   228
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   229
-- Drop all known v0.5 types (the dirty way)
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   230
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   231
DROP TYPE address_maildir CASCADE;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   232
DROP TYPE dovecotpassword CASCADE;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   233
DROP TYPE dovecotuser CASCADE;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   234
DROP TYPE recipient_destination CASCADE;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   235
DROP TYPE recipient_transport CASCADE;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   236
DROP TYPE recipient_uid CASCADE;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   237
DROP TYPE sender_login CASCADE;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   238
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   239
-- ######################## TYPEs ########################################### --
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   240
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   241
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   242
-- 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
   243
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   244
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
   245
    address varchar(320),
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   246
    maildir text
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   247
);
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   248
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   249
-- 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
   250
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   251
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
   252
    userid    varchar(320),
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   253
    password  varchar(270),
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   254
    smtp      boolean,
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   255
    pop3      boolean,
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   256
    imap      boolean,
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   257
    sieve     boolean
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   258
);
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   259
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   260
-- 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
   261
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   262
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
   263
    userid      varchar(320),
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   264
    uid         bigint,
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   265
    gid         bigint,
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   266
    home        text,
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   267
    mail        text,
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   268
    quota_rule  text
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
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   271
-- 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
   272
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   273
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
   274
    userid      varchar(320),
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   275
    uid         bigint,
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   276
    gid         bigint,
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   277
    home        text,
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   278
    mail        text
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   279
);
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   280
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   281
-- 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
   282
--                          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
   283
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   284
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
   285
    recipient   varchar(320),
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   286
    destination text
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   287
);
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   288
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   289
-- 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
   290
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   291
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
   292
    recipient   varchar(320),
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   293
    transport   text
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   294
);
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   295
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   296
-- 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
   297
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   298
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
   299
    recipient   varchar(320),
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   300
    uid         bigint
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   301
);
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   302
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   303
-- 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
   304
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   305
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
   306
    sender  varchar(320),
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   307
    login   text
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   308
);
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   309
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   310
-- ######################## FUNCTIONs ####################################### --
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   311
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   312
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   313
-- 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
   314
--      varchar localpart
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   315
--      varchar the_domain
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   316
-- Returns: dovecotpassword records
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   317
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   318
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
   319
    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
   320
AS $$
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   321
    DECLARE
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   322
        record dovecotpassword;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   323
        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
   324
    BEGIN
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   325
        FOR record IN
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   326
            SELECT userid, passwd, smtp, pop3, imap, sieve
530
95dd123b552e Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 528
diff changeset
   327
              FROM users, service_set, domain_data
95dd123b552e Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 528
diff changeset
   328
             WHERE users.gid = (SELECT gid
95dd123b552e Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 528
diff changeset
   329
                                  FROM domain_name
95dd123b552e Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 528
diff changeset
   330
                                 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
   331
               AND local_part = localpart
530
95dd123b552e Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 528
diff changeset
   332
               AND users.gid = domain_data.gid
95dd123b552e Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 528
diff changeset
   333
               AND CASE WHEN
95dd123b552e Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 528
diff changeset
   334
                     users.ssid IS NOT NULL
95dd123b552e Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 528
diff changeset
   335
                     THEN
95dd123b552e Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 528
diff changeset
   336
                       service_set.ssid = users.ssid
95dd123b552e Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 528
diff changeset
   337
                     ELSE
95dd123b552e Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 528
diff changeset
   338
                       service_set.ssid = domain_data.ssid
95dd123b552e Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 528
diff changeset
   339
                     END
502
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   340
            LOOP
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   341
                RETURN NEXT record;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   342
            END LOOP;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   343
        RETURN;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   344
    END;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   345
$$ LANGUAGE plpgsql STABLE
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   346
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
   347
EXTERNAL SECURITY INVOKER;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   348
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   349
-- 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
   350
-- field quota_rule.
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   351
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   352
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
   353
    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
   354
AS $$
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   355
    DECLARE
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   356
        record dovecotquotauser;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   357
        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
   358
        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
   359
    BEGIN
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   360
        FOR record IN
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   361
            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
   362
                   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
   363
                   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
   364
              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
   365
             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
   366
               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
   367
               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
   368
               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
   369
               AND domain_data.gid = did
530
95dd123b552e Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 528
diff changeset
   370
               AND CASE WHEN
95dd123b552e Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 528
diff changeset
   371
                     users.qid IS NOT NULL
95dd123b552e Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 528
diff changeset
   372
                   THEN
95dd123b552e Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 528
diff changeset
   373
                     quotalimit.qid = users.qid
95dd123b552e Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 528
diff changeset
   374
                   ELSE
95dd123b552e Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 528
diff changeset
   375
                     quotalimit.qid = domain_data.qid
95dd123b552e Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 528
diff changeset
   376
                   END
502
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   377
            LOOP
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   378
                RETURN NEXT record;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   379
            END LOOP;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   380
        RETURN;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   381
    END;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   382
$$ LANGUAGE plpgsql STABLE
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   383
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
   384
EXTERNAL SECURITY INVOKER;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   385
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   386
-- 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
   387
--      varchar localpart
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   388
--      varchar the_domain
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   389
-- Returns: dovecotuser records
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   390
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   391
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
   392
    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
   393
AS $$
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   394
    DECLARE
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   395
        record dovecotuser;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   396
        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
   397
        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
   398
    BEGIN
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   399
        FOR record IN
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   400
            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
   401
                   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
   402
              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
   403
             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
   404
               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
   405
               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
   406
               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
   407
               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
   408
            LOOP
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   409
                RETURN NEXT record;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   410
            END LOOP;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   411
        RETURN;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   412
    END;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   413
$$ LANGUAGE plpgsql STABLE
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   414
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
   415
EXTERNAL SECURITY INVOKER;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   416
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   417
-- 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
   418
--      varchar localpart
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   419
--      varchar the_domain
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   420
-- 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
   421
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   422
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
   423
    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
   424
    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
   425
AS $$
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   426
    DECLARE
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   427
        record recipient_destination;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   428
        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
   429
        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
   430
    BEGIN
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   431
        FOR record IN
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   432
            SELECT recipient, destination
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   433
              FROM relocated
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   434
             WHERE gid = did
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   435
               AND address = localpart
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   436
            LOOP
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   437
                RETURN NEXT record;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   438
            END LOOP;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   439
        RETURN;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   440
    END;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   441
$$ LANGUAGE plpgsql STABLE
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   442
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
   443
EXTERNAL SECURITY INVOKER;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   444
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   445
-- 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
   446
--      varchar localpart
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   447
--      varchar the_domain
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   448
-- 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
   449
--      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
   450
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   451
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
   452
    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
   453
AS $$
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   454
    DECLARE
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   455
        rec sender_login;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   456
        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
   457
        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
   458
    BEGIN
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   459
        -- 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
   460
        FOR rec IN
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   461
            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
   462
              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
   463
             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
   464
               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
   465
               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
   466
            LOOP
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   467
                RETURN NEXT rec;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   468
            END LOOP;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   469
        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
   470
            -- 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
   471
            FOR rec IN
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   472
                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
   473
                  FROM alias
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   474
                 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
   475
                   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
   476
                LOOP
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   477
                    RETURN NEXT rec;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   478
                END LOOP;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   479
        END IF;
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 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
   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: recipient_transport records
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   490
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   491
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
   492
    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
   493
    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
   494
AS $$
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   495
    DECLARE
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   496
        record recipient_transport;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   497
        recipient varchar(320) := localpart || '@' || the_domain;
530
95dd123b552e Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 528
diff changeset
   498
        did bigint := (SELECT gid FROM domain_name WHERE domainname = the_domain);
550
867d950ce7b7 Fix transport_maps function for non-existent domains
martin f. krafft <madduck@madduck.net>
parents: 549
diff changeset
   499
        transport_id bigint;
502
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   500
    BEGIN
550
867d950ce7b7 Fix transport_maps function for non-existent domains
martin f. krafft <madduck@madduck.net>
parents: 549
diff changeset
   501
        IF did IS NULL THEN
867d950ce7b7 Fix transport_maps function for non-existent domains
martin f. krafft <madduck@madduck.net>
parents: 549
diff changeset
   502
            RETURN;
867d950ce7b7 Fix transport_maps function for non-existent domains
martin f. krafft <madduck@madduck.net>
parents: 549
diff changeset
   503
        END IF;
867d950ce7b7 Fix transport_maps function for non-existent domains
martin f. krafft <madduck@madduck.net>
parents: 549
diff changeset
   504
867d950ce7b7 Fix transport_maps function for non-existent domains
martin f. krafft <madduck@madduck.net>
parents: 549
diff changeset
   505
        SELECT tid INTO transport_id
867d950ce7b7 Fix transport_maps function for non-existent domains
martin f. krafft <madduck@madduck.net>
parents: 549
diff changeset
   506
          FROM users
867d950ce7b7 Fix transport_maps function for non-existent domains
martin f. krafft <madduck@madduck.net>
parents: 549
diff changeset
   507
         WHERE gid = did AND local_part = localpart;
867d950ce7b7 Fix transport_maps function for non-existent domains
martin f. krafft <madduck@madduck.net>
parents: 549
diff changeset
   508
530
95dd123b552e Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 528
diff changeset
   509
        IF transport_id IS NULL THEN
95dd123b552e Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 528
diff changeset
   510
            SELECT tid INTO STRICT transport_id
95dd123b552e Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 528
diff changeset
   511
              FROM domain_data
95dd123b552e Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 528
diff changeset
   512
             WHERE gid = did;
95dd123b552e Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 528
diff changeset
   513
        END IF;
95dd123b552e Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 528
diff changeset
   514
502
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   515
        FOR record IN
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   516
            SELECT recipient, transport
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   517
              FROM transport
530
95dd123b552e Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 528
diff changeset
   518
             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
   519
            LOOP
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   520
                RETURN NEXT record;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   521
            END LOOP;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   522
        RETURN;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   523
    END;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   524
$$ LANGUAGE plpgsql STABLE
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   525
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
   526
EXTERNAL SECURITY INVOKER;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   527
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   528
-- 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
   529
--      varchar localpart
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   530
--      varchar the_domain
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   531
-- 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
   532
-- ---
518
5ec2068d02af Enable interpolation of alias destinations
martin f. krafft <madduck@madduck.net>
parents: 515
diff changeset
   533
CREATE OR REPLACE FUNCTION _interpolate_destination(
5ec2068d02af Enable interpolation of alias destinations
martin f. krafft <madduck@madduck.net>
parents: 515
diff changeset
   534
    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
   535
    RETURNS varchar
5ec2068d02af Enable interpolation of alias destinations
martin f. krafft <madduck@madduck.net>
parents: 515
diff changeset
   536
AS $$
5ec2068d02af Enable interpolation of alias destinations
martin f. krafft <madduck@madduck.net>
parents: 515
diff changeset
   537
    DECLARE
5ec2068d02af Enable interpolation of alias destinations
martin f. krafft <madduck@madduck.net>
parents: 515
diff changeset
   538
        result varchar(320);
5ec2068d02af Enable interpolation of alias destinations
martin f. krafft <madduck@madduck.net>
parents: 515
diff changeset
   539
    BEGIN
5ec2068d02af Enable interpolation of alias destinations
martin f. krafft <madduck@madduck.net>
parents: 515
diff changeset
   540
        IF position('%' in destination) = 0 THEN
5ec2068d02af Enable interpolation of alias destinations
martin f. krafft <madduck@madduck.net>
parents: 515
diff changeset
   541
            RETURN destination;
5ec2068d02af Enable interpolation of alias destinations
martin f. krafft <madduck@madduck.net>
parents: 515
diff changeset
   542
        END IF;
5ec2068d02af Enable interpolation of alias destinations
martin f. krafft <madduck@madduck.net>
parents: 515
diff changeset
   543
        result := replace(destination, '%n', localpart);
5ec2068d02af Enable interpolation of alias destinations
martin f. krafft <madduck@madduck.net>
parents: 515
diff changeset
   544
        result := replace(result, '%d', the_domain);
5ec2068d02af Enable interpolation of alias destinations
martin f. krafft <madduck@madduck.net>
parents: 515
diff changeset
   545
        result := replace(result, '%=', localpart || '=' || the_domain);
5ec2068d02af Enable interpolation of alias destinations
martin f. krafft <madduck@madduck.net>
parents: 515
diff changeset
   546
        RETURN result;
5ec2068d02af Enable interpolation of alias destinations
martin f. krafft <madduck@madduck.net>
parents: 515
diff changeset
   547
    END;
5ec2068d02af Enable interpolation of alias destinations
martin f. krafft <madduck@madduck.net>
parents: 515
diff changeset
   548
$$ LANGUAGE plpgsql STABLE
5ec2068d02af Enable interpolation of alias destinations
martin f. krafft <madduck@madduck.net>
parents: 515
diff changeset
   549
RETURNS NULL ON NULL INPUT
5ec2068d02af Enable interpolation of alias destinations
martin f. krafft <madduck@madduck.net>
parents: 515
diff changeset
   550
EXTERNAL SECURITY INVOKER;
5ec2068d02af Enable interpolation of alias destinations
martin f. krafft <madduck@madduck.net>
parents: 515
diff changeset
   551
502
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   552
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
   553
    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
   554
    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
   555
AS $$
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   556
    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
   557
        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
   558
        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
   559
        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
   560
        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
   561
        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
   562
    BEGIN
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   563
        FOR record IN
549
44a808af6cf4 Revert caching of destination interpolation
martin f. krafft <madduck@madduck.net>
parents: 548
diff changeset
   564
            SELECT recipient,
44a808af6cf4 Revert caching of destination interpolation
martin f. krafft <madduck@madduck.net>
parents: 548
diff changeset
   565
                _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
   566
              FROM alias
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   567
             WHERE gid = did
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   568
               AND address = localpart
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   569
            LOOP
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   570
                RETURN NEXT record;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   571
            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
   572
511
0244f1344b04 Make virtual_alias_maps function search catchall when no aliases are found
martin f. krafft <madduck@madduck.net>
parents: 503
diff changeset
   573
        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
   574
            -- 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
   575
            -- 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
   576
            -- 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
   577
            -- 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
   578
            -- 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
   579
            -- 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
   580
            -- 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
   581
            -- 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
   582
            OPEN catchall_cursor FOR
549
44a808af6cf4 Revert caching of destination interpolation
martin f. krafft <madduck@madduck.net>
parents: 548
diff changeset
   583
                SELECT recipient,
44a808af6cf4 Revert caching of destination interpolation
martin f. krafft <madduck@madduck.net>
parents: 548
diff changeset
   584
                    _interpolate_destination(destination, localpart, the_domain)
514
d863a44a6353 Make PL/pgSQL function feed back identity for mailboxes/relocated when there
martin f. krafft <madduck@madduck.net>
parents: 512
diff changeset
   585
                  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
   586
                 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
   587
            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
   588
d863a44a6353 Make PL/pgSQL function feed back identity for mailboxes/relocated when there
martin f. krafft <madduck@madduck.net>
parents: 512
diff changeset
   589
            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
   590
                -- 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
   591
                -- 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
   592
                -- 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
   593
                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
   594
                    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
   595
                      FROM users
512
821d3ffaaaca Modify virtual_alias_maps function to check mailboxes/relocated first
martin f. krafft <madduck@madduck.net>
parents: 511
diff changeset
   596
                    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
   597
                      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
   598
                    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
   599
                      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
   600
                    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
   601
                      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
   602
                    LOOP
821d3ffaaaca Modify virtual_alias_maps function to check mailboxes/relocated first
martin f. krafft <madduck@madduck.net>
parents: 511
diff changeset
   603
                        RETURN NEXT record;
821d3ffaaaca Modify virtual_alias_maps function to check mailboxes/relocated first
martin f. krafft <madduck@madduck.net>
parents: 511
diff changeset
   604
                    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
   605
d863a44a6353 Make PL/pgSQL function feed back identity for mailboxes/relocated when there
martin f. krafft <madduck@madduck.net>
parents: 512
diff changeset
   606
                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
   607
                    -- 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
   608
                    -- 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
   609
                    -- 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
   610
                    LOOP
d863a44a6353 Make PL/pgSQL function feed back identity for mailboxes/relocated when there
martin f. krafft <madduck@madduck.net>
parents: 512
diff changeset
   611
                        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
   612
                        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
   613
                        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
   614
                    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
   615
                END IF;
512
821d3ffaaaca Modify virtual_alias_maps function to check mailboxes/relocated first
martin f. krafft <madduck@madduck.net>
parents: 511
diff changeset
   616
            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
   617
            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
   618
        END IF;
502
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   619
        RETURN;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   620
    END;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   621
$$ LANGUAGE plpgsql STABLE
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   622
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
   623
EXTERNAL SECURITY INVOKER;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   624
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   625
-- 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
   626
--      varchar localpart
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   627
--      varchar the_domain
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   628
-- 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
   629
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   630
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
   631
   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
   632
AS $$
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   633
    DECLARE
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   634
        rec address_maildir;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   635
        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
   636
        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
   637
    BEGIN
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   638
        FOR rec IN
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   639
            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
   640
              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
   641
             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
   642
               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
   643
               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
   644
               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
   645
            LOOP
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   646
                RETURN NEXT rec;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   647
            END LOOP;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   648
        RETURN;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   649
    END;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   650
$$ LANGUAGE plpgsql STABLE
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   651
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
   652
EXTERNAL SECURITY INVOKER;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   653
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   654
-- 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
   655
--      varchar localpart
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   656
--      varchar the_domain
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   657
-- 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
   658
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   659
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
   660
    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
   661
AS $$
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   662
    DECLARE
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   663
        record recipient_uid;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   664
        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
   665
    BEGIN
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   666
        FOR record IN
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   667
            SELECT recipient, uid
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   668
              FROM users
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   669
             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
   670
                            FROM domain_name
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   671
                           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
   672
               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
   673
            LOOP
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   674
                RETURN NEXT record;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   675
            END LOOP;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   676
        RETURN;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   677
    END;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   678
$$ LANGUAGE plpgsql STABLE
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   679
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
   680
EXTERNAL SECURITY INVOKER;