vdasuqf8

1CREATE TABLE hub
2(
3    id BIGINT,
4    name VARCHAR
5);
6
7CREATE OR REPLACE FUNCTION insert_trigger()
8    RETURNS TRIGGER AS
9$BODY$
10DECLARE
11    part bigint = 100000;
12    old_table_id int = ceil((OLD.id / part));
13    new_table_id int = ceil((NEW.id / part));
14
15BEGIN
16    IF (TG_RELNAME = 'inherit_' || new_table_id ) THEN
17        RETURN NEW;
18    end if;
19
20    IF NOT EXISTS (SELECT FROM pg_catalog.pg_tables
21              WHERE  schemaname = 'public'
22              AND    tablename  = 'inherit_' || new_table_id) THEN
23        EXECUTE 'CREATE TABLE IF NOT EXISTS inherit_' || new_table_id || ' () INHERITS(hub);';
24        EXECUTE 'ALTER TABLE inherit_'  || new_table_id || ' ADD CONSTRAINT partrtition_check CHECK (id >=' || part * new_table_id || 'and id <' || part * (new_table_id + 1) ||  ');';
25    END IF;
26
27    IF (TG_OP = 'UPDATE') THEN
28        EXECUTE 'DELETE FROM inherit_' || old_table_id || ' WHERE id = ' || OLD.id || ' AND name = ' || OLD.name;
29	EXECUTE 'INSERT INTO inherit_' || new_table_id || ' (id, name) VALUES ('|| NEW.id || ', ' || NEW.name || ');';
30    END IF;
31
32    RETURN NULL;
33END
34$BODY$
35LANGUAGE plpgsql;
36
37
38CREATE TRIGGER before_insert_trigger
39    BEFORE INSERT ON hub
40    FOR EACH ROW
41EXECUTE PROCEDURE insert_trigger();