How to List User-Defined vs. Built-In Functions in SQLite

Understanding SQLite Function Types and Visibility Challenges

SQLite’s extensible architecture allows developers to create custom functions that operate within SQL queries or interact with the database engine programmatically. However, distinguishing between built-in functions (e.g., SUM(), SUBSTR()) and user-defined functions (UDFs) registered via APIs like sqlite3_create_function() is not straightforward. A common misconception arises from assuming that all functions—whether internal, external, or user-created—reside in a unified registry accessible through SQL queries. This misunderstanding leads to confusion when attempting to programmatically enumerate functions, especially in scenarios requiring dynamic introspection of the database environment. The core issue revolves around SQLite’s separation of concerns: built-in functions are part of the SQL execution engine, while UDFs are bound to specific database connections at runtime and lack centralized metadata storage.

The problem is exacerbated by SQLite’s documentation referring to both SQL-invokable functions and C-language API methods as "functions," blurring the line between SQL-operational entities and programmatic interfaces. For example, pragma_function_list returns a list of SQL-invokable functions but excludes UDFs registered via the C API. Meanwhile, functions exposed through SQLite’s shared library (e.g., sqlite3_open()) are unrelated to SQL execution and belong to the API layer. Developers must therefore clarify whether they need to introspect SQL-operational functions (used in SELECT statements), API methods (called from application code), or symbols in compiled binaries (e.g., DLLs). Each category requires distinct approaches for enumeration, and conflating them results in incomplete or inaccurate results.

Why SQLite Does Not Provide a Direct Method to List User-Defined Functions

1. Architectural Separation Between SQL and API Layers
   SQLite’s design strictly separates SQL semantics from its C API. Built-in functions like LOWER() or RANDOM() are hardcoded into the SQL parser and execution engine. User-defined functions, however, are registered at runtime using sqlite3_create_function(), which binds them to a specific database connection. These UDFs exist in memory and are not persisted to the database schema or catalog. Consequently, SQLite’s metadata tables (e.g., sqlite_master) and PRAGMAs like pragma_function_list only reflect built-in or SQL-defined functions, not runtime-registered UDFs.

2. Lack of Persistent Metadata for Runtime Objects
   Unlike tables or indexes, which are recorded in sqlite_master, UDFs registered via the C API do not create schema entries. This omission is intentional: UDFs are often transient, connection-specific, or context-dependent (e.g., using sqlite3_create_window_function for window operations). Persisting them would complicate connection pooling, schema migrations, and multi-threaded environments. Thus, SQLite prioritizes lightweight runtime registration over metadata tracking, leaving developers to manage UDF visibility manually.

3. Ambiguity in Function Terminology
   The term "function" in SQLite documentation refers to multiple entities:

   • SQL Functions: Invoked within SQL statements (e.g., ABS(-5)).
   • API Functions: C-language methods like sqlite3_prepare_v2().
   • Shared Library Symbols: Exported symbols in sqlite3.dll or libsqlite3.so.
     Without explicit disambiguation, developers may assume these categories overlap or share a common registry. For instance, pragma_function_list lists SQL functions but not API methods, while tools like nm or DumpBin.exe list shared library symbols unrelated to SQL operations.

Strategies to Enumerate SQL-Operational and API Functions

Step 1: Use pragma_function_list with Filtering

The pragma_function_list command returns a table of all SQL-invokable functions, including built-ins and SQL-defined UDFs (created via CREATE FUNCTION in SQLite 3.30+). To isolate user-defined functions:

SELECT DISTINCT name 
FROM pragma_function_list 
WHERE origin NOT IN ('builtin', 'sqlite_define');

Limitations:

• This works only for UDFs defined via SQL’s CREATE FUNCTION, which are rare in practice.
• UDFs registered via sqlite3_create_function() do not appear here, as they lack an origin tag.

Step 2: Maintain a Manual UDF Registry

Since SQLite lacks introspection for API-registered UDFs, track them explicitly:

1. Create a Metadata Table:

   CREATE TABLE udf_registry (
       name TEXT PRIMARY KEY,
       arg_count INTEGER,
       type TEXT CHECK(type IN ('scalar', 'aggregate', 'window'))
   );
   

2. Register UDFs with a Wrapper:
   In your application code, wrap sqlite3_create_function() calls to insert metadata into udf_registry:

   void register_udf(
       sqlite3 *db,
       const char *name,
       int arg_count,
       void (*func)(sqlite3_context*, int, sqlite3_value**),
       int func_type
   ) {
       sqlite3_create_function(db, name, arg_count, SQLITE_UTF8, NULL, func, NULL, NULL);
       char *sql = sqlite3_mprintf("INSERT OR IGNORE INTO udf_registry VALUES (%Q, %d, %Q)", name, arg_count, func_type);
       sqlite3_exec(db, sql, NULL, NULL, NULL);
       sqlite3_free(sql);
   }
   

Advantages:

• Provides a queryable source of truth for UDFs.
• Works across connections if the registry is stored in a shared database.

