CODEWITHSUNDEEP
databasepostgresqlevent-triggers
Jose Hermosilla Rodrigo
Jose Hermosilla Rodrigo

Reputation: 3683

Get created table name

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

Upvotes: 5

Views: 875

Answers (1)

Abelisto
Abelisto

Reputation: 15614

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.

Upvotes: 5

Related Questions