update_tables_0.4.x-0.5.pgsql
author Pascal Volk <neverseen@users.sourceforge.net>
Sun, 24 Aug 2008 04:57:11 +0000
changeset 62 869de537a020
parent 42 9d10877e1c10
child 67 e4d25f50164d
permissions -rw-r--r--
* 'VirtualMailManager/VirtualMailManager.py' - Made VirtualMailManager.domainInfo() more flexible * 'vmm' - Adjusted to changes in VirtualMailManager's domainInfo() * 'man/de/man1/vmm.1' * 'man/man1/vmm.1' - updated * 'po/de.po' * 'po/vmm.pot' - updated
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
42
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
     1
-- $Id$ 
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
     2
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
     3
SET client_encoding = 'UTF8';
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
     4
SET client_min_messages = warning;
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
     5
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
     6
ALTER SEQUENCE domains_gid RENAME TO domain_gid;
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
     7
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
     8
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
     9
CREATE TABLE domain_data (
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    10
    gid         bigint NOT NULL DEFAULT nextval('domain_gid'),
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    11
    tid         bigint NOT NULL DEFAULT 1,
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    12
    domaindir   varchar(40) NOT NULL,
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    13
    CONSTRAINT  pkey_domain_data PRIMARY KEY (gid),
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    14
    CONSTRAINT  fkey_domain_data_tid_transport FOREIGN KEY (tid)
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    15
        REFERENCES transport (tid)
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    16
);
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    17
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    18
CREATE TABLE domain_name (
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    19
    domainname  varchar(255) NOT NULL,
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    20
    gid         bigint NOT NULL,
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    21
    is_primary  boolean NOT NULL,
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    22
    CONSTRAINT  pkey_domain_name PRIMARY KEY (domainname),
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    23
    CONSTRAINT  fkey_domain_name_gid_domain_data FOREIGN KEY (gid)
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    24
        REFERENCES domain_data (gid)
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    25
);
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    26
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    27
INSERT INTO domain_data (gid, tid, domaindir) 
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    28
    SELECT gid, tid, domaindir
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    29
      FROM domains;
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    30
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    31
INSERT INTO domain_name (domainname, gid, is_primary) 
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    32
    SELECT domainname, gid, TRUE
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    33
      FROM domains;
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    34
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    35
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    36
ALTER TABLE users DROP CONSTRAINT fkey_users_gid_domains;
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    37
ALTER TABLE users ADD CONSTRAINT fkey_users_gid_domain_data FOREIGN KEY (gid)
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    38
    REFERENCES domain_data (gid);
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    39
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    40
ALTER TABLE alias DROP CONSTRAINT fkey_alias_gid_domains;
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    41
ALTER TABLE alias ADD CONSTRAINT fkey_alias_gid_domain_data FOREIGN KEY (gid)
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    42
    REFERENCES domain_data (gid);
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    43
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    44
ALTER TABLE relocated DROP CONSTRAINT fkey_relocated_gid_domains;
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    45
ALTER TABLE relocated ADD CONSTRAINT fkey_relocated_gid_domain_data
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    46
    FOREIGN KEY (gid) REFERENCES domain_data (gid);
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    47
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    48
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    49
CREATE OR REPLACE VIEW dovecot_password AS
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    50
    SELECT local_part || '@' || domain_name.domainname AS "user",
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    51
           passwd AS "password", smtp, pop3, imap, managesieve
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    52
      FROM users
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    53
           LEFT JOIN domain_name USING (gid);
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    54
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    55
CREATE OR REPLACE VIEW dovecot_user AS
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    56
    SELECT local_part || '@' || domain_name.domainname AS userid,
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    57
           uid, gid, domain_data.domaindir || '/' || uid AS home,
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    58
           '~/' || maillocation.maillocation AS mail
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    59
      FROM users
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    60
           LEFT JOIN domain_data USING (gid)
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    61
           LEFT JOIN domain_name USING (gid)
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    62
           LEFT JOIN maillocation USING (mid);
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    63
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    64
CREATE OR REPLACE VIEW postfix_gid AS
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    65
    SELECT gid, domainname
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    66
      FROM domain_name;
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    67
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    68
CREATE OR REPLACE VIEW postfix_uid AS
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    69
    SELECT local_part || '@' || domain_name.domainname AS address, uid
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    70
      FROM users
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    71
           LEFT JOIN domain_name USING (gid);
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    72
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    73
CREATE OR REPLACE VIEW postfix_maildir AS
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    74
    SELECT local_part || '@' || domain_name.domainname AS address,
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    75
           domain_data.domaindir||'/'||uid||'/'||maillocation.maillocation||'/'
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    76
           AS maildir
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    77
      FROM users
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    78
           LEFT JOIN domain_data USING (gid)
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    79
           LEFT JOIN domain_name USING (gid)
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    80
           LEFT JOIN maillocation USING (mid);
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    81
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    82
CREATE OR REPLACE VIEW postfix_relocated AS
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    83
    SELECT address || '@' || domain_name.domainname AS address, destination
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    84
      FROM relocated
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    85
           LEFT JOIN domain_name USING (gid);
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    86
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    87
CREATE OR REPLACE VIEW postfix_alias AS
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    88
    SELECT address || '@' || domain_name.domainname AS address, destination, gid
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    89
      FROM alias
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    90
           LEFT JOIN domain_name USING (gid);
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    91
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    92
CREATE OR REPLACE VIEW postfix_transport AS
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    93
    SELECT local_part || '@' || domain_name.domainname AS address,
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    94
           transport.transport
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    95
      FROM users
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    96
           LEFT JOIN transport USING (tid)
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    97
           LEFT JOIN domain_name USING (gid);
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    98
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    99
DROP VIEW vmm_domain_info;
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   100
CREATE OR REPLACE VIEW vmm_domain_info AS
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   101
    SELECT gid, domainname, transport, domaindir,
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   102
           count(uid) AS accounts,
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   103
           aliases,
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   104
           (SELECT count(gid)
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   105
              FROM domain_name
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   106
             WHERE domain_name.gid = domain_data.gid
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   107
               AND NOT domain_name.is_primary) AS aliasdomains
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   108
      FROM domain_data
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   109
           LEFT JOIN domain_name USING (gid)
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   110
           LEFT JOIN transport USING (tid)
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   111
           LEFT JOIN users USING (gid)
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   112
           LEFT JOIN vmm_alias_count USING (gid)
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   113
     WHERE domain_name.is_primary
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   114
  GROUP BY gid, domainname, transport, domaindir, aliases;
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   115
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   116
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   117
DROP TABLE domains;
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   118
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   119
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   120
CREATE LANGUAGE plpgsql;
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   121
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   122
CREATE OR REPLACE FUNCTION domain_primary_trigger() RETURNS TRIGGER AS $$
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   123
DECLARE
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   124
    primary_count bigint;
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   125
BEGIN
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   126
    SELECT INTO primary_count count(gid) + NEW.is_primary::integer
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   127
      FROM domain_name
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   128
     WHERE domain_name.gid = NEW.gid
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   129
       AND is_primary;
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   130
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   131
    IF (primary_count > 1) THEN
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   132
        RAISE EXCEPTION 'There can only be one domain marked as primary.';
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   133
    END IF;
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   134
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   135
    RETURN NEW;
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   136
END;
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   137
$$ LANGUAGE plpgsql STABLE;
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   138
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   139
CREATE TRIGGER primary_count BEFORE INSERT OR UPDATE ON domain_name
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   140
    FOR EACH ROW EXECUTE PROCEDURE domain_primary_trigger();