optional function extension in a trigger

(1.2) By mlaw (tantaman) on 2022-11-14 17:21:31 edited from 1.1 [link] [source]

I was hoping it'd be possible to add a trigger that only runs when a given function extension exists on the current connection.

I've tried a few things such as a WHEN EXISTS statement against pragma_function_list but what I'm trying to do just doesn't seem possible (sqlite fails in prepare: Error: in prepare, no such function: some_function (1)). Sounds like function definitions are resolved during compilation of the SQL rather than during execution so no combination of branching will ever enable this.

Am I wrong / is there some way to accomplish what is outlined below?

CREATE TRIGGER ai AFTER INSERT ON foo
  WHEN EXISTS (SELECT 1 FROM pragma_function_list WHERE name = 'some_function')
BEGIN
  INSERT INTO bar VALUES (some_function());
END;

Only run the trigger when some_function exists on the current connection.

(2) By Gunter Hick (gunter_hick) on 2022-11-15 07:48:41 in reply to 1.2 [source]

The trigger program is compiled when the CREATE TRIGGER statement is executed (during initial execution or when the schema is loaded from the file). Any functions referenced in triggers need to be defined at that point in time. Your schema will fail to load unless the functions referenced in trigger programs are defined.

You should be able to build an extension that creates your user defined functions and then executes one or more CREATE TEMP TRIGGER statements that use the now-defined functions. See https://sqlite.org/loadext.html

(3) By mlaw (tantaman) on 2022-11-15 16:26:29 in reply to 2 [link] [source]

ah, perfect. How'd I not think of create temp trigger!