How to automatically create a comment on CREATE in Postgres?

Question

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?

You want what Postgres calls an *event trigger*. The subject is rather detailed, so start with the documentation: https://www.postgresql.org/docs/current/event-trigger-definition.html. — Gordon Linoff, Apr 10 '21 at 11:41

Jim Jones · Accepted Answer · 2021-04-12T06:44:06.250

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

How to automatically create a comment on CREATE in Postgres?

1 Answers1