On Tue, Mar 23, 2010 at 4:23 AM, Sergio Ramazzina <sramazz...@gmail.com>wrote:
> 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. > Are you sure that you're using a BEFORE trigger? Can you send the actual trigger that calls the above function? --Scott M
