w5vvvFPa

1CREATE OR REPLACE FUNCTION tbl_scdr_buz_insert_trigger() RETURNS TRIGGER AS $$
2BEGIN
3    EXECUTE 'UPDATE scdr_buz_'|| to_char(NEW.start_time, 'YYYY_MM') ||' sc SET c_total_calls = sc.c_total_calls + ($1).c_total_calls WHERE (sc.c_prefix_id = ($1).c_prefix_id AND sc.v_prefix_id = ($1).v_prefix_id AND sc.start_time = ($1).start_time)'
4    USING NEW;
5    EXECUTE 'INSERT INTO scdr_buz_'|| to_char(NEW.start_time, 'YYYY_MM') ||'(customer_name, ...) Select ($1).* 
6 WHERE NOT EXISTS (SELECT * FROM scdr_buz_'|| to_char(NEW.start_time, 'YYYY_MM') ||' WHERE (...))'
7    USING NEW;
8    RETURN NULL;
9    EXCEPTION
10        WHEN undefined_table THEN
11            EXECUTE 'CREATE TABLE IF NOT EXISTS scdr_buz_'|| to_char(NEW.start_time, 'YYYY_MM') ||' (CHECK ( start_time >= '''|| to_char(NEW.start_time, 'YYYY-MM-01 00:00') ||''' AND start_time < '''|| to_char(NEW.start_time + INTERVAL '1 month', 'YYYY-MM-01 00:00') ||''' )) INHERITS (scdr_buz)';
12            EXECUTE 'CREATE INDEX i_buz_scdr_'|| to_char(NEW.start_time, 'YYYY_MM') ||' ON scdr_buz_'|| to_char(NEW.start_time, 'YYYY_MM') ||' (switch_name, customer_name, client_name_id, vendor_name_id, vendor_connection, c_prefix_id, v_prefix_id, start_time, c_billing_prefix, v_billing_prefix)';
13            EXECUTE 'CREATE INDEX i_buz_scdr_starttime_'|| to_char(NEW.start_time, 'YYYY_MM') ||' ON scdr_buz_'|| to_char(NEW.start_time, 'YYYY_MM') ||' (start_time)';
14
15        EXECUTE 'UPDATE scdr_buz_'|| to_char(NEW.start_time, 'YYYY_MM') ||' sc SET c_total_calls = sc.c_total_calls + ($1).c_total_calls WHERE (sc.c_prefix_id = ($1).c_prefix_id AND sc.v_prefix_id = ($1).v_prefix_id AND sc.start_time = ($1).start_time)'
16        USING NEW;
17        EXECUTE 'INSERT INTO scdr_buz_'|| to_char(NEW.start_time, 'YYYY_MM') ||'(customer_name, ...) Select ($1).* 
18     WHERE NOT EXISTS (SELECT * FROM scdr_buz_'|| to_char(NEW.start_time, 'YYYY_MM') ||' WHERE (...))'
19        USING NEW;
20        RETURN NULL;
21END
22$$
23LANGUAGE plpgsql;
24
25
26CREATE TRIGGER fk_checkTrigger_buz_scdr
27BEFORE INSERT ON scdr_buz
28FOR EACH ROW
29EXECUTE PROCEDURE tbl_scdr_buz_insert_trigger();