postgresqltriggerseventtrigger

Event Trigger to apply triggers to new tables


I am trying to implement functionality to automatically apply triggers to any new "items" tables as they are created.

I tried to do this using an EVENT TRIGGER, firstly with this function:

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
        IF obj.object_type = 'table' AND obj.object_identity LIKE '%.items' THEN
            EXECUTE format('
                CREATE OR REPLACE TRIGGER item_create_trigger
                AFTER INSERT ON %I
                FOR EACH ROW
                EXECUTE FUNCTION public.sync_insert();', obj.object_identity);
        END IF;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

Then created an event trigger ON ddl_command_end:

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

However, when I try and do a CREATE TABLE test.items, I get the following error:

ERROR:  relation "test.items" does not exist
CONTEXT:  SQL statement "
                CREATE OR REPLACE TRIGGER item_create_trigger
                AFTER INSERT ON "test.items"
                FOR EACH ROW
                EXECUTE FUNCTION public.sync_insert();"
PL/pgSQL function apply_trigger_on_wi_table_creation() line 12 at EXECUTE 
SQL state: 42P01

Even though this trigger is called ON ddl_command_end, the table does not exist until the trigger exits.

Is there a native PostgreSQL solution to this problem? Perhaps using LISTEN/NOTIFY? Thanks!


Solution

  • The problem is that the double quotes in "test.items" make the database take this for the table name rather than a schema qualified table (which would be test.items or "test"."items").

    The documentation says that

    Name Type Description
    ... ... ...
    object_identity text Text rendering of the object identity, schema-qualified. Each identifier included in the identity is quoted if necessary.

    So you can use %s like

    EXECUTE format(
               E'CREATE TRIGGER item_create_trigger\n'
               'AFTER INSERT ON %s\n'
               'FOR EACH ROW\n'
               'EXECUTE FUNCTION public.sync_insert();',
               obj.object_identity
            );