Using SQLite

Chapter 9. SQL Functions and Extensions

SQLite allows a developer to expand the SQL environment by creating custom SQL functions. Although these functions are used in SQL statements, the code to implement the function is written in C.

SQLite supports three kinds of custom functions. Simple scalar functions are the first type. These take some set of parameters and return a single value. An example would be the built-in function abs(), which takes a single numeric parameter and returns the absolute value of that number.

The second type of function is an aggregate function, or aggregator. These are SQL functions, such as sum() or avg(), that are used in conjunction with GROUP BY clauses to summarize or otherwise aggregate a series of values together into a final result.

The last type of custom function is a collation. Collations are used to define a custom sort orders for an index or an ORDER BY clause. Conceptually, collation functions are quite simple: they take two text values and return a greater than, less than, or equal status. In practice, collations can become quite complex, especially when dealing with natural language strings.

This chapter will also take a look at how to package up a set of custom features into an SQLite extension. Extensions are a standard way to package custom functions, aggregations, collations, virtual tables (see Chapter 10), or any other custom feature. Extensions are a handy and standardized way to bundle up sets of related functions or customizations ...