sql - How to handle errors in a select statement when attempting an insert or fail? -

is there way handle errors in select statement when attempting insert or fail? specifically, want insert elements table, select statement used generate these elements failing. have elements select statement succeeded inserted, overall statement fail. thought insert or fail this, not. more specifically, imagine if defined new sqlite function "log"

#include <string> #include <sqlite3ext.h> #include <cmath> sqlite_extension_init1  extern "c" {     int sqlite3_log_init(         sqlite3 * db,         char ** err,         sqlite3_api_routines const * const api     ); }  // compute log of input void mylog(      sqlite3_context *context,     int argc,     sqlite3_value **argv ){     // grab number     auto num = sqlite3_value_double(argv[0]);      // if positive, take log     if(num > 0.)          sqlite3_result_double(context, log(num));      // otherwise, throw error     else {         auto msg = std::string("can't take log of nonpositive number");         sqlite3_result_error(context,msg.c_str(),msg.size());     } }  // initialize functions int sqlite3_log_init(     sqlite3 *db,     char **err,     sqlite3_api_routines const * const api ){     sqlite_extension_init2(api)      // register log function     if( int ret = sqlite3_create_function(         db, "log", 1, sqlite_any, 0, mylog, 0, 0)     ) {         *err=sqlite3_mprintf("error registering log: %s",             sqlite3_errmsg(db));         return ret;     }      // if we've made far, should ok     return sqlite_ok; } 

this can compiled with

g++ -std=c++14 log.cpp -shared -o log.so -fpic 

basically, above function takes log of element. example,

sqlite> select log(1); 0.0 sqlite> select log(0); error: can't take log of nonpositve number 

now, consider following sequence of sql operations

sqlite> .load "./log.so" sqlite> create table foo (num real); sqlite> insert foo values (2.), (1.), (0.); sqlite> create table bar (num real); sqlite> insert or fail bar select log(num) foo; error: can't take log of nonpositve number sqlite> select * bar; sqlite>  

basically, table bar empty because select statement failed on 0. want have happen table bar contain elements log(2.) , log(1.), error still thrown. there way have happen?

sqlite's on conflict clause applies unique, not null, check, , primary key constraints, not able use insert or ignore.

once user-defined function returns error, not possible suppress it.

you function's result undefined, , let return null (which can filter out).

alternatively, rows have valid values:

insert bar select log(num) foo num > 0;