6YNWhxbt

1CREATE TABLE creatives (
2    id          bigserial   NOT NULL,
3    dsp_id      integer     NOT NULL,
4    bid_id      text        NOT NULL, -- тащемта явно не все креативы в мире имеют это поле... но пока что openrtb, а там посмотрим
5    click_url   text        NOT NULL,
6    title       text        NOT NULL,
7    thumbs      text[][]    NOT NULL,
8    dump        jsonb       NOT NULL,
9    ctime       timestamp   NOT NULL DEFAULT now(),
10    info        json
11) PARTITION BY RANGE (ctime);
12
13CREATE OR REPLACE FUNCTION week_table_suffix_from_ts(ts timestamp with time zone) RETURNS text
14    LANGUAGE plpgsql
15    AS $$
16DECLARE
17    table_suffix text := replace(substring((date_trunc('week', ts))::text from 1 for 10),'-','');
18BEGIN
19    RETURN table_suffix;
20END;
21$$;
22
23CREATE OR REPLACE FUNCTION creatives_partitioning_trigger_f() RETURNS trigger
24    LANGUAGE plpgsql
25    AS $$
26DECLARE
27    table_prefix    text    := 'creatives_';
28    current_time    timestamp with time zone := now();
29    table_suffix    text    := week_table_suffix_from_ts(current_time);
30    table_name      text    := 'creatives_' || table_suffix;
31    table_exists    boolean;
32BEGIN
33    EXECUTE
34        'SELECT EXISTS (SELECT 1 FROM pg_tables WHERE schemaname = $1 AND tablename = $2)'
35        INTO table_exists
36        USING 'public', table_name;
37
38    IF NOT table_exists THEN
39        EXECUTE format(
40            'CREATE TABLE IF NOT EXISTS %I PARTITION OF creatives FOR VALUES FROM (%L) TO (%L)'::text,
41            table_name,
42            date_trunc('week', current_time),
43            date_trunc('week', current_time + interval '1 week')
44        );
45
46        EXECUTE format( -- это ж всё равно serial, поэтому unique не делаю
47            'CREATE INDEX IF NOT EXISTS %I ON %I (id)'::text,
48            table_name || '_id', table_name
49        );
50
51        EXECUTE format(
52            'CREATE INDEX IF NOT EXISTS %I ON %I (bid_id)'::text,
53            table_name || '_bidid', table_name
54        );
55
56        EXECUTE format(
57            'CREATE INDEX IF NOT EXISTS %I ON %I (dsp_id, ctime)'::text,
58            table_name || '_dspid_ctime', table_name
59        );
60    END IF;
61
62    RETURN NULL;
63END;
64$$;
65
66CREATE TRIGGER creatives_partitioning_trigger BEFORE INSERT ON creatives FOR EACH STATEMENT EXECUTE PROCEDURE creatives_partitioning_trigger_f();