From: Bruce Momjian <bruce@momjian.us>
Message-Id: <200801041527.m04FR7L19863@momjian.us>
Subject: Partitioning documentation example
To: Simon Riggs <simon@2ndquadrant.com>
Date: Fri, 4 Jan 2008 10:27:07 -0500 (EST)
CC: PostgreSQL-documentation <pgsql-docs@postgresql.org>
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Content-Type: text/plain; charset="US-ASCII"

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;

It seems to me it would be much clearer if we added a second example
that used to_char() to create the INSERT statement dynamically based on
NEW.logdate:

	CREATE OR REPLACE FUNCTION measurement_insert_trigger() 
	RETURNS TRIGGER AS $$
	BEGIN
	   EXECUTE 'INSERT INTO measurement_y' || to_char(NEW.logdate,'YYYY_mMM') || ' VALUES (NEW.*)';
	END;
	$$
	LANGUAGE plpgsql;

It will of course fail if the table does not exist, which I think is
what we want.  This trigger function would not have to be modified when
new tables are added.

-- 
  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. +