optional function extension in a trigger
(1.2) By mlaw (tantaman) on 2022-11-14 17:21:31 edited from 1.1 [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 [link] [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
!