PostgreSQL 10 High Performance

Date change update trigger

One of the optional things usually left out of PostgreSQL partitioning examples is an update trigger. Consider the case where you update a row and change the date; this could require relocating it to another partition. If you want to allow for this case, you need to install a TRIGGER into each partition:

CREATE OR REPLACE FUNCTION orders_2004_01_update_trigger()
RETURNS TRIGGER AS $$
BEGIN
   IF ( NEW.orderdate != OLD.orderdate ) THEN
      DELETE FROM orders_2004_01
         WHERE OLD.orderid=orderid;
      INSERT INTO orders values(NEW.*);
   END IF;
   RETURN NULL;
END;
$$
LANGUAGE plpgsql;
CREATE TRIGGER update_orders_2004_01
     BEFORE UPDATE ON orders_2004_01
     FOR EACH ROW
     EXECUTE PROCEDURE orders_2004_01_update_trigger();

As in the INSERT ...