postgresql分区表

需要设置constraint_exclusion = on。

CREATE TABLE t20110101
(
    CONSTRAINT timecheck CHECK (
    time >= '2011-01-01'::timestamp with time zone
    AND
    time < '2011-01-02'::timestamp with time zone)
)
INHERITS (t);

CREATE TRIGGER trig_t_insert
  BEFORE INSERT
  ON t
  FOR EACH ROW
  EXECUTE PROCEDURE t_insert();

CREATE OR REPLACE FUNCTION t_insert()
  RETURNS trigger AS
$BODY$
DECLARE
        t timestamp with time zone;
BEGIN
        select into t TIMESTAMP WITH TIME ZONE 'epoch' + (NEW.time_of_t::bigint/1000000) * INTERVAL '1 second';
        NEW.time := t;

        if t >= '2011-03-19 00:00:00+00' and t < '2011-03-20 00:00:00+00' then
                insert into t20110319 select NEW.*;
        elsif t >= '2011-03-20 00:00:00+00' and t < '2011-03-21 00:00:00+00' then
                insert into t20110320 select NEW.*;
        elsif t >= '2011-03-18 00:00:00+00' and t < '2011-03-19 00:00:00+00' then
                insert into t20110318 select NEW.*;
        else
                raise warning 'The time (%) is not accepted by subtable, storing in supertable',NEW.time_of_t;
                return NEW;
    end if;

    return NULL;
END
$BODY$
  LANGUAGE 'plpgsql';