Custom SQL Functions

Brief

SQLiteStudio allows user to create additional SQL functions to be used in SQL queries. You can create for example function "test123()" which will return a random string for you and you will be able to type:

INSERT INTO myTable VALUES (test123());

Custom SQL functions in SQLiteStudio are very useful if your application uses some custom SQL functions for itself and you want to be able to emulate your application's environment in SQLiteStudio, so you can execute similar queries on the database.

Other use case is when you want to simplify some database maintanance tasks. Creating function which does some more complicated operations using some simple input values can be treated as a procedure by SQLite, because you can execute it with:

SELECT functionName(input_arguments);

Defining your own functions

To create/edit/delete SQL functions, open Custom SQL Functions editor from main toolbar, main menu, or by configured hotkey (Alt+1 by default or Option+1 on macOS).

In the functions editor you need to define few of things:

1. Function name. It needs to be unique.
2. Is your function going to be Scalar or Aggregate?
   1. Scalar functions are those applied to every single data row separately. Example of such function is "length()".
   2. Aggregate functions are used together with GROUP BY clause and they are applied to the entire result set. Example of such function is "min()".
3. Implementation language - a programming language that you will use to define what the function actually do.
4. Input arguments - list of named input arguments. Currently they're just symbolic names used by a SQL syntax assistant to suggest what are expected input arguments. Nothing more.
5. Databases - for which databases the function should be registered by SQLiteStudio.
6. For Scalar function - a single implementation field is required. It's the code in chosen programing language that does the actual function's job. The return value of this code becomes the result of the function call.
7. For Aggregate function there are 3 implementation fields:
   1. Initialization code - this code is executed once, at the begining of grouped query results. You can use it to initialize some variables to "0" values, or something like that. This field is optional.
   2. Per step code - this code is executed for each data row from the results. Each iteration uses the same context, which means all variables are shared across all steps.
   3. Final step code - it's executed after last "per step" code. You can calculate the final result and releasy any resources you acquired. This code must return the result of the aggregate function.
8. For Window function there are 4 implementation fields:
   1. Initialization code - same as in the Aggregate function.
   2. Per step code - same as in the Aggregate function.
   3. Inverse step code - this code is executed for each data row from the results which should should be subtracted from the accumulated values. This function is called when particular row of data falls out of the current aggregation window. Each iteration uses the same context, which means all variables are shared across all steps.
   4. Final step code - same as in the Aggregate function.

Function arguments usage in the code

Arguments passed to function are available in the code as a variables, whose names depend on certain implementation language you chose. For Tcl arguments are available in "$argv". For JavaScript they're in "arguments" array. To learn how to handle input arguments in certain language, read about the specific plugin that provides support for that language. Official plugins are described at Scripting languages plugins page).

Final remarks

Custom SQL functions created in SQLiteStudio exist only in context of SQLiteStudio. Functions are not persisted in database, it's just not how SQLite databases work. If you want to use some function in your own application, you have to register that function in SQLite from your application.

Custom scripts repository

There is a collection of useful scripts maintained here on the wiki: https://github.com/pawelsalawa/sqlitestudio/wiki/Scripts_repository