aV9jj0z2

1CREATE TABLE cdrs (
2    i_cdr bigint NOT NULL,
3    i_cdrs_connection bigint NOT NULL,
4    i_call bigint NOT NULL,
5    customer_name character varying(156) NOT NULL,
6    client_name_id character varying(256) NOT NULL,
7    connection_name character varying(156) NOT NULL,
8    vendor_name_id character varying(256) NOT NULL,    
9    setup_time timestamp with time zone NOT NULL,
10    c_result_id bigint NOT NULL,
11    v_result_id bigint NOT NULL
12    );
13	
14CREATE UNIQUE INDEX i_cdrs_connection ON cdrs(i_cdrs_connection)
15
16CREATE OR REPLACE FUNCTION cdrs_insert_trigger() RETURNS TRIGGER AS $$
17BEGIN
18    EXECUTE 'INSERT INTO cdrs_'|| to_char(NEW.setup_time, 'YYYY_MM_DD') ||' SELECT ($1).*'
19    USING NEW;
20    RETURN NULL;
21    EXCEPTION
22        WHEN undefined_table THEN
23            EXECUTE 'CREATE TABLE IF NOT EXISTS cdrs_'|| to_char(NEW.setup_time, 'YYYY_MM_DD') ||' (CHECK ( setup_time >= '''|| to_char(NEW.setup_time, 'YYYY-MM-DD 00:00') ||''' AND setup_time < '''|| to_char(NEW.setup_time + INTERVAL '1 day', 'YYYY-MM-DD 00:00') ||''' )) INHERITS (cdrs)';
24            EXECUTE 'CREATE UNIQUE INDEX i_cdrs_connection_'|| to_char(NEW.setup_time, 'YYYY_MM_DD') ||' ON cdrs_'|| to_char(NEW.setup_time, 'YYYY_MM_DD') ||' (i_cdrs_connection)';
25            EXECUTE 'CREATE INDEX i_cdr_'|| to_char(NEW.setup_time, 'YYYY_MM_DD') ||' ON cdrs_'|| to_char(NEW.setup_time, 'YYYY_MM_DD') ||' (i_cdr)';
26            EXECUTE 'CREATE INDEX i_call_'|| to_char(NEW.setup_time, 'YYYY_MM_DD') ||' ON cdrs_'|| to_char(NEW.setup_time, 'YYYY_MM_DD') ||' (i_call)';
27            EXECUTE 'CREATE INDEX setup_time_'|| to_char(NEW.setup_time, 'YYYY_MM_DD') ||' ON cdrs_'|| to_char(NEW.setup_time, 'YYYY_MM_DD') ||' (setup_time)';
28
29        EXECUTE 'INSERT INTO cdrs_'|| to_char(NEW.setup_time, 'YYYY_MM_DD') ||' SELECT ($1).*'
30        USING NEW;
31        RETURN NULL;
32END
33$$
34LANGUAGE plpgsql;
35
36
37CREATE TRIGGER fk_checkTrigger_cdrs
38BEFORE INSERT ON cdrs
39FOR EACH ROW
40EXECUTE PROCEDURE cdrs_insert_trigger();