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