获取创建的表名 [英] Get created table name

问题描述

我正在尝试创建一个事件触发器，该事件触发器在创建表时执行.发生这种情况时，我想在一个表中插入名称(该表有2列 id 和 tablename )，表的创建.阅读文档时，我找不到如何获取表名.

I'm trying to create a event trigger, executed whenever a table is created. When this happens I would like to insert into a table ( which has 2 columns id and tablename ) the name of the table created. Reading the docs I'm not able to find how can i get the table name.

到目前为止，我有这个:

So far I have this:

CREATE OR REPLACE FUNCTION insert_layer()
RETURNS event_trigger
AS $$
    DECLARE r RECORD;
    BEGIN
            RAISE NOTICE 'event for % ', tg_tag;
            -- I would like to execute this
            --EXECUTE format('INSERT INTO "public.Layers"(name) VALUES(' || tableNameHere || ')') INTO result;
    END;
$$
LANGUAGE plpgsql;

CREATE EVENT TRIGGER insert_layer_event ON ddl_command_start 
WHEN TAG IN ('CREATE TABLE')
EXECUTE PROCEDURE insert_layer();

推荐答案

要能够检索一些其他信息，请在ddl_command_end 触发器上使用而不是在ddl_command_start 上使用.在调用此类触发器的函数中，您可以使用 pg_event_trigger_ddl_commands函数 :

To be able to retrieve some additional information, use on ddl_command_end trigger instead of on ddl_command_start. In the function calling such trigger you can use pg_event_trigger_ddl_commands function:

CREATE OR REPLACE FUNCTION insert_layer()
RETURNS event_trigger
AS $$
    DECLARE r RECORD;
    BEGIN
            RAISE NOTICE 'event for % ', tg_tag;
            -- I would like to execute this
            r := pg_event_trigger_ddl_commands(); 
            INSERT INTO public."Layers"(name) VALUES(r.object_identity);
    END;
$$
LANGUAGE plpgsql;

请注意代码更改:

1)您不需要使用 EXECUTE
2)"public.Layers" 表示在当前架构中名称恰好为"public.Layers" 的表，而不是表中的 Layers 表架构 public .

1) You do not need to use EXECUTE
2) "public.Layers" means the table with the name exactly "public.Layers" in the current schema, not the table Layers in the schema public.

这篇关于获取创建的表名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！