CODEWITHSUNDEEP
sqliteuser-defined-functions
Dmitry Pashkevich
Dmitry Pashkevich

Reputation: 13516

How do I call user-defined function only if it exists?

SQLite allows to define custom functions that can be called from SQL statements. I use this to get notified of trigger activity in my application:

CREATE TRIGGER AfterInsert AFTER INSERT ON T
BEGIN
  -- main trigger code involves some extra db modifications
  -- ...
  
  -- invoke application callback
  SELECT ChangeNotify('T', 'INSERT', NEW.id);
END;

However, user-defined functions are added only to current database connection. There may be other clients who haven't defined ChangeNotify and I don't want them to get a "no such function" error.

Is is possible to call a function only if it's defined? Any alternative solution is also appreciated.

Upvotes: 0

Views: 5197

Answers (3)

Matt Wonlaw
Matt Wonlaw

Reputation: 12443

It would be possible by combining a query against pragma_function_list and a WHEN statement on the trigger --

CREATE TRIGGER AfterInsert AFTER INSERT ON T
WHEN EXISTS (SELECT 1 FROM pragma_function_list WHERE name = 'ChangeNotify')
BEGIN
  SELECT ChangeNotify('T', 'INSERT', NEW.id);
END;

except that query preparation attempts to resolve functions prior to execution. So, afaik, this isn't possible to do in a trigger.

I need to do the same thing and asked here: https://sqlite.org/forum/forumpost/a997b1a01d Hopefully they come back with a solution.

Update

SQLite forum suggestion is to use create temp trigger when your extension loads -- https://sqlite.org/forum/forumpost/96160a6536e33f71

This is actually a great solution as temp triggers are:

  • not visible to other connections
  • are cleaned up when the connection creating them ends

Upvotes: 2

user1461607
user1461607

Reputation: 2760

Prior to calling your user defined function, you can check if the function exists by selecting from pragma_function_list;

select exists(select 1 from pragma_function_list where name='ChangeNotify');
1

Upvotes: 3

CL.
CL.

Reputation: 180030

SQLite is designed as an embedded database, so it is assumed that your application controls what is done with the database.

SQLite has no SQL function to check for user-defined functions.

If you want to detect changes made only from your own program, use sqlite3_update_hook.

Upvotes: 2

Related Questions