PostgreSQL: update record on master table and move record in child partitions

Question

How to define an update trigger function for Updating records in the master table which has partitions defined on it. I've a table which has partitions defined on it and code for the insert trigger function which will insert data in the child tables whenever there is an insert in the master table. The code I've is as follows:

    --Master table
        CREATE TABLE "SMS_RECEIPT"
        (
          "ID" integer NOT NULL,
          "ACCOUNT_INFO" character varying(255),
          "CHARGE" character varying(255),
          "DELIVERY_INFO" character varying(255),
          "DESTINATION" character varying(255),
          "MESSAGE_ID" character varying(255),
          "RECEIPT_TYPE" integer,
          "SMS_CENTRE" character varying(255),
          "SMS_ID" character varying(255),
          "SOURCE" character varying(255),
          "STATUS" character varying(255),
          timedate date,
          "FLAG" character varying(255),
          "STID" character varying(255),
          CONSTRAINT "SMS_RECEIPT_pkey" PRIMARY KEY ("ID")
        );

--child partition tables
        CREATE TABLE SMS_RECEIPT_y2015m01 (
        CHECK ( timedate >=  '2015-01-01' AND timedate <  '2015-01-31' )
         ) INHERITS ("SMS_RECEIPT");
         CREATE TABLE SMS_RECEIPT_y2015m02 (
        CHECK ( timedate >=  '2015-02-01' AND timedate <  '2015-02-28' )
         ) INHERITS ("SMS_RECEIPT");
        CREATE TABLE SMS_RECEIPT_y2015m03 (
        CHECK ( timedate >=  '2015-03-01' AND timedate <  '2015-03-31' )
         ) INHERITS ("SMS_RECEIPT");
        CREATE TABLE SMS_RECEIPT_y2015m04 (
        CHECK ( timedate >=  '2015-04-01' AND timedate <  '2015-04-30' )
         ) INHERITS ("SMS_RECEIPT");
        CREATE TABLE SMS_RECEIPT_y2015m05 ( 
        CHECK ( timedate >=  '2015-05-01' AND timedate <  '2015-05-31' )
        ) INHERITS ("SMS_RECEIPT");
        CREATE TABLE SMS_RECEIPT_y2015m06 ( 
        CHECK ( timedate >=  '2015-06-01' AND timedate <  '2015-06-30' )
        ) INHERITS ("SMS_RECEIPT");
        CREATE TABLE SMS_RECEIPT_y2015m07 ( 
        CHECK ( timedate >=  '2015-07-01' AND timedate <  '2015-07-31' )
        ) INHERITS ("SMS_RECEIPT");
        CREATE TABLE SMS_RECEIPT_y2015m08 ( 
        CHECK ( timedate >=  '2015-08-01' AND timedate <  '2015-08-31' )
        ) INHERITS ("SMS_RECEIPT");
        CREATE TABLE SMS_RECEIPT_y2015m09 ( 
        CHECK ( timedate >=  '2015-09-01' AND timedate <  '2015-09-30' )
        ) INHERITS ("SMS_RECEIPT");
        CREATE TABLE SMS_RECEIPT_y2015m010 ( 
        CHECK ( timedate >=  '2015-10-01' AND timedate <  '2015-10-31' )
        ) INHERITS ("SMS_RECEIPT");


        ALTER TABLE  SMS_RECEIPT_y2015m01 ADD CONSTRAINT  SMS_RECEIPT_y2015m01_pkey PRIMARY KEY ("ID");
        ALTER TABLE  SMS_RECEIPT_y2015m02 ADD CONSTRAINT  SMS_RECEIPT_y2015m02_pkey PRIMARY KEY ("ID");
        ALTER TABLE  SMS_RECEIPT_y2015m03 ADD CONSTRAINT  SMS_RECEIPT_y2015m03_pkey PRIMARY KEY ("ID");
        ALTER TABLE  SMS_RECEIPT_y2015m04 ADD CONSTRAINT  SMS_RECEIPT_y2015m04_pkey PRIMARY KEY ("ID");
        ALTER TABLE  SMS_RECEIPT_y2015m05 ADD CONSTRAINT  SMS_RECEIPT_y2015m05_pkey PRIMARY KEY ("ID");
        ALTER TABLE  SMS_RECEIPT_y2015m06 ADD CONSTRAINT  SMS_RECEIPT_y2015m06_pkey PRIMARY KEY ("ID");
        ALTER TABLE  SMS_RECEIPT_y2015m07 ADD CONSTRAINT  SMS_RECEIPT_y2015m07_pkey PRIMARY KEY ("ID");
        ALTER TABLE  SMS_RECEIPT_y2015m08 ADD CONSTRAINT  SMS_RECEIPT_y2015m08_pkey PRIMARY KEY ("ID");
        ALTER TABLE  SMS_RECEIPT_y2015m09 ADD CONSTRAINT  SMS_RECEIPT_y2015m09_pkey PRIMARY KEY ("ID");
        ALTER TABLE  SMS_RECEIPT_y2015m010 ADD CONSTRAINT  SMS_RECEIPT_y2015m010_pkey PRIMARY KEY ("ID");


        CREATE INDEX idxSMS_RECEIPT_y2015m01_TIME_DATE ON SMS_RECEIPT_y2015m01 (timedate);
        CREATE INDEX idxSMS_RECEIPT_y2015m02_TIME_DATE ON SMS_RECEIPT_y2015m02 (timedate);
        CREATE INDEX idxSMS_RECEIPT_y2015m03_TIME_DATE ON SMS_RECEIPT_y2015m03 (timedate);
        CREATE INDEX idxSMS_RECEIPT_y2015m04_TIME_DATE ON SMS_RECEIPT_y2015m04 (timedate);
        CREATE INDEX idxSMS_RECEIPT_y2015m05_TIME_DATE ON SMS_RECEIPT_y2015m05 (timedate);
        CREATE INDEX idxSMS_RECEIPT_y2015m06_TIME_DATE ON SMS_RECEIPT_y2015m06 (timedate);
        CREATE INDEX idxSMS_RECEIPT_y2015m07_TIME_DATE ON SMS_RECEIPT_y2015m07 (timedate);
        CREATE INDEX idxSMS_RECEIPT_y2015m08_TIME_DATE ON SMS_RECEIPT_y2015m08 (timedate);
        CREATE INDEX idxSMS_RECEIPT_y2015m09_TIME_DATE ON SMS_RECEIPT_y2015m09 (timedate);
        CREATE INDEX idxSMS_RECEIPT_y2015m010_TIME_DATE ON SMS_RECEIPT_y2015m010 (timedate);


        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 NEW;
        END;
        $$
        LANGUAGE plpgsql;


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

