How to automatically create a comment on CREATE in Postgres?

I would like to create a trigger which gets fired once a table is created in a schema. This trigger should add a comment to the newly created table, something like:

CREATE TRIGGER my_trigger 
   AFTER CREATE ON my_schema
   EXECUTE PROCEDURE add_comment;

With add_comment doing somthibg like:

...
BEGIN
   EXECUTE '
      COMMENT ON TABLE ' || new_table || ' IS ''Created by ' || CURRENT_USER || ' on ' || CURRENT_TIME ''';
   ';

However, it seems I can only add triggers on table level. How could I achieve to add comments to newly created tables in pure Postgres?

Answer

You need an EVENT TRIGGER.

First create a function:

CREATE OR REPLACE FUNCTION trg_create_table_comment() RETURNS event_trigger
LANGUAGE plpgsql AS $$
DECLARE obj record;    
BEGIN
  SELECT * FROM pg_event_trigger_ddl_commands() INTO obj;
  EXECUTE 'COMMENT ON TABLE ' || obj.object_identity || ' IS ''Created by ' || SESSION_USER || ' on ' || NOW() || '''';
END;
$$;

Then attach it to an EVENT TRIGGER:

CREATE EVENT TRIGGER trg_create_table ON ddl_command_end
WHEN TAG IN ('CREATE TABLE','CREATE TABLE AS')
EXECUTE PROCEDURE trg_create_table_comment();

After you create a table it will be triggered:

CREATE TABLE foo (id int);

SELECT schemaname,tablename,tableowner,description
FROM pg_tables t
LEFT JOIN pg_description d ON d.objoid = t.tablename::regclass::oid
WHERE tablename = 'foo';
    
 schemaname | tablename | tableowner |                    description                    
------------+-----------+------------+---------------------------------------------------
 public     | foo       | jones      | Created by jones on 2021-04-10 20:22:22.174874+02