Unable to update a record with a trigger using a dynamic column on a PostgreSQL DB I'm trying to get a column named XXX_LASTCHANGE updated whenever a record is updated.

As the XXX would suggest, this is a dynamic value I want to pass with the trigger definition rather than creating dozens of similar triggers.

I came up with the following, but I cannot get this working as an error message is raised saying the error stack is full.

CREATE OR REPLACE FUNCTION update_lastchange()   
RETURNS TRIGGER AS $$
BEGIN       

    EXECUTE format('
        UPDATE "%s" t
        SET    "%s_LASTCHANGE" = NOW()
        WHERE  ($1)."%s_ID" = ($2)."%s_ID"', 
        TG_TABLE_NAME, TG_ARGV[0], TG_ARGV[0], TG_ARGV[0])
    USING NEW, OLD;
    RETURN NEW;
END;
$$ language 'plpgsql';

CREATE TRIGGER cnt_lastchange BEFORE UPDATE ON "CONTENT" FOR EACH ROW EXECUTE PROCEDURE update_lastchange('CNT');


I'm not sure what I'm missing. Any hint is really appreciated.
In PostgreSQL, every UPDATE will create a new row version. An attempt to modify the old row version in a BEFORE trigger won't work.

The new table row version that is about to be added to the database is available in the NEW variable of the trigger. You have to modify NEW in order to modify the new table row.

Dynamic modification of NEW is what you need, but this is hard to impossible in PL/pgSQL.

I suggest that you use a PL/Python trigger instead:

CREATE OR REPLACE FUNCTION update_lastchange() RETURNS trigger
   LANGUAGE plpythonu AS
'TD["new"][TD["args"][0] + "_lastchange"] = plpy.execute("SELECT current_timestamp")[0]["current_timestamp"]
return "MODIFY"';

CREATE TRIGGER update_lastchange
   BEFORE UPDATE ON content
   FOR EACH ROW EXECUTE FUNCTION update_lastchange('cnt');


What does the trigger do?

It runs SELECT current_timestamp on the database to get the current timestamp and retrieves that value from the result set (the column labeled current_timestamp from the first row (row 0) of the result set).

Then it stores that value in the appropriate entry of the TD["new"] mapping that contains NEW in PL/Python.

Finally, "MODIFY" is returned to indicate that the UPDATE should proceed.