=========================
Renaming a Database Table
=========================

Renaming a table involves a few steps:

1. Rename the table
2. Rename dependent objects, like constraints, indexes, and sequences.
3. Create a writable view using the old name so deployed code keeps working.

For example:

.. code-block:: sql

    -- Copyright 2011 Canonical Ltd.
    -- This software is licensed under the GNU Affero General Public License version 3 (see the file LICENSE).
    SET client_min_messages=ERROR;

    -- Rename the table
    ALTER TABLE IrcId RENAME TO IrcNickname;

    -- And rename sequences, constraints, and indexes to match.
    ALTER SEQUENCE ircid_id_seq RENAME TO ircnickname_id_seq;
    ALTER TABLE IrcNickname
        -- With Slony 1.2.22, renaming the primary key fails. Leave these
        -- with the old names for now.
        -- DROP CONSTRAINT ircid_pkey,
        -- ADD CONSTRAINT ircnickname_pkey PRIMARY KEY (id),
        DROP CONSTRAINT ircid_person_fk,
        ADD CONSTRAINT ircnickname_person_fk
            FOREIGN KEY (person) REFERENCES Person;
    ALTER INDEX ircid_person_idx RENAME TO ircnickname_person_idx;

    -- Create a view with the old table name for backwards compatibility.
    CREATE OR REPLACE VIEW IrcId AS SELECT * FROM IrcNickname;

    -- Make the backwards compatibility view updatable.
    CREATE OR REPLACE RULE IrcId_ins AS ON INSERT TO IrcId DO INSTEAD
        INSERT INTO IrcNickname VALUES (
            COALESCE(NEW.id, nextval('ircnickname_id_seq')),
            NEW.person,
            NEW.network,
            NEW.nickname)
        RETURNING IrcNickname.*;

    CREATE OR REPLACE RULE IrcId_upd AS ON UPDATE TO IrcId DO INSTEAD
        UPDATE IrcNickname SET
            id = NEW.id,
            person = NEW.person,
            network = NEW.network,
            nickname = NEW.nickname
        WHERE id = OLD.id
        RETURNING IrcNickname.*;

    CREATE OR REPLACE RULE IrcId_del AS ON DELETE TO IrcId DO INSTEAD
        DELETE FROM IrcNickname WHERE id = OLD.id
        RETURNING IrcNickname.*;

    INSERT INTO LaunchpadDatabaseRevision VALUES (2208, 85, 0);