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.
Note
This is work in progress. You can contribute to this page or request updates.
Sections TODO:
- Logical Operators
- Comparison Operators
- Mathematical Functions and Operators
- String Functions and Operators
- Binary String Functions and Operators
- Bit String Functions and Operators
- Pattern Matching
- Data Type Formatting Functions
- Conditional Expressions
- Subquery Expressions
- Row and Array Comparisons
- System Information Functions
Typical Scalar Functions
These are typical scalar SQL functions returning a single value based on arguments.
TODO: taken from sqlite, split to categories
- SQLite docs: Core Functions
- PostgreSQL docs: Functions and Operators
| Name | Description | sqlite | pgsql | mysql | mssql | xbase |
|---|---|---|---|---|---|---|
| abs(X) | DONE 2.9.8 | DONE 2.9.8 | DONE 2.9.8 | |||
| ceiling(X) | DONE 2.9.8, using (CASE WHEN X = CAST(X AS INT) THEN CAST(X AS INT) WHEN X >= 0 THEN CAST(X AS INT) + 1 ELSE CAST(X AS INT) END) | DONE 2.9.8 | DONE 2.9.8 | |||
| char(X1,X2,...,XN) | DONE 2.9.8 | |||||
| coalesce(X,Y,...) | DONE 2.9.8, using (CASE WHEN X = CAST(X AS INT) THEN CAST(X AS INT) WHEN X >= 0 THEN CAST(X AS INT) + 1 ELSE CAST(X AS INT) END) | DONE 2.9.8 | DONE 2.9.8 | |||
| floor(X) | DONE 2.9.8, using (CASE WHEN X >= 0 OR X = CAST(X AS INT) THEN CAST(X AS INT) ELSE CAST(X AS INT) - 1 END) | DONE 2.9.8 | DONE 2.9.8 | |||
| greatest(X,Y,...) & alias max(X,Y,...) | DONE 2.9.8 (adds COLLATE ) | DONE 2.9.8, uses (CASE WHEN (v0) IS NULL OR .. OR (vN) IS NULL THEN NULL ELSE GREATEST(v0,..,vN) END) | DONE 2.9.8, see pgsql, TODO since 5.0.13 GREATEST() is enough | |||
| hex(X) | DONE 2.9.8 | DONE 2.9.8 using ENCODE(v, 'hex') | DONE 2.9.8 | |||
| ifnull(X,Y) | DONE 2.9.8 | DONE 2.9.8, use coalesce() | DONE 2.9.8 | |||
| instr(X,Y) | DONE 2.9.8 | TODO use CREATE FUNCTION [1] | DONE 2.9.8 | |||
| least(X,Y,...) & alias min(X,Y,...) | DONE 2.9.8 (adds COLLATE ) | DONE 2.9.8, uses (CASE WHEN (v0) IS NULL OR .. OR (vN) IS NULL THEN NULL ELSE GREATEST(v0,..,vN) END) | DONE 2.9.8, see pgsql, TODO since 5.0.13 LEAST() is enough | |||
| length(X) | DONE 2.9.8 | DONE 2.9.8, for BLOBs octet_length() is used because length() for BLOB returns number of bits | DONE 2.9.8, char_length() is used because length() in mysql returns number of bytes, what is not right for multibyte (unicode) encodings | |||
| lower(X) | DONE 2.9.8 | DONE 2.9.8 | DONE 2.9.8 | |||
| ltrim(X) | DONE 2.9.8 | DONE 2.9.8 | DONE 2.9.8 | |||
| ltrim(X,Y) | DONE 2.9.8 | DONE 2.9.8 | NOT supported, TODO | |||
| nullif(X,Y) | DONE 2.9.8 | DONE 2.9.8 | DONE 2.9.8 | |||
| random() | Returns a random floating-point value v in the range 0 <= v < 1.0. NOTE: rand(X) which sets seed isn't portable between MySQL and PostgreSQL, and does not exist in SQLite, so we don't support it | DONE 2.9.8, uses (RANDOM() +9223372036854775807) /18446744073709551615) | DONE 2.9.8, uses random() | DONE 2.9.8, uses rand() | ||
| random(X,Y) | Returns returns a random integer that is equal or greater than X and less than Y | DONE 2.9.8, uses (X + CAST((Y - X) * (RANDOM() +9223372036854775807) /18446744073709551615 AS INT)) | DONE 2.9.8, uses (X + FLOOR(RANDOM() * (Y - X)) | DONE 2.9.8, uses (X + FLOOR(RANDOM() * (Y - X)) | ||
| replace(X,Y,Z) | ||||||
| round(X) | DONE 2.9.8 | DONE 2.9.8 | DONE 2.9.8 | |||
| round(X,Y) | DONE 2.9.8 | DONE 2.9.8 | DONE 2.9.8 (support for Y<0 ignored) | |||
| rtrim(X) | DONE 2.9.8 | DONE 2.9.8 | DONE 2.9.8 | |||
| rtrim(X,Y) | DONE 2.9.8 | DONE 2.9.8 | NOT supported, TODO | |||
| soundex(X) | DONE 2.9.8 | DONE 2.9.8, "CREATE EXTENSION fuzzystrmatch" is called on connection | DONE 2.9.8 | |||
| substr(X,Y,Z) | DONE 2.9.1 | DONE 2.9.1 | DONE 2.9.1 | |||
| substr(X,Y) | DONE 2.9.1 | DONE 2.9.1 | DONE 2.9.1 | |||
| trim(X) | DONE 2.9.8 | DONE 2.9.8 | DONE 2.9.8 | |||
| trim(X,Y) | DONE 2.9.8 | DONE 2.9.8 | NOT supported, TODO | |||
| unicode(X) | DONE 2.9.8 | DONE 2.9.8, uses ASCII(X) | DONE 2.9.8, ORD(CONVERT(X USING UTF16)) | |||
| upper(X) | DONE 2.9.8 | DONE 2.9.8 | DONE 2.9.8 |
Date And Time Scalar Functions
- SQLite docs: Date And Time Functions
- PostgreSQL docs: Date/Time Functions and Operators
| 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, ...) |
Trigonometric Scalar Functions
- SQLite has no such functions built-in but there's extension-functions.c at https://www.sqlite.org/contrib that we maybe can reuse. (check licese).
- PostgreSQL has them enabled, just test: https://www.postgresql.org/docs/9.6/static/functions-math.html#FUNCTIONS-MATH-TRIG-TABLE
- MySQL has them enabled: https://dev.mysql.com/doc/refman/5.7/en/mathematical-functions.html
| Name | Description | sqlite | pgsql | mysql | mssql | xbase |
|---|---|---|---|---|---|---|
| ....TODO |
SQLite-specific Scalar 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. | |||||
| last_insert_rowid() | Returns the ROWID of the last row insert from the database connection which invoked the function. The last_insert_rowid() SQL function is a wrapper around the sqlite3_last_insert_rowid() C/C++ interface function. | |||||
| glob(X,Y) | The glob(X,Y) function is equivalent to the expression "Y GLOB X". Note that the X and Y arguments are reversed in the glob() function relative to the infix GLOB operator. | DONE 2.9.7 but ifdef'd | won't add; and even GLOB operator missing | |||
| like(X,Y), like(X,Y,Z) | The like() function is used to implement the "Y LIKE X [ESCAPE Z]" expression. If the optional ESCAPE clause is present, then the like() function is invoked with three arguments. Otherwise, it is invoked with two arguments only. Note that the X and Y parameters are reversed in the like() function relative to the infix LIKE operator. | DONE 2.9.7 but ifdef'd | won't add: use LIKE operator instead | |||
| 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(). |
|||||
| printf(FORMAT,...) | Works like the sqlite3_mprintf() C-language function and the printf() function from the standard C library. The first argument is a format string that specifies how to construct the output string using values taken from subsequent arguments. If the FORMAT argument is missing or NULL then the result is NULL. The %n format is silently ignored and does not consume an argument. The %p format is an alias for %X. The %z format is interchangeable with %s. If there are too few arguments in the argument list, missing arguments are assumed to have a NULL value, which is translated into 0 or 0.0 for numeric formats or an empty string for %s. | |||||
| quote(X) | Returns the text of an SQL literal which is the value of its argument suitable for inclusion into an SQL statement. Strings are surrounded by single-quotes with escapes on interior quotes as needed. BLOBs are encoded as hexadecimal literals. Strings with embedded NUL characters cannot be represented as string literals in SQL and hence the returned string literal is truncated prior to the first NUL. | |||||
| randomblob(N) | Returns an N-byte blob containing pseudo-random bytes. If N is less than 1 then a 1-byte random blob is returned. Hint: applications can generate globally unique identifiers using this function together with hex() and/or lower() like this: hex(randomblob(16)) lower(hex(randomblob(16))) |
|||||
| 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. | |||||
| typeof(X) | maybe pg_typeof() but it's very different | |||||
| 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). | |||||
| zeroblob(N) | Returns a BLOB consisting of N bytes of 0x00. SQLite manages these zeroblobs very efficiently. Zeroblobs can be used to reserve space for a BLOB that is later written using incremental BLOB I/O. This SQL function is implemented using the sqlite3_result_zeroblob() routine from the C/C++ interface. |
Aggregate functions
- SQLite docs: Aggregate Functions
- PostgreSQL docs: Aggregate Functions
| Name | Description | sqlite | pgsql | mysql | mssql | xbase |
|---|---|---|---|---|---|---|
| avg(X) | ||||||
| count(X) count(*) |
||||||
| group_concat(X) group_concat(X,Y) |
||||||
| max(X) | ||||||
| min(X) | ||||||
| sum(X) total(X) |
Further work
- Model sections after pgsql, which probably has one of the larger feature set: [2]
- Add comparison for other backends: Sybase/MSSQL, xBase.
- Functions missing in SQLite that can be added via the extension API: [3], extension-functions.c
Links
- SQL (Wikipedia)
- SQL Tutorial: www.tutorialspoint.com/sql/index.htm
- MySQL Standards Compliance
- sql-92 standard
- Things to find out about when moving from MySQL to PostgreSQL
- Comparison of different SQL implementations