Sergio Ramazzina wrote: > 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. That should not happen. Can you send the DDL used to create the tables and the CREATE TRIGGER statement? Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
