From: Bruce Momjian <bruce@momjian.us>
Message-Id: <200801051737.m05HbWC08088@momjian.us>
Subject: Re: Partitioning documentation example
In-Reply-To: 
To: PostgreSQL-documentation <pgsql-docs@postgresql.org>
Date: Sat, 5 Jan 2008 12:37:32 -0500 (EST)
CC: Simon Riggs <simon@2ndquadrant.com>
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Content-Type: text/plain; charset="US-ASCII"

bruce wrote:
> Simon, I was looking at the new table partitioning documentation that
> recommends triggers:
> 
> 	http://developer.postgresql.org/pgdocs/postgres/ddl-partitioning.html#DDL-PARTITIONING-IMPLEMENTATION
> 
> and came upon this trigger function example:
> 
> 	CREATE OR REPLACE FUNCTION measurement_insert_trigger()
> 	RETURNS TRIGGER AS $$
> 	BEGIN
> 	    IF ( NEW.logdate >= DATE '2006-02-01' AND NEW.logdate < DATE '2006-03-01' ) THEN
> 	        INSERT INTO measurement_y2006m02 VALUES (NEW.*);
> 	    ELSIF ( NEW.logdate >= DATE '2006-03-01' AND NEW.logdate < DATE '2006-04-01' ) THEN
> 	        INSERT INTO measurement_y2006m03 VALUES (NEW.*);
> 	    ...
> 	    ELSIF ( NEW.logdate >= DATE '2008-01-01' AND NEW.logdate < DATE '2008-02-01' ) THEN
> 	        INSERT INTO measurement_y2008m01 VALUES (NEW.*);
> 	    ELSE
> 	        RAISE EXCEPTION 'Date out of range.  Fix the measurement_insert_trigger() function!';
> 	    END IF;
> 	    RETURN NULL;
> 	END;
> 	$$
> 	LANGUAGE plpgsql;

Because my EXECUTE example didn't work I have created a new example
using date_trunc(), which I think is less error-prone than the
comparisons done in the original example:

	CREATE OR REPLACE FUNCTION measurement_insert_trigger()
	RETURNS TRIGGER AS $$
	BEGIN
	    IF date_trunc('month', NEW.logdate) = '2006-02-01' THEN
		INSERT INTO measurement_y2006m02 VALUES (NEW.*);
	    ELSIF  date_trunc('month', NEW.logdate) = '2006-03-01' THEN
		INSERT INTO measurement_y2006m03 VALUES (NEW.*);
	    ...
	    ELSIF  date_trunc('month', NEW.logdate) = '2008-01-01' THEN
		INSERT INTO measurement_y2008m01 VALUES (NEW.*);
	    ELSE
		RAISE EXCEPTION 'Date out of range.  Fix the measurement_insert_trigger() function!';
	    END IF;
	    RETURN NULL;
	END;
	$$
	LANGUAGE plpgsql;

-- 
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://postgres.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +