partioning.sql

/* CREATING CHILD TABLES */
CREATE TABLE mail_message_y2014 (CHECK(date >= DATE '2014-01-01' AND date < DATE '2015-01-01')) INHERITS (mail_message);
CREATE TABLE mail_message_y2015 (CHECK(date >= DATE '2015-01-01' AND date < DATE '2016-01-01')) INHERITS (mail_message);
CREATE TABLE mail_message_y2016 (CHECK(date >= DATE '2016-01-01' AND date < DATE '2017-01-01')) INHERITS (mail_message);

/* CREATING INDEXES */

CREATE INDEX mail_message_y2014_id ON mail_message_y2014 (id);
CREATE INDEX mail_message_y2015_id ON mail_message_y2015 (id);
CREATE INDEX mail_message_y2016_id ON mail_message_y2016 (id);

CREATE INDEX mail_message_y2014_date ON mail_message_y2014 (date);
CREATE INDEX mail_message_y2015_date ON mail_message_y2015 (date);
CREATE INDEX mail_message_y2016_date ON mail_message_y2016 (date);

CREATE INDEX mail_message_y2014_author_id ON mail_message_y2014 (author_id);
CREATE INDEX mail_message_y2015_author_id ON mail_message_y2015 (author_id);
CREATE INDEX mail_message_y2016_author_id ON mail_message_y2016 (author_id);

CREATE INDEX mail_message_y2014_model_res_id ON mail_message_y2014 (model, res_id);
CREATE INDEX mail_message_y2015_model_res_id ON mail_message_y2015 (model, res_id);
CREATE INDEX mail_message_y2016_model_res_id ON mail_message_y2016 (model, res_id);

CREATE INDEX mail_message_y2014_model ON mail_message_y2014 (model);
CREATE INDEX mail_message_y2015_model ON mail_message_y2015 (model);
CREATE INDEX mail_message_y2016_model ON mail_message_y2016 (model);

CREATE INDEX mail_message_y2014_res_id ON mail_message_y2014 (res_id);
CREATE INDEX mail_message_y2015_res_id ON mail_message_y2015 (res_id);
CREATE INDEX mail_message_y2016_res_id ON mail_message_y2016 (res_id);

CREATE INDEX mail_message_y2014_parent_id ON mail_message_y2014 (parent_id);
CREATE INDEX mail_message_y2015_parent_id ON mail_message_y2015 (parent_id);
CREATE INDEX mail_message_y2016_parent_id ON mail_message_y2016 (parent_id);

CREATE INDEX mail_message_y2014_subtype_id ON mail_message_y2014 (subtype_id);
CREATE INDEX mail_message_y2015_subtype_id ON mail_message_y2015 (subtype_id);
CREATE INDEX mail_message_y2016_subtype_id ON mail_message_y2016 (subtype_id);

/* CREATING TRIGGERS */

CREATE OR REPLACE FUNCTION mail_message_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
    IF ( NEW.date >= DATE '2014-01-01' AND
         NEW.date < DATE '2015-01-01' ) THEN
        INSERT INTO mail_message_y2014 VALUES (NEW.*);
    ELSIF ( NEW.date >= DATE '2015-01-01' AND
            NEW.date < DATE '2016-01-01' ) THEN
        INSERT INTO mail_message_y2015 VALUES (NEW.*);
    ELSIF ( NEW.date >= DATE '2016-01-01' AND
            NEW.date < DATE '2017-01-01' ) THEN
        INSERT INTO mail_message_y2016 VALUES (NEW.*);
    ELSE
        RETURN NEW;
    END IF;
    RETURN NULL;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER insert_mail_message_trigger
    BEFORE INSERT ON mail_message
    FOR EACH ROW EXECUTE PROCEDURE mail_message_insert_trigger();

/* INSERTING AND DELETING VALUES */

INSERT INTO mail_message_y2014 (SELECT * FROM mail_message WHERE date >= DATE '2014-01-01' AND date < DATE '2015-01-01');
INSERT INTO mail_message_y2015 (SELECT * FROM mail_message WHERE date >= DATE '2015-01-01' AND date < DATE '2016-01-01');
INSERT INTO mail_message_y2016 (SELECT * FROM mail_message WHERE date >= DATE '2016-01-01' AND date < DATE '2017-01-01');
DELETE FROM mail_message WHERE tableoid = ('mail_message'::regclass)::oid;