CREATE OR REPLACE FUNCTION cdr_partition_rule()
  RETURNS trigger AS
$BODY$
declare
_month text;
_year text;
_schema text;
_table text;
_parent_table text;
_primary_key text;
_table_owner text;
_table_exists text;
_sql text;
begin
-- Configurables
_schema := 'public';
_parent_table := 'cdr';
_primary_key := 'acctid';
_table_owner := 'all_users';
-- End Configurables


-- Get Current Month / Year
_month := (select trim(to_char(now(), 'month')));
_year := (select trim(to_char(now(), 'yyyy')));
-- Set the partition string on the new row
new.partition_string = _month || '_' || _year;

-- Set the current partitioned table based on parent table, month and year
_table := _parent_table || '_' || _month || '_' || _year;

-- Need to see if this table already exists
_table_exists := (select schemaname || '.' || tablename from pg_tables
		where schemaname = _schema and tablename = _table);
-- Check the return..
if _table_exists is null then
		-- Partitioned table does not exist. We need to create it and all of the constraints, rules, etc.
		_sql := 'CREATE TABLE ' || _schema || '.' || _table || ' (CONSTRAINT "C_' || _parent_table || '_partition_rule_' || _month || '_' || _year || '"
		CHECK ( partition_string = ''' || _month || '_' || _year || '''), 
		CONSTRAINT "PK_' || _primary_key || '_' || _month || '_' || _year || '" PRIMARY KEY (' || _primary_key || ')) inherits (' || _schema || '.' || _parent_table || ');';
        execute(_sql); -- Execute sql var to create the table.

		-- Set permissions
		_sql := 'ALTER TABLE ' || _schema || '.' || _table || ' OWNER TO ' || _table_owner || ';';
		execute(_sql); -- Execute sql var to create permissions.
		
		----------------------------------------------------------
		-- Rules / Triggers / Constraints / etc
		_sql := 'CREATE RULE ' || _parent_table || '_insert_' || _month || '_' || _year || ' AS ON INSERT TO ' || _schema || '.' || _parent_table || ' 
		WHERE ( new.partition_string = ''' || _month || '_' || _year || ''' )
		DO INSTEAD
		INSERT INTO ' || _schema || '.' || _table || ' (calldate,clid,src,dst,dcontext,channel,dstchannel,lastapp,
                                                                lastdata,duration,billsec,disposition,amaflags,accountcode,uniqueid,
                                                                userfield,srcip,dstip,partition_string)
                                                             VALUES (new.calldate, new.clid, new.src, new.dst, new.dcontext, new.channel, new.dstchannel, new.lastapp,
                                                                new.lastdata, new.duration, new.billsec, new.disposition, new.amaflags, new.accountcode, new.uniqueid,
                                                                new.userfield, coalesce(new.srcip, ''0.0.0.0''), coalesce(new.dstip, ''0.0.0.0''), new.partition_string);';
		execute(_sql); -- Create rule on parent table for this month / year

		_sql := 'CREATE UNIQUE INDEX "IX_cdr_unique_id_' || _month || '_' || _year || '" ON ' || _table || ' USING btree (calldate, uniqueid, src, dst);';
		execute(_sql);

		_sql := 'CREATE INDEX "IX_dst_' || _month || '_' || _year || '" ON ' || _table || ' USING btree (dst);';
		execute(_sql);

		_sql := 'CREATE INDEX "IX_normal_calls_' || _month || '_' || _year || '" ON ' || _table || ' USING btree
                          (acctid, calldate, clid, src, dst, dcontext, lastapp, duration, billsec, disposition, accountcode, srcip, dstip)
                          WHERE disposition::text = ''ANSWERED''::text AND (dcontext::text ~~ ''outbound%''::text AND dstchannel::text !~~ ''%iaxmodem%''::text AND lastapp::text = ''Dial''::text OR dcontext::text = ''main''::text);';
                execute(_sql);

                _sql := 'CREATE INDEX "IX_src_' || _month || '_' || _year || '" ON ' || _table || ' USING btree (src);';
                execute(_sql);

                _sql := 'CREATE INDEX "IX_acctcode_' || _month || '_' || _year || '" ON ' || _table || ' USING btree (accountcode);';
                execute(_sql);
		-- End Rules / Triggers / Constraints / etc
		----------------------------------------------------------
end if;
_sql := 'insert into ' || _schema || '.' || _table || ' (calldate,clid,src,dst,dcontext,channel,dstchannel,lastapp,
                                                         lastdata,duration,billsec,disposition,amaflags,accountcode,uniqueid,
                                                         userfield,srcip,dstip,partition_string) 
		             values (''' || new.calldate || ''',''' || new.clid || ''',''' || new.src ||  ''',''' || new.dst || ''',''' || new.dcontext || 
                                ''',''' || new.channel || ''',''' || new.dstchannel || ''',''' || new.lastapp || ''',''' || new.lastdata || ''',' ||
                                new.duration || ',' || new.billsec || ',''' || new.disposition || ''',' || new.amaflags || ',''' || new.accountcode ||
                                ''',''' || new.uniqueid || ''',''' || new.userfield || ''',''' || coalesce(new.srcip,'0.0.0.0') || ''',''' || coalesce(new.dstip, '0.0.0.0') || ''',''' || new.partition_string || ''');';
execute(_sql);
return null;

end;
$BODY$
  LANGUAGE 'plpgsql'