CQL stored procs have a very simple contract so it is easy to declare procedures and then implement them in regular C, the C functions just have to conform to the contract. However, there is no notion of functions at all and this makes it very inconvenient to use some external code and is not doing database things and wants to return values. Even a random number generator or something would be difficult to use because it could not be called in the context of an expression. To allow for this CQL adds declared functions
In an other example of the two-headed nature of CQL, there are two ways to declare functions. As we have already
seen you can make function-like procedures and call them like functions simply by making a procedure with an
out parameter. However, there are also cases where it is reasonable to make function calls to external functions of other kinds. There are three major types of functions you might wish to call.
Ordinary Scalar Functions
These functions are written in regular C and provide for the ability to do operations on in-memory objects. For instance, you could create functions that allow you to read and write from a dictionary. You can declare these functions like so:
Such a function is not known to SQLite and therefore cannot appear in SQL statements. CQL will enforce this.
The above function returns a text reference, and, importantly, this is a borrowed reference. The dictionary is presumably holding on to the reference and as long as it is not mutated the reference is valid. CQL will retain this reference as soon as it is stored and release it automatically when it is out of scope. So, in this case, the dictionary continues to own the object.
It is also possible to declare functions that create objects. Such as this example:
This declaration tells CQL that the function will create a new object for our use. CQL does not retain the provided object, rather assuming ownership of the presumably one reference count the object already has. When the object goes out of scope it is release as usual.
If we also declare this procedure:
Then with this family of declarations we could write something like this:
Note: Ordinary scalar functions may not use the database in any way, when they are invoked they will not
be provided with the database pointer and so they will be unable to do any database operations. To do
database operations use regular procedures. You can create a function-like-procedure using the
SQL Scalar Functions
SQLite includes the ability to add new functions to its expressions using
order to use this function in CQL, you must also provide its prototype definition to the compiler. You
can do so like in this example:
This introduces the function
strencode to the compiler for use in SQL constructs. With this done you
could write a procedure something like this:
This presumably returns the "encoded" text, whatever that might be. Note that if
is not called before this code runs, a run-time error will ensue. Just as CQL must assume that declared
tables really are created, it also assumes that declared function really are created. This is another case
of telling the compiler in advance what the situation will be at runtime.
SQLite allows for many flexible kinds of user defined functions. CQL doesn't concern itself with the details of the implementation of the function, it only needs the signature so that it can validate calls.
Note that SQL Scalar Functions cannot contain
object parameters. To pass an
object, you should instead pass
the memory address of this object using a
LONG INT parameter. To access the address of an
object at runtime, you should use
ptr() function. See the notes section below for more information.
See also: Create Or Redefine SQL Functions.
SQL Table Valued Functions
More recent versions of SQLite also include the ability to add table-valued functions to statements in place of actual tables. These functions can use their arguments to create a "virtual table" value for use in place of a table. For this
to work, again SQLite must be told of the existence of the table. There are a series of steps to make this happen
sqlite3_create_module which are described in the SQLite documents under "The Virtual Table Mechanism Of SQLite."
Once that has been done, a table-valued function can be defined for most object types. For instance it is possible to create a table-valued function like so:
This is just like the previous type of select function but the return type is a table shape. Once the above has been done you can legally write something like this:
This construct is very general indeed but the runtime set up for it is much more complicated than scalar functions and only more modern versions of SQLite even support it.
Notes on Builtin Functions
Some of the SQLite builtin functions are hard-coded, these are the functions that have semantics that are not readily captured with a simple prototype. Other SQLite functions can be declared wtih
declare select funtion ... and then used.
CQL's hard-coded builtin list includes:
Nullable casts an operand to the nullable version of its type and otherwise does nothing. This cast might be useful if you need an exact type match in a situation. It is stripped from any generated SQL and generated C so it has no runtime effect at all other than the indirect consequences of changing the storage class of its operand.
Ptr is used to cause a reference type variable to be bound as a long integer to SQLite. This is a way of giving object pointers to SQLite UDFs. Not all versions of Sqlite support
binding object variables, so passing memory addresses is the best we can do on all versions.