/*

part_functions.sql 

- A set of functions aiding in creating partitioned tables with PostgreSQL 8.1+

@author <a href="mailto:myfirstname.mylastname@wirelesscar.com">Mikael Carneholm</a>

This file is public domain, feel free to redistribute!

DISCLAIMER: UNDERSTAND WHAT YOU ARE DOING AND USE THE FUNCTIONS WITH CARE, 
THE AUTHOR DOES NOT TAKE ANY RESPONSIBLITY FOR DATA LOSS DUE TO MISUSE

Contents: 
---------
partition_add(tblname text, part_suffix text, condition text, tbspc text)
partition_delete(tblname text, part_suffix text)
range_partition_year(tblname text, part_col text, the_year int, tbspc text)
range_partition_years_between(tblname text, part_col text, start_year int, end_year int, tbspc text)
range_partition_years_between_ba(tblname text, part_col text, start_year int, end_year int, tbspc text)
partition_index(tblname text, part_col text, tbspc text)
range_partition_index_year(tblname text, part_col text, the_year int, tbspc text)
range_partition_index_years_between(tblname text, part_col text, start_year int, end_year int, tbspc text)
range_partition_index_years_between_ba(tblname text, part_col text, start_year int, end_year int, tbspc text)
partition_truncate_all(parent text)
partition_drop_all(parent text)


Steps needed to create 12 indexed subpartitions per year for table 'orders', for years 2001 - 2005
+ 2 indexed partitions holding data before 2001-01-01 00:00:00 and after 2005-12-31 23:59:59

1. CREATE TABLE orders (
	order_id BIGSERIAL PRIMARY KEY,
	customer_id INTEGER NOT NULL,
	product_id INTEGER NOT NULL,
	order_date DATE NOT NULL
);

2. select range_partition_years_between_ba('orders', 'order_date', 2001, 2005, 'tbspc1');
3. select range_partition_index_years_between_ba('orders', 'order_date', 2001, 2005, 'tbspc2');
*/

/*-----------------------------------------------------------------------------------------------------------------
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'' ', 'tbspc1');
   => created table: orders_0501 (on tablespace tbspc1)
   => created or updated function: orders_update_func
   => created or updated rules:  orders_update_rule, orders_0501_insert_rule

select partition_add( 'customers', 'uscanada', 'country in (''us'',''canada'') ', null);
   => created table: customers_uscanada (on default tablespace)
   => created or updated function: customers_update_func
   => created or updated rules: customers_update_rule, customers_uscanada_insert_rule

Note: strings passed as the condition param need to be escaped (see above example)
------------------------------------------------------------------------------------------------------------------*/
create or replace function partition_add(tblname text, part_suffix text, condition text, tbspc text) returns text as $$
declare
	table_to_partition pg_class.relname%TYPE := null;
	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

	-- check that table exists
	select pgc.relname into table_to_partition from pg_class pgc where pgc.relname = tblname;
	if table_to_partition is null then
		raise exception 'table % does not exist', tblname;
	end if;

	-- check presence of PK
	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));

	if pk_name is null then
		raise exception 'table % has no primary key', tblname;
	end if;

	-- ok, proceed
	name_base := tblname || '_' || part_suffix;

	-- create column lists for rules & functions
	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;

	-- chop off last ',' from column lists
	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 command string
	create_table_cmd := 'create table ' || name_base ||
	 ' ( ' ||
	 'constraint ' || name_base || '_insert_check check ('|| condition ||')' ||
	 ' ) ' ||
	 'inherits (' || tblname || ')';

	if tbspc is not null then
		create_table_cmd := create_table_cmd||' tablespace '||tbspc;
	end if; 

	create_table_cmd := create_table_cmd||';';
         
	-- insert rule command string
	create_insert_rule_cmd := 'create or replace rule '|| name_base ||'_insert_rule as '||
	 'on insert to '|| tblname ||' where ('|| condition ||') '||
	 'do instead insert into '|| name_base ||'('||table_columns||') values ('|| table_columns_new ||');' ;
	
	-- update function command string
	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;';

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

	raise notice 'Creating table %', name_base;
	execute create_table_cmd;	
	raise notice 'Creating rule %', name_base ||'_insert_rule';
	execute create_insert_rule_cmd;
	raise notice 'Creating/replacing function %', tblname ||'_update_func';
	execute create_update_func_cmd;
	raise notice 'Creating/replacing rule %', tblname ||'_update_rule';
	execute create_update_rule_cmd;

	return 'PARTITION';

end;
$$
language plpgsql;


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

Example usage:
select partition_delete('mytable', '0501');
   => dropped table: mytable_0501
   => dropped rules: mytable_0501_insert_rule
------------------------------------------------------------------------------------------------------------------*/
create or replace function partition_delete(tblname text, part_suffix text) returns text as $$
begin
	raise notice 'Dropping rule %', tblname ||'_'|| part_suffix ||'_insert_rule';
	execute 'drop rule '|| tblname ||'_'|| part_suffix ||'_insert_rule on '|| tblname;
	raise notice 'Dropping table %', tblname ||'_'|| part_suffix;
	execute 'drop table '|| tblname ||'_'|| part_suffix;

	return 'DROP';

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, 'tbspc1');
   => created tables: mytable_200501, mytable_200502, mytable_200503 etc (on tablespace tbspc1)
   => created or updated function: mytable_update_func
   => created or updates rules:  mytable_update_rule, mytable_200501_insert_rule, mytable_200502_insert_rule, etc
select range_partition_year('mytable', 'datecol', 2005, null);
   => same as above, but the tables will be created on default tablespace
------------------------------------------------------------------------------------------------------------------*/
create or replace function range_partition_year(tblname text, part_col text, the_year int, tbspc text) returns text 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''', tbspc);
	perform partition_add(tblname, c_year||'02', part_col||' >= '''||c_year||'0201'' and '|| part_col ||' < '''||c_year||'0301''', tbspc);
	perform partition_add(tblname, c_year||'03', part_col||' >= '''||c_year||'0301'' and '|| part_col ||' < '''||c_year||'0401''', tbspc);
	perform partition_add(tblname, c_year||'04', part_col||' >= '''||c_year||'0401'' and '|| part_col ||' < '''||c_year||'0501''', tbspc);
	perform partition_add(tblname, c_year||'05', part_col||' >= '''||c_year||'0501'' and '|| part_col ||' < '''||c_year||'0601''', tbspc);
	perform partition_add(tblname, c_year||'06', part_col||' >= '''||c_year||'0601'' and '|| part_col ||' < '''||c_year||'0701''', tbspc);
	perform partition_add(tblname, c_year||'07', part_col||' >= '''||c_year||'0701'' and '|| part_col ||' < '''||c_year||'0801''', tbspc);
	perform partition_add(tblname, c_year||'08', part_col||' >= '''||c_year||'0801'' and '|| part_col ||' < '''||c_year||'0901''', tbspc);
	perform partition_add(tblname, c_year||'09', part_col||' >= '''||c_year||'0901'' and '|| part_col ||' < '''||c_year||'1001''', tbspc);
	perform partition_add(tblname, c_year||'10', part_col||' >= '''||c_year||'1001'' and '|| part_col ||' < '''||c_year||'1101''', tbspc);
	perform partition_add(tblname, c_year||'11', part_col||' >= '''||c_year||'1101'' and '|| part_col ||' < '''||c_year||'1201''', tbspc);
	perform partition_add(tblname, c_year||'12', part_col||' >= '''||c_year||'1201'' and '|| part_col ||' < '''||to_char(the_year+1, '9999')||'0101''', tbspc);

	return 'PARTITION';

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, 'tbspc1');
   => created tables: mytable_2001*, mytable_2002*, mytable_2003* (on tablespace tbspc1)
   => created or updated function: mytable_update_func
   => created or updated rules: mytable_update, mytable_2001??_insert_rule, mytable_2002??_insert_rule, etc
select range_partition_years_between('mytable', 'datecol', 2001, 2003, null);
   => same as above, but the tables will be created on default tablespace
------------------------------------------------------------------------------------------------------------------*/
create or replace function range_partition_years_between(tblname text, part_col text, start_year int, end_year int, tbspc text) returns text as $$
begin
	for i in start_year..end_year loop
		perform range_partition_year(tblname, part_col, i, tbspc);
	end loop;
	
	return 'PARTITION';

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, 'tbspc1');
   => created tables: mytable_before2001, mytable_2001*, mytable_2002*, mytable_2003*, mytable_after2003 (on tablespace tbspc1)
   => created or updated function: mytable_update_func
   => created or updated rules:  mytable_update,
				 mytable_before2001, 
				 mytable_2001??_insert_rule, 
				 mytable_2002??_insert_rule, 
				 mytable_2003??_insert_rule, 
				 mytable_after2003_insert_rule
select range_partition_years_between_ba('mytable', 'datecol', 2001, 2003, null); 
   => same as above, but the tables will be created on default tablespace
------------------------------------------------------------------------------------------------------------------*/
create or replace function range_partition_years_between_ba(tblname text, part_col text, start_year int, end_year int, tbspc text) returns text 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''', tbspc);
	for i in start_year..end_year loop
		perform range_partition_year(tblname, part_col, i, tbspc);
	end loop;
	perform partition_add(tblname, 'after'||c_end_year, part_col||' >= '''||to_char(end_year+1, '9999')||'0101''', tbspc);

	return 'PARTITION';

end;
$$
language plpgsql;

/*-----------------------------------------------------------------------------------------------------------------
Utility function that creates an index on column part_col for table tblname

Example usage:
select partition_index('orders_200501', 'order_date', 'tbspc1');
   => created index: idx_orders_200501_order_date
select partition_index('orders_200501', 'order_date', null);
   => same as above, but the index will be created on default tablespace
------------------------------------------------------------------------------------------------------------------*/
create or replace function partition_index(tblname text, part_col text, tbspc text) returns text as $$
declare
	create_index_cmd text := '';
begin
	create_index_cmd := 'create index idx_'||tblname||'_'||part_col||' on '||tblname||'('||part_col||')';
	if tbspc is not null then
		create_index_cmd := create_index_cmd||' tablespace '||tbspc;
	end if;
	create_index_cmd := create_index_cmd||';';

	raise notice 'Creating index %', tblname||'_'||part_col;
	execute create_index_cmd;

	return 'CREATE';

end;
$$
language plpgsql;

/*-----------------------------------------------------------------------------------------------------------------
Utility function that creates an index on column part_col for the 12 month partitions on table tblname for one year

Example usage:
select range_partition_index_year('orders', 'order_date', 2005, 'tbspc1');
   => created indexes: idx_orders_200501_order_date .. idx_orders_200512_order_date
select range_partition_index_year('orders', 'order_date', 2005, null);
   => same as above, but the indexes will be created on default tablespace
------------------------------------------------------------------------------------------------------------------*/
create or replace function range_partition_index_year(tblname text, part_col text, the_year int, tbspc text) returns text as $$
begin
	for i in 1..12 loop
		perform partition_index(tblname||'_'||the_year::text||lpad(i::text, 2, '0'), part_col, tbspc);
	end loop;

	return 'CREATE';

end;
$$
language plpgsql;

/*-----------------------------------------------------------------------------------------------------------------
Utility function that creates an index on column part_col for the 12 month partitions on table tblname 
for years between start_year and end_year

Example usage:
select range_partition_index_years_between('orders', 'order_date', 2003, 2005, 'tbspc1');
   => created indexes: idx_orders_2003??_order_date .. idx_orders_2005??_order_date
select range_partition_index_years_between('orders', 'order_date', 2003, 2005, null);
   => same as above, but the indexes will be created on default tablespace
------------------------------------------------------------------------------------------------------------------*/
create or replace function range_partition_index_years_between(tblname text, part_col text, start_year int, end_year int, tbspc text) returns text as $$
begin
	for i in start_year..end_year loop
		perform range_partition_index_year(tblname, part_col, i, tbspc);		
	end loop;

	return 'CREATE';

end;
$$
language plpgsql;

/*-----------------------------------------------------------------------------------------------------------------
Utility function that creates an index on column part_col for the 12 month partitions on table tblname 
for years between start_year and end_year, creating ((end_year-start_year)+3)*12 indexes
(ba = "before" and "after" partitions)

Example usage:
select range_partition_index_years_between_ba('orders', 'order_date', 2003, 2005, 'tbspc1');
   => created indexes:  idx_orders_before2003_order_date, 
			idx_orders_2003??_order_date,
			idx_orders_2004??_order_date,
			idx_orders_2005??_order_date,
			idx_orders_after2005_order_date
select range_partition_index_years_between_ba('orders', 'order_date', 2003, 2005, null);
   => same as above, but the indexes will be created on default tablespace
------------------------------------------------------------------------------------------------------------------*/
create or replace function range_partition_index_years_between_ba(tblname text, part_col text, start_year int, end_year int, tbspc text) returns text as $$
begin
	perform partition_index(tblname||'_before'||start_year::text, part_col, tbspc);
	for i in start_year..end_year loop
		perform range_partition_index_year(tblname, part_col, i, tbspc);		
	end loop;
	perform partition_index(tblname||'_after'||end_year::text, part_col, tbspc);

	return 'CREATE';

end;
$$
language plpgsql;

/*-----------------------------------------------------------------------------------------------------------------
Utility function that truncates a table along with all it's children (partitions)

Example usage:
select partition_truncate_all('mytable')
	=> truncated tables: mytable*
------------------------------------------------------------------------------------------------------------------*/
create or replace function partition_truncate_all(parent text) returns text as $$
declare
	tblname record;
begin
	for tblname in execute 'select tablename from pg_tables where tableowner = '''|| user ||''' and tablename like '''||parent||'%''' loop
		execute 'truncate '||tblname.tablename::text;
	end loop;

	return 'TRUNCATE';
end;
$$
language plpgsql;

/*-----------------------------------------------------------------------------------------------------------------
Utility function that drops a table along with all it's children (partitions)

Example usage:
select partition_drop_all('mytable')
	=> dropped tables: mytable*
------------------------------------------------------------------------------------------------------------------*/
create or replace function partition_drop_all(parent text) returns text as $$
declare
	tblname record;
begin
	for tblname in execute 'select distinct tablename from pg_tables where tableowner = '''|| user ||''' and tablename like '''||parent||'_%'' order by tablename desc' loop
		execute 'drop table '||tblname.tablename::text||' cascade';
	end loop;
	
	return 'DROP';

end;
$$
language plpgsql;

-- TODO: range_partition_analyze*