Re: Before triggers and usage in partitioned tables

Hi Scott,

here's the trigger and the function

CREATE OR REPLACE FUNCTION tpm_wind_dcn_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
IF ( NEW.data >= DATE '2010-01-01' AND NEW.data < DATE '2010-01-02' )
THEN
INSERT INTO tp_wind_dcn_day1 VALUES (NEW.*);
ELSIF ( NEW.data >= DATE '2010-01-02' AND NEW.data < DATE '2010-01-03' )
THEN
INSERT INTO tp_wind_dcn_day2 VALUES (NEW.*);
ELSIF ( NEW.data >= DATE '2010-01-03' AND NEW.data < DATE '2010-01-04' )
THEN
INSERT INTO tp_wind_dcn_day3 VALUES (NEW.*);
ELSIF ( NEW.data >= DATE '2010-01-04' AND NEW.data < DATE '2010-01-05' )
THEN
INSERT INTO tp_wind_dcn_day4 VALUES (NEW.*);
ELSIF ( NEW.data >= DATE '2010-01-05' AND NEW.data < DATE '2010-01-06' )
THEN
INSERT INTO tp_wind_dcn_day5 VALUES (NEW.*);
ELSIF ( NEW.data >= DATE '2010-01-06' AND NEW.data < DATE '2010-01-07' )
THEN
INSERT INTO tp_wind_dcn_day6 VALUES (NEW.*);
ELSIF ( NEW.data >= DATE '2010-01-07' AND NEW.data < DATE '2010-01-08' )
THEN
INSERT INTO tp_wind_dcn_day7 VALUES (NEW.*);
ELSIF ( NEW.data >= DATE '2010-01-08' AND NEW.data < DATE '2010-01-09' )
THEN
INSERT INTO tp_wind_dcn_day8 VALUES (NEW.*);
ELSIF ( NEW.data >= DATE '2010-01-09' AND NEW.data < DATE '2010-01-10' )
THEN
INSERT INTO tp_wind_dcn_day9 VALUES (NEW.*);
ELSIF ( NEW.data >= DATE '2010-01-10' AND NEW.data < DATE '2010-01-11' )
THEN
INSERT INTO tp_wind_dcn_day10 VALUES (NEW.*);
ELSIF ( NEW.data >= DATE '2010-01-11' AND NEW.data < DATE '2010-01-12' )
THEN
INSERT INTO tp_wind_dcn_day11 VALUES (NEW.*);
ELSIF ( NEW.data >= DATE '2010-01-12' AND NEW.data < DATE '2010-01-13' )
THEN
INSERT INTO tp_wind_dcn_day12 VALUES (NEW.*);
ELSIF ( NEW.data >= DATE '2010-01-13' AND NEW.data < DATE '2010-01-14' )
THEN
INSERT INTO tp_wind_dcn_day13 VALUES (NEW.*);
ELSIF ( NEW.data >= DATE '2010-01-14' AND NEW.data < DATE '2010-01-15' )
THEN
INSERT INTO tp_wind_dcn_day14 VALUES (NEW.*);
ELSIF ( NEW.data >= DATE '2010-01-15' AND NEW.data < DATE '2010-01-16' )
THEN
INSERT INTO tp_wind_dcn_day15 VALUES (NEW.*);
ELSIF ( NEW.data >= DATE '2010-01-16' AND NEW.data < DATE '2010-01-17' )
THEN
INSERT INTO tp_wind_dcn_day16 VALUES (NEW.*);
ELSIF ( NEW.data >= DATE '2010-01-17' AND NEW.data < DATE '2010-01-18' )
THEN
INSERT INTO tp_wind_dcn_day17 VALUES (NEW.*);
ELSIF ( NEW.data >= DATE '2010-01-18' AND NEW.data < DATE '2010-01-19' )
THEN
INSERT INTO tp_wind_dcn_day18 VALUES (NEW.*);
ELSIF ( NEW.data >= DATE '2010-01-19' AND NEW.data < DATE '2010-01-20' )
THEN
INSERT INTO tp_wind_dcn_day19 VALUES (NEW.*);
ELSIF ( NEW.data >= DATE '2010-01-20' AND NEW.data < DATE '2010-01-21' )
THEN
INSERT INTO tp_wind_dcn_day20 VALUES (NEW.*);
ELSIF ( NEW.data >= DATE '2010-01-21' AND NEW.data < DATE '2010-01-22' )
THEN
INSERT INTO tp_wind_dcn_day21 VALUES (NEW.*);
ELSIF ( NEW.data >= DATE '2010-01-22' AND NEW.data < DATE '2010-01-23' )
THEN
INSERT INTO tp_wind_dcn_day22 VALUES (NEW.*);
ELSIF ( NEW.data >= DATE '2010-01-23' AND NEW.data < DATE '2010-01-24' )
THEN
INSERT INTO tp_wind_dcn_day23 VALUES (NEW.*);
ELSIF ( NEW.data >= DATE '2010-01-24' AND NEW.data < DATE '2010-01-25' )
THEN
INSERT INTO tp_wind_dcn_day24 VALUES (NEW.*);
ELSIF ( NEW.data >= DATE '2010-01-25' AND NEW.data < DATE '2010-01-26' )
THEN
INSERT INTO tp_wind_dcn_day25 VALUES (NEW.*);
ELSIF ( NEW.data >= DATE '2010-01-26' AND NEW.data < DATE '2010-01-27' )
THEN
INSERT INTO tp_wind_dcn_day26 VALUES (NEW.*);
ELSIF ( NEW.data >= DATE '2010-01-27' AND NEW.data < DATE '2010-01-28' )
THEN
INSERT INTO tp_wind_dcn_day27 VALUES (NEW.*);
ELSIF ( NEW.data >= DATE '2010-01-28' AND NEW.data < DATE '2010-01-29' )
THEN
INSERT INTO tp_wind_dcn_day28 VALUES (NEW.*);
ELSIF ( NEW.data >= DATE '2010-01-29' AND NEW.data < DATE '2010-01-30' )
THEN
INSERT INTO tp_wind_dcn_day29 VALUES (NEW.*);
ELSIF ( NEW.data >= DATE '2010-01-30' AND NEW.data < DATE '2010-01-31' )
THEN
INSERT INTO tp_wind_dcn_day30 VALUES (NEW.*);
ELSIF ( NEW.data >= DATE '2010-01-31' AND NEW.data < DATE '2010-02-01' )
THEN
INSERT INTO tp_wind_dcn_day31 VALUES (NEW.*);
ELSE
RAISE EXCEPTION 'Date out of range. Fix the
tpm_wind_dcn_insert_trigger() function!';
END IF;
RETURN NULL;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER insert_tpm_wind_dcn_trigger
BEFORE INSERT ON tpm_wind_dcn
FOR EACH ROW EXECUTE PROCEDURE tpm_wind_dcn_insert_trigger();

Thanks in advance for your help.

Regards

Sergio

2010/3/23 Scott Mead <scott(dot)lists(at)enterprisedb(dot)com>

> On Tue, Mar 23, 2010 at 4:23 AM, Sergio Ramazzina <sramazzina(at)gmail(dot)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
>