Re: Before triggers and usage in partitioned tables - Mailing list pgsql-general

Hi everybody,

I'm new to postgresql and I need some help to understand the behaviour of before insert triggers in postgresql. I'm trying the sample
documented in the user manual about implementing table partitions (http://www.postgresql.org/docs/8.4/interactive/ddl-partitioning.html)
and I've a problem with my before insert trigger that I'm not able to understand.

I copied the trigger source down here for reference

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;

The strange thing is that each time I insert a new row in my measurement table (the master one) I get two rows inserted in the database one in the
master table (measurement) and one in the relative partition table. It seems that the RETURN NULL, that is needed to prevent the insertion in
the master table, isn't well understood by the rdbms. Is there anyone that can explain me the why of this behavior or what I'm doing wrong.

Thanks to everyone who helps me.