Re: [DOCS] Partitioning documentation example

[Bruce Momjian]

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  <[EMAIL PROTECTED]>http://momjian.us
  EnterpriseDB http://postgres.enterprisedb.com

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

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [DOCS] Partitioning documentation example

[Tom Lane]

Bruce Momjian <[EMAIL PROTECTED]> writes:
> 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:

This is not an improvement either.  You can't represent the check
constraints that way (at least not if you want the planner to do
constraint exclusion with them) and I don't think it's "less
error-prone" to have a different representation in the trigger than
you have in the constraints.

regards, tom lane

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [DOCS] Partitioning documentation example

[Bruce Momjian]

Tom Lane wrote:
> Bruce Momjian <[EMAIL PROTECTED]> writes:
> > 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:
> 
> This is not an improvement either.  You can't represent the check
> constraints that way (at least not if you want the planner to do
> constraint exclusion with them) and I don't think it's "less
> error-prone" to have a different representation in the trigger than
> you have in the constraints.

I see, so date_trunc() can't be used for constraint exclusion in the
CHECK constraint, and the trigger should match;  makes sense.

-- 
  Bruce Momjian  <[EMAIL PROTECTED]>http://momjian.us
  EnterpriseDB http://postgres.enterprisedb.com

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

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings