Skip to content

Instantly share code, notes, and snippets.

@josemoralesp
Created July 12, 2017 15:41
Show Gist options
  • Save josemoralesp/4e0a80c28e92539c0b22c35e17b6f6cf to your computer and use it in GitHub Desktop.
Save josemoralesp/4e0a80c28e92539c0b22c35e17b6f6cf to your computer and use it in GitHub Desktop.
/* 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