Postgres statement-level trigger ERROR: transition tables cannot be specified for triggers with column lists

Question

I have a table my_table with columns id (int), value (varchar), and modified (timestamp). I want to update the timestamp by the trigger each time I set a new value. I'm changing thousands of rows in one query so I do not want to use the row-level trigger (aka FOR EACH ROW) and modify the whole updated set in one invocation and not the thousands for each row. This is the trigger I'm trying to create:

CREATE TRIGGER my_trigger
AFTER UPDATE OF value
ON my_schema.my_table
REFERENCING NEW TABLE AS updated OLD TABLE AS prev
FOR EACH STATEMENT
EXECUTE PROCEDURE my_schema.my_trigger_function()

I'm getting an error during the creation:

[0A000] ERROR: transition tables cannot be specified for triggers with column lists

How can I access the updated data set if I remove the "REFERENCING NEW TABLE AS updated OLD TABLE AS prev" and won't be able to query and join on updated or prev transition tables? If I remove the "OF value" part I'd get the recursive trigger invocation since the trigger fires itself again by changing the same table but a different column. So the best solution I've come up with it's to eliminate the second recursive invocation by the IF condition:

CREATE TRIGGER my_trigger
AFTER UPDATE
ON my_schema.my_table
REFERENCING NEW TABLE AS updated OLD TABLE AS prev
FOR EACH STATEMENT
EXECUTE PROCEDURE my_schema.my_trigger_function()

CREATE OR REPLACE FUNCTION my_schema.my_trigger_function()
    RETURNS TRIGGER
    LANGUAGE PLPGSQL
AS
$$
BEGIN

    IF EXISTS(SELECT 1
               FROM updated
                        INNER JOIN prev ON updated.modified = prev.modified) THEN
        UPDATE my_schema.my_table
        SET modified = NOW()
        WHERE id IN (SELECT id FROM updated);
    END IF;

    RETURN NULL;
END;
$$;

If I knew how to access all updated rows with "AFTER UPDATE OF value" without updated and prev I'd make it better, that's why I'm here.

Answer 1

You could avoid recursive invocation of your trigger by adding WHEN (PG_TRIGGER_DEPTH() = 0) to the declaration:

CREATE TRIGGER my_trigger
AFTER UPDATE ON my_schema.my_table
REFERENCING NEW TABLE AS updated OLD TABLE AS prev
FOR EACH STATEMENT
WHEN (PG_TRIGGER_DEPTH() = 0)
EXECUTE PROCEDURE my_schema.my_trigger_function()