Get created table name

Question

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();

For the trigger on `ddl_command_end` you can use the [`pg_event_trigger_ddl_commands` function](https://www.postgresql.org/docs/current/static/functions-event-triggers.html): `RAISE INFO 'Table: %', (pg_event_trigger_ddl_commands()).object_identity;` — Abelisto, Oct 08 '16 at 20:36
Thank you very much!! If you write an answer I'll accept! =) — Jose Hermosilla Rodrigo, Oct 08 '16 at 20:41

score 5 · Accepted Answer · edited Oct 08 '16 at 21:45

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;

Pay attention to the code changes:

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.

Get created table name

1 Answers1

Linked