sql - 如何在 Postgres 中自动创建对 CREATE 的评论？

Question

我想创建一个触发器，一旦在模式中创建表，它就会被触发。此触发器应向新创建的表添加注释，例如:

CREATE TRIGGER my_trigger 
   AFTER CREATE ON my_schema
   EXECUTE PROCEDURE add_comment;

与 add_comment做 somthibg 喜欢:

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

但是，似乎我只能在表级别添加触发器。我怎样才能实现在纯 Postgres 中向新创建的表添加注释？

Answer 1

您需要一个 EVENT TRIGGER .
首先创建一个函数:

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;
$$;

然后将其连接到 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();

创建表后，它将被触发:

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