[sqlite] Implementing a CREATE_FUNCTION function

[Dan Bishop]

It's convenient to be able to define new functions in C.  But sometimes, 
it would be *more* convenient to be able to define new functions in 
SQL.  This could be done by registering a CREATE_FUNCTION() function; 
then you could write something like:

SELECT CREATE_FUNCTION('LEFT', 2, 'SUBSTR(?1, 1, ?2)');

My first idea for implementing CREATE_FUNCTION is:

1. Create (if not exists) a table with columns for the function name, 
number of arguments, and SQL expression.
2. Add the new function to the table.
3. Call sqlite3_create_function to register the new function.

C doesn't have the ability to create functions at runtime, so the xFunc 
parameter would refer to a common global function, which would:

1. Look up the SQL expression corresponding to the SQL function name.
2. Evaluate the expression.

But how do I get the SQL function name from within the xFunc function?  
Can I get it from the sqlite3_context object, or do I have to use 
sqlite3_user_data()?

Or is there a better approach?

As a first step, I've written an EVAL() function, which supports 
parameter binding.

// EVAL(expr [, param]*)
void x_sqlite_eval(sqlite3_context* pContext,
                   int argc,
                   sqlite3_value* argv[])
{
   sqlite3*             pDB     = sqlite3_context_db_handle(pContext);
   sqlite3_stmt*        pStmt   = NULL;
   const unsigned char* expr    = NULL;
   char*                sql     = NULL;
   size_t               len;
   int                  err;
   int                  index;

   if (argc == 0)
   {
      sqlite3_result_null(pContext);
      return;
   }

   expr = sqlite3_value_text(argv[0]);
   len = sqlite3_value_bytes(argv[0]);

   // Build the SQL statement "SELECT (expr)"

   sql = malloc(len + 10); 
 
   if (sql == NULL)
   {
      sqlite3_result_error_nomem(pContext);
      return;
   }

   memcpy(sql, "SELECT (", 8);
   memcpy(sql + 8, expr, len);
   sql[8 + len] = ')';

   err = sqlite3_prepare_v2(pDB, sql, len + 9, &pStmt, NULL);

   if (err != SQLITE_OK)
   {
      sqlite3_result_error_code(pContext, err);
      goto EXIT;
   }

   // bind parameters
   for (index = 1; index < argc; index++)
   {
      err = sqlite3_bind_value(pStmt, index, argv[index]);

      if (err != SQLITE_OK)
      {
         sqlite3_result_error_code(pContext, err);
         goto EXIT;
      }
   }

   // execute the statement
   err = sqlite3_step(pStmt);

   if (err != SQLITE_ROW)
   {
      sqlite3_result_error_code(pContext, err);
      goto EXIT;
   }

   sqlite3_result_value(pContext, sqlite3_column_value(pStmt, 0));

EXIT:
   sqlite3_finalize(pStmt);
}

_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users