Hi. https://www.sqlite.org/pragma.html#pragma_function_list does not describe
the format of the table, or the meaning of the columns. Is it documented elsewhere?

In my particular SQLite GUI tool of choice, I see 189 s functions, 26 w ones, and 3 a ones.
I guess s is for scalar, w for window, and a for aggregate, right? But what distinguishes a from w?
Which API is used to register it I guess?

What about the flags column? I see large numbers like 2099200 and 2097152.
Is https://www.sqlite.org/c3ref/c_deterministic.html an exhaustive list of these flags?

Also, how to know when this pragma was added and/or updated?
I see different columns in different software (or none at all).

In my particular SQLite GUI tool of choice, I see 189 s functions, 26 w ones, and 3 a ones. I guess s is for scalar, w for window, and a for aggregate, right? But what distinguishes a from w? Which API is used to register it I guess?

Yes and Yes.

The difference between an aggregate function and a window function is that an aggregate function can be used only in aggregate expressions, whereas a window function can be used in either aggregate expressions or window expressions.

An aggregate function only aggregates forward. A window function knows how to remove prior values from the aggregate.

see https://sqlite.org/changes.html

function_list was made available by default in 3.30.0 and the output format was update in 3.31.0

The flags column also includes the "preferred text encoding"

Thanks.

Yeah, I missed the https://www.sqlite.org/c3ref/c_any.html flags for preferred text encoding.
Although I suspect that's what the encoding column might correspond to.
Especially since those encoding values are not bit flags, but 1, 2, 3, 4, 5.

But I guess my main point remain, is that more official doc would be great.
This thread won't be easily found by others pondering the same questions.

Here's a query with flags as individual boolean columns:

select name
     , case type
       when 's' then 'scalar'
       when 'w' then 'aggregate'
       when 'a' then 'window'
       end as "type"
     , narg as "arg-count"
     , enc as "preferred-text-encoding"
     , (flags & 0x000000800)!=0 as "deterministic"
     , (flags & 0x000080000)!=0 as "directonly"
     , (flags & 0x000100000)!=0 as "subtype"
     , (flags & 0x000200000)!=0 as "innocuous"
  from pragma_function_list 

And here's another query with flags as a single text column:

select name
     , case type
       when 's' then 'scalar'
       when 'w' then 'aggregate'
       when 'a' then 'window'
       end as "type"
     , narg as "arg-count"
     , enc as "preferred-text-encoding"
     , ( select group_concat(f, ' | ') from
         ( select case when (flags & 0x000000800)!=0 then 'deterministic' end as f
           union all
           select case when (flags & 0x000080000)!=0 then 'directonly' end as f
           union all
           select case when (flags & 0x000100000)!=0 then 'subtype' end as f
           union all
           select case when (flags & 0x000200000)!=0 then 'innocuous' end as f
         )       
       ) as "flags"
  from pragma_function_list

(I'm sure there's a better way to write that last query)

???

  when 's' then 'scalar'
  when 'w' then 'aggregate'
  when 'a' then 'window'

Should that not be:

  when 's' then 'scalar'
  when 'a' then 'aggregate'
  when 'w' then 'window'