SQLite pragma_function_list()

The SQLite pragma_function_list() table-valued function returns a list of functions that have been registered with SQLite, including functions that were added by extensions.

Here's how to interpret its output.

First, an example:

select * from pragma_function_list() order by random()

I'm using order by random() here just to mix things up a bit. Here are the first five results:

name builtin type enc narg flags
likely 1 s utf8 1 2099200
json_extract 1 s utf8 -1 2048
ceiling 1 s utf8 1 2099200
ulid_bytes 0 s utf8 1 0
row_number 1 w utf8 0 2097152

In this example ulid_bytes is a function added because I loaded the sqlite-ulid extension.

I hadn't realized that json_extract() could take unlimited arguments - I thought it just took a value and a path. From the above I learned that this works:

select json_extract('{"foo": "bar", "bar": "baz"}', '$.foo', '$.bar') as bits

This returns a JSON array corresponding to each argument past the first one:

["bar", "baz"]

Interpreting those flags

I found this forum post by D. Richard Hipp explaining how the flags work:

The flags column is an internal implementation detail and is subject to change. But a few of the bits are fixed. From sqlite3.h:

#define SQLITE_DETERMINISTIC    0x000000800
#define SQLITE_DIRECTONLY       0x000080000
#define SQLITE_SUBTYPE          0x000100000
#define SQLITE_INNOCUOUS        0x000200000

(I couldn't find these lines in the source code when I looked for them just now.)

But going by that forum post, the following query helps me understand what those flags mean:

  flags & 0x000000800 != 0 as deterministic,
  flags & 0x000080000 != 0 as directonly,
  flags & 0x000100000 != 0 as subtype,
  flags & 0x000200000 != 0 as innocuous

Run against a Datasette instance returns the following data (truncated to the highlights):

name narg flags type deterministic directonly subtype innocuous
group_concat 1 2097152 w 0 0 0 1
group_concat 2 2097152 w 0 0 0 1
julianday -1 2099200 s 1 0 0 1
load_extension 1 524288 s 0 1 0 0
load_extension 2 524288 s 0 1 0 0
fts3_tokenizer 2 524288 s 0 1 0 0
fts3_tokenizer 1 524288 s 0 1 0 0

Here's the official SQLite documentation for those function flags.

As a SQL view to support facets

I decided it would be useful to be able to browse these using facets. I came up with the following SQL view:

create view functions as
select *,
  case when flags & 0x800 != 0 then '1' else '0' end as 'deterministic',
  case when flags & 0x000100000 != 0 then '1' else '0' end as 'subtype',
  case when flags & 0x000200000 != 0 then '1' else '0' end as 'innocuous',
  case when flags & 0x000080000 != 0 then '1' else '0' end as 'directonly'
from pragma_function_list();

The case statements are necessary because Datasette doesn't currently facet views correctly if they return integer values.

Since I saved this to a Gist I can open it in Datasette Lite like this:


Functions shown in Datasette Lite with facets for deterministic, subtype, innocuous and directonly

Created 2023-01-27T19:06:08-08:00, updated 2024-03-20T16:19:11-07:00 · History · Edit