Skip to main content

Chapter 8: Functions

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, CQL procedures have their own calling conventions and this makes it very inconvenient to use external code that 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 another 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.

Function Types​

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:

declare function dict_get_value(dict object, key_ text not null) text;

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:

declare function dict_create() create object;

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 released as usual.

If we also declare this procedure:

declare procedure dict_add(
    dict object not null,
    key_ text not null,
    value text not null);

then with this family of declarations we could write something like this:

create proc create_and_init(out dict object not null)
begin
  set dict := dict_create();
  call dict_add(dict, "k1", "v1");
  call dict_add(dict, "k2", "v2");
  if (dict_get_value(dict, "k1") == dict__get_value(dict, "k2")) then
    call printf("insanity has ensued\n");
  end if;
end;

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 out convention discussed previously.

SQL Scalar Functions​

SQLite includes the ability to add new functions to its expressions using sqlite3_create_function. In order to use this function in CQL, you must also provide its prototype definition to the compiler. You can do so following this example:

declare select function strencode(t text not null) text not null;

This introduces the function strencode to the compiler for use in SQL constructs. With this done you could write a procedure something like this:

create table foo(id integer, t text);

create procedure bar(id_ integer)
begin
   select strencode(T1.t) from foo T1 where T1.id = id_;
end;

This presumably returns the "encoded" text, whatever that might be. Note that if sqlite3_create_function 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 the 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 beginning with 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:

declare select function dict_contents(dict object not null)
   (k text not null, v text not null);

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:

create proc read_dict(dict object not null, pattern text)
begin
  if pattern is not null then
    select k, v from dict_contents(dict) T1 where T1.k LIKE pattern;
  else
    select k, v from dict_contents(dict);
  end if;
end;

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.

SQL Functions with Unchecked Parameter Types​

Certain SQL functions like json_extract are variadic (they accept variable number of arguments). To use such functions within CQL, you can declare a SQL function to have untyped parameters by including the NO CHECK clause instead of parameter types.

For example:

declare select function json_extract no check text;

This is also supported for SQL table-valued functions:

declare select function table_valued_function no check (t text, i int);

Note: currently the NO CHECK clause is not supported for non SQL Ordinary Scalar Functions.

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 with declare select function ... and then used.

CQL's hard-coded builtin list includes:

Aggregate Functions

  • count
  • max
  • min
  • sum
  • total
  • avg
  • group_concat

Scalar Functions

  • ifnull
  • nullif
  • upper
  • char
  • abs
  • instr
  • coalesce
  • last_insert_rowid
  • printf
  • strftime
  • date
  • time
  • datetime
  • julianday
  • substr
  • replace
  • round
  • trim
  • ltrim
  • rtrim

Window Functions

  • row_number
  • rank
  • dense_rank
  • percent_rank
  • cume_dist
  • ntile
  • lag
  • lead
  • first_value
  • last_value
  • nth_value

Special Functions

  • nullable
  • sensitive
  • ptr

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.

Sensitive casts an operand to the sensitive 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.

Last updated on by timch326