Step 3: Introspect sqlite_master for SQL-Defined Functions

SQLite 3.30+ allows creating scalar functions via SQL:

CREATE FUNCTION hex2dec(hex TEXT) RETURNS INTEGER 
AS $$ SELECT CAST(printf('%d', CAST('0x' || hex AS INTEGER)) AS INTEGER); $$;

These functions appear in sqlite_master as type function:

SELECT name, type 
FROM sqlite_master 
WHERE type = 'function';

Caveats:

• Limited to SQL-defined functions, not C UDFs.
• Requires SQLite ≥ 3.30.

Step 4: Use Reflection with the C API

For applications with direct C/C++ access, use sqlite3_db_status() and sqlite3_next_stmt() to scan active functions:

1. Iterate Over Prepared Statements:

   sqlite3_stmt *stmt = NULL;
   while ((stmt = sqlite3_next_stmt(db, stmt)) != NULL) {
       const char *sql = sqlite3_sql(stmt);
       // Parse SQL to identify function usages (e.g., regex for function calls)
   }
   

2. Hook sqlite3_create_function() Calls:
   Override the function registration process in debug builds to log UDFs:

   int (*original_create_function)(sqlite3*, const char*, int, int, void*, void (*)(sqlite3_context*,int,sqlite3_value**), void (*)(sqlite3_context*,int,sqlite3_value**), void (*)(sqlite3_context*));
   
   int traced_create_function(
       sqlite3 *db,
       const char *zFuncName,
       int nArg,
       int eTextRep,
       void *pApp,
       void (*xFunc)(sqlite3_context*,int,sqlite3_value**),
       void (*xStep)(sqlite3_context*,int,sqlite3_value**),
       void (*xFinal)(sqlite3_context*)
   ) {
       printf("UDF Registered: %s\n", zFuncName);
       return original_create_function(db, zFuncName, nArg, eTextRep, pApp, xFunc, xStep, xFinal);
   }
   
   // Override in testing environments
   original_create_function = sqlite3_create_function;
   sqlite3_create_function = traced_create_function;
   

Use Cases:

• Debugging or testing environments where UDF registration must be audited.
• Not suitable for production due to performance overhead.

Step 5: Analyze Shared Library Symbols

For compiled SQLite extensions or UDFs distributed as shared libraries, use platform-specific tools to list exported symbols:

• Windows:

  dumpbin /exports sqlite3.dll | findstr "sqlite3_"
  

• Linux/macOS:

  nm -gD libsqlite3.so | grep ' T ' | awk '{print $3}'
  

Insights:

• Lists all C API functions (e.g., sqlite3_open_v2, sqlite3_create_function).
• Does not correlate with SQL-invokable functions.

Step 6: Query SQLite’s Internal Hash Table (Advanced)

SQLite maintains a private hash table (sqlite3GlobalFunctions) for built-in functions. While undocumented and volatile, it can be accessed in debug builds:

extern const sqlite3APIs;
void list_builtins() {
    FuncDefHash *pHash = &sqlite3GlobalFunctions;
    for (int i=0; i<pHash->nSlot; i++) {
        FuncDef *p = pHash->a[i];
        while (p) {
            printf("Built-in: %s\n", p->zName);
            p = p->pNext;
        }
    }
}

Risks:

• Relies on internal implementation details that may change without notice.
• Requires recompiling SQLite with debug symbols.

Step 7: Use Extension Loader Metadata (SQLite 3.39+)

SQLite 3.39 introduced sqlite3_load_extension() enhancements allowing optional metadata tables. If your UDFs are packaged as extensions, design them to register metadata:

int sqlite3_extension_init(
    sqlite3 *db, 
    char **pzErrMsg, 
    const sqlite3_api_routines *pApi
) {
    sqlite3_create_function(db, "my_udf", 1, SQLITE_UTF8, NULL, my_udf, NULL, NULL);
    sqlite3_exec(db, 
        "CREATE TABLE IF NOT EXISTS extension_meta ("
        "func_name TEXT, "
        "func_type TEXT);"
        "INSERT INTO extension_meta VALUES ('my_udf', 'scalar');",
        NULL, NULL, NULL);
    return SQLITE_OK;
}

Query:

SELECT func_name FROM extension_meta;

Best Practices:

• Standardize metadata table names across extensions.
• Combine with sqlite3_auto_extension() for automatic registration.

Final Recommendations

1. For SQL-Only Contexts: Use pragma_function_list and sqlite_master to list SQL-defined functions, but recognize their limitations for API-registered UDFs.
2. For Native Applications: Implement a manual UDF registry or hook function registration to track UDFs programmatically.
3. For Extension Developers: Embed metadata tables within extensions to self-document available functions.
4. For Debugging: Use platform-specific symbol dumpers or override sqlite3_create_function() in debug builds.

By aligning your approach with SQLite’s architectural constraints and rigorously distinguishing between SQL functions, API methods, and binary symbols, you can achieve accurate introspection tailored to your use case.