/*-----------------------------------------------------------------------------------------------------------------
Utility function that creates a range/list partition with accompanying insert & update rules 
(+supporting update function) for a table

Example usage:
select partition_add( 'orders', '0501', 'order_date >= ''20050101'' and order_date < ''20050201'' ');
   => created table: orders_0501
   => created or updated function: orders_update_func
   => created or updated rules:  orders_update, orders_insert_0501

select partition_add( 'customers', 'uscanada', 'country in (''us'',''canada'') ');
   => created table: customers_uscanada
   => created or updated function: customers_update_func
   => created or updated rules: customers_update, customers_insert_uscanada

Note: strings passed as the condition param need to be escaped (see above example)

Author: Mikael Carneholm lowercase(firstname.lastname)@wirelesscar.com
------------------------------------------------------------------------------------------------------------------*/
create or replace function partition_add(tblname text, part_suffix text, condition text) returns void as $$
declare
	name_base text := '';
	table_columns text := '';
	table_columns_new text := '';
	oldrow_string text := '';
	column_name record;
	pk_name pg_attribute.attname%TYPE;
	create_table_cmd text := '';
	create_insert_rule_cmd text := '';
	create_update_func_cmd text := '';
	create_update_rule_cmd text := '';
begin
	name_base := tblname || '_' || part_suffix;

	for column_name in execute 'select pga.attname from pg_attribute pga, pg_class pgc where pga.attrelid = pgc.relfilenode and pgc.relname = '''|| tblname || ''' and pga.attnum > 0' loop	
		table_columns := table_columns || column_name.attname || ',';
		table_columns_new := table_columns_new ||'NEW.'|| column_name.attname || ',';
		oldrow_string := oldrow_string ||'oldrow.'|| column_name.attname || ',';
	end loop;

	table_columns := substr(table_columns,1,length(table_columns)-1);
	table_columns_new := substr(table_columns_new,1,length(table_columns_new)-1);
	oldrow_string := substr(oldrow_string,1,length(oldrow_string)-1);

	create_table_cmd := 'create table ' || name_base ||
	 ' ( ' ||
	 'constraint ' || name_base || '_insert_check check ('|| condition ||')' ||
	 ' ) ' ||
	 'inherits (' || tblname || ');' ;
         
	create_insert_rule_cmd := 'create rule '|| tblname ||'_insert_'|| part_suffix ||' as '||
	 'on insert to '|| tblname ||' where ('|| condition ||') '||
	 'do instead insert into '|| name_base ||'('||table_columns||') values ('|| table_columns_new ||');' ;

	select a.attname::text into pk_name
	from pg_class c 
	join pg_constraint o
		on ( c.relname = tblname and c.oid = o.conrelid and o.contype='p')
	join pg_attribute a 
		on (a.attrelid = c.oid and a.attnum = any (o.conkey));
	
	create_update_func_cmd := 'create or replace function '||tblname||'_update_func(oldrow '||tblname||') returns void as '''||
	  'begin '||
	  '  delete from '||tblname||' where '||pk_name||'=oldrow.'||pk_name||'; '||
	  '  insert into '||tblname||' ('||table_columns||') values ('||oldrow_string||'); '||
	  'end; '''||
	  'language plpgsql;';

	create_update_rule_cmd := 'create or replace rule '||tblname||'_update as '||
	  'on update to '||tblname||' '||
	  'do instead select '||tblname||'_update_func(NEW);';

	execute create_table_cmd;	
	execute create_insert_rule_cmd;	     
	execute create_update_func_cmd;
	execute create_update_rule_cmd;

end;
$$
language plpgsql;

/*-----------------------------------------------------------------------------------------------------------------
Utility function that drops a partition with along its accompanying insert rule

Example usage:
select range_partition_delete('mytable', '0501');
   => dropped table: mytable_0501
   => dropped rules: mytable_insert_0501

Author: Mikael Carneholm lowercase(firstname.lastname)@wirelesscar.com
------------------------------------------------------------------------------------------------------------------*/
create or replace function partition_delete(tblname text, part_suffix text) returns void as $$
begin
	execute 'drop rule '|| tblname ||'_insert_'|| part_suffix ||' on '|| tblname;
	execute 'drop table '|| tblname || '_' || part_suffix;
end;
$$
language plpgsql;

/*-----------------------------------------------------------------------------------------------------------------
Utility function that creates 12 month partitions on table tblname on column part_col, for year the_year 

Example usage:
select range_partition_year('mytable', 'datecol', 2005);
   => created tables: mytable_200501, mytable_200502, mytable_200503 etc 
   => created or updated function: mytable_update_func
   => created or updates rules:  mytable_update, mytable_insert_200501, mytable_insert_200502, etc

Author: Mikael Carneholm lowercase(firstname.lastname)@wirelesscar.com
------------------------------------------------------------------------------------------------------------------*/
create or replace function range_partition_year(tblname text, part_col text, the_year int) returns void as $$
declare
	c_year text := trim(to_char(the_year, '9999'));	
