pgsql/update_tables_0.5.x-0.6.pgsql
author "martin f. krafft" <madduck@debian.org>
Tue, 07 Aug 2012 21:53:41 +0000
changeset 582 605f8c115711
parent 550 867d950ce7b7
child 595 86b967c0f1a1
permissions -rw-r--r--
Due to the UNION query in address_list, the assumption that the list of gids received from the database would be continuous does not hold. To prevent addresses for domains with multiple entry types from being listed, it is necessary to check the list of domain gids for every entry. Signed-off-by: martin f. krafft <madduck@debian.org> --- VirtualMailManager/common.py | 4 +--- 1 file changed, 1 insertion(+), 3 deletions(-)
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
    managesieve 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, managesieve)
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, managesieve) 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;
500
5ccc9c6e5193 pgsql: Removed most of the VIEWs.
Pascal Volk <user@localhost.localdomain.org>
parents: 437
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_11 (
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
    path        varchar(16) NOT NULL,
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 297
diff changeset
   101
    current     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
   102
    CONSTRAINT  pkey_userquota_11 PRIMARY KEY (uid, path),
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 297
diff changeset
   103
    CONSTRAINT  fkey_userquota_11_uid_users FOREIGN KEY (uid)
387
05dc4e1f8dff pgsql/{create,update}_tables*: Added the ON DELETE CASCADE clause
Pascal Volk <neverseen@users.sourceforge.net>
parents: 382
diff changeset
   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_11() 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
    UPDATE userquota_11
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 297
diff changeset
   110
       SET current = current + NEW.current
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 297
diff changeset
   111
     WHERE uid = NEW.uid AND path = NEW.path;
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 297
diff changeset
   112
    IF found THEN
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 297
diff changeset
   113
        RETURN NULL;
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 297
diff changeset
   114
    ELSE
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
END;
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 297
diff changeset
   118
$$ LANGUAGE plpgsql;
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 297
diff changeset
   119
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 297
diff changeset
   120
CREATE TRIGGER mergeuserquota_11 BEFORE INSERT ON userquota_11
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 297
diff changeset
   121
    FOR EACH ROW EXECUTE PROCEDURE merge_userquota_11();
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 297
diff changeset
   122
437
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
   123
-- Adjust tables (services)
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
   124
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
   125
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
   126
    FOREIGN KEY (ssid) REFERENCES service_set (ssid);
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
   127
530
95dd123b552e Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 528
diff changeset
   128
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
   129
-- save current service sets
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
   130
UPDATE users u
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
   131
   SET ssid = ss.ssid
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
   132
  FROM service_set ss
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
   133
 WHERE ss.smtp = u.smtp
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
   134
   AND ss.pop3 = u.pop3
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
   135
   AND ss.imap = u.imap
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
   136
   AND ss.managesieve = u.managesieve;
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
   137
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
   138
ALTER TABLE users DROP COLUMN smtp;
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
   139
ALTER TABLE users DROP COLUMN pop3;
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
   140
ALTER TABLE users DROP COLUMN imap;
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
   141
ALTER TABLE users DROP COLUMN managesieve;
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
   142
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
   143
    FOREIGN KEY (ssid) REFERENCES service_set (ssid);
9823548b2717 pgsql: Added new table `service_set'.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 391
diff changeset
   144
382
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 297
diff changeset
   145
-- ---
503
492c179094c9 Add database table for catchall destinations
martin f. krafft <madduck@madduck.net>
parents: 502
diff changeset
   146
-- Catchall
492c179094c9 Add database table for catchall destinations
martin f. krafft <madduck@madduck.net>
parents: 502
diff changeset
   147
-- ---
530
95dd123b552e Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 528
diff changeset
   148
-- 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
   149
-- ---
548
92ef34f07da3 pgsql: update_tables_*: Added missing `NOT' to `… tid DROP NOT NULL;'.
Pascal Volk <user@localhost.localdomain.org>
parents: 538
diff changeset
   150
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
   151
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
   152
-- 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
   153
-- 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
   154
95dd123b552e Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 528
diff changeset
   155
-- 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
   156
-- 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
   157
-- 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
   158
-- 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
   159
UPDATE users
95dd123b552e Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 528
diff changeset
   160
   SET tid = NULL
95dd123b552e Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 528
diff changeset
   161
 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
   162
                FROM domain_data
95dd123b552e Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 528
diff changeset
   163
               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
   164
95dd123b552e Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 528
diff changeset
   165
-- ---
503
492c179094c9 Add database table for catchall destinations
martin f. krafft <madduck@madduck.net>
parents: 502
diff changeset
   166
492c179094c9 Add database table for catchall destinations
martin f. krafft <madduck@madduck.net>
parents: 502
diff changeset
   167
CREATE TABLE catchall (
492c179094c9 Add database table for catchall destinations
martin f. krafft <madduck@madduck.net>
parents: 502
diff changeset
   168
    gid         bigint NOT NULL,
492c179094c9 Add database table for catchall destinations
martin f. krafft <madduck@madduck.net>
parents: 502
diff changeset
   169
    destination varchar(320) NOT NULL,
492c179094c9 Add database table for catchall destinations
martin f. krafft <madduck@madduck.net>
parents: 502
diff changeset
   170
    CONSTRAINT  pkey_catchall PRIMARY KEY (gid, destination),
492c179094c9 Add database table for catchall destinations
martin f. krafft <madduck@madduck.net>
parents: 502
diff changeset
   171
    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
   172
        REFERENCES domain_data (gid)
492c179094c9 Add database table for catchall destinations
martin f. krafft <madduck@madduck.net>
parents: 502
diff changeset
   173
);
492c179094c9 Add database table for catchall destinations
martin f. krafft <madduck@madduck.net>
parents: 502
diff changeset
   174
492c179094c9 Add database table for catchall destinations
martin f. krafft <madduck@madduck.net>
parents: 502
diff changeset
   175
-- ---
538
1f9ea5658627 Modify SQL to add note field to domain_data and users
martin f. krafft <madduck@madduck.net>
parents: 530
diff changeset
   176
-- 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
   177
-- ---
1f9ea5658627 Modify SQL to add note field to domain_data and users
martin f. krafft <madduck@madduck.net>
parents: 530
diff changeset
   178
1f9ea5658627 Modify SQL to add note field to domain_data and users
martin f. krafft <madduck@madduck.net>
parents: 530
diff changeset
   179
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
   180
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
   181
1f9ea5658627 Modify SQL to add note field to domain_data and users
martin f. krafft <madduck@madduck.net>
parents: 530
diff changeset
   182
-- ---
500
5ccc9c6e5193 pgsql: Removed most of the VIEWs.
Pascal Volk <user@localhost.localdomain.org>
parents: 437
diff changeset
   183
-- Restore view
382
5e6bcb2e010e pgsql/*: Added tables, triggers and functions for quota support.
Pascal Volk <neverseen@users.sourceforge.net>
parents: 297
diff changeset
   184
-- ---
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
   185
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
   186
    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
   187
           (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
   188
              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
   189
             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
   190
           (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
   191
              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
   192
             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
   193
           (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
   194
              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
   195
             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
   196
               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
   197
           (SELECT count(gid)
09fa019bb330 Include catch-all count in domaininfo output
martin f. krafft <madduck@madduck.net>
parents: 514
diff changeset
   198
              FROM catchall
09fa019bb330 Include catch-all count in domaininfo output
martin f. krafft <madduck@madduck.net>
parents: 514
diff changeset
   199
             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
   200
      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
   201
           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
   202
           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
   203
     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
   204
  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
   205
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   206
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   207
-- 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
   208
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   209
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
   210
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
   211
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
   212
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
   213
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
   214
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
   215
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
   216
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   217
-- ######################## TYPEs ########################################### --
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   218
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   219
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   220
-- 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
   221
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   222
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
   223
    address varchar(320),
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   224
    maildir text
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   225
);
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   226
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   227
-- 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
   228
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   229
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
   230
    userid      varchar(320),
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   231
    password    varchar(270),
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   232
    smtp        boolean,
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   233
    pop3        boolean,
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   234
    imap        boolean,
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   235
    managesieve boolean
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   236
);
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   237
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   238
-- 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
   239
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   240
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
   241
    userid      varchar(320),
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   242
    uid         bigint,
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   243
    gid         bigint,
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   244
    home        text,
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   245
    mail        text,
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   246
    quota_rule  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 dovecotuser(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 dovecotuser 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
    uid         bigint,
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   254
    gid         bigint,
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   255
    home        text,
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   256
    mail        text
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   257
);
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   258
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   259
-- 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
   260
--                          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
   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 recipient_destination AS (
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   263
    recipient   varchar(320),
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   264
    destination text
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   265
);
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   266
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   267
-- 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
   268
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   269
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
   270
    recipient   varchar(320),
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   271
    transport   text
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
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   274
-- 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
   275
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   276
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
   277
    recipient   varchar(320),
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   278
    uid         bigint
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 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
   282
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   283
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
   284
    sender  varchar(320),
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   285
    login   text
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   286
);
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
-- ######################## FUNCTIONs ####################################### --
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   289
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
-- 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
   292
--      varchar localpart
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   293
--      varchar the_domain
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   294
-- Returns: dovecotpassword records
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
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
   297
    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
   298
AS $$
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   299
    DECLARE
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   300
        record dovecotpassword;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   301
        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
   302
    BEGIN
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   303
        FOR record IN
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   304
            SELECT userid, passwd, smtp, pop3, imap, managesieve
530
95dd123b552e Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 528
diff changeset
   305
              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
   306
             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
   307
                                  FROM domain_name
95dd123b552e Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 528
diff changeset
   308
                                 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
   309
               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
   310
               AND service_set.ssid = users.ssid
530
95dd123b552e Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 528
diff changeset
   311
               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
   312
               AND CASE WHEN
95dd123b552e Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 528
diff changeset
   313
                  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
   314
                  THEN
95dd123b552e Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 528
diff changeset
   315
                    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
   316
                  ELSE
95dd123b552e Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 528
diff changeset
   317
                    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
   318
                  END
502
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   319
            LOOP
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   320
                RETURN NEXT record;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   321
            END LOOP;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   322
        RETURN;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   323
    END;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   324
$$ LANGUAGE plpgsql STABLE
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   325
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
   326
EXTERNAL SECURITY INVOKER;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   327
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   328
-- 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
   329
-- field quota_rule.
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   330
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   331
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
   332
    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
   333
AS $$
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   334
    DECLARE
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   335
        record dovecotquotauser;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   336
        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
   337
        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
   338
    BEGIN
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   339
        FOR record IN
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   340
            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
   341
                   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
   342
                   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
   343
              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
   344
             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
   345
               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
   346
               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
   347
               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
   348
               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
   349
               AND CASE WHEN
95dd123b552e Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 528
diff changeset
   350
                     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
   351
                   THEN
95dd123b552e Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 528
diff changeset
   352
                     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
   353
                   ELSE
95dd123b552e Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 528
diff changeset
   354
                     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
   355
                   END
502
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   356
            LOOP
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   357
                RETURN NEXT record;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   358
            END LOOP;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   359
        RETURN;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   360
    END;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   361
$$ LANGUAGE plpgsql STABLE
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   362
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
   363
EXTERNAL SECURITY INVOKER;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   364
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   365
-- 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
   366
--      varchar localpart
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   367
--      varchar the_domain
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   368
-- Returns: dovecotuser records
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   369
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   370
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
   371
    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
   372
AS $$
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   373
    DECLARE
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   374
        record dovecotuser;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   375
        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
   376
        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
   377
    BEGIN
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   378
        FOR record IN
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   379
            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
   380
                   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
   381
              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
   382
             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
   383
               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
   384
               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
   385
               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
   386
               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
   387
            LOOP
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   388
                RETURN NEXT record;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   389
            END LOOP;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   390
        RETURN;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   391
    END;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   392
$$ LANGUAGE plpgsql STABLE
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   393
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
   394
EXTERNAL SECURITY INVOKER;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   395
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   396
-- 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
   397
--      varchar localpart
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   398
--      varchar the_domain
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   399
-- 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
   400
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   401
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
   402
    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
   403
    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
   404
AS $$
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   405
    DECLARE
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   406
        record recipient_destination;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   407
        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
   408
        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
   409
    BEGIN
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   410
        FOR record IN
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   411
            SELECT recipient, destination
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   412
              FROM relocated
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   413
             WHERE gid = did
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   414
               AND address = localpart
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   415
            LOOP
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   416
                RETURN NEXT record;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   417
            END LOOP;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   418
        RETURN;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   419
    END;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   420
$$ LANGUAGE plpgsql STABLE
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   421
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
   422
EXTERNAL SECURITY INVOKER;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   423
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   424
-- 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
   425
--      varchar localpart
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   426
--      varchar the_domain
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   427
-- 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
   428
--      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
   429
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   430
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
   431
    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
   432
AS $$
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   433
    DECLARE
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   434
        rec sender_login;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   435
        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
   436
        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
   437
    BEGIN
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   438
        -- 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
   439
        FOR rec IN
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   440
            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
   441
              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
   442
             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
   443
               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
   444
               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
   445
            LOOP
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   446
                RETURN NEXT rec;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   447
            END LOOP;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   448
        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
   449
            -- 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
   450
            FOR rec IN
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   451
                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
   452
                  FROM alias
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   453
                 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
   454
                   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
   455
                LOOP
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   456
                    RETURN NEXT rec;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   457
                END LOOP;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   458
        END IF;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   459
        RETURN;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   460
    END;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   461
$$ LANGUAGE plpgsql STABLE
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   462
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
   463
EXTERNAL SECURITY INVOKER;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   464
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   465
-- 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
   466
--      varchar localpart
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   467
--      varchar the_domain
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   468
-- 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
   469
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   470
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
   471
    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
   472
    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
   473
AS $$
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   474
    DECLARE
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   475
        record recipient_transport;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   476
        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
   477
        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
   478
        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
   479
    BEGIN
550
867d950ce7b7 Fix transport_maps function for non-existent domains
martin f. krafft <madduck@madduck.net>
parents: 549
diff changeset
   480
        IF did IS NULL THEN
867d950ce7b7 Fix transport_maps function for non-existent domains
martin f. krafft <madduck@madduck.net>
parents: 549
diff changeset
   481
            RETURN;
867d950ce7b7 Fix transport_maps function for non-existent domains
martin f. krafft <madduck@madduck.net>
parents: 549
diff changeset
   482
        END IF;
867d950ce7b7 Fix transport_maps function for non-existent domains
martin f. krafft <madduck@madduck.net>
parents: 549
diff changeset
   483
867d950ce7b7 Fix transport_maps function for non-existent domains
martin f. krafft <madduck@madduck.net>
parents: 549
diff changeset
   484
        SELECT tid INTO transport_id
867d950ce7b7 Fix transport_maps function for non-existent domains
martin f. krafft <madduck@madduck.net>
parents: 549
diff changeset
   485
          FROM users
867d950ce7b7 Fix transport_maps function for non-existent domains
martin f. krafft <madduck@madduck.net>
parents: 549
diff changeset
   486
         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
   487
530
95dd123b552e Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 528
diff changeset
   488
        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
   489
            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
   490
              FROM domain_data
95dd123b552e Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 528
diff changeset
   491
             WHERE gid = did;
95dd123b552e Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 528
diff changeset
   492
        END IF;
95dd123b552e Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 528
diff changeset
   493
502
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   494
        FOR record IN
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   495
            SELECT recipient, transport
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   496
              FROM transport
530
95dd123b552e Modify SQL update script to accept NULL fields for users
martin f. krafft <madduck@madduck.net>
parents: 528
diff changeset
   497
             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
   498
            LOOP
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   499
                RETURN NEXT record;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   500
            END LOOP;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   501
        RETURN;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   502
    END;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   503
$$ LANGUAGE plpgsql STABLE
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   504
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
   505
EXTERNAL SECURITY INVOKER;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   506
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   507
-- 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
   508
--      varchar localpart
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   509
--      varchar the_domain
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   510
-- 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
   511
-- ---
518
5ec2068d02af Enable interpolation of alias destinations
martin f. krafft <madduck@madduck.net>
parents: 515
diff changeset
   512
CREATE OR REPLACE FUNCTION _interpolate_destination(
5ec2068d02af Enable interpolation of alias destinations
martin f. krafft <madduck@madduck.net>
parents: 515
diff changeset
   513
    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
   514
    RETURNS varchar
5ec2068d02af Enable interpolation of alias destinations
martin f. krafft <madduck@madduck.net>
parents: 515
diff changeset
   515
AS $$
5ec2068d02af Enable interpolation of alias destinations
martin f. krafft <madduck@madduck.net>
parents: 515
diff changeset
   516
    DECLARE
5ec2068d02af Enable interpolation of alias destinations
martin f. krafft <madduck@madduck.net>
parents: 515
diff changeset
   517
        result varchar(320);
5ec2068d02af Enable interpolation of alias destinations
martin f. krafft <madduck@madduck.net>
parents: 515
diff changeset
   518
    BEGIN
5ec2068d02af Enable interpolation of alias destinations
martin f. krafft <madduck@madduck.net>
parents: 515
diff changeset
   519
        IF position('%' in destination) = 0 THEN
5ec2068d02af Enable interpolation of alias destinations
martin f. krafft <madduck@madduck.net>
parents: 515
diff changeset
   520
            RETURN destination;
5ec2068d02af Enable interpolation of alias destinations
martin f. krafft <madduck@madduck.net>
parents: 515
diff changeset
   521
        END IF;
5ec2068d02af Enable interpolation of alias destinations
martin f. krafft <madduck@madduck.net>
parents: 515
diff changeset
   522
        result := replace(destination, '%n', localpart);
5ec2068d02af Enable interpolation of alias destinations
martin f. krafft <madduck@madduck.net>
parents: 515
diff changeset
   523
        result := replace(result, '%d', the_domain);
5ec2068d02af Enable interpolation of alias destinations
martin f. krafft <madduck@madduck.net>
parents: 515
diff changeset
   524
        result := replace(result, '%=', localpart || '=' || the_domain);
5ec2068d02af Enable interpolation of alias destinations
martin f. krafft <madduck@madduck.net>
parents: 515
diff changeset
   525
        RETURN result;
5ec2068d02af Enable interpolation of alias destinations
martin f. krafft <madduck@madduck.net>
parents: 515
diff changeset
   526
    END;
5ec2068d02af Enable interpolation of alias destinations
martin f. krafft <madduck@madduck.net>
parents: 515
diff changeset
   527
$$ LANGUAGE plpgsql STABLE
5ec2068d02af Enable interpolation of alias destinations
martin f. krafft <madduck@madduck.net>
parents: 515
diff changeset
   528
RETURNS NULL ON NULL INPUT
5ec2068d02af Enable interpolation of alias destinations
martin f. krafft <madduck@madduck.net>
parents: 515
diff changeset
   529
EXTERNAL SECURITY INVOKER;
5ec2068d02af Enable interpolation of alias destinations
martin f. krafft <madduck@madduck.net>
parents: 515
diff changeset
   530
502
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   531
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
   532
    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
   533
    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
   534
AS $$
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   535
    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
   536
        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
   537
        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
   538
        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
   539
        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
   540
        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
   541
    BEGIN
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   542
        FOR record IN
549
44a808af6cf4 Revert caching of destination interpolation
martin f. krafft <madduck@madduck.net>
parents: 548
diff changeset
   543
            SELECT recipient,
44a808af6cf4 Revert caching of destination interpolation
martin f. krafft <madduck@madduck.net>
parents: 548
diff changeset
   544
                _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
   545
              FROM alias
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   546
             WHERE gid = did
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   547
               AND address = localpart
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   548
            LOOP
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   549
                RETURN NEXT record;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   550
            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
   551
511
0244f1344b04 Make virtual_alias_maps function search catchall when no aliases are found
martin f. krafft <madduck@madduck.net>
parents: 503
diff changeset
   552
        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
   553
            -- 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
   554
            -- 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
   555
            -- 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
   556
            -- 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
   557
            -- 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
   558
            -- 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
   559
            -- 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
   560
            -- 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
   561
            OPEN catchall_cursor FOR
549
44a808af6cf4 Revert caching of destination interpolation
martin f. krafft <madduck@madduck.net>
parents: 548
diff changeset
   562
                SELECT recipient,
44a808af6cf4 Revert caching of destination interpolation
martin f. krafft <madduck@madduck.net>
parents: 548
diff changeset
   563
                    _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
   564
                  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
   565
                 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
   566
            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
   567
d863a44a6353 Make PL/pgSQL function feed back identity for mailboxes/relocated when there
martin f. krafft <madduck@madduck.net>
parents: 512
diff changeset
   568
            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
   569
                -- 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
   570
                -- 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
   571
                -- 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
   572
                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
   573
                    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
   574
                      FROM users
512
821d3ffaaaca Modify virtual_alias_maps function to check mailboxes/relocated first
martin f. krafft <madduck@madduck.net>
parents: 511
diff changeset
   575
                    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
   576
                      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
   577
                    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
   578
                      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
   579
                    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
   580
                      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
   581
                    LOOP
821d3ffaaaca Modify virtual_alias_maps function to check mailboxes/relocated first
martin f. krafft <madduck@madduck.net>
parents: 511
diff changeset
   582
                        RETURN NEXT record;
821d3ffaaaca Modify virtual_alias_maps function to check mailboxes/relocated first
martin f. krafft <madduck@madduck.net>
parents: 511
diff changeset
   583
                    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
   584
d863a44a6353 Make PL/pgSQL function feed back identity for mailboxes/relocated when there
martin f. krafft <madduck@madduck.net>
parents: 512
diff changeset
   585
                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
   586
                    -- 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
   587
                    -- 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
   588
                    -- 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
   589
                    LOOP
d863a44a6353 Make PL/pgSQL function feed back identity for mailboxes/relocated when there
martin f. krafft <madduck@madduck.net>
parents: 512
diff changeset
   590
                        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
   591
                        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
   592
                        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
   593
                    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
   594
                END IF;
512
821d3ffaaaca Modify virtual_alias_maps function to check mailboxes/relocated first
martin f. krafft <madduck@madduck.net>
parents: 511
diff changeset
   595
            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
   596
            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
   597
        END IF;
502
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   598
        RETURN;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   599
    END;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   600
$$ LANGUAGE plpgsql STABLE
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   601
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
   602
EXTERNAL SECURITY INVOKER;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   603
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   604
-- 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
   605
--      varchar localpart
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   606
--      varchar the_domain
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   607
-- 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
   608
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   609
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
   610
   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
   611
AS $$
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   612
    DECLARE
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   613
        rec address_maildir;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   614
        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
   615
        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
   616
    BEGIN
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   617
        FOR rec IN
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   618
            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
   619
              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
   620
             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
   621
               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
   622
               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
   623
               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
   624
            LOOP
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   625
                RETURN NEXT rec;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   626
            END LOOP;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   627
        RETURN;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   628
    END;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   629
$$ LANGUAGE plpgsql STABLE
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   630
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
   631
EXTERNAL SECURITY INVOKER;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   632
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   633
-- 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
   634
--      varchar localpart
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   635
--      varchar the_domain
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   636
-- 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
   637
-- ---
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   638
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
   639
    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
   640
AS $$
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   641
    DECLARE
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   642
        record recipient_uid;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   643
        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
   644
    BEGIN
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   645
        FOR record IN
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   646
            SELECT recipient, uid
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   647
              FROM users
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   648
             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
   649
                            FROM domain_name
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   650
                           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
   651
               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
   652
            LOOP
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   653
                RETURN NEXT record;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   654
            END LOOP;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   655
        RETURN;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   656
    END;
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   657
$$ LANGUAGE plpgsql STABLE
e1b32377032f pgsql: Merged types and functions into the create_tables… files.
Pascal Volk <user@localhost.localdomain.org>
parents: 500
diff changeset
   658
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
   659
EXTERNAL SECURITY INVOKER;