xqvtAQnv

1CREATE TABLE leaf_data(
2    ts timestamptz default now(),
3    data JSONB,
4    leaf VARCHAR(30)
5);
6
7CREATE TABLE data(
8    ts timestamptz default now(),
9    data JSONB,
10    leaf VARCHAR(30)
11) PARTITION BY LIST (leaf);
12
13CREATE TRIGGER inserto_into_data
14    BEFORE INSERT
15    ON leaf_data
16    FOR EACH ROW
17    EXECUTE PROCEDURE data_partition();
18
19CREATE OR REPLACE FUNCTION data_partition()
20    RETURNS TRIGGER AS $$
21    DECLARE
22        data_table TEXT;
23        leaf_table TEXT;
24        minute_table TEXT;
25        minute TEXT;
26        minute_ts TEXT;
27        new_tables TEXT;
28        new_insert TEXT;
29    BEGIN
30        data_table  := 'data';
31        leaf_table  := 'leaf_' || NEW.leaf;
32        minute_ts   := date_trunc('minute', NEW.ts);
33        minute      := date_part('minute', NEW.ts);
34        minute_table := leaf_table || '_' || minute;
35        IF NOT EXISTS (SELECT relname FROM pg_class WHERE relname=leaf_table) THEN
36            RAISE NOTICE 'Creating Partition : %', leaf_table;
37            new_tables := 'CREATE TABLE ' || leaf_table
38                || ' PARTITION OF data (ts, data, leaf ) '
39                || ' FOR VALUES IN ( ''' || NEW.leaf || ''' ) '
40                || ' PARTITION BY RANGE ( ts );';
41            EXECUTE new_tables;
42        END IF;
43        IF NOT EXISTS (SELECT relname FROM pg_class WHERE relname=minute_table) THEN
44            RAISE NOTICE 'Creating Partition : %', minute_table;
45            new_tables := ' CREATE TABLE ' || minute_table
46                || ' PARTITION OF ' || leaf_table || ' ( ts, data, leaf ) '
47                || ' FOR VALUES FROM ('' ' || minute_ts || ''' )  TO ( '' ' || minute_ts::timestamp + interval'1 minute' || ' '' ); ';
48            EXECUTE new_tables;
49        END IF;
50        new_insert := 'INSERT INTO data (leaf, data) VALUES (''' ||  NEW.leaf ||'''' || ','
51        || '''' ||NEW.data|| '''' || ');';
52        EXECUTE new_insert;
53        RETURN NULL;
54    END;
55    $$ LANGUAGE plpgsql VOLATILE;