begin
	perform partition_add(tblname, c_year||'01', part_col||' >= '''||c_year||'0101'' and '|| part_col ||' < '''||c_year||'0201''');
	perform partition_add(tblname, c_year||'02', part_col||' >= '''||c_year||'0201'' and '|| part_col ||' < '''||c_year||'0301''');
	perform partition_add(tblname, c_year||'03', part_col||' >= '''||c_year||'0301'' and '|| part_col ||' < '''||c_year||'0401''');
	perform partition_add(tblname, c_year||'04', part_col||' >= '''||c_year||'0401'' and '|| part_col ||' < '''||c_year||'0501''');
	perform partition_add(tblname, c_year||'05', part_col||' >= '''||c_year||'0501'' and '|| part_col ||' < '''||c_year||'0601''');
	perform partition_add(tblname, c_year||'06', part_col||' >= '''||c_year||'0601'' and '|| part_col ||' < '''||c_year||'0701''');
	perform partition_add(tblname, c_year||'07', part_col||' >= '''||c_year||'0701'' and '|| part_col ||' < '''||c_year||'0801''');
	perform partition_add(tblname, c_year||'08', part_col||' >= '''||c_year||'0801'' and '|| part_col ||' < '''||c_year||'0901''');
	perform partition_add(tblname, c_year||'09', part_col||' >= '''||c_year||'0901'' and '|| part_col ||' < '''||c_year||'1001''');
	perform partition_add(tblname, c_year||'10', part_col||' >= '''||c_year||'1001'' and '|| part_col ||' < '''||c_year||'1101''');
	perform partition_add(tblname, c_year||'11', part_col||' >= '''||c_year||'1101'' and '|| part_col ||' < '''||c_year||'1201''');
	perform partition_add(tblname, c_year||'12', part_col||' >= '''||c_year||'1201'' and '|| part_col ||' < '''||to_char(the_year+1, '9999')||'0101''');
end;
$$
language plpgsql;

/*-----------------------------------------------------------------------------------------------------------------
Utility function that range partitions a table on column part_col between start_year and end_year,
creating ((end_year-start_year)+1)*12 partitions

Example usage:
select range_partition_years_between('mytable', 'datecol', 2001, 2003);
   => created tables: mytable_2001*, mytable_2002*, mytable_2003*
   => created or updated function: mytable_update_func
   => created or updated rules: mytable_update, mytable_insert_2001*, mytable_insert_2002, mytable_insert_2003*

Author: Mikael Carneholm lowercase(firstname.lastname)@wirelesscar.com
------------------------------------------------------------------------------------------------------------------*/
create or replace function range_partition_years_between(tblname text, part_col text, start_year int, end_year int) returns void as $$
declare
	c_start_year text := trim(to_char(start_year, '9999'));
	c_end_year text := trim(to_char(end_year, '9999'));
begin
	for i in start_year..end_year loop
		perform range_partition_year(tblname, part_col, i);
	end loop;
end;
$$
language plpgsql;

/*-----------------------------------------------------------------------------------------------------------------
Utility function that range partitions a table on column part_col between start_year and end_year,
creating ((end_year-start_year)+3)*12 partitions (ba = "before" and "after" partitions)

Example usage:
select range_partition_years_between_ba('mytable', 'datecol', 2001, 2003);
   => created tables: mytable_before2001, mytable_2001*, mytable_2002*, mytable_2003*, mytable_after2003
   => created or updated function: mytable_update_func
   => created or updated rules: mytable_update, mytable_insert_before2001, mytable_insert_2001*, mytable_insert_2002, mytable_insert_2003*, mytable_insert_after2003

Author: Mikael Carneholm lowercase(firstname.lastname)@wirelesscar.com
------------------------------------------------------------------------------------------------------------------*/
create or replace function range_partition_years_between_ba(tblname text, part_col text, start_year int, end_year int) returns void as $$
declare
	c_start_year text := trim(to_char(start_year, '9999'));
	c_end_year text := trim(to_char(end_year, '9999'));
begin
	perform partition_add(tblname, 'before'||c_start_year, part_col||' < '''||start_year||'0101''');
	for i in start_year..end_year loop
		perform range_partition_year(tblname, part_col, i);
	end loop;
	perform partition_add(tblname, 'after'||c_end_year, part_col||' >= '''||to_char(end_year+1, '9999')||'0101''');
end;
$$
language plpgsql;
