Generic Postgres 9.5 trigger to convert an UPDATE into modified INSERT

Question

Is it possible to create a generic (not table-specific) trigger in Postgres 9.5 that would perform on instead of update that converts the update into an insert?

Basically what I want to do is (pseudocode):

sql instead of UPDATE on TG_TABLE_NAME INSERT on TG_TABLE_NAME

I know I can create a very table-specific trigger that maps each value into an insert statement. What I'm trying to do is get away from creating this trigger on every single table.

Is there something preventing you from changing the initial update into an insert before you execute the query? — Tim Biegeleisen
It's more a safety thing. People should not update to this table, it's an audit table. But if they did... well, you could just change it into an insert and it would be fine... — Zac

Patrick Patrick · Accepted Answer · 2016-01-20T04:32:16

It is a bit of an oddball idea (nothing personal), but how about this:

CREATE FUNCTION not_update_but_insert() RETURNS trigger AS $$
BEGIN
  INSERT INTO TG_TABLE_NAME  -- Do an INSERT...
    SELECT NEW.*;            -- ... using the values from the row to be updated
  RETURN NULL;               -- Fail the UPDATE
END;
$$ LANGUAGE plpgsql;

Obviously this would not work for any table that has a PRIMARY KEY or other UNIQUE constraints. You do have to CREATE TRIGGER x BEFORE UPDATE for every table this would apply to, so analyze your table structure before creating the trigger.

There is obviously a work-around - at least for the PKs based on a sequence - by examining the information_schema for "safe" columns in TG_TABLE_NAME and then assembling their names into strings to splice into the INSERT statement (column list of main statement and select list). Just leave the columns with sequences or appropriate default values out. This, however, does not address UNIQUE constraints that have no obvious replacement (like a user name or an email address).

Generic Postgres 9.5 trigger to convert an UPDATE into modified INSERT

1 Answers