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 is the name of the function
• builtin shows if the function is built-in to SQLite or was added by an extension
• type tells you what kind of function it is - the options are s for scalar, w for window and a for aggregate.
• enc I'm not sure about. It's always utf8 from what I've seen.
• narg is the arity of the function: the number of arguments it takes. -1 means an unlimited number of arguments.
• flags is more complicated, see below

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"]

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:

select
  name,
  narg,
  flags,
  type,
  flags & 0x000000800 != 0 as deterministic,
  flags & 0x000080000 != 0 as directonly,
  flags & 0x000100000 != 0 as subtype,
  flags & 0x000200000 != 0 as innocuous
from
  pragma_function_list()

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

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

• deterministic means that the function is guaranteed to return the same result for the same input, which is a useful hint that the SQL query executor can reuse those values without re-calculating them every time.
• directonly means that the function can only be called from "top-level SQL", not from triggers, views or things like generated columns or check constraints.
• innocuous means that the function is "unlikely to cause problems even if misused" - crucially, it means the function has no side effects.
• subtype looks like it's a window function concern - though I haven't yet found an example of a function that uses it. The documentation says "Specifying this flag makes no difference for scalar or aggregate user functions. However, if it is not specified for a user-defined window function, then any sub-types belonging to arguments passed to the window function may be discarded before the window function is called (i.e. sqlite3_value_subtype() will always return 0)." I don't understand the implications of this at all.

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:

https://lite.datasette.io/?sql=https://gist.github.com/simonw/c6fa2d722e7599f3874f27cb19fc8fe4#/data/functions?_facet=deterministic&_facet=subtype&_facet=innocuous&_facet=directonly

Related

• sqlite Seeing which functions are unique to a specific SQLite / Datasette instance - 2022-08-23
• sqlite List all columns in a SQLite database - 2020-05-06
• sqlite The SQLite now argument is stable within the same query - 2023-02-05
• sqlite A one-liner to output details of the current Python's SQLite - 2023-08-19
• sqlite SQLite aggregate filter clauses - 2021-08-04
• sqlite json_extract() path syntax in SQLite - 2022-01-18
• sqlite Running Steampipe extensions in sqlite-utils and Datasette - 2023-12-20
• sqlite Calculating the size of a SQLite database file using SQL - 2023-08-21
• sqlite Trying out SQLite extensions on macOS - 2022-08-03
• sqlite Counting SQLite virtual machine operations - 2022-03-20

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