Now I have to write the update trigger function, In a way that whenever there is an update in the master table, the record should move across the partitions accordingly.

currently when I write the update statement on master table it throws me error as:

ERROR:  new row for relation "sms_receipt_y2015m03" violates check constraint "sms_receipt_y2015m03_timedate_check"
DETAIL:  Failing row contains (2, asdf, asdf, asdf, asdf, asdf, 1234, asdfasd, asdfasdf, adsfad, adsfasd, 2015-04-22, adsf, were).
********** Error **********

ERROR: new row for relation "sms_receipt_y2015m03" violates check constraint "sms_receipt_y2015m03_timedate_check"
SQL state: 23514
Detail: Failing row contains (2, asdf, asdf, asdf, asdf, asdf, 1234, asdfasd, asdfasdf, adsfad, adsfasd, 2015-04-22, adsf, were).

so, I've written a trigger function for before update as follows:

Create or replace function sms_receipt_func_update_trigger()
returns trigger as $$
declare total integer;
begin
        IF exists( select 1 from SMS_RECEIPT_y2015m01 where "ID"=OLD."ID" and timedate = OLD.timedate ) THEN
                delete from SMS_RECEIPT_y2015m01 where "ID"=OLD."ID";

        ELSIF exists( select 1 from SMS_RECEIPT_y2015m02 where "ID"=OLD."ID" and timedate = OLD.timedate ) THEN
                delete from SMS_RECEIPT_y2015m02 where "ID"=OLD."ID";

    ELSIF exists( select 1 from SMS_RECEIPT_y2015m03 where "ID"=OLD."ID" and timedate = OLD.timedate ) THEN
                delete from SMS_RECEIPT_y2015m03 where "ID"=OLD."ID";
        ELSIF exists( select 1 from SMS_RECEIPT_y2015m04 where "ID"=OLD."ID" and timedate = OLD.timedate ) THEN
                delete from SMS_RECEIPT_y2015m04 where "ID"=OLD."ID";

    ELSIF exists( select 1 from SMS_RECEIPT_y2015m05 where "ID"=OLD."ID" and timedate = OLD.timedate ) THEN
                delete from SMS_RECEIPT_y2015m05 where "ID"=OLD."ID";
    ELSIF exists( select 1 from SMS_RECEIPT_y2015m06 where "ID"=OLD."ID" and timedate = OLD.timedate ) THEN
                delete from SMS_RECEIPT_y2015m06 where "ID"=OLD."ID";
        ELSIF exists( select 1 from SMS_RECEIPT_y2015m07 where "ID"=OLD."ID" and timedate = OLD.timedate ) THEN
                delete from SMS_RECEIPT_y2015m07 where "ID"=OLD."ID";

    ELSIF exists( select 1 from SMS_RECEIPT_y2015m08 where "ID"=OLD."ID" and timedate = OLD.timedate ) THEN
                delete from SMS_RECEIPT_y2015m01 where "ID"=OLD."ID";

    ELSIF exists( select 1 from SMS_RECEIPT_y2015m09 where "ID"=OLD."ID" and timedate = OLD.timedate ) THEN
                delete from SMS_RECEIPT_y2015m09 where "ID"=OLD."ID";

    ELSIF exists( select 1 from SMS_RECEIPT_y2015m010 where "ID"=OLD."ID" and timedate = OLD.timedate ) THEN
                delete from SMS_RECEIPT_y2015m010 where "ID"=OLD."ID";
            ELSE
        RAISE EXCEPTION 'Date out of range.  Fix the measurement_insert_trigger() function!';
    END IF;
    RETURN NEW;
