How to write a generic update trigger for PostgreSQL view without known columns?

Question

I'm using a PostgreSQL view to simulate a legacy table that has since been split into two separate tables, so we can maintain backwards compatibility with a range of services. The goal is essentially for this view to function transparently as if it was the original table before the split. Using an INSTEAD OF INSERT trigger I've been able to handle inserts into the two tables pretty easily.

However I'm struggling to figure out how to write an INSTEAD OF UPDATE trigger, because I don't know which columns will be changed in any one request (I'm using sequelize so I don't really have any way of controlling what the requests are going to be, it could be SET a single column, it could be all of them). All examples I've found online seem to be updating known fields (either some meta-column related to the query or knowing what the shape of the update is going to be).

CREATE FUNCTION update_legacy_table_trigger()
RETURNS trigger AS $$
  BEGIN
    UPDATE table_a SET ??? WHERE id = NEW.id;
    UPDATE table_b SET ??? WHERE table_a_id = NEW.id; 
  END;
$$ LANGUAGE plpgsql;

A few example cases:

Imagine:

• legacy_table is the name of the view
• table_a has the columns: id, name, description, type, shared_column and
• table_b has the columns: id, table_a_id (foreign key to table_a), price, shared_column.

Example A:

UPDATE legacy_table SET name="Test", price="10.00" WHERE id="123";

I'd expect the trigger to behave something like:

UPDATE table_a SET name="Test" WHERE id="123";
UPDATE table_b SET price="10.00" WHERE table_a_id="123";

Example B: it could also receive updates for only one table

UPDATE legacy_table SET price="10.00" WHERE id="123";

So we'd expect the trigger to behave like:

UPDATE table_b SET price="10.00" WHERE table_a_id="123";

Example C: perhaps a lot of columns in the one request

UPDATE legacy_table SET name="Test", price="10.00", description="A test", type="foo", shared_column="bar" WHERE id="123";

So the trigger should behave like:

UPDATE table_a SET name="Test", description="A test", type="foo", shared_column="bar" WHERE id="123";
UPDATE table_b SET price="10.00", shared_column="bar" WHERE table_a_id="123";

There might be cases where updates need to be made to both tables, but I don't believe there are any cases where the name of the column in the original UPDATE would be any different from the name of the columns in the new tables.

How would I go about writing this INSTEAD OF UPDATE trigger for these cases where I don't know explicitly what the UPDATE query will be?

Answer 1

You have to write the update statement for all the columns in your trigger function. Your trigger function will look like below:

CREATE FUNCTION update_legacy_table_trigger()
RETURNS trigger AS $$
  BEGIN
    UPDATE table_a SET name=new.name, description=new.description,type=new.type, shared_column=new.shared_column WHERE id = NEW.id;
    UPDATE table_b SET price=new.price, shared_columns=new.shared_columns WHERE table_a_id = NEW.id; 
    return null;
  END;
$$ 
LANGUAGE plpgsql;

and you have to write your trigger on your view as below:

CREATE TRIGGER trg_update_legacy 
INSTEAD OF UPDATE
ON legacy_table
FOR EACH ROW
EXECUTE PROCEDURE update_legacy_table_trigger();

DEMO

This will handle all the update cases because NEW will contain updated row and we are updating all the fields with the data of NEW row.