What is the SQL statement that will return the list of PRAGMAs in the version of SQLite in use?

see the documentation

Thanks; I had looked but not closely enough, obviously.

Actually, I'm interested in the list of PRAGMAs which do NOT have their respective default values(s). Can I get this list using SQL?

Can I get this list using SQL?

Not just like that. You'll have to write a script/program that executes the pragmas you are interested in against your database, and compare the outcome with the outcome of the same script run against a "default database".

Something like:

for p in $(cat relevant_pragmas) ; do printf ".print %s::\nPRAGMA %s;\n" "$p" "$p" ; done | sqlite3 default.sqlite >pragma0.txt
for p in $(cat relevant_pragmas) ; do printf ".print %s::\nPRAGMA %s;\n" "$p" "$p" ; done | sqlite3  yourdb.sqlite >pragma1.txt
diff -u pragma0.txt pragma1.txt

-- 
Regards,
Kees

It would be nice if there was an eponymous virtual table that would let you do the pragma operation as part of an SQL select statement, e.g.:

SQL select name,type from sqlite_pragma('function_list') where builtin=0;

What does builtin mean?

Available in the DLL but needs to be enabled?

OR

Can be available subject to loading an extension?

OR

Something else?

You could have your application retrieve the names of the pragma's, remove the ones you don't want to run from the list, then execute the pragma and collect the results:

pragma_list = [[row[0], None] for row in db.cursor().execute('pragma pragma_list')]
for e in pragma_list:
   if not e[0] in ['foreign_key_check', 'integrity_check', 'quick_check' ]:
      try:
         e[1] = db.cursor().execute('pragma ' + e[0]).fetchone()[0]
      except:
         pass

after which the list pragma_list will contain a list of all pragma's together with the result obtained from running that pragma.

If builtin is true (not null and non-zero) that means that the function is builtin (is part of SQLite3). If builtin is false (zero or null) that means that the function was not "builtin" and was added by a user by some method (even though to a lay observer it appears to be part of the structure).

Note that the concept of "builtin" is the same as the concept of "builtin" when buying a house. If the bathroom is "builtin" that means that it came with the house. If you add another bathroom, then that was not "builtin" when you bought the house, even though it may now form an integral part of the structure.

Perhaps a more apropos description would be "written and maintained by the authors of SQLite3" although there is no reason why a "user" could not add their own functions that report as being "builtin" if they wanted to do so.

Using version 3.35.1 out of the box (i.e. neither loading any extension nor defining any functions), consider this session:

SQLite version 3.35.1 2021-03-15 16:53:57
Enter ".help" for usage hints.
sqlite> select count(*) from pragma_function_list where builtin != 1;
count(*)
--------
84
sqlite> select * from pragma_function_list where name like 'geo%';
name                    builtin  type  enc   narg  flags
----------------------  -------  ----  ----  ----  -------
geopoly_regular         0        s     utf8  4     2099200
geopoly_contains_point  0        s     utf8  3     2099200
geopoly_debug           0        s     utf8  1     524288
geopoly_bbox            0        s     utf8  1     2099200
geopoly_overlap         0        s     utf8  2     2099200
geopoly_xform           0        s     utf8  7     2099200
geopoly_svg             0        s     utf8  -1    2099200
geopoly_area            0        s     utf8  1     2099200
geopoly_blob            0        s     utf8  1     2099200
geopoly_within          0        s     utf8  2     2099200
geopoly_json            0        s     utf8  1     2099200
geopoly_ccw             0        s     utf8  1     2099200
geopoly_group_bbox      0        a     utf8  1     2099200
sqlite>

So, the geo* functions are NOT written and maintained by the authors of SQLite3 (as you so succinctly described) and I did not add/define them.

1. Where do they come from?
2. Can they be used outright?
3. Can they be used subject to some other configuration oe pre-condition?

1. it's an SQLite extension, from DRH himself
2. Not sure what you mean. Of course they can be used.
3. Many things in SQLite can be turned ON/OFF by a #define, including Geopoly.

So your Out-of-the-Box is simply that whoever built the SQLite lib or CLI you used,
included the Geopoly extension in the build, that's it.

Whether it should be considered built-in or not, well, who cares?
It does come from the SQLite3 authors (the main one in fact), but since
it's an extension, that's likely why it's not considered built-in I suspect. FWIW.

Well they are, but they are not builtin functions. They are part of the geopoly extension which happens to be maintained by the authors of SQLite3 and also happens to be included in the CLI.

Technically, the "builtin" flag represents whether or not the function is registered in the "builtin" table or is in the "user defined" table.

They can be used for working with geopoly data.