END;
$$
LANGUAGE plpgsql;


create trigger trigger_sms_receipt_update 
before update on "SMS_RECEIPT"
for each row execute procedure
sms_receipt_func_update_trigger();

But, still I am not able to solve this issue. Can someone tell me how to modify the update trigger, so as to make this work. Thanks in advance.

Modified this, so to use the view and the instead of trigger. Now, it works for the insertion method. But, how to handle the Update thing?

   CREATE TABLE "SMS_RECEIPT"
(
  "ID" integer NOT NULL,
  "ACCOUNT_INFO" character varying(255),
  "CHARGE" character varying(255),
  "DELIVERY_INFO" character varying(255),
  "DESTINATION" character varying(255),
  "MESSAGE_ID" character varying(255),
  "RECEIPT_TYPE" integer,
  "SMS_CENTRE" character varying(255),
  "SMS_ID" character varying(255),
  "SOURCE" character varying(255),
  "STATUS" character varying(255),
  timedate date,
  "FLAG" character varying(255),
  "STID" character varying(255),
  CONSTRAINT "SMS_RECEIPT_pkey" PRIMARY KEY ("ID")
);

create view "sms_receipt_view" as select * from "SMS_RECEIPT";

CREATE TABLE SMS_RECEIPT_y2015m01 (
CHECK ( timedate >=  '2015-01-01' AND timedate <  '2015-01-31' )
 ) INHERITS ("SMS_RECEIPT");
 CREATE TABLE SMS_RECEIPT_y2015m02 (
CHECK ( timedate >=  '2015-02-01' AND timedate <  '2015-02-28' )
 ) INHERITS ("SMS_RECEIPT");
CREATE TABLE SMS_RECEIPT_y2015m03 (
CHECK ( timedate >=  '2015-03-01' AND timedate <  '2015-03-31' )
 ) INHERITS ("SMS_RECEIPT");
CREATE TABLE SMS_RECEIPT_y2015m04 (
CHECK ( timedate >=  '2015-04-01' AND timedate <  '2015-04-30' )
 ) INHERITS ("SMS_RECEIPT");
CREATE TABLE SMS_RECEIPT_y2015m05 (
CHECK ( timedate >=  '2015-05-01' AND timedate <  '2015-05-31' )
) INHERITS ("SMS_RECEIPT");
CREATE TABLE SMS_RECEIPT_y2015m06 (
CHECK ( timedate >=  '2015-06-01' AND timedate <  '2015-06-30' )
) INHERITS ("SMS_RECEIPT");
CREATE TABLE SMS_RECEIPT_y2015m07 (
CHECK ( timedate >=  '2015-07-01' AND timedate <  '2015-07-31' )
) INHERITS ("SMS_RECEIPT");
CREATE TABLE SMS_RECEIPT_y2015m08 (
CHECK ( timedate >=  '2015-08-01' AND timedate <  '2015-08-31' )
) INHERITS ("SMS_RECEIPT");
CREATE TABLE SMS_RECEIPT_y2015m09 (
CHECK ( timedate >=  '2015-09-01' AND timedate <  '2015-09-30' )
) INHERITS ("SMS_RECEIPT");
CREATE TABLE SMS_RECEIPT_y2015m010 (
CHECK ( timedate >=  '2015-10-01' AND timedate <  '2015-10-31' )
) INHERITS ("SMS_RECEIPT");


