Clever Geek Handbook

PostgreSQL: write update on master table and write write to child sections

How to define an update trigger function to update records in the master table on which the partitions are defined. I have a table that has sections defined and code for an insert trigger function that will insert data into child tables whenever there is an insert in the main table. The code I have is the following:

    --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 a function to start the update. This way, whenever there is an update in the master table, the record should navigate through the sections accordingly.

Currently, when I write an update statement on the main table, it throws an error:

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 wrote the trigger function before the update like this:

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 cannot solve this problem. Can someone tell me how to change the update trigger to make this work. Thanks in advance.

Changed this so use view and instead of trigger. It now works for the insert method. But how to handle the update?

   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();
+3


source to share


1 answer


The problem is that with a trigger, you need to return an old or new record.

You need instead . But it is only available through views. Therefore you need:

  • Create a view (this is ok in the master table)
  • Create trigger "instead of" on view
  • Refresh and insert into view only

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

I checked the code and found a bit of a fix. Like this.
First of all, I inserted an entry for testing:

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 should only be fired 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 entry. If not, I just update via the master table. If I need to move, I delete the old record and insert the new record into the view (which is why I start the insert view).
Warning: in my simulation, I have never changed the intended key. If you need to update the ID, you will need to adapt some code.

Then I checked 2 cases. In my scenario the 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;

Do some tests, but they should work.

+3


source






More articles:


All Articles