update_tables_0.4.x-0.5.pgsql
author Pascal Volk <neverseen@users.sourceforge.net>
Wed, 03 Sep 2008 01:44:25 +0000
changeset 74 67a454ea5472
parent 71 4c94ba297698
child 80 5dedc673524e
permissions -rw-r--r--
'create_optional_types_and_functions.pgsql' - Added functions postfix_relocated_map() and postfix_transport_map() and type recipient_transport
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
71
4c94ba297698 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 67
diff changeset
    36
ALTER TABLE users DROP CONSTRAINT pkye_users;
4c94ba297698 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 67
diff changeset
    37
ALTER TABLE users ADD CONSTRAINT  pkey_users PRIMARY KEY (local_part, gid);
42
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    38
ALTER TABLE users DROP CONSTRAINT fkey_users_gid_domains;
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    39
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
    40
    REFERENCES domain_data (gid);
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    41
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    42
ALTER TABLE alias DROP CONSTRAINT fkey_alias_gid_domains;
67
e4d25f50164d * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 42
diff changeset
    43
ALTER TABLE alias DROP CONSTRAINT pkey_alias;
42
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    44
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
    45
    REFERENCES domain_data (gid);
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    46
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    47
ALTER TABLE relocated DROP CONSTRAINT fkey_relocated_gid_domains;
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    48
ALTER TABLE relocated ADD CONSTRAINT fkey_relocated_gid_domain_data
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    49
    FOREIGN KEY (gid) REFERENCES domain_data (gid);
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    50
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    51
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    52
CREATE OR REPLACE VIEW dovecot_password AS
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    53
    SELECT local_part || '@' || domain_name.domainname AS "user",
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    54
           passwd AS "password", smtp, pop3, imap, managesieve
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    55
      FROM users
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    56
           LEFT JOIN domain_name USING (gid);
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    57
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    58
CREATE OR REPLACE VIEW dovecot_user AS
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    59
    SELECT local_part || '@' || domain_name.domainname AS userid,
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    60
           uid, gid, domain_data.domaindir || '/' || uid AS home,
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    61
           '~/' || maillocation.maillocation AS mail
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    62
      FROM users
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    63
           LEFT JOIN domain_data USING (gid)
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    64
           LEFT JOIN domain_name USING (gid)
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    65
           LEFT JOIN maillocation USING (mid);
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    66
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    67
CREATE OR REPLACE VIEW postfix_gid AS
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    68
    SELECT gid, domainname
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    69
      FROM domain_name;
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    70
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    71
CREATE OR REPLACE VIEW postfix_uid AS
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    72
    SELECT local_part || '@' || domain_name.domainname AS address, uid
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    73
      FROM users
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    74
           LEFT JOIN domain_name USING (gid);
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    75
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    76
CREATE OR REPLACE VIEW postfix_maildir AS
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    77
    SELECT local_part || '@' || domain_name.domainname AS address,
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    78
           domain_data.domaindir||'/'||uid||'/'||maillocation.maillocation||'/'
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    79
           AS maildir
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    80
      FROM users
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    81
           LEFT JOIN domain_data USING (gid)
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    82
           LEFT JOIN domain_name USING (gid)
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    83
           LEFT JOIN maillocation USING (mid);
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    84
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    85
CREATE OR REPLACE VIEW postfix_relocated AS
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    86
    SELECT address || '@' || domain_name.domainname AS address, destination
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    87
      FROM relocated
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    88
           LEFT JOIN domain_name USING (gid);
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    89
67
e4d25f50164d * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 42
diff changeset
    90
DROP VIEW postfix_alias;
e4d25f50164d * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 42
diff changeset
    91
DROP VIEW vmm_domain_info;
e4d25f50164d * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 42
diff changeset
    92
DROP VIEW vmm_alias_count;
e4d25f50164d * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 42
diff changeset
    93
e4d25f50164d * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 42
diff changeset
    94
ALTER TABLE alias ALTER address TYPE varchar(64);
e4d25f50164d * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 42
diff changeset
    95
ALTER TABLE alias ADD CONSTRAINT pkey_alias 
e4d25f50164d * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 42
diff changeset
    96
    PRIMARY KEY (gid, address, destination);
e4d25f50164d * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 42
diff changeset
    97
42
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    98
CREATE OR REPLACE VIEW postfix_alias AS
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
    99
    SELECT address || '@' || domain_name.domainname AS address, destination, gid
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   100
      FROM alias
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   101
           LEFT JOIN domain_name USING (gid);
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   102
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   103
CREATE OR REPLACE VIEW postfix_transport AS
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   104
    SELECT local_part || '@' || domain_name.domainname AS address,
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   105
           transport.transport
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   106
      FROM users
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   107
           LEFT JOIN transport USING (tid)
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   108
           LEFT JOIN domain_name USING (gid);
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   109
67
e4d25f50164d * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 42
diff changeset
   110
CREATE OR REPLACE VIEW vmm_alias_count AS
e4d25f50164d * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 42
diff changeset
   111
    SELECT count(DISTINCT address) AS aliases, gid
e4d25f50164d * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 42
diff changeset
   112
      FROM alias 
e4d25f50164d * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 42
diff changeset
   113
  GROUP BY gid;
e4d25f50164d * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents: 42
diff changeset
   114
42
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   115
CREATE OR REPLACE VIEW vmm_domain_info AS
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   116
    SELECT gid, domainname, transport, domaindir,
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   117
           count(uid) AS accounts,
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   118
           aliases,
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   119
           (SELECT count(gid)
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   120
              FROM domain_name
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   121
             WHERE domain_name.gid = domain_data.gid
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   122
               AND NOT domain_name.is_primary) AS aliasdomains
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   123
      FROM domain_data
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   124
           LEFT JOIN domain_name USING (gid)
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   125
           LEFT JOIN transport USING (tid)
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   126
           LEFT JOIN users USING (gid)
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   127
           LEFT JOIN vmm_alias_count USING (gid)
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   128
     WHERE domain_name.is_primary
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   129
  GROUP BY gid, domainname, transport, domaindir, aliases;
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
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   132
DROP TABLE domains;
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   133
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
CREATE LANGUAGE plpgsql;
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   136
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   137
CREATE OR REPLACE FUNCTION domain_primary_trigger() RETURNS TRIGGER AS $$
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   138
DECLARE
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   139
    primary_count bigint;
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   140
BEGIN
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   141
    SELECT INTO primary_count count(gid) + NEW.is_primary::integer
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   142
      FROM domain_name
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   143
     WHERE domain_name.gid = NEW.gid
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   144
       AND is_primary;
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   145
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   146
    IF (primary_count > 1) THEN
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   147
        RAISE EXCEPTION 'There can only be one domain marked as primary.';
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   148
    END IF;
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   149
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   150
    RETURN NEW;
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   151
END;
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   152
$$ LANGUAGE plpgsql STABLE;
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   153
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   154
CREATE TRIGGER primary_count BEFORE INSERT OR UPDATE ON domain_name
9d10877e1c10 * 'create_tables.pgsql'
Pascal Volk <neverseen@users.sourceforge.net>
parents:
diff changeset
   155
    FOR EACH ROW EXECUTE PROCEDURE domain_primary_trigger();