CREATE UNIQUE INDEX SMS_RECEIPT_unique ON "SMS_RECEIPT" USING btree ("ID");

ALTER TABLE  SMS_RECEIPT_y2015m01 ADD CONSTRAINT SMS_RECEIPT_y2015m01_pkey PRIMARY KEY ("ID");
ALTER TABLE  SMS_RECEIPT_y2015m02 ADD CONSTRAINT SMS_RECEIPT_y2015m02_pkey PRIMARY KEY ("ID");
ALTER TABLE  SMS_RECEIPT_y2015m03 ADD CONSTRAINT SMS_RECEIPT_y2015m03_pkey PRIMARY KEY ("ID");
ALTER TABLE  SMS_RECEIPT_y2015m04 ADD CONSTRAINT SMS_RECEIPT_y2015m04_pkey PRIMARY KEY ("ID");
ALTER TABLE  SMS_RECEIPT_y2015m05 ADD CONSTRAINT SMS_RECEIPT_y2015m05_pkey PRIMARY KEY ("ID");
ALTER TABLE  SMS_RECEIPT_y2015m06 ADD CONSTRAINT SMS_RECEIPT_y2015m06_pkey PRIMARY KEY ("ID");
ALTER TABLE  SMS_RECEIPT_y2015m07 ADD CONSTRAINT SMS_RECEIPT_y2015m07_pkey PRIMARY KEY ("ID");
ALTER TABLE  SMS_RECEIPT_y2015m08 ADD CONSTRAINT SMS_RECEIPT_y2015m08_pkey PRIMARY KEY ("ID");
ALTER TABLE  SMS_RECEIPT_y2015m09 ADD CONSTRAINT SMS_RECEIPT_y2015m09_pkey PRIMARY KEY ("ID");
ALTER TABLE  SMS_RECEIPT_y2015m010 ADD CONSTRAINT SMS_RECEIPT_y2015m010_pkey PRIMARY KEY ("ID");


CREATE INDEX idxSMS_RECEIPT_y2015m01_TIME_DATE ON SMS_RECEIPT_y2015m01 (timedate);
CREATE INDEX idxSMS_RECEIPT_y2015m02_TIME_DATE ON SMS_RECEIPT_y2015m02 (timedate);
CREATE INDEX idxSMS_RECEIPT_y2015m03_TIME_DATE ON SMS_RECEIPT_y2015m03 (timedate);
CREATE INDEX idxSMS_RECEIPT_y2015m04_TIME_DATE ON SMS_RECEIPT_y2015m04 (timedate);
CREATE INDEX idxSMS_RECEIPT_y2015m05_TIME_DATE ON SMS_RECEIPT_y2015m05 (timedate);
CREATE INDEX idxSMS_RECEIPT_y2015m06_TIME_DATE ON SMS_RECEIPT_y2015m06 (timedate);
CREATE INDEX idxSMS_RECEIPT_y2015m07_TIME_DATE ON SMS_RECEIPT_y2015m07 (timedate);
CREATE INDEX idxSMS_RECEIPT_y2015m08_TIME_DATE ON SMS_RECEIPT_y2015m08 (timedate);
CREATE INDEX idxSMS_RECEIPT_y2015m09_TIME_DATE ON SMS_RECEIPT_y2015m09 (timedate);
CREATE INDEX idxSMS_RECEIPT_y2015m010_TIME_DATE ON SMS_RECEIPT_y2015m010 (timedate);


create or replace function sms_receipt_func_update_trigger()
returns trigger as $$
begin
    update "sms_receipt_view" set timedate = new.timedate where "ID"=new."ID";
return new;
end;
$$
LANGUAGE plpgsql;


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 NEW;
END;
$$
LANGUAGE plpgsql;    

CREATE TRIGGER trigger_SMS_RECEIPT_insert
    Instead of INSERT OR UPDATE ON "sms_receipt_view"
    FOR EACH ROW EXECUTE PROCEDURE SMS_RECEIPT_func_insert_trigger();

create trigger trigger_sms_receipt_update
instead of update on "sms_receipt_view"
for each row execute procedure sms_receipt_func_update_trigger();

user_0 · Accepted Answer

The problem is that with the trigger you need to return the old or the new record.

You need an instead of trigger. But it is available only on views. So you need to:

Create a view (it is ok on the master table)
Create a trigger "instead of" on the view
Update and insert just on the view

See the table here: http://www.postgresql.org/docs/9.4/static/sql-createtrigger.html

I tested the code and found few thing to fix. Here we go.
First of all, I inserted a record to test:

INSERT INTO sms_receipt_view(
        "ID", "ACCOUNT_INFO", "CHARGE", "DELIVERY_INFO", "DESTINATION", 
        "MESSAGE_ID", "RECEIPT_TYPE", "SMS_CENTRE", "SMS_ID", "SOURCE", 
        "STATUS", timedate, "FLAG", "STID")
VALUES (1, 'acc_info', 'charge', 'delivery info', 'my dest', 
        'ssss id mess', 4, 'vodafone center', 'dff33', '3333 3 33333', 
        'ok', '20150601 15:25', 'myflag', 'stid');

Second, the insert trigger must be fired just on insert, so i run the code:

DROP TRIGGER trigger_sms_receipt_insert ON sms_receipt_view;

CREATE TRIGGER trigger_sms_receipt_insert
  INSTEAD OF INSERT
  ON sms_receipt_view
  FOR EACH ROW
  EXECUTE PROCEDURE sms_receipt_func_insert_trigger();

And then I rewrote the update trigger:

CREATE OR REPLACE FUNCTION sms_receipt_func_update_trigger()
  RETURNS trigger AS
$BODY$
begin
    --update "sms_receipt_view" set timedate = new.timedate where "ID"=new."ID";
    --check if I need to move record
    IF date_part('year', OLD.timedate) <> date_part('year', NEW.timedate) OR date_part('month', OLD.timedate) <> date_part('month', NEW.timedate) THEN
    DELETE FROM "SMS_RECEIPT" WHERE "ID" = NEW."ID";

    INSERT INTO sms_receipt_view ("ID", "ACCOUNT_INFO", "CHARGE", "DELIVERY_INFO", "DESTINATION", "MESSAGE_ID", "RECEIPT_TYPE",
       "SMS_CENTRE", "SMS_ID", "SOURCE", "STATUS", timedate, "FLAG", "STID")
    VALUES (NEW."ID", NEW."ACCOUNT_INFO", NEW."CHARGE", NEW."DELIVERY_INFO", NEW."DESTINATION", NEW."MESSAGE_ID", NEW."RECEIPT_TYPE",
       NEW."SMS_CENTRE", NEW."SMS_ID", NEW."SOURCE", NEW."STATUS", NEW.timedate, NEW."FLAG", NEW."STID"
    );
    ELSE
        UPDATE "SMS_RECEIPT"
        SET "ACCOUNT_INFO" = NEW."ACCOUNT_INFO",
        "CHARGE" = NEW."CHARGE",
        "DELIVERY_INFO" = NEW."DELIVERY_INFO",
        "DESTINATION" = NEW."DESTINATION",
        "MESSAGE_ID" = NEW."MESSAGE_ID",
        "RECEIPT_TYPE" = NEW."RECEIPT_TYPE",
        "SMS_CENTRE" = NEW."SMS_CENTRE",
        "SMS_ID" = NEW."SMS_ID",
        "SOURCE" = NEW."SOURCE",
        "STATUS" = NEW."STATUS",
        timedate = NEW.timedate,
        "FLAG" = NEW."FLAG",
        "STID" = NEW."STID"
        WHERE "ID" = NEW."ID";
    END IF;
return NULL;
end;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION sms_receipt_func_update_trigger()
  OWNER TO postgres;

First I check if we really need to move the record. If not, I simply update via master table. If I need to move I delete old record and insert the new record in the view (so I trigger the insert view).
Warning: in my simulation i supposed key never change. If you need to update the id you will need to adapt some code.

Then I tested the 2 case. In my scenario id was: 1

UPDATE sms_receipt_view
SET "SMS_CENTRE" = 'tim center'
--timedate = '20150501'
WHERE "ID" = 1;

UPDATE sms_receipt_view
SET "SMS_CENTRE" = 'tim center',
timedate = '20150501'
WHERE "ID" = 1;

Make some test, but should work.

PostgreSQL: update record on master table and move record in child partitions

Answers (1)

Related Questions