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 ;