PostgreSQL如何自动为新创建的items表应用触发器？

问题分析 #

你尝试通过ddl_command_end事件触发器自动为新创建的items表添加触发器，但执行CREATE TABLE test.items时出现relation "test.items" does not exist错误。核心原因是代码中错误地将object_identity（格式为模式.表名）作为单个标识符传入format('%I')，导致PostgreSQL将"test.items"解析为单个表名（而非test模式下的items表），从而找不到对应关系。

修正后的实现方案 #

1. 修正PL/pgSQL函数 #

将函数中引用表的方式改为拆分模式名和表名，使用schema_name和object_name字段分别获取模式和表名，确保正确生成带模式的表标识符：

CREATE OR REPLACE FUNCTION public.apply_trigger_on_item_table_creation()
RETURNS event_trigger AS $$
DECLARE
    obj record;
BEGIN
    FOR obj IN
        SELECT * FROM pg_event_trigger_ddl_commands()
    LOOP
        -- 匹配所有名为items的表，无论所属模式
        IF obj.object_type = 'table' AND obj.object_name = 'items' THEN
            EXECUTE format('
                CREATE OR REPLACE TRIGGER item_create_trigger
                AFTER INSERT ON %I.%I
                FOR EACH ROW
                EXECUTE FUNCTION public.sync_insert();', obj.schema_name, obj.object_name);
        END IF;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

2. 保留原事件触发器配置 #

原事件触发器的配置无需修改：

CREATE EVENT TRIGGER create_item_table_trigger
ON ddl_command_end
WHEN TAG IN ('CREATE TABLE')
EXECUTE FUNCTION public.apply_trigger_on_item_table_creation();

关键说明 #

• ddl_command_end事件触发时，新创建的表已经存在于当前事务中，之前的错误并非表未创建，而是标识符引用错误。
• 使用schema_name和object_name拆分模式与表名，结合%I格式化符，能正确生成"test"."items"这样的合法标识符，避免将模式.表名误解析为单个表名。
• 无需使用LISTEN/NOTIFY，PostgreSQL原生事件触发器即可满足需求。

内容的提问来源于stack exchange，提问作者robbieperry22