PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – 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!

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    EDIT: As the legendary Laurenz Albe answered, this solution is overly complicated, and a simple %s placeholder fixes the issue.

    As Frank Heikens pointed out, the problem was not with the timing, but the fact that the schema + table name were being written into the CREATE TRIGGER SQL as a single string ("test.items" instead of test.items).

    To fix this, I made the following changes to apply_trigger_on_item_table_creation():

    1. Declare variables for the separated schema and table names:
    DECLARE
    obj record;
    new_schema TEXT;
    new_table TEXT;
    1. Split obj.object_identity:
    new_schema := split_part(obj.object_identity, '.', 1);
new_table := split_part(obj.object_identity, '.', 2);
    1. Modify format to accept 2 identity placeholders:
    EXECUTE format('
    CREATE OR REPLACE TRIGGER item_create_trigger
    AFTER INSERT ON %I.%I
    FOR EACH ROW
    EXECUTE FUNCTION public.sync_insert();', new_schema, new_table);

  2. 0

    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_triggern'
           'AFTER INSERT ON %sn'
           'FOR EACH ROWn'
           'EXECUTE FUNCTION public.sync_insert();',
           obj.object_identity
        );
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search