Postgres Partition Table Trigger inserting duplicate records

Question

I have a trigger applied on my database table. But when i insert data into it via hibernate jpa it creates duplicate rows in master table. Here is the trigger

CREATE OR REPLACE FUNCTION SMS_RECEIPT_func_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
    IF ( NEW.timedate >=  '2015-01-01' AND NEW.timedate <  '2015-01-31' ) THEN
        INSERT INTO SMS_RECEIPT_y2015m01 VALUES (NEW.*);

    ELSIF ( NEW.timedate >=  '2015-02-01' AND NEW.timedate <  '2015-02-28' ) THEN
        INSERT INTO SMS_RECEIPT_y2015m02 VALUES (NEW.*);
    ELSIF ( NEW.timedate >=  '2015-03-01' AND NEW.timedate <  '2015-03-31' ) THEN
        INSERT INTO SMS_RECEIPT_y2015m03 VALUES (NEW.*);
    ELSIF ( NEW.timedate >=  '2015-04-01' AND NEW.timedate <  '2015-04-30' ) THEN
        INSERT INTO SMS_RECEIPT_y2015m04 VALUES (NEW.*);
    ELSIF ( NEW.timedate >=  '2015-05-01' AND NEW.timedate <  '2015-05-31' ) THEN
        INSERT INTO SMS_RECEIPT_y2015m05 VALUES (NEW.*);
    ELSIF ( NEW.timedate >=  '2015-06-01' AND NEW.timedate <  '2015-06-30' ) THEN
  INSERT INTO SMS_RECEIPT_y2015m06 VALUES (NEW.*);
    ELSIF ( NEW.timedate >=  '2015-07-01' AND NEW.timedate <  '2015-07-31' ) THEN
  INSERT INTO SMS_RECEIPT_y2015m07 VALUES (NEW.*);
    ELSIF ( NEW.timedate >=  '2015-08-01' AND NEW.timedate <  '2015-08-31' ) THEN
  INSERT INTO SMS_RECEIPT_y2015m08 VALUES (NEW.*);
    ELSIF ( NEW.timedate >=  '2015-09-01' AND NEW.timedate <  '2015-09-30' ) THEN
  INSERT INTO SMS_RECEIPT_y2015m09 VALUES (NEW.*);
    ELSIF ( NEW.timedate >=  '2015-10-01' AND NEW.timedate <  '2015-10-31' ) THEN
  INSERT INTO SMS_RECEIPT_y2015m010 VALUES (NEW.*);
    ELSE
        RAISE EXCEPTION 'Date out of range.  Fix the measurement_insert_trigger() function!';
    END IF;
    RETURN NULL;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER trigger_SMS_RECEIPT_insert
    AFTER INSERT ON "SMS_RECEIPT"
    FOR EACH ROW EXECUTE PROCEDURE SMS_RECEIPT_func_insert_trigger();

If use Before in place of AFTER it gives the following error in java

  CREATE TRIGGER trigger_SMS_RECEIPT_insert
  BEFORE INSERT ON "SMS_RECEIPT"
  FOR EACH ROW EXECUTE PROCEDURE SMS_RECEIPT_func_insert_trigger();

Caused by: org.hibernate.StaleStateException: Batch update returned unexpected row count from update [0]; actual row count: 0; expected: 1

I also tried using INSTEAD OF inplace of AFTER and before but its giving

Tables cannot have INSTEAD OF triggers. I am new to table partitioning and using triggers. I have searched for all this in google and also gone through the post here: Insert trigger ends up inserting duplicate rows in partitioned table

But it didn't work for me.

Can anyone tell me how to resolve this.

Answer 1

You can (pick one)

1. Create a view and build on it the trigger with INSTEAD OF. Then you mae insert on the view, not on the table (you can have a trigger on the table raising an error if you want to be sure).
2. Do not make direct insert. Call a stored procedure and move logic there

More on trigger and where they can be applied here: http://www.postgresql.org/docs/9.3/static/sql-createtrigger.html