SQLite Forum

User-defined functions in the CLI?

User-defined functions in the CLI?

(1.1) Originally by anonymous with edits by Richard Hipp (drh) on 2021-09-29 00:08:29 from 1.0 [link] [source]

Is it possible to use sqlite3_create_Function to create user-defined function in the CLI?

(2) By Larry Brasfield (larrybr) on 2021-09-27 23:00:52 in reply to 1.0 [link] [source]

Not now. In general, the library's C API is not exposed at the CLI's user interface. The CLI, in part, is a shim between {people and shell scripts) and that C API.

(3) By anonymous on 2021-09-28 03:09:09 in reply to 2 [source]

Really? The docs say:

From the command-line shell, extensions can be loaded using the ".load" dot-command. For example:

.load ./YourCode

This worked fine when I tested it a moment ago.

(4.1) By Harald Hanche-Olsen (hanche) on 2021-09-28 16:16:48 edited from 4.0 in reply to 3 [link] [source]

That just loads SQL code. And SQLite dot commands, I think. But it can't do what you can't do from the command line.

Edit: Oops, that was mistaken. Sorry.

(6) By anonymous on 2021-09-28 15:06:36 in reply to 4.0 [link] [source]

The command for running SQL code from a file is .read, not .load.

(7) By Ryan Smith (cuz) on 2021-09-28 15:19:47 in reply to 4.0 [link] [source]

I believe that actually loads programmable extensions, not SQL.

It does however differ from what the OP wanted, actually I'm not very sure what the OP wants. Loading extensions are very possible (not just in the CLI, but any SQLite engine instance where allowed), alternatively, compiling added code into the CLI is possible too.

The original request reads as if the OP wished to access the sqlite_create_function API somehow from an already-compiled CLI but not via a loaded library, which is very much not possible currently, as Larry explained.

I recall someone here posting an extension which lets you add sqlite UDF functions, taking a Name and an SQL statement, and any time after that you use that Name inside a query, the SQL code will execute.

Something like:

  SELECT MAKEFUNC('Twice', 'SELECT ?1 * 2');

  SELECT Twice(3);
--> 6

I thought it quite clever at the time, but had no cause to use it yet and unfortunately do not recall the name, but someone else here may.

(8.1) Originally by Keith Medcalf (kmedcalf) with edits by Stephan Beal (stephan) on 2021-09-29 02:21:24 from 8.0 in reply to 7 [link] [source]

which is very much not possible currently

That is actually untrue. You can export symbols from an executable (such as the CLI). You do not actually need to name the "shared object" with a .dll or .so extension if you do not want to do so. You can call it "ThumpingGiraff.Eaters" and STILL have it be the CLI application and STILL export symbols for external use.

I suspect that what the OP is asking is whether the CLI has a "CREATE FUNCTION" command and a compiler. The answer is no. However, the CLI does "contain" sqlite3_create_function and calls it quite fine for defining functions such as date(), time(), datetime(), strftime(), sin(), cos(), etc, etc, as specified in the documentation.

Although you can type "sqlite3_create_function" at the CLI user interface, it will have no different effect than if you type that phrase into a word processor.

(9) By anonymous on 2021-09-29 13:05:24 in reply to 7 [link] [source]

Maybe that one? https://github.com/0x09/sqlite-statement-vtab/

Mentioned here and here.

Can be (ab)used as 'function' by wrapping the table-valued-function as scalar subquery ...

sqlite> .load ./statement_vtab

sqlite> CREATE VIRTUAL TABLE twice USING statement((SELECT 2 * :x as y));
sqlite> select (select y from twice(9)) as result;

or - arguably less clumsy - with a join

sqlite> create table t1(v);
sqlite> insert into t1 values(4),(7),(99);

sqlite> select t1.v, twice.y as r from t1 left join twice(t1.v);

(5) By anonymous on 2021-09-28 11:36:05 in reply to 1.0 [link] [source]

(Other 'anonymous' than OP)

No, not directly. But using an extension you can create functions using Lua in the CLI.

Check out Lua for SQLite