Automatically add update triggers for postgres updated_at columns

[rbalik]

This may be a bit complex, but there is a "standardized" way of handling an updated_at column in postgres.

The idea is you create a function like this:

          CREATE FUNCTION update_updated_at_column() RETURNS trigger
            LANGUAGE plpgsql
            AS $$
          BEGIN
            NEW.updated_at = NOW();
            RETURN NEW;
          END;

This only has to be done once per database.

And then attach it to the column when it's created like this:

CREATE TRIGGER sometable_update_trigger BEFORE UPDATE ON sometable FOR EACH ROW EXECUTE PROCEDURE update_updated_at_column();

I'm doing this manually in migrations right now but it would be really cool if Phinx could do this automatically as part of the addTimestamps and addTimestampsWithTimezone calls.

[rbalik]

In case this is helpful to anyone, I'm doing this with traits right now where I use the trait in the migration class. I call one function in the up and one in the down. Unfortunately can't use change() but otherwise it saves some time:

<?php

trait UpdatedAt
{
    abstract public function execute(string $sql, array $params = []): int;
    function setUpdatedAtOnTables(array $tablesWithUpdatedAt)
    {
        foreach ($tablesWithUpdatedAt as $table) {
            $triggerSql = <<<SQL
                CREATE TRIGGER {$table}_update_trigger BEFORE UPDATE ON $table FOR EACH ROW EXECUTE PROCEDURE update_updated_at_column();
            SQL;
            $this->execute($triggerSql);
        }
    }

    function dropUpdatedAtOnTables(array $tablesWithUpdatedAt)
    {
        foreach ($tablesWithUpdatedAt as $table) {
            $triggerSql = "DROP TRIGGER {$table}_update_trigger ON $table;";
            $this->execute($triggerSql);
        }
    }
}

[rbalik]

(This assumes the database already has the function in it)