Scott. I almost forgot about this example. Consider group_concat's orthogonal function group_replace.
I adapted this from SQLite replace and group_concat. The author disclaims all rights to the following code: --------------- struct StrRepl { const unsigned char* zStr; u32 nStr; u8 replError; /* STRREPL_NOMEM or STRREPL_TOOBIG */ }; typedef struct StrRepl StrRepl; #define STRREPL_NOMEM 1 #define STRREPL_TOOBIG 2 /* ** Aggregate group_replace(A,B,C) result string is derived from A by replacing ** every exact match occurrence of B with C. A is presumed constant over the group ** while B and C may vary at each step. Collating sequences are not used. */ static void groupReplaceStep( sqlite3_context *context, int argc, sqlite3_value **argv ) { const unsigned char *zStr; /* The input string A */ const unsigned char *zPattern; /* The pattern string B */ const unsigned char *zRepl; /* The replacement string C */ unsigned char *zOut; /* The output */ int nStr; /* Size of zStr */ int nPattern; /* Size of zPattern */ int nRepl; /* Size of zRep */ i64 nOut; /* Maximum size of zOut */ int loopLimit; /* Last zStr[] that might match zPattern[] */ int i, j; /* Loop counters */ assert(argc == 3); UNUSED_PARAMETER(argc); zStr = sqlite3_value_text(argv[0]); if (zStr == 0) return; nStr = sqlite3_value_bytes(argv[0]); assert(zStr == sqlite3_value_text(argv[0])); /* No encoding change */ zPattern = sqlite3_value_text(argv[1]); if (zPattern == 0) { assert(sqlite3_value_type(argv[1]) == SQLITE_NULL || sqlite3_context_db_handle(context)->mallocFailed); return; } if (zPattern[0] == 0) { assert(sqlite3_value_type(argv[1]) != SQLITE_NULL); sqlite3_result_value(context, argv[0]); return; } nPattern = sqlite3_value_bytes(argv[1]); assert(zPattern == sqlite3_value_text(argv[1])); /* No encoding change */ zRepl = sqlite3_value_text(argv[2]); if (zRepl == 0) return; nRepl = sqlite3_value_bytes(argv[2]); assert(zRepl == sqlite3_value_text(argv[2])); StrRepl* pStrRepl = (StrRepl*) sqlite3_aggregate_context(context, sizeof (StrRepl)); if (pStrRepl) { if (pStrRepl->replError) return; if (!pStrRepl->zStr) { if (sqlite3_value_type(argv[0]) == SQLITE_NULL || sqlite3_value_type(argv[1]) == SQLITE_NULL) return; pStrRepl->nStr = sqlite3_value_bytes(argv[0]); pStrRepl->zStr = contextMalloc(context,pStrRepl->nStr); memcpy((void*)pStrRepl->zStr,sqlite3_value_text(argv[0]),pStrRepl->nStr); /* No encoding change */ } zPattern = sqlite3_value_text(argv[1]); nPattern = sqlite3_value_bytes(argv[1]); assert(zPattern == sqlite3_value_text(argv[1])); /* No encoding change */ if (0 == pStrRepl->nStr || 0 == nPattern) return; zRepl = sqlite3_value_text(argv[2]); nRepl = sqlite3_value_bytes(argv[2]); if (0 == zRepl) return; nOut = pStrRepl->nStr + 1; assert(nOut < SQLITE_MAX_LENGTH); zOut = contextMalloc(context, (i64) nOut); if (0 == zOut) return; loopLimit = pStrRepl->nStr - nPattern; for (i = j = 0; i <= loopLimit; i++) { if (pStrRepl->zStr[i] != zPattern[0] || memcmp(&(pStrRepl->zStr)[i], zPattern, nPattern)) { zOut[j++] = pStrRepl->zStr[i]; } else { u8 *zOld; sqlite3 *db = sqlite3_context_db_handle(context); nOut += nRepl - nPattern; testcase(nOut - 1 == db->aLimit[SQLITE_LIMIT_LENGTH]); testcase(nOut - 2 == db->aLimit[SQLITE_LIMIT_LENGTH]); if (nOut - 1 > db->aLimit[SQLITE_LIMIT_LENGTH]) { pStrRepl->replError = STRREPL_TOOBIG; sqlite3_free(zOut); return; } zOld = zOut; zOut = sqlite3_realloc64(zOut, (int) nOut); if (zOut == 0) { pStrRepl->replError = STRREPL_NOMEM; sqlite3_free(zOld); return; } memcpy(&zOut[j], zRepl, nRepl); j += nRepl; i += nPattern - 1; } } assert(j + pStrRepl->nStr - i + 1 == nOut); memcpy(&zOut[j], &pStrRepl->zStr[i], pStrRepl->nStr - i); j += pStrRepl->nStr - i; assert(j <= nOut); zOut[j] = 0; void* pFree = (void*)pStrRepl->zStr; pStrRepl->zStr = zOut; pStrRepl->nStr = nOut; sqlite3_free(pFree); } } static void groupReplaceFinalize(sqlite3_context *context) { const char **pzVal; StrRepl* pStrRepl = sqlite3_aggregate_context(context, 0); if (pStrRepl) { if (pStrRepl->replError == STRREPL_TOOBIG) { sqlite3_result_error_toobig(context); } else if (pStrRepl->replError == STRREPL_NOMEM) { sqlite3_result_error_nomem(context); } else { sqlite3_result_text(context, pStrRepl->zStr, -1, sqlite3_free); } } } On Tue, Jan 2, 2018 at 4:57 PM, Scott Robison <sc...@casaderobison.com> wrote: > On Tue, Jan 2, 2018 at 5:46 PM, petern <peter.nichvolo...@gmail.com> > wrote: > > Hi Scott. > > > >>Are there other aggregate functions that take multiple arguments? > > > > Absolutely. I've got a few in my code which deserialize table rows into > > runtime objects. Fortunately, the DISTINCT filter makes no sense in that > > use case, so I didn't bump into this issue myself. > > Thanks for the info. In doing some quick searches, I found multiple > descriptions of "generic SQL" aggregates that gave a syntax of > "aggregate([DISTINCT|ALL] expression)", which led me to assume that > maybe the standard only allows that syntax with a single expression, > not an expression list. I say maybe because the examples I found were > clearly not the standard. > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users