cakephp / phinx

PHP Database Migrations for Everyone
https://phinx.org
MIT License
4.45k stars 895 forks source link

Automatically add update triggers for postgres updated_at columns #2259

Open rbalik opened 7 months ago

rbalik commented 7 months ago

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.