postgresqlfunctioneventstriggersplpgsql

postgres event trigger and function not working


i looked for a event trigger which fired up after creation of a table. I found some good posts here and do some test. My problem is that nothing happens, no errors nor output. So please give some advice what i do wrong. Here my code. I'am complete new in postgres, come from oracle, so please forgive me for this maybe "simple" question.

create table createt (tname char(20));

CREATE OR REPLACE FUNCTION insert()
RETURNS event_trigger
AS $$
DECLARE r RECORD;
BEGIN
r := pg_event_trigger_ddl_commands();
INSERT INTO createt VALUES(r.object_identity);
END;
$$
LANGUAGE plpgsql;

CREATE EVENT TRIGGER insert_event ON ddl_command_end
WHEN TAG IN ('CREATE TABLE')
EXECUTE PROCEDURE insert();

So in this testcase i want to put the tablename of the newly createt table in the table createt! But nothing happens. How can i check if the trigger is fired up. Or how can i debug the function?

Thanks for your time and advices.


Solution

  • The function pg_event_trigger_ddl_commands() returns set of rows. The trigger function must be ready to accept more than a single row, e.g.:

    CREATE OR REPLACE FUNCTION insert()
    RETURNS event_trigger
    AS $$
    BEGIN
        INSERT INTO createt 
        SELECT object_identity
        FROM pg_event_trigger_ddl_commands();
    END;
    $$
    LANGUAGE plpgsql;
    

    char(20) is not enough for object_identity, use text:

    DROP TABLE createt;
    CREATE TABLE createt (tname text);
    

    What happens when you create a table with serial primary key?

    create table my_table(id serial primary key);
    

    Let's check:

    select *
    from createt
    
             tname          
    ------------------------
     public.my_table_id_seq
     public.my_table
     public.my_table_pkey
     public.my_table_id_seq
    (4 rows)    
    

    There are two actions on the sequence, first to crete it and the other to set owned by with alter sequence.