SQLite Forum

Save and restore a PRAGMA state

Save and restore a PRAGMA state

(1) By Richard PArkins (rparkins) on 2020-12-16 11:08:58 [source]

Is it possible to save and restore the state of a PRAGMA in an SQL script?

Obviously one can do it in code, but a pragma_value has to be a literal, which seems to prevent me from doing it in a script.

The only solution that I can think of is to create a user-defined function which executes a PRAGMA with a value passed to the function, but it seems unfortunate that this is not a built-in.

Should you say that this is no different from doing it in code, the reason why it is different is that I have a program that presents a UI allowing the user to create, edit, and execute SQL statements and scripts which can be saved in or read from files: the UI can also create some simple types of statement for the user and directly edit or search a table.

My user might want to create a script which only runs correctly if some PRAGMA has a particular value (for example PRAGMA legacy_alter_table may need to be true), but does not permanently change the value of the PRAGMA.

Providing a built-in function in my program would enable users to do this without having to write code. The SQL script to do this is a bit tricky, but looks something like this, where set_pragma is the user-defined function:-

CREATE TABLE temp.save_pragma_legacy_table AS
    SELECT * FROM pragma_legacy_alter_table() ;

body of script

SELECT * FROM set_pragma('pragma_legacy_table', 
    SELECT * FROM temp.save_pragma_legacy_table) ;
DROP TABLE temp.save_pragma_legacy_table ;