In MySQL, if a column type is TIMESTAMP or DATETIME and on update current_timestamp is set, the value of the field is automatically updated to the current time as the update command updates the line record. This feature is very useful in application development, making it easy to record the last update time of each line.

But this feature is not standard SQL grammar. After migrating to PostgreSQL, the database has to add set updated_at = current_timestamp in every update statement, which will inevitably be missed. So I developed PgAUT Plug-in, providing MySQL-like functionality.

I. Marker Auto Update Fields

The principle of plug-in is to create a trigger automatically when creating tables, and assign the values of fields that need to be updated automatically to clock_timestamp() before the update statement is executed.

In order to identify which fields need to be updated automatically, a special alias is created for the timestamp type by using the "domain", which is subsequently used as the type of field that needs to be updated automatically:

create domain auto_update_timestamp as timestamp;

-- Create table
create table foo (
  id bigint primary key,
  c1 auto_update_timestamp default current_timestamp
);
-- Modify table
alter table foo add column c2 auto_update_timestamp;

2. Create Trigger

Knowing which columns need to be updated automatically, you can bind a trigger to the table to update the values automatically when updating:

create or replace foo_on_update_handler() returns trigger as 
$$

begin
  new.c1 = clock_timestamp();
  new.c2 = clock_timestamp();
  return new;
end;

$$
 language plpgsql;

create trigger foo_on_update_trigger
 before update on foo
 for each row execute
 procedure foo_on_update_handler();

Event Trigger

Unlike ordinary triggers, Event trigger Capture all DDL changes, such as CREATE TABLE, ALTER TABLE, DROP TABLE, etc. With this feature, triggers can be created automatically when creating tables:

create or replace function table_event_trigger_handler()
 returns event_trigger as 
$$

declare
  _e record;
  _sql text;
  _schema_name text;
  _table_name text;
begin
  -- adopt pg_event_trigger_ddl_commands Get the element information of the current change
  for _e in select * from pg_event_trigger_ddl_commands() loop
    if _e.object_type = 'table' and _e.command_tag = 'CREATE TABLE' then
      -- from pg_event_trigger_ddl_commands Can't get the name of the table
      -- Only by hand pg_class Get in schema and table Name
      select
        pg_namespace.nspname,
        pg_class.relname
      into
        _schema_name,
        _table_name
      from
        pg_class
      inner join
        pg_namespace
      on
        pg_class.relnamespace = pg_namespace.oid
      where
        pg_class.oid = _e.objid;

      -- All types of change tables obtained from column information tables are auto_update_timestamp Column names
      -- Assemble into a group new.<column-name> := clock_timestamp();
      select
        string_agg('  new.' || column_name || ' := clock_timestamp();', E'\n')
      into
        _sql
      from
        information_schema.columns
      where
        table_schema = _schema_name
        and table_name = _table_name
        and domain_name = 'auto_update_timestamp';

      -- Execute dynamic statements to create trigger functions
      execute format($SQL$
create or replace function %s.%s_on_update_handler() returns trigger as $HANDLER$
begin
  %s
  return new;
end;
$HANDLER$ language plpgsql;
$SQL$, _schema_name, _table_name, _sql);

      -- Execute dynamic statements to create triggers
      execute format($SQL$
create trigger %s_%s_on_update_trigger
 before update on %s.%s
 for each row execute
 procedure %s.%s_on_update_handler()
$SQL$, _schema_name, _table_name
     , _schema_name, _table_name
     , _schema_name, _table_name);

    end if;
  end loop;
end;

$$
 language plpgsql;

-- Create event triggers to process CREATE TABLE Event
create event trigger table_event_trigger
  on ddl_command_end
  when tag in ('CREATE TABLE')
  execute procedure table_event_trigger_handler();

Complete other event triggers

The above code implements the automatic creation of trigger functions and triggers when CREATE TABLE is processed, and binds them to new tables. It also needs to create ALTER TABLE to update trigger functions when table fields are added or deleted, and DROP TABLE to delete trigger functions when table fields are added or deleted.

Details can be found in the source code of the plug-in: https://github.com/redraiment/pgaut/blob/master/pgaut--1.0.0.sql