#!/usr/bin/php
<?php
$trigger_statement = "CREATE OR REPLACE FUNCTION page_access_insert()
RETURNS TRIGGER AS $$
BEGIN
";

# Makes a trigger that includes all current partitions for page_access in it.  Could be big.
$conn = pg_connect("dbname=smarlowe");
# query to find all the tables in the partitions schema.
$query = "set search_path=partitions;
set datestyle='ISO,MDY';
SELECT 
    relname, 
    substring(relname from '[0-9]\+') as ymd, 
    substring(relname from '[0-9]\+')::date as today, 
    (substring(relname from '[0-9]\+')::date + interval '1 day')::date as tomorrow 
FROM pg_catalog.pg_class c 
LEFT JOIN pg_catalog.pg_namespace n 
ON n.oid = c.relnamespace 
WHERE pg_catalog.pg_table_is_visible(c.oid) and n.nspname='partitions' and relkind='r' and relname ilike 'page_access_%'
order by relname;";
$res = pg_query($query);
$firstpost = 0;
# Now we iterate the results and make the if / elsif parts of our trigger
while ($row = pg_fetch_assoc($res)){
	$ymd = $row['ymd'];
	$today = $row['today'];
	$tomorrow = $row['tomorrow'];
	if (!$firstpost){
		$trigger_statement.= "    IF ";
	} else {
		$trigger_statement.= "    ELSIF ";
	}
	$trigger_statement.= "( NEW.\"timestamp\" >= DATE '$today' AND NEW.\"timestamp\" < DATE '$tomorrow' ) THEN
        INSERT INTO partitions.page_access_$ymd VALUES (NEW.*);\n";
	$firstpost = 1;
} 
$trigger_statement.= "    ELSE
        RAISE EXCEPTION 'Date out of range.  Fix the page_access_insert_trigger() function';
    END IF;
    RETURN NULL;
END;
$$
LANGUAGE plpgsql;";
print "\n";
print $trigger_statement;
?>
Drop trigger if exists page_access_insert on page_access;
create trigger page_access_insert before insert on page_access for each row execute procedure page_access_insert();