From: | Reid Thompson <reid(dot)thompson(at)ateb(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Partitioned table question |
Date: | 2009-09-24 18:02:59 |
Message-ID: | 1253815379.2708.26.camel@raker.ateb.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Assuming the examples on
http://www.postgresql.org/docs/8.3/static/ddl-partitioning.html
where measurement has children as noted....
CREATE TABLE measurement (
city_id int not null,
logdate date not null,
peaktemp int,
unitsales int
);
CREATE TABLE measurement_y2006m02 ( ) INHERITS (measurement);
CREATE TABLE measurement_y2006m03 ( ) INHERITS (measurement);
...
CREATE TABLE measurement_y2007m11 ( ) INHERITS (measurement);
CREATE TABLE measurement_y2007m12 ( ) INHERITS (measurement);
CREATE TABLE measurement_y2008m01 ( ) INHERITS (measurement);
and insertion is governed ala the trigger example
CREATE TRIGGER insert_measurement_trigger
BEFORE INSERT ON measurement
FOR EACH ROW EXECUTE PROCEDURE measurement_insert_trigger();
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;
If I know that no incoming data will be going into measurement_y2007m11
and I NO INHERIT measurement_y2007m11 from measurement, do I have to
immediately update the function measurement_insert_trigger() to remove
references to measurement_y2007m11, or will the function continue to
work fine and I can update it when convenient?
Thanks,
reid
From | Date | Subject | |
---|---|---|---|
Next Message | Alvaro Herrera | 2009-09-24 18:41:19 | Re: lazy vacuum and AccessExclusiveLock |
Previous Message | Richard Huxton | 2009-09-24 17:46:47 | Re: How to have ant's <sql> task insert special chars appropriately? |