As Part 1 (Howto create PostgreSQL table partitioning) shows, making of PostgreSQL partitioning needs a lot of SQL commands. So this Part 2 explains how SQL commands for PostgreSQL partitioning can be made with a simple PHP script. This example script make SQL for child tables, indexes, trigger function and parent table trigger. This example script can make PostgreSQL table partitioning with using Date ranges. Script can be configured with following configuration section:

Initial parameters

Set the parent table name:


$parent_table_name = "impressions_by_day";

Set check field name (only one field can be used)

	
$check_field = "day";

Set the start date where partitioning starts

	
$initial_start_date = "2009-01-01";

Set child tables count and Set how many months data is stored one partition.
Following setup produce tables for four years:


$tables = 24;
$months_step = 2;

Example tables: 36 and months_step: 1 – means three year partitions
Example tables: 10 and months_step: 6 – means five year partitions

Following booleans configure what is created

	
$create_child_tables = true;
$create_indexes = true;
$create_trigger_function = true;
$create_trigger = true;

Example PHP script for ganerating PostgreSQL table partitioning


<?php

	// Set timezone
	date_default_timezone_set('UTC');

	// Initial parameters
	// Set the parent table name
	$parent_table_name = "impressions_by_day";
	// Set check field name (only one field can be used)
	$check_field = "day";
	// Set the start date where partitioning starts
	$initial_start_date = "2009-01-01";
	/* 
	*  Set child tables count and 
	*  Set how many months data is stored one partition.
	*  Following setup means tables for four years.
	*  tables: 36 and months_step: 1 - means three year partitions
	*/
	$tables = 36;
	$months_step = 1;
	// Following booleans configure what is created
	$create_child_tables = true;
	$create_indexes = true;
	$create_trigger_function = true;
	$create_trigger = true;

	// Empty fields for queries
	$child_table_queries = "";
	$index_queries = "";
	$trigger_function_query = "";
	$trigger_query = "";

	$start_date = $initial_start_date;

	for ($i = 1; $i <= $tables; $i++) {
		// Parse start date to array
		$start_date_array = date_parse($start_date);

		// Add one month to end date
		$end_date = date("Y-m-d", mktime(0, 0, 0, $start_date_array["month"]+$months_step, $start_date_array["day"], $start_date_array["year"]));

		// Generate new table name
		// 	y = start year
		//  m = start month
		// 	ms = months step
		// 	Example table name impressions_by_day_y2010m1ms6 means child table which starts at January 2010 and ends June 2010
		$new_table_name = $parent_table_name . "_y" . $start_date_array["year"] . "m" . $start_date_array["month"] . "ms" . $months_step;

		// Simply just add child tables CREATE TABLEs to $child_table_queries variable
		if ($create_child_tables) {
			$child_table_queries .= "CREATE TABLE " . $new_table_name . " (" . "\n";
			$child_table_queries .= "\t" . "PRIMARY KEY (advertiser_id, day), \n";
			$child_table_queries .= "\t" . "CHECK ( " . $check_field . " >= DATE '" . $start_date . "' AND " . $check_field . " < DATE '" . $end_date . "' )" . "\n";
			$child_table_queries .= ") INHERITS (" . $parent_table_name . ");" . "\n\n";
		}

		// Create Indexes
		if ($create_indexes) {
			$index_queries .= "CREATE INDEX " . $new_table_name . "_" . $check_field . " ON " . $new_table_name . " (" . $check_field . ");" . "\n";
		}

		// Create Trigger function (exact same rules as child tables)
		if ($create_trigger_function) {
			// Function start and first if condition
			if ($initial_start_date == $start_date) {
				$trigger_function_query .= "CREATE OR REPLACE FUNCTION " . $parent_table_name . "_insert_trigger()" . "\n";
				$trigger_function_query .= "RETURNS TRIGGER AS $$" . "\n";
				$trigger_function_query .= "BEGIN" . "\n";
				$trigger_function_query .= "\t" . "IF ( NEW." . $check_field . " >= DATE '" . $start_date . "' AND NEW." . $check_field . " < DATE '" . $end_date . "' ) THEN " . "\n";
						$trigger_function_query .= "\t\t" . "INSERT INTO " . $new_table_name . " VALUES (NEW.*); " . "\n";
			}
			else {
				$trigger_function_query .= "\t" . "ELSIF ( NEW." . $check_field . " >= DATE '" . $start_date . "' AND NEW." . $check_field . " < DATE '" . $end_date . "' ) THEN " . "\n";
				$trigger_function_query .= "\t\t" . "INSERT INTO " . $new_table_name . " VALUES (NEW.*);" . "\n";
			}

			// Else condition and Function end 
			if ($i == $tables) {
				$trigger_function_query .= "\t" . "ELSE" . "\n";
				$trigger_function_query .= "\t\t" . "RAISE EXCEPTION 'Date out of range.  Something wrong with the " . $parent_table_name . "_insert_trigger() function!';" . "\n";
				$trigger_function_query .= "\t" . "END IF;" . "\n";
				$trigger_function_query .= "\t" . "RETURN NULL;" . "\n";
				$trigger_function_query .= "END;" . "\n";
				$trigger_function_query .= "$$" . "\n";
				$trigger_function_query .= "LANGUAGE plpgsql;" . "\n";
			}
		}

		// Next start date is last end date
		$start_date = $end_date;
	}

	// Create Trigger to parent table
	if ($create_trigger) {
		$trigger_query .= "CREATE TRIGGER insert_" . $parent_table_name . "_trigger " . "\n";
		$trigger_query .= "\t" . "BEFORE INSERT ON " . $parent_table_name . " " . "\n";
		$trigger_query .= "\t" . "FOR EACH ROW EXECUTE PROCEDURE " . $parent_table_name . "_insert_trigger();" . "\n";
	}

	// Print all selected
	if ($create_child_tables) {
		echo $child_table_queries;
		echo "\n" . "-----------------------------------------------" . "\n\n";
	}
	if ($create_indexes) {
		echo $index_queries;
		echo "\n" . "-----------------------------------------------" . "\n\n";
	}
	if ($create_trigger_function) {
		echo $trigger_function_query;
		echo "\n" . "-----------------------------------------------" . "\n\n";
	}
	if ($create_trigger) {
		echo $trigger_query;
	}
?>

Output looks like following:


CREATE TABLE impressions_by_day_y2009m1ms3 (
	PRIMARY KEY (advertiser_id, day), 
	CHECK ( day >= DATE '2009-01-01' AND day < DATE '2009-04-01' )
) INHERITS (impressions_by_day);

CREATE TABLE impressions_by_day_y2009m4ms3 (
	PRIMARY KEY (advertiser_id, day), 
	CHECK ( day >= DATE '2009-04-01' AND day < DATE '2009-07-01' )
) INHERITS (impressions_by_day);

...

CREATE TABLE impressions_by_day_y2013m7ms3 (
	PRIMARY KEY (advertiser_id, day), 
	CHECK ( day >= DATE '2013-07-01' AND day < DATE '2013-10-01' )
) INHERITS (impressions_by_day);

CREATE TABLE impressions_by_day_y2013m10ms3 (
	PRIMARY KEY (advertiser_id, day), 
	CHECK ( day >= DATE '2013-10-01' AND day < DATE '2014-01-01' )
) INHERITS (impressions_by_day);


-----------------------------------------------

CREATE INDEX impressions_by_day_y2009m1ms3_day ON impressions_by_day_y2009m1ms3 (day);
CREATE INDEX impressions_by_day_y2009m4ms3_day ON impressions_by_day_y2009m4ms3 (day);
...
CREATE INDEX impressions_by_day_y2013m7ms3_day ON impressions_by_day_y2013m7ms3 (day);
CREATE INDEX impressions_by_day_y2013m10ms3_day ON impressions_by_day_y2013m10ms3 (day);

-----------------------------------------------

CREATE OR REPLACE FUNCTION impressions_by_day_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
	IF ( NEW.day >= DATE '2009-01-01' AND NEW.day < DATE '2009-04-01' ) THEN 
		INSERT INTO impressions_by_day_y2009m1ms3 VALUES (NEW.*); 
	ELSIF ( NEW.day >= DATE '2009-04-01' AND NEW.day < DATE '2009-07-01' ) THEN 
		INSERT INTO impressions_by_day_y2009m4ms3 VALUES (NEW.*);
        ...
	ELSIF ( NEW.day >= DATE '2013-07-01' AND NEW.day < DATE '2013-10-01' ) THEN 
		INSERT INTO impressions_by_day_y2013m7ms3 VALUES (NEW.*);
	ELSIF ( NEW.day >= DATE '2013-10-01' AND NEW.day < DATE '2014-01-01' ) THEN 
		INSERT INTO impressions_by_day_y2013m10ms3 VALUES (NEW.*);
	ELSE
		RAISE EXCEPTION 'Date out of range.  Something wrong with the impressions_by_day_insert_trigger() function!';
	END IF;
	RETURN NULL;
END;
$$
LANGUAGE plpgsql;

-----------------------------------------------

CREATE TRIGGER insert_impressions_by_day_trigger 
	BEFORE INSERT ON impressions_by_day 
	FOR EACH ROW EXECUTE PROCEDURE impressions_by_day_insert_trigger();

Real usage

1. Set desired configurations
2. Run PHP script and save output to file:


php create_child_tables.php > /tmp/postgresql_partitioning.sql

3. Add tables, indexes, trigger function and trigger to database with psql command:


\i /tmp/postgresql_partitioning.sql