Created
July 12, 2017 15:41
-
-
Save josemoralesp/4e0a80c28e92539c0b22c35e17b6f6cf to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* 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; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment