*** DRAFT ***

SQLite C Interface

Create Or Redefine SQL Functions

int sqlite3_create_function(
  sqlite3 *db,
  const char *zFunctionName,
  int nArg,
  int eTextRep,
  void *pApp,
  void (*xFunc)(sqlite3_context*,int,sqlite3_value**),
  void (*xStep)(sqlite3_context*,int,sqlite3_value**),
  void (*xFinal)(sqlite3_context*)
);
int sqlite3_create_function16(
  sqlite3 *db,
  const void *zFunctionName,
  int nArg,
  int eTextRep,
  void *pApp,
  void (*xFunc)(sqlite3_context*,int,sqlite3_value**),
  void (*xStep)(sqlite3_context*,int,sqlite3_value**),
  void (*xFinal)(sqlite3_context*)
);
int sqlite3_create_function_v2(
  sqlite3 *db,
  const char *zFunctionName,
  int nArg,
  int eTextRep,
  void *pApp,
  void (*xFunc)(sqlite3_context*,int,sqlite3_value**),
  void (*xStep)(sqlite3_context*,int,sqlite3_value**),
  void (*xFinal)(sqlite3_context*),
  void(*xDestroy)(void*)
);

R-57479-00042:[These functions (collectively known as "function creation routines") are used to add SQL functions or aggregates or to redefine the behavior of existing SQL functions or aggregates. ] The only differences between these routines are the text encoding expected for the second parameter (the name of the function being created) and the presence or absence of a destructor callback for the application data pointer.

R-44114-07168:[The first parameter is the database connection to which the SQL function is to be added. ] R-63464-64949:[If an application uses more than one database connection then application-defined SQL functions must be added to each database connection separately. ]

R-32023-34269:[The second parameter is the name of the SQL function to be created or redefined. ] R-24176-11856:[The length of the name is limited to 255 bytes in a UTF-8 representation, exclusive of the zero-terminator. ] R-36651-49821:[Note that the name length limit is in UTF-8 bytes, not characters nor UTF-16 bytes. ] R-14222-20715:[Any attempt to create a function with a longer name will result in SQLITE_MISUSE being returned. ]

R-17847-41579:[The third parameter (nArg) is the number of arguments that the SQL function or aggregate takes. ] R-41161-50390:[If this parameter is -1, then the SQL function or aggregate may take any number of arguments between 0 and the limit set by sqlite3_limit(SQLITE_LIMIT_FUNCTION_ARG). ] If the third parameter is less than -1 or greater than 127 then the behavior is undefined.

R-53205-22341:[The fourth parameter, eTextRep, specifies what text encoding this SQL function prefers for its parameters. ] The application should set this parameter to SQLITE_UTF16LE if the function implementation invokes sqlite3_value_text16le() on an input, or SQLITE_UTF16BE if the implementation invokes sqlite3_value_text16be() on an input, or SQLITE_UTF16 if sqlite3_value_text16() is used, or SQLITE_UTF8 otherwise. R-64203-64297:[The same SQL function may be registered multiple times using different preferred text encodings, with different implementations for each encoding. ] R-12993-07777:[When multiple implementations of the same function are available, SQLite will pick the one that involves the least amount of data conversion. ]

R-10382-19306:[The fourth parameter may optionally be ORed with SQLITE_DETERMINISTIC to signal that the function will always return the same result given the same inputs within a single SQL statement. ] Most SQL functions are deterministic. The built-in random() SQL function is an example of a function that is not deterministic. The SQLite query planner is able to perform additional optimizations on deterministic functions, so use of the SQLITE_DETERMINISTIC flag is recommended where possible.

R-17939-38828:[The fifth parameter is an arbitrary pointer. The implementation of the function can gain access to this pointer using sqlite3_user_data(). ]

R-56801-63935:[The sixth, seventh and eighth parameters, xFunc, xStep and xFinal, are pointers to C-language functions that implement the SQL function or aggregate. ] R-27228-35217:[A scalar SQL function requires an implementation of the xFunc callback only; NULL pointers must be passed as the xStep and xFinal parameters. ] R-17719-44813:[An aggregate SQL function requires an implementation of xStep and xFinal and NULL pointer must be passed for xFunc. ] R-46513-12418:[To delete an existing SQL function or aggregate, pass NULL pointers for all three function callbacks. ]

R-42916-59066:[If the ninth parameter to sqlite3_create_function_v2() is not NULL, then it is destructor for the application data pointer. The destructor is invoked when the function is deleted, either by being overloaded or when the database connection closes. ] R-23774-64117:[The destructor is also invoked if the call to sqlite3_create_function_v2() fails. ] R-55350-58300:[When the destructor callback of the tenth parameter is invoked, it is passed a single argument which is a copy of the application data pointer which was the fifth parameter to sqlite3_create_function_v2(). ]

R-24843-55391:[It is permitted to register multiple implementations of the same functions with the same name but with either differing numbers of arguments or differing preferred text encodings. ] R-44809-60674:[SQLite will use the implementation that most closely matches the way in which the SQL function is used. ] R-07963-00293:[A function implementation with a non-negative nArg parameter is a better match than a function implementation with a negative nArg. ] R-29748-45567:[A function where the preferred text encoding matches the database encoding is a better match than a function where the encoding is different. ] R-13160-27901:[A function where the encoding difference is between UTF16le and UTF16be is a closer match than a function where the encoding difference is between UTF8 and UTF16. ]

R-42168-23975:[Built-in functions may be overloaded by new application-defined functions. ]

R-39412-46882:[An application-defined function is permitted to call other SQLite interfaces. ] However, such calls must not close the database connection nor finalize or reset the prepared statement in which the function is running.

See also lists of Objects, Constants, and Functions.

*** DRAFT ***