Kexi/Plugins/Queries/SQL Functions

This page lists SQL functions that Kexi supports or wants to support. Aggregate functions are included. Support in particular SQL backends is compared (SQLite, PostgreSQL, MySQL). For practical reasons emphasis is put on concrete implementation instead of focusing on an SQL standard.

Functions

• SQLite docs: Core Functions

Name	Description	sqlite	pgsql	mysql	mssql	xbase
abs(X)
char(X1,X2,...,XN)
coalesce(X,Y,...)
glob(X,Y)
ifnull(X,Y)
instr(X,Y)
hex(X)
length(X)
like(X,Y)
like(X,Y,Z)
lower(X)
ltrim(X)
ltrim(X,Y)
max(X,Y,...)
min(X,Y,...)
nullif(X,Y)
printf(FORMAT,...)
quote(X)
random()
randomblob(N)
replace(X,Y,Z)
round(X)
round(X,Y)
rtrim(X)
rtrim(X,Y)
soundex(X)
substr(X,Y,Z)
substr(X,Y)
trim(X)
trim(X,Y)
typeof(X)
unicode(X)
upper(X)
zeroblob(N)

Date And Time Functions

• SQLite docs: Date And Time Functions

Name	Description	sqlite	pgsql	mysql	mssql	xbase
date(timestring, modifier, modifier, ...)
time(timestring, modifier, modifier, ...)
datetime(timestring, modifier, modifier, ...)
julianday(timestring, modifier, modifier, ...)
strftime(format, timestring, modifier, modifier, ...)

Aggregate functions

• SQLite docs: Aggregate Functions

Name	Description	sqlite	pgsql	mysql	mssql	xbase

SQLite-specific Functions

• SQLite docs: Core Functions

Name	Description	sqlite	pgsql	mysql	mssql	xbase
changes()	Returns the number of database rows that were changed or inserted or deleted by the most recently completed INSERT, DELETE, or UPDATE statement, exclusive of statements in lower-level triggers. The changes() SQL function is a wrapper around the sqlite3_changes() C/C++ function and hence follows the same rules for counting changes.
likelihood(X,Y)	Returns argument X unchanged. The value Y in likelihood(X,Y) must be a floating point constant between 0.0 and 1.0, inclusive. The likelihood(X) function is a no-op that the code generator optimizes away so that it consumes no CPU cycles during run-time (that is, during calls to sqlite3_step()). The purpose of the likelihood(X,Y) function is to provide a hint to the query planner that the argument X is a boolean that is true with a probability of approximately Y. The unlikely(X) function is short-hand for likelihood(X,0.0625). The likely(X) function is short-hand for likelihood(X,0.9375).
likely(X)	Returns the argument X unchanged. The likely(X) function is a no-op that the code generator optimizes away so that it consumes no CPU cycles at run-time (that is, during calls to sqlite3_step()). The purpose of the likely(X) function is to provide a hint to the query planner that the argument X is a boolean value that is usually true. The likely(X) function is equivalent to likelihood(X,0.9375). See also: unlikely(X).
load_extension(X) load_ extension(X,Y)	Loads SQLite extensions out of the shared library file named X using the entry point Y. The result of load_extension() is always a NULL. If Y is omitted then the default entry point name is used. The load_extension() function raises an exception if the extension fails to load or initialize correctly. The load_extension() function will fail if the extension attempts to modify or delete an SQL function or collating sequence. The extension can add new functions or collating sequences, but cannot modify or delete existing functions or collating sequences because those functions and/or collating sequences might be used elsewhere in the currently running SQL statement. To load an extension that changes or deletes functions or collating sequences, use the sqlite3_load_extension() C-language API. For security reasons, extension loaded is turned off by default and must be enabled by a prior call to sqlite3_enable_load_extension().
sqlite_ compileoption_ get(N)	A wrapper around the sqlite3_compileoption_get() C/C++ function. This routine returns the N-th compile-time option used to build SQLite or NULL if N is out of range. See also the compile_options pragma.
sqlite_ compileoption_ used(X)	A wrapper around the sqlite3_compileoption_used() C/C++ function. When the argument X to sqlite_compileoption_used(X) is a string which is the name of a compile-time option, this routine returns true (1) or false (0) depending on whether or not that option was used during the build.
sqlite_ source_id()	Returns a string that identifies the specific version of the source code that was used to build the SQLite library. The string returned by sqlite_source_id() begins with the date and time that the source code was checked in and is follows by an SHA1 hash that uniquely identifies the source tree. This function is an SQL wrapper around the sqlite3_sourceid() C interface.
sqlite_version()	Returns the version string for the SQLite library that is running. This function is an SQL wrapper around the sqlite3_libversion() C-interface.
total_changes()	Returns the number of row changes caused by INSERT, UPDATE or DELETE statements since the current database connection was opened. This function is a wrapper around the sqlite3_total_changes() C/C++ interface.
unlikely(X)	Returns the argument X unchanged. The unlikely(X) function is a no-op that the code generator optimizes away so that it consumes no CPU cycles at run-time (that is, during calls to sqlite3_step()). The purpose of the unlikely(X) function is to provide a hint to the query planner that the argument X is a boolean value that is usually not true. The unlikely(X) function is equivalent to likelihood(X, 0.0625).

Further work

Add comparison for other backends: Sybase/MSSQL